Twice now I've had the DCI's in multiple templates disappear. The most recent occurrence was this past weekend. I'm the only one with management access so I know it's not something I've done. It's frustrating to have to re-create these DCI's. This time I'm going to export them to ease re-creating them, but the bigger issue is why are they disappearing? What do I need to do to debug this issue?
I'll have to check with our Sys Admins. This is running on a MSSQL cluster and occasionally the database gets moved to another server by the cluster service. I wonder if that factors in. BTW: I'm running 1.2.16.
The long answer is that I am rebuilding our NetXMS monitoring server. The previous one was in service for two years. With all of the new features that have been added, in addition to more experience with NetXMS, I've found better, cleaner ways to do the same monitoring. That being said, I had created 4 templates, each with 6-12 SNMP-based DCI's and a couple of them had thresholds set. It appears that none of the DCI's are being saved to the database.
It looks like a possible issue with the DCItem::saveToDB function. Here is the debug output from one attempt at binding a template to a node. It looks like the "INSERT INTO items" query is off by one parameter since 15943 is the template id and not the node id:
[14-Oct-2014 16:49:33.119] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,8168)" [1 ms]
[14-Oct-2014 16:49:33.119] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,10760)" [0 ms]
[14-Oct-2014 16:49:33.120] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,10909)" [1 ms]
[14-Oct-2014 16:49:33.120] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,11082)" [0 ms]
[14-Oct-2014 16:49:33.121] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,11255)" [1 ms]
[14-Oct-2014 16:49:33.121] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,11384)" [0 ms]
[14-Oct-2014 16:49:33.122] [DEBUG] Successful sync query: "INSERT INTO dct_node_map (template_id,node_id) VALUES (15943,11765)" [1 ms]
[14-Oct-2014 16:49:33.122] [DEBUG] Successful sync query: "DELETE FROM acl WHERE object_id=15943" [0 ms]
[14-Oct-2014 16:49:33.122] [DEBUG] {0x7f9e68018130} Successful prepare: "SELECT item_id FROM items WHERE item_id=?" [0 ms]
[14-Oct-2014 16:49:33.122] [DEBUG] {0x7f9e68018130} bind at pos 1: "282"
[14-Oct-2014 16:49:33.122] [DEBUG] Successful prepared sync query: "SELECT item_id FROM items WHERE item_id=?" [0 ms]
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} Successful prepare: "INSERT INTO items (node_id,template_id,name,source,datatype,polling_interval,retention_time,status,delta_calculation,transformation,description,instance,template_item_id,flags,resource_id,proxy_node,base_units,unit_multiplier,custom_units_name,perftab_settings,system_tag,snmp_port,snmp_raw_value_type,instd_method,instd_data,instd_filter,samples,comments,item_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" [0 ms]
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 1: "15943"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 2: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 3: "Dummy"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 4: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 5: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 6: "300"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 7: "5"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 8: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 9: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 10: "use US::Utilities;
use US::CiscoCommon;
return ciscoEnvMonTemperatureStatusValue($node->name);"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 11: "Temperature"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 12: ""
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 13: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 14: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 15: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 16: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 17: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 18: "1"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 19: ""
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 20: "<config>
<enabled>false</enabled>
<autoScale>true</autoScale>
<logScaleEnabled>false</logScaleEnabled>
<minYScaleValue>1</minYScaleValue>
<maxYScaleValue>100</maxYScaleValue>
<timeRange>1</timeRange>
<timeUnits>1</timeUnits>
<type>0</type>
<color>49152</color>
<title></title>
<name></name>
<showThresholds>false</showThresholds>
<parentDciId>0</parentDciId>
<order>100</order>
</config>"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 21: ""
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 22: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 23: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 24: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 25: ""
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 26: ""
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 27: "0"
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 28: ""
[14-Oct-2014 16:49:33.123] [DEBUG] {0x7f9e6804a350} bind at pos 29: "282"
[14-Oct-2014 16:49:33.124] [DEBUG] Failed prepared sync query: "INSERT INTO items (node_id,template_id,name,source,datatype,polling_interval,retention_time,status,delta_calculation,transformation,description,instance,template_item_id,flags,resource_id,proxy_node,base_units,unit_multiplier,custom_units_name,perftab_settings,system_tag,snmp_port,snmp_raw_value_type,instd_method,instd_data,instd_filter,samples,comments,item_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" [1 ms]
[14-Oct-2014 16:49:33.124] [ERROR] SQL query failed (Query = "INSERT INTO items (node_id,template_id,name,source,datatype,polling_interval,retention_time,status,delta_calculation,transformation,description,instance,template_item_id,flags,resource_id,proxy_node,base_units,unit_multiplier,custom_units_name,perftab_settings,system_tag,snmp_port,snmp_raw_value_type,instd_method,instd_data,instd_filter,samples,comments,item_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"): Operand type clash: int is incompatible with text
[14-Oct-2014 16:49:33.125] [DEBUG] Event::expandText(event=0x7f9e6804f620 sourceObject=100 template='Database query failed (Query: %1; Error: %2)' alarmMsg='(null)' alarmKey='(null)')
[14-Oct-2014 16:49:33.125] [DEBUG] CorrelateEvent: event SYS_DB_QUERY_FAILED id 2625549 source ourserver.ourdomain.com [100]
Hopefully this is what you need.
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
netxms dbo items item_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56
netxms dbo items node_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 2 NO 56
netxms dbo items template_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 3 NO 56
netxms dbo items template_item_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 4 NO 56
netxms dbo items name 12 varchar 1023 1023 NULL NULL 1 NULL NULL 12 NULL 1023 5 YES 39
netxms dbo items description 12 varchar 255 255 NULL NULL 1 NULL NULL 12 NULL 255 6 YES 39
netxms dbo items flags 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 7 NO 56
netxms dbo items source 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 8 NO 56
netxms dbo items snmp_port 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 9 NO 56
netxms dbo items datatype 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 10 NO 56
netxms dbo items polling_interval 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 11 NO 56
netxms dbo items retention_time 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 12 NO 56
netxms dbo items status 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 13 NO 56
netxms dbo items snmp_raw_value_type 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 14 NO 56
netxms dbo items delta_calculation 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 15 NO 56
netxms dbo items transformation -1 text 2147483647 2147483647 NULL NULL 0 NULL NULL -1 NULL 2147483647 16 NO 35
netxms dbo items instance 12 varchar 255 255 NULL NULL 1 NULL NULL 12 NULL 255 17 YES 39
netxms dbo items system_tag 12 varchar 255 255 NULL NULL 1 NULL NULL 12 NULL 255 18 YES 39
netxms dbo items resource_id 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 19 NO 56
netxms dbo items proxy_node 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 20 NO 56
netxms dbo items base_units 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 21 NO 56
netxms dbo items unit_multiplier 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 22 NO 56
netxms dbo items custom_units_name 12 varchar 63 63 NULL NULL 1 NULL NULL 12 NULL 63 23 YES 39
netxms dbo items perftab_settings -1 text 2147483647 2147483647 NULL NULL 1 NULL NULL -1 NULL 2147483647 24 YES 35
netxms dbo items instd_method 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 25 NO 56
netxms dbo items instd_data 12 varchar 255 255 NULL NULL 1 NULL NULL 12 NULL 255 26 YES 39
netxms dbo items instd_filter -1 text 2147483647 2147483647 NULL NULL 1 NULL NULL -1 NULL 2147483647 27 YES 35
netxms dbo items samples 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 28 NO 56
netxms dbo items comments -1 text 2147483647 2147483647 NULL NULL 1 NULL NULL -1 NULL 2147483647 29 YES 35
Yes, thank you. There was a bug (related to MS SQL only), I've fixed it. Release 1.2.17 will contain this fix (we plan to release it tomorrow).
Best regards,
Victor