[SOLVED] Problems with Mysql (ERROR: SQL Assoc Failed)

[williams1075]

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.

[BSOD2600]

But I am still having the problem.

Specifically what problem now since the database is repaired?

Might want to run a chkdsk /f too.

[williams1075]

But I am still having the problem.

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

[BSOD2600]

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

[williams1075]

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

[BSOD2600]

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

[williams1075]

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

[BSOD2600]

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?

[williams1075]

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...

[BSOD2600]

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

[TheWitness]

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

TheWitness

[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

[Super-Mike]

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

[Rael]

Life saver.

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

Thanks!

[TheWitness]

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

TheWitness