ERROR: SQL Cell Failed!, Error:'2006' & ERROR: SQL Assoc Fai

[tsolaaa]

Hi Guys,

Relatively new to Cacti and need some help.

Half of my graphs are not showing any new data (while the other half are) and I am getting the following errors inside cacti.log

Code: Select all

12/16/2014 10:04:59 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'2006', SQL:"select  poller_output.output,  poller_output.time,  UNIX_TIMESTAMP(poller_output.time) as unix_time,  poller_output.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  from (poller_output,poller_item)  where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)  LIMIT 10000"
12/16/2014 10:04:59 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'2006', SQL:"SELECT count(*) FROM poller_time WHERE poller_id=0 AND end_time>'0000-00-00 00:00:00'"
12/16/2014 10:04:59 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'2006', SQL:"select  poller_output.output,  poller_output.time,  UNIX_TIMESTAMP(poller_output.time) as unix_time,  poller_output.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  from (poller_output,poller_item)  where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)  LIMIT 10000"
12/16/2014 10:04:59 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'2006', SQL:"SELECT count(*) FROM poller_time WHERE poller_id=0 AND end_time>'0000-00-00 00:00:00'"
12/16/2014 10:04:59 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'2006', SQL:"select  poller_output.output,  poller_output.time,  UNIX_TIMESTAMP(poller_output.time) as unix_time,  poller_output.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  from (poller_output,poller_item)  where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)  LIMIT 10000"
12/16/2014 10:05:00 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'2006', SQL:"SELECT count(*) FROM poller_time WHERE poller_id=0 AND end_time>'0000-00-00 00:00:00'"
12/16/2014 10:05:00 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'2006', SQL:"select  poller_output.output,  poller_output.time,  UNIX_TIMESTAMP(poller_output.time) as unix_time,  poller_output.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  from (poller_output,poller_item)  where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)  LIMIT 10000"
12/16/2014 10:05:00 AM - POLLER: Poller[0] Maximum runtime of 298 seconds exceeded. Exiting.
12/16/2014 10:05:00 AM - SYSTEM STATS: Time:298.8408 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:481 RRDsProcessed:157
12/16/2014 10:05:00 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'2006', SQL:"REPLACE INTO settings (name,value) VALUES ('stats_poller','Time:298.8408 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:481 RRDsProcessed:157')'
12/16/2014 10:05:00 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'2006', SQL:"SELECT COUNT(*) FROM poller_command"
12/16/2014 10:05:00 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'2006', SQL:"REPLACE INTO settings (name,value) VALUES ('stats_recache','RecacheTime:0.0 HostsRecached:0')'
12/16/2014 10:05:00 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'2006', SQL:"SELECT 1 AS id, ph.name, ph.file, ph.function  FROM plugin_hooks AS ph  LEFT JOIN plugin_config AS pc  ON pc.directory=ph.name  WHERE ph.status = 1 AND hook = 'poller_bottom'  AND ph.name IN ('settings', 'boost', 'dsstats')  UNION  SELECT pc.id, ph.name, ph.file, ph.function  FROM plugin_hooks AS ph  LEFT JOIN plugin_config AS pc  ON pc.directory=ph.name  WHERE ph.status = 1 AND hook = 'poller_bottom'  AND ph.name NOT IN ('settings', 'boost', 'dsstats')  ORDER BY id ASC"

I have already did a bunch of searching on Google and found pretty much the same two answers.

1.) MySQL database could be broken/corrupted.

- I have already analysed/optimised/repaired all the databases, no errors found.

Code: Select all

mysqlcheck -u cacti -p --auto-repair --optimize --all-databases

2.) There could be two pollers running in cron.

- I have already looked through all the crons I could find but only see the one job listed.

crontab -l -u cacti
crontab -l -u root
crontab -l -u nagios
crontab -l -u nrpe
crontab -e -u cactiuser
crontab -l -u cactiuser

They don't show any jobs.


/etc/crontab - is empty
/etc/cron.d - shows cacti
/etc/cron.d/cacti - shows - */5 * * * * cacti /usr/bin/php /usr/share/cacti/poller.php > /dev/null 2>&1


Running (ten minutes after a reboot) ps aux | grep cacti shows:

Code: Select all

root      2484  0.0  0.0 100944   612 pts/0    S+   11:57   0:00 tail -f cacti.log
cacti     3088  0.3  0.5 288248 21024 ?        S    12:00   0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti     3117  0.6  0.4 284540 17300 ?        S    12:00   0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti     4594  0.0  0.0 106096  1136 ?        Ss   12:05   0:00 /bin/sh -c /usr/bin/php /usr/share/cacti/poller.php > /dev/null 2>&1
cacti     4595 22.5  0.4 286496 18536 ?        S    12:05   0:57 /usr/bin/php /usr/share/cacti/poller.php
cacti     4600  0.8  0.5 288248 21028 ?        S    12:05   0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti     4603  0.0  0.0 158212  1496 ?        S    12:05   0:00 /usr/bin/rrdtool -
cacti     4604  1.2  0.4 284540 17292 ?        S    12:05   0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
root      5046  0.0  0.0 103252   836 pts/1    S+   12:09   0:00 grep cacti

