Multiple SQL Row Failed errors and missing data from graphs

[doobie76]

I recently upgraded our windows install of cacti from 0.8.7g to 0.8.7h

I don't know if its just since the upgrade or if something else has happened but we get a lot of Error 1064 and Error 1062s in the cacti logs and at the same time missing data from the graphs.

I'm not really a cacti or MySQL expert so don't really know where to start looking.

10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
10/26/2011 02:10:53 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="

10/26/2011 02:10:49 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (961, '', '2011-10-26 14:10:45', 'Nodata:0')'
10/26/2011 02:10:46 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (964, '', '2011-10-26 14:10:45', 'DBSize:12833 LogSize:3072')'
10/26/2011 02:10:45 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (239, 'TotalSent', '2011-10-26 14:10:02', 'U')'
10/26/2011 02:10:45 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1545, 'traffic_out', '2011-10-26 14:10:44', '1795022007')'
10/26/2011 02:10:45 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1546, 'traffic_in', '2011-10-26 14:10:44', '322578151')'
10/26/2011 02:10:45 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1546, 'traffic_out', '2011-10-26 14:10:44', '4248807279')'
10/26/2011 02:10:45 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1547, 'traffic_in', '2011-10-26 14:10:44', '1250633456')'
10/26/2011 02:10:45 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1547, 'traffic_out', '2011-10-26 14:10:44', '3873947360')'
10/26/2011 02:10:44 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1508, 'hdd_total', '2011-10-26 14:10:43', '1073152000')'

Any help would be appreciated or pointers in the right direction. I've done a MYSQL DB check and it came back ok, the technical pages within Cacti didn't suggest any issues with the database.

Thanks

Darren

[TheWitness]

Interesting. Do you have any Data Sources that are 'Not' associated with a host? You can goto that section and select 'None' for host.

Also, what plugins are you running?

TheWitness

[doobie76]

I did have some datasources not assigned to hosts, I've deleted these.

Its a very basic install on Windows, just cacti, MYSQL, rrd and spine although I'm not using spine.

Don't believe there are any other plugins installed.

[doobie76]

We've now got some data items that are timeshifting by about 48 hours.

Attached is a graph with 4 data items, 1 of which is out by 48 hours. The blue and purple areas should pretty much overlap each other but as you can see the blue equivalent is plotted 2 days after the purple.

Also we have gaps which we believe are caused by the errors mentioned in the first post. Any help would be appreciated because my only current option is to rebuild the server completely.

[rvd]

Hello,

Same error a lot of times

11/03/2011 04:31:10 PM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="

but occuring in log only each time poller is trying to graph export

Cacti Version - 0.8.7h
Plugin Architecture - 3.0
Poller Type - Cactid v
Server Info - Linux 2.6.18-194.32.1.el5PAE
Web Server - Apache/2.2.3 (Red Hat)
PHP - 5.1.6
PHP Extensions - libxml, xml, wddx, tokenizer, sysvshm, sysvsem, sysvmsg, standard, SimpleXML, sockets, SPL, shmop, session, Reflection, pspell, posix, mime_magic, iconv, hash, gmp, gettext, ftp, exif, date, curl, ctype, calendar, bz2, zlib, pcre, openssl, apache2handler, dbase, dom, gd, ldap, mbstring, mcrypt, mysql, mysqli, PDO, pdo_mysql, pdo_sqlite, snmp, xmlreader, xmlwriter, xsl
MySQL - 5.0.77
RRDTool - 1.4.4
SNMP - 5.3.2.2
Plugins

• Global Plugin Settings (settings - v0.7)
  Host Info (hostinfo - v0.2)
  Update Checker (update - v0.4)
  Poller Performance Report (pollperf - v0.32)
  PHP Network Weathermap (weathermap - v0.97a)
  
  SuperLinks (superlinks - v0.

[luofanbin]

Hello


Same error here after upgrading to 0.8.7h :

Code: Select all

11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id="
11/04/2011 04:17:56 AM - CMDPHP: Poller[0] ERROR: SQL Row Failed!, Error:'1064', SQL:"select * from host where id=" 

Cacti Version - 0.8.7h
Plugin Architecture - 3.0
Poller Type - SPINE
OS : Ubuntu 10.04.3 LTS (Lucid)
Web Server - Apache/2.2.14 (Ubuntu)
Plugins : Slowlog 1.1

[doobie76]

Hi luofanbin

Do you get any data being plotted incorrectly or missing completely or just the errors in the log?

[ladar]

I found the bug. The function substitute_host_data is called even when building a graph that doesn't reference a host. The code will use the host id from the data items if the graph doesn't reference specify a host. If the graph and the data items have no host id, the error is triggered.

Attached is a patch. Someone might want to ensure it ends getting committed to the repo.

[TheWitness]

Done. Thanks for finding this.

[doobie76]

Hi TheWitness

I'm running Cacti on windows, can you advise how to apply this patch?

Thanks

Darren

[doobie76]

Hi ladar

I'm running Cacti on windows, can you advise how to apply this patch?

Thanks

Darren

[noname]

- Patch for Windows

But you can simply edit 'lib/variables.php' as mentioned in the patch file.. (Delete 1 line, and add 6 lines)

[doobie76]

Not sure if its related but now get these errors :

11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='2'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='2'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='4'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='4'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='5'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='5'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='10'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='10'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='3'"
11/07/2011 01:35:56 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1054', SQL:"select field_value from host_snmp_cache where host_id=N and field_name='hrStorageAllocationUnits' and snmp_index='3'"

Could these be related or should I raise a separate thread?

I also still get hundreds of these quite often:

11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1708, 'traffic_out', '2011-11-07 13:31:11', '1110124235')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1708, 'traffic_in', '2011-11-07 13:31:11', '3801038306')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1707, 'traffic_out', '2011-11-07 13:31:11', '2133013179')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1707, 'traffic_in', '2011-11-07 13:31:11', '2958216759')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1074, 'cisco_memfree', '2011-11-07 13:31:11', '142452496')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1073, '5min_cpu', '2011-11-07 13:31:11', '12')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1075, 'cisco_memused', '2011-11-07 13:31:11', '45253960')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1076, 'traffic_out', '2011-11-07 13:31:11', '2409484653')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1076, 'traffic_in', '2011-11-07 13:31:11', '1671521198')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1077, 'traffic_out', '2011-11-07 13:31:11', '865079661')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1077, 'traffic_in', '2011-11-07 13:31:11', '2913799245')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1078, 'traffic_in', '2011-11-07 13:31:11', '3145278350')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1079, 'traffic_in', '2011-11-07 13:31:11', '3391415254')'
11/07/2011 01:31:11 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1062', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1078, 'traffic_out', '2011-11-07 13:31:11', '3438816611')'

[rvd]

I found the bug. The function substitute_host_data is called even when building a graph that doesn't reference a host. The code will use the host id from the data items if the graph doesn't reference specify a host. If the graph and the data items have no host id, the error is triggered.

Attached is a patch. Someone might want to ensure it ends getting committed to the repo.

I don't understand why in this patch :

if ($host != 0) {

and not :

if ($host_id != 0) {

Please, ladar, could you explain this to me ?

[TheWitness]

I've reworked the patch in SVN. Please use the file there.