NetXMS Support Forum

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?

Hi!

Did you restart netxmsd in between? If yes, check server's log file for possible SQL errors - maybe your changes was not saved in database. Check audit log to see when last changes to template objects was done.

Best regards,
Victor

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.

I have a bunch of these errors:

[13-Oct-2014 16:48:07.811] [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

Hi,

it happens when server tries to save DCI configuration to database. What DCIs did you edit recently?

Best regards,
Victor

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]

Hi,

can you please send definition of table "items" as it is created in your database?

Best regards,
Victor

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

Thanks for your help.  I'll download the new version tomorrow, then.