Before I restarted, ps aux | grep cacti showed

cacti 2720 0.0 0.5 288248 21024 ? S 09:40 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 2737 0.0 0.4 284540 17296 ? S 09:40 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 4109 0.0 0.5 288248 21020 ? S 09:45 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 4124 0.0 0.4 284540 17292 ? S 09:45 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 4945 0.0 0.5 288248 21028 ? S 09:50 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 4976 0.0 0.4 284540 17292 ? S 09:50 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 6343 0.0 0.5 288248 21024 ? S 09:55 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 6347 0.0 0.4 284540 17492 ? S 09:55 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 7011 0.0 0.5 288248 21020 ? S 10:00 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 7015 0.0 0.4 284540 17296 ? S 10:00 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 8836 0.0 0.5 288248 21028 ? S 10:05 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 8855 0.0 0.4 284540 17532 ? S 10:05 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 9612 0.0 0.5 288248 21024 ? S 10:10 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 9616 0.0 0.4 284540 17296 ? S 10:10 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
root 10066 0.0 0.4 284544 17332 pts/2 S+ 10:11 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 10967 0.0 0.5 288248 21028 ? S 10:15 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 10971 0.0 0.4 284540 17296 ? S 10:15 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 11711 0.0 0.5 288248 21028 ? S 10:20 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 11732 0.0 0.4 284540 17384 ? S 10:20 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 13135 0.0 0.5 288248 21024 ? S 10:25 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 13139 0.0 0.4 284540 17292 ? S 10:25 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 13848 0.0 0.5 288248 21028 ? S 10:30 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 13880 0.0 0.4 284540 17296 ? S 10:30 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 15223 0.0 0.5 288248 21020 ? S 10:35 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 15227 0.0 0.4 284540 17460 ? S 10:35 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 15983 0.0 0.5 288248 21028 ? S 10:40 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 16015 0.0 0.4 284540 17292 ? S 10:40 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 17345 0.0 0.5 288248 21028 ? S 10:45 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 17349 0.0 0.4 284540 17292 ? S 10:45 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 18029 0.0 0.5 288248 21024 ? S 10:50 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 18038 0.0 0.4 284540 17296 ? S 10:50 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 19377 0.0 0.5 288248 21024 ? S 10:55 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 19381 0.1 0.4 284540 17384 ? S 10:55 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 20069 0.0 0.5 288248 21028 ? S 11:00 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 20073 0.1 0.4 284540 17500 ? S 11:00 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 21470 0.0 0.5 288248 21028 ? S 11:05 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 21474 0.1 0.4 284540 17292 ? S 11:05 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 22200 0.0 0.5 288248 21028 ? S 11:10 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 22204 0.1 0.4 284540 17328 ? S 11:10 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 23523 0.1 0.5 288248 21024 ? S 11:15 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 23527 0.1 0.4 284540 17296 ? S 11:15 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 24263 0.1 0.5 288248 21024 ? S 11:20 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 24267 0.2 0.4 284540 17296 ? S 11:20 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 25594 0.1 0.5 288248 21028 ? S 11:25 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 25598 0.2 0.4 284540 17292 ? S 11:25 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 26326 0.2 0.5 288248 21024 ? S 11:30 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 26330 0.3 0.4 284540 17404 ? S 11:30 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 27689 0.2 0.5 288248 21028 ? S 11:35 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 27693 0.4 0.4 284540 17292 ? S 11:35 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 28406 0.5 0.5 288248 21024 ? S 11:40 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 28438 0.7 0.4 284540 17596 ? S 11:40 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
cacti 29762 0.0 0.0 106096 1136 ? Ss 11:45 0:00 /bin/sh -c /usr/bin/php /usr/share/cacti/poller.php > /dev/null 2>&1
cacti 29763 23.1 0.4 286496 18536 ? S 11:45 0:29 /usr/bin/php /usr/share/cacti/poller.php
cacti 29767 1.7 0.5 288248 21028 ? S 11:45 0:02 /usr/bin/php -q /usr/share/cacti/cmd.php 0 20
cacti 29770 0.0 0.0 158212 1496 ? S 11:45 0:00 /usr/bin/rrdtool -
cacti 29771 2.6 0.4 284540 17212 ? S 11:45 0:03 /usr/bin/php -q /usr/share/cacti/script_server.php cmd
root 30006 0.0 0.0 103256 848 pts/1 S+ 11:47 0:00 grep cacti

Which looks like something is going out of control?

My MySQL conf file shows:

Code: Select all

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections = 1500
max_allowed_packet = 64

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have already verified that all the .rrd files inside /var/lib/cacti/rra are owned by cacti and not root.

I am running CentOS 6.6, MySQL 5.1.73, Cacti 0.8.8b - All fresh install, no upgrading.


*Update*

I just ran the "repair_database.php " and the "rebuild_poller_cache.php" inside the cacti CLI folder then rebooted the machine.

But now Cacti is not polling any of the devices anymore.

I can confirm that the cron is running every 5 minutes.

Code: Select all

