[SOLVED] Problems with Mysql (ERROR: SQL Assoc Failed)
Moderators: Developers, Moderators
-
- Posts: 27
- Joined: Mon Sep 26, 2005 1:10 pm
- Location: williamsport, pa
[SOLVED] Problems with Mysql (ERROR: SQL Assoc Failed)
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.
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.
Re: Problems with Mysql (ERROR: SQL Assoc Failed)
Specifically what problem now since the database is repaired?williams1075 wrote:But I am still having the problem.
Might want to run a chkdsk /f too.
| Scripts: Monitor processes | RFC1213 MIB | DOCSIS Stats | Dell PowerEdge | Speedfan | APC UPS | DOCSIS CMTS | 3ware | Motorola Canopy |
| Guides: Windows Install | [HOWTO] Debug Windows NTFS permission problems |
| Tools: Windows All-in-one Installer |
-
- Posts: 27
- Joined: Mon Sep 26, 2005 1:10 pm
- Location: williamsport, pa
Re: Problems with Mysql (ERROR: SQL Assoc Failed)
I am still getting the errors I listed above in the cacti log.BSOD2600 wrote:Specifically what problem now since the database is repaired?williams1075 wrote:But I am still having the problem.
Might want to run a chkdsk /f too.
tried a chkdsk... no good
The SQL Assoc Failed! errors? Try clearing the poller cache.
| Scripts: Monitor processes | RFC1213 MIB | DOCSIS Stats | Dell PowerEdge | Speedfan | APC UPS | DOCSIS CMTS | 3ware | Motorola Canopy |
| Guides: Windows Install | [HOWTO] Debug Windows NTFS permission problems |
| Tools: Windows All-in-one Installer |
-
- Posts: 27
- Joined: Mon Sep 26, 2005 1:10 pm
- Location: williamsport, pa
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
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.
| Scripts: Monitor processes | RFC1213 MIB | DOCSIS Stats | Dell PowerEdge | Speedfan | APC UPS | DOCSIS CMTS | 3ware | Motorola Canopy |
| Guides: Windows Install | [HOWTO] Debug Windows NTFS permission problems |
| Tools: Windows All-in-one Installer |
-
- Posts: 27
- Joined: Mon Sep 26, 2005 1:10 pm
- Location: williamsport, pa
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?
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?
| Scripts: Monitor processes | RFC1213 MIB | DOCSIS Stats | Dell PowerEdge | Speedfan | APC UPS | DOCSIS CMTS | 3ware | Motorola Canopy |
| Guides: Windows Install | [HOWTO] Debug Windows NTFS permission problems |
| Tools: Windows All-in-one Installer |
-
- Posts: 27
- Joined: Mon Sep 26, 2005 1:10 pm
- Location: williamsport, pa
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...
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?
| Scripts: Monitor processes | RFC1213 MIB | DOCSIS Stats | Dell PowerEdge | Speedfan | APC UPS | DOCSIS CMTS | 3ware | Motorola Canopy |
| Guides: Windows Install | [HOWTO] Debug Windows NTFS permission problems |
| Tools: Windows All-in-one Installer |
- TheWitness
- Developer
- Posts: 17047
- Joined: Tue May 14, 2002 5:08 pm
- Location: MI, USA
- Contact:
You need to repair your database. I have a script, but I have to get it from another computer.
TheWitness
TheWitness
True understanding begins only when we realize how little we truly understand...
Life is an adventure, let yours begin with Cacti!
Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages
For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Life is an adventure, let yours begin with Cacti!
Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages
For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
- TheWitness
- Developer
- Posts: 17047
- Joined: Tue May 14, 2002 5:08 pm
- Location: MI, USA
- Contact:
Here is the script. Place in your CLI directory. If you do not have a CLI directory, place it in your cacti root.
TheWitness
TheWitness
- Attachments
-
- repair_database.zip
- (1.51 KiB) Downloaded 9439 times
True understanding begins only when we realize how little we truly understand...
Life is an adventure, let yours begin with Cacti!
Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages
For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Life is an adventure, let yours begin with Cacti!
Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages
For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
-
- Posts: 23
- Joined: Thu Jan 26, 2006 12:39 pm
- Contact:
- TheWitness
- Developer
- Posts: 17047
- Joined: Tue May 14, 2002 5:08 pm
- Location: MI, USA
- Contact:
I believe that if you look into /var/www/html/cacti/cli, you will find it there aleardy in 0.8.7b.
TheWitness
TheWitness
True understanding begins only when we realize how little we truly understand...
Life is an adventure, let yours begin with Cacti!
Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages
For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Life is an adventure, let yours begin with Cacti!
Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages
For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
Who is online
Users browsing this forum: No registered users and 1 guest