Data Source Statistics SQL error

[eschoeller]

I'm getting this error in my logs when I enable Data Source Statistics collection

2017/10/16 00:24:42 - DBCALL ERROR: A DB Exec Failed!, Error:1064, SQL:'INSERT INTO data_source_stats_hourly_cache (local_data_id, rrd_name, time, `value`) VALUES (30775, 'infeedPhaseCurrent1', '2017-10-16 00:24:30', 311), (30775, 'infeedPhaseCurrent2', '2017-10-16 00:24:30', 461), (30775, 'infeedPhaseCurrent3', '2017-10-16 00:24:30', 483), (30776, 'infeedPhaseVoltage3', '2017-10-16 00:24:30', 2393), (30776, 'infeedPhaseVoltage2', '2017-10-16 00:24:30', 2401), (30776, 'infeedPhaseVoltage1', '2017-10-16 00:24:30', 2412), (30777, 'infeedPhaseVoltage2', '2017-10-16 00:24:30', 2399), (30777, 'infeedPhaseVoltage1', '2017-10-16 00:24:30', 2411), (30777, 'infeedPhaseVoltage3', '2017-10-16 00:24:30', 2392), (30778, 'infeedFactor2', '2017-10-16 00:24:30', 92), (30778, 'infeedFactor1', '2017-10-16 00:24:30', 94), (30778, 'infeedFactor3', '2017-10-16 00:24:30', 94), (30779, 'infeedFactor3', '2017-10-16 00:24:30', 95), (30779, 'infeedFactor2', '2017-10-16 00:24:30', 97), (30779, 'infeedFactor1', '2017-10-16 00:24:30', 94), (30780, 'humidity', '2017-10-16 00:24:30', 41), (30780, 'temperature', '2017-10-16 00:24:30', 230), (30835, 'total_it_watts_ups', '2017-10-16 00:24:38', 47045.5523656), (30835, 'average_latency', '2017-10-16 00:24:38', 39), (30835, 'data_hit_rate', '2017-10-16 00:24:38', 100), (30836, 'total_it_watts_pdu', '2017-10-16 00:24:38', 121734.282716667), (30836, 'average_latency', '2017-10-16 00:24:38', 99), (30836, 'data_hit_rate', '2017-10-16 00:24:38', 100), (30958, 'apparentPower', '2017-10-16 00:24:34', 1017), (30959, 'energy', '2017-10-16 00:24:34', 29000), (30963, 'vaUsed', '2017-10-16 00:24:34', 101), (30964, 'vaUsed', '2017-10-16 00:24:34', 94), (30965, 'powerFactor', '2017-10-16 00:24:34', 84), (30966, 'powerFactor', '2017-10-16 00:24:34', 88), (30967, 'infeedLoad2', '2017-10-16 00:24:34', 315), (30967, 'infeedLoad1', '2017-10-16 00:24:34', 345), (30967, 'infeedLoad3', '2017-10-16 00:24:34', 239), (30968, 'infeedLoad3', '2017-10-16 00:24:34', 189), (30968, 'infeedLoad1', '2017-10-16 00:24:34', 190), (30968, 'infeedLoad2', '2017-10-16 00:24:34', 341), (31043, 'udpInErrors', '2017-10-16 00:24:36', NULL), (31043, 'udpNoPorts', '2017-10-16 00:24:36', NULL), (31044, 'tempProbeReading', '2017-10-16 00:24:36', 24), (31045, 'voltageProbeReading', '2017-10-16 00:24:36', 210000), (31047, 'voltageProbeReading', '2017-10-16 00:24:36', 208000), (31048, 'coolingDeviceReadin', '2017-10-16 00:24:36', 3600), (31049, 'coolingDeviceReadin', '2017-10-16 00:24:36', 3600), (31050, 'coolingDeviceReadin', '2017-10-16 00:24:36', 3600), (31051, 'coolingDeviceReadin', '2017-10-16 00:24:36', 3600), (31052, 'coolingDeviceReadin', '2017-10-16 00:24:36', 3600), (31054, 'amperageReading', '2017-10-16 00:24:36', 4), (31055, 'amperageReading', '2017-10-16 00:24:36', 2), (31057, 'cpu_nice', '2017-10-16 00:24:36', NULL), (31058, 'cpu_system', '2017-10-16 00:24:36', 35.266666666667), (31059, 'cpu_user', '2017-10-16 00:24:36', 28.95), (31060, 'load_1min', '2017-10-16 00:24:36', 0.07), (31062, 'load_15min', '2017-10-16 00:24:36', 0.00), (31063, 'load_5min', '2017-10-16 00:24:36', 0.03), (31065, 'mem_cache', '2017-10-16 00:24:36', 5821908), (31193, 'inst_volts_an', '2017-10-16 00:24:39', 287.947), (31193, 'inst_volts_bn', '2017-10-16 00:24:39', 286.890), (31193, 'inst_volts_cn', '2017-10-16 00:24:39', 286.636), (31193, 'inst_volts_ab', '2017-10-16 00:24:39', 497.814), (31193, 'inst_volts_bc', '2017-10-16 00:24:39', 496.632), (31193, 'inst_volts_ca', '2017-10-16 00:24:39', 497.578), (31193, 'inst_freq', '2017-10-16 00:24:39', 60.01), (31193, 'inst_current_a', '2017-10-16 00:24:39', 7.441), (31193, 'inst_current_b', '2017-10-16 00:24:39', 6.265), (31193, 'inst_current_c', '2017-10-16 00:24:39', 5.245), (31193, 'inst_current_n', '2017-10-16 00:24:39', 1.895), (31193, 'inst_watts', '2017-10-16 00:24:39', 3974), (31193, 'inst_vars', '2017-10-16 00:24:39', -3707), (31193, 'inst_va', '2017-10-16 00:24:39', 5447), (31193, 'inst_power_factor', '2017-10-16 00:24:39', 0.729), (31202, 'wathrec', '2017-10-16 00:24:39', 85053580), (31202, 'wathdel', '2017-10-16 00:24:39', 0), (31202, 'wathnet', '2017-10-16 00:24:39', 85053580), (31202, 'wathtot', '2017-10-16 00:24:39', 85053600), (31202, 'varhpos', '2017-10-16 00:24:39', 19300), (31202, 'varhneg', '2017-10-16 00:24:39', -84045780), (31202, 'varhnet', '2017-10-16 00:24:39', -), (31213, 'inst_volts_an', '2017-10-16 00:24:39', 287.990), (31213, 'inst_volts_bn', '2017-10-16 00:24:39', 286.970), (31213, 'inst_volts_cn', '2017-10-16 00:24:39', 286.631), (31213, 'inst_volts_ab', '2017-10-16 00:24:39', 497.922), (31213, 'inst_volts_bc', '2017-10-16 00:24:39', 496.699), (31213, 'inst_volts_ca', '2017-10-16 00:24:39', 497.616), (31213, 'inst_freq', '2017-10-16 00:24:39', 60.01), (31213, 'inst_current_a', '2017-10-16 00:24:39', 10.651), (31213, 'inst_current_b', '2017-10-16 00:24:39', 8.443), (31213, 'inst_current_c', '2017-10-16 00:24:39', 7.164), (31213, 'inst_current_n', '2017-10-16 00:24:39', 5.650), (31213, 'inst_watts', '2017-10-16 00:24:39', 6329), (31213, 'inst_vars', '2017-10-16 00:24:39', -1476.953), (31213, 'inst_va', '2017-10-16 00:24:39', 7500), (31213, 'inst_power_factor', '2017-10-16 00:24:39', 0.843), (31222, 'wathrec', '2017-10-16 00:24:39', 122936110), (31222, 'wathdel', '2017-10-16 00:24:39', -3240), (31222, 'wathnet', '2017-10-16 00:24:39', 122932880), (31222, 'wathtot', '2017-10-16 00:24:39', 122939360), (31222, 'varhpos', '2017-10-16 00:24:39', 451320), (31222, 'varhneg', '2017-10-16 00:24:39', -73881270) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)'