12/16/2014 01:35:02 PM - SYSTEM STATS: Time:0.1283 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:0 RRDsProcessed:0
12/16/2014 01:40:01 PM - POLLER: Poller[0] NOTE: Poller Int: '300', Cron Int: '300', Time Since Last: '300', Max Runtime '298', Poller Runs: '1'
12/16/2014 01:40:01 PM - POLLER: Poller[0] NOTE: There are no items in your poller for this polling cycle!
12/16/2014 01:40:01 PM - SYSTEM STATS: Time:0.1070 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:0 RRDsProcessed:0
12/16/2014 01:45:01 PM - POLLER: Poller[0] NOTE: Poller Int: '300', Cron Int: '300', Time Since Last: '300', Max Runtime '298', Poller Runs: '1'
12/16/2014 01:45:01 PM - POLLER: Poller[0] DEBUG: About to Spawn a Remote Process [CMD: /usr/bin/php, ARGS: -q "/usr/share/cacti/cmd.php" 0 21]
12/16/2014 01:45:01 PM - POLLER: Poller[0] NOTE: There are no items in your poller for this polling cycle!
12/16/2014 01:45:01 PM - SYSTEM STATS: Time:0.1069 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:0 RRDsProcessed:0
12/16/2014 01:45:51 PM - POLLER: Poller[0] NOTE: There are no items in your poller for this polling cycle!
12/16/2014 01:45:57 PM - POLLER: Poller[0] NOTE: There are no items in your poller for this polling cycle!
12/16/2014 01:50:01 PM - POLLER: Poller[0] NOTE: Poller Int: '300', Cron Int: '300', Time Since Last: '300', Max Runtime '298', Poller Runs: '1'
12/16/2014 01:50:01 PM - POLLER: Poller[0] DEBUG: About to Spawn a Remote Process [CMD: /usr/bin/php, ARGS: -q "/usr/share/cacti/cmd.php" 0 21]
12/16/2014 01:50:01 PM - POLLER: Poller[0] NOTE: There are no items in your poller for this polling cycle!
12/16/2014 01:50:01 PM - SYSTEM STATS: Time:0.1069 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:0 RRDsProcessed:0

When trying to poll it manually by running the cmd.php it just gives me the "There are no items in your poller for this polling cycle!"

[tsolaaa]

Ok, suddenly it started polling again.

It is polling every 5 minutes with no errors in cacti.log that I can see but still only some of the devices are actually being graphed.

2/16/2014 02:30:00 PM - POLLER: Poller[0] Maximum runtime of 298 seconds exceeded. Exiting.
12/16/2014 02:30:00 PM - SYSTEM STATS: Time:298.8495 Method:cmd.php Processes:1 Threads:N/A Hosts:19 HostsPerProcess:19 DataSources:429 RRDsProcessed:128
12/16/2014 02:30:01 PM - POLLER: Poller[0] NOTE: Poller Int: '300', Cron Int: '300', Time Since Last: '300', Max Runtime '298', Poller Runs: '1'
12/16/2014 02:30:01 PM - POLLER: Poller[0] WARNING: There are '1' detected as overrunning a polling process, please investigate
12/16/2014 02:30:01 PM - PHPSVR: Poller[0] PHP Script Server has Started - Parent is cmd
12/16/2014 02:30:01 PM - CMDPHP: Poller[0] PHP Script Server Started Properly
12/16/2014 02:30:01 PM - CMDPHP: Poller[0] Host[2] SNMP: Host responded to SNMP

etc.
I have no plugins installed and not sure what would be overrunning the polling process as stated in the error above.

And yes, there are data sources linked

[cigamit]

Some of the scripts / graphs are most likely causing the poller to hang. You should be able to tell in the log which it is. If not, then you need to disable all hosts but one, and watch the logs. Slowly re-enable each host after a few pollings to determine which one it is, when you find it. We can then discuss from there.

[bartc]

I have exactly the same problem. Tried everything but polling still taking too long. It changed yesterday and noone was messing with the server at that time. Out of ideas.

[bartc]

After 2 days I finally fixed it and it seems stable for few days.

1. See if there are any php zombie processes.
2. Disable poller through the gui
3. Kill all php processes
4. Disable all nodes
5. Enable poller through cacti GUI again
6. Start Enabling hosts one by one (or by groups if you have large number of hosts)

Poller Time was good now but I could see something strange happening. In my case I could see in the processes that poller_commands.php was hanging after every poller cycle. My poller is cmd.php and interval is 5 min so every 5 min I was getting new php process running poller_commands.php which never stopped.

In the log file I could see "Recache event detected" for 2 hosts. This was holding poller_commands.php causing php processes to turn to zombies at some point. I tried manual recache from cli scripts but it was stuck as well. Eventually I had:

1. Disabled poller through the gui.
2. Removed 2 hosts that I saw recache event for.
3. Checked if all datasources are removed from affected nodes.
4. Started poller again.

At that moment I saw no more recache events for hosts that I removed but I saw other recache events for different ones. Luckily in the log I saw following "RECACHE STATS: RecacheTime: 22.5524 HostsRecached:5". Since then everything is running smoothly and I have no more issues.


My cacti version is 0.8.8b