I just upgraded last week to the newest version on Cacti "0.8.7a", everything went well for a couple days. Then the graphs stopped recording data. So I looked at the Log file and saw this:

12/05/2007 09:50:15 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"select poller_output.output, poller_output.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) "

12/05/2007 09:50:14 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"select poller_output.output, poller_output.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/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

12/05/2007 09:50:13 AM - CACTID: Poller[0] ERROR: Problem with MySQL: 'Table '.\cacti\poller_output' is marked as crashed and should be repaired'

I found a thread that said that this meant the Mysql Database was corrupt and to use "mysqlcheck --auto-repair --databases cacti". I ran the command and got the following output.

cacti.cdef OK
cacti.cdef_items OK
cacti.colors OK
cacti.data_input OK
cacti.data_input_data OK
cacti.data_input_fields OK
cacti.data_local OK
cacti.data_template OK
cacti.data_template_data OK
cacti.data_template_data_rra OK
cacti.data_template_rrd OK
cacti.graph_local OK
cacti.graph_template_input OK
cacti.graph_template_input_defs OK
cacti.graph_templates OK
cacti.graph_templates_gprint OK
cacti.graph_templates_graph OK
cacti.graph_templates_item OK
cacti.graph_tree OK
cacti.graph_tree_items OK
cacti.host OK
cacti.host_graph OK
cacti.host_snmp_cache OK
cacti.host_snmp_query OK
cacti.host_template OK
cacti.host_template_graph OK
cacti.host_template_snmp_query OK
cacti.poller OK
cacti.poller_command OK
cacti.poller_item OK
cacti.poller_reindex OK
cacti.poller_time OK
cacti.rra OK
cacti.rra_cf OK
cacti.settings OK
cacti.settings_graphs OK
cacti.settings_tree OK
cacti.snmp_query OK
cacti.snmp_query_graph OK
cacti.snmp_query_graph_rrd OK
cacti.snmp_query_graph_rrd_sv OK
cacti.snmp_query_graph_sv OK
cacti.user_auth OK
cacti.user_auth_perms OK
cacti.user_auth_realm OK
cacti.user_log OK
cacti.version OK


But I am still having the problem.

I could try a clean install, but there is a lot of historical data, and configured devices on this machine.

Does anyone have any suggestions.

Specifically what problem now since the database is repaired?

Might want to run a chkdsk /f too.

I am still getting the errors I listed above in the cacti log.

tried a chkdsk... no good

The SQL Assoc Failed! errors? Try clearing the poller cache.

clearing the poller cache didn't work.

I read some threads on using just the cmd.php for polling to fix the problem. I don't think this is a option. I was using spine (cactid ) because of the large amount of devices we are monitoring.

I also read something about a patch to install, I am looking into that now.

Here is a result of the errors I am getting running cmd.php

__________________________

12/06/2007 08:35:39 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"select poller_output.output, poller_output.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/06/2007 08:35:38 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1129, 'errors_out', '2007-12-06 08:35:36', '0')'
12/06/2007 08:35:38 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1129, 'discards_out', '2007-12-06 08:35:36', '0')'
12/06/2007 08:35:38 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1129, 'discards_in', '2007-12-06 08:35:36', '0')'
12/06/2007 08:35:38 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1129, 'errors_in', '2007-12-06 08:35:36', '0')'
12/06/2007 08:35:38 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1128, 'errors_out', '2007-12-06 08:35:36', '0')'
12/06/2007 08:35:38 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (1128, 'discards_out', '2007-12-06 08:35:36', '0')'


Thanks for the help

Hmm, odd. Going to need a developer to comment, since I've been seeing this often lately.

Is there anyway to get a developer to look at this issue? Can I email one a link to this thread?

Both seem to be taking a "break" from cacti. Who knows when they'll get back.

Do you have a backup before the upgrade? If so, you could try to restore it and then do an upgrade again; paying close attention to any errors during it. Was your previous 0.8.6j installation fully patched?

All is backed up except MySQL, I think something happened during the backup because it won't restore.

I didn't have all the patches before the upgrade. Is that necessary, since you are basically replacing the software on a major upgrade? All that I returned was the rrd files and the mysql database?

Thanks...

Go ahead and try to run one of those failed MySQL commands manually against the cacti db. What is the error?

You need to repair your database. I have a script, but I have to get it from another computer.

TheWitness

Here is the script. Place in your CLI directory. If you do not have a CLI directory, place it in your cacti root.

TheWitness

bash-3.00# php repair_database.php
Repairing All Cacti Database Tables
PHP Fatal error: Call to undefined function db_fetch_assoc() in /var/www/cacti/repair_database.php on line 79
bash-3.00#

php 5.2.5

Life saver.

TheWitness can you make sure this handy little script is included in the next rev of Cacti in the cli directory?

Thanks!

I believe that if you look into /var/www/html/cacti/cli, you will find it there aleardy in 0.8.7b.

TheWitness