2017/10/16 00:24:42 - CMDPHP ERROR: A DB Exec Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), (31213, 'inst_volts_an', '2017-10-16 00:24:39', 287.990), (31213, 'inst_volts' at line 1

2017/10/16 00:24:42 - CMDPHP SQL Backtrace: (/poller.php: 539 process_poller_output)(/lib/poller.php: 508 dsstats_poller_output)(/lib/dsstats.php: 681 db_execute)(/lib/database.php: 135 db_execute_prepared)(/lib/database.php: 208 cacti_debug_backtrace)

This is the part that trips it up:

mysql> INSERT INTO data_source_stats_hourly_last (local_data_id, rrd_name, `value`, calculated) VALUES (31202, 'varhnet', '2017-10-16 00:24:39', -) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`), `calculated`=VALUES(`calculated`);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ON DUPLICATE KEY UPDATE `value`=VALUES(`value`), `calculated`=VALUES(`calculat' at line 1

I am not sure why this particular data source is returning '-' values but it's not working with the INSERT very well. The '-' should probably be NULL.

[cigamit]

Yup, searching the code in just a few minutes.

[cigamit]

I've committed a patch. Let me know if it works. The only think I can think of is that this is a holdover from the earlier versions of dsstats.

[cigamit]

No feedback. I'm sure it's working though.

[eschoeller]

Sorry I just had a chance to upgrade. I'm still seeing these issues:

2017/11/12 23:19:02 - ERROR PHP NOTICE: Undefined offset: 1 in file: /cacti/cacti-1.1.27-prod/lib/dsstats.php  on line: 259
2017/11/12 23:19:02 - CMDPHP PHP ERROR NOTICE Backtrace: (/poller_dsstats.php: 191 dsstats_get_and_store_ds_avgpeak_values)(/lib/dsstats.php: 64 dsstats_obtain_data_source_avgpeak_values)(/lib/dsstats.php: 259 CactiErrorHandler)(/lib/functions.php: 4408 cacti_debug_backtrace)

2017/11/12 23:18:45 - CMDPHP ERROR: A DB Exec Failed!, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' NULL), (31213, 'inst_volts_an', 285.033, 285.033), (31213, 'inst_volts_bn', 286' at line 1
2017/11/12 23:18:45 - CMDPHP SQL Backtrace: (/poller.php: 539 process_poller_output)(/lib/poller.php: 508 dsstats_poller_output)(/lib/dsstats.php: 684 db_execute)(/lib/database.php: 135 db_execute_prepared)(/lib/database.php: 208 cacti_debug_backtrace)

2017/11/12 23:17:45 - CMDPHP ERROR: A DB Exec Failed!, Error: Unknown column 'nan' in 'field list'
2017/11/12 23:17:45 - CMDPHP SQL Backtrace: (/poller.php: 539 process_poller_output)(/lib/poller.php: 508 dsstats_poller_output)(/lib/dsstats.php: 681 db_execute)(/lib/database.php: 135 db_execute_prepared)(/lib/database.php: 208 cacti_debug_backtrace)

And now I get the added benefit of seeing a bunch of these type messages too:

2017/11/12 23:19:02 - DSSTATS WARNING: File '/cacti/cacti-1.1.27-prod/rra/xxx_55042.rrd' Does not exist
2017/11/12 23:19:02 - DSSTATS WARNING: File '/cacti/cacti-1.1.27-prod/rra/yyy_55043.rrd' Does not exist
2017/11/12 23:19:06 - DSSTATS WARNING: File '/cacti/cacti-1.1.27-prod/rra/zzz_58499.rrd' Does not exist

[cigamit]

Missed that case. 'Nan' for guages....

[cigamit]

Resolved.