Cacti (home)ForumsRepositoryDocumentation
Cacti: offical forums and support
It is currently Thu Aug 21, 2014 8:55 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: [SOLVED] Problems with Mysql (ERROR: SQL Assoc Failed)
PostPosted: Wed Dec 05, 2007 9:54 am 
Offline

Joined: Mon Sep 26, 2005 1:10 pm
Posts: 27
Location: williamsport, pa
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.


Top
 Profile  
 
 Post subject: Re: Problems with Mysql (ERROR: SQL Assoc Failed)
PostPosted: Wed Dec 05, 2007 2:44 pm 
Offline
Cacti Moderator
User avatar

Joined: Sat May 08, 2004 12:44 pm
Posts: 11297
Location: USA
williams1075 wrote:
But I am still having the problem.

Specifically what problem now since the database is repaired?

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 |


Top
 Profile  
 
 Post subject: Re: Problems with Mysql (ERROR: SQL Assoc Failed)
PostPosted: Wed Dec 05, 2007 3:10 pm 
Offline

Joined: Mon Sep 26, 2005 1:10 pm
Posts: 27
Location: williamsport, pa
BSOD2600 wrote:
williams1075 wrote:
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Dec 05, 2007 8:52 pm 
Offline
Cacti Moderator
User avatar

Joined: Sat May 08, 2004 12:44 pm
Posts: 11297
Location: USA
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 |


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 06, 2007 8:36 am 
Offline

Joined: Mon Sep 26, 2005 1:10 pm
Posts: 27
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Dec 07, 2007 3:36 am 
Offline
Cacti Moderator
User avatar

Joined: Sat May 08, 2004 12:44 pm
Posts: 11297
Location: USA
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 |


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 10, 2007 9:33 am 
Offline

Joined: Mon Sep 26, 2005 1:10 pm
Posts: 27
Location: williamsport, pa
Is there anyway to get a developer to look at this issue? Can I email one a link to this thread?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 10, 2007 1:12 pm 
Offline
Cacti Moderator
User avatar

Joined: Sat May 08, 2004 12:44 pm
Posts: 11297
Location: USA
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?

_________________

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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 10, 2007 1:29 pm 
Offline

Joined: Mon Sep 26, 2005 1:10 pm
Posts: 27
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...


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 10, 2007 4:21 pm 
Offline
Cacti Moderator
User avatar

Joined: Sat May 08, 2004 12:44 pm
Posts: 11297
Location: USA
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 |


Top
 Profile  
 
 Post subject:
PostPosted: Sat Dec 15, 2007 2:55 pm 
Offline
Developer
User avatar

Joined: Tue May 14, 2002 5:08 pm
Posts: 14861
Location: MI, USA
You need to repair your database. I have a script, but I have to get it from another computer.

TheWitness

_________________
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of MacTrack, Boost, CLog, SpikeKill, Platform RTM, DSStats, maintainer of Spine, lot's of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
Gandalfs Official Debugging Help
Central Plugin Repository
Central Templates Repository


Top
 Profile  
 
 Post subject:
PostPosted: Sat Dec 15, 2007 3:05 pm 
Offline
Developer
User avatar

Joined: Tue May 14, 2002 5:08 pm
Posts: 14861
Location: MI, USA
Here is the script. Place in your CLI directory. If you do not have a CLI directory, place it in your cacti root.

TheWitness


Attachments:
repair_database.zip [1.51 KiB]
Downloaded 4559 times

_________________
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of MacTrack, Boost, CLog, SpikeKill, Platform RTM, DSStats, maintainer of Spine, lot's of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
Gandalfs Official Debugging Help
Central Plugin Repository
Central Templates Repository
Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 24, 2008 1:32 am 
Offline

Joined: Thu Jan 26, 2006 12:39 pm
Posts: 23
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 27, 2008 10:09 am 
Offline
Cacti User

Joined: Thu Apr 05, 2007 9:28 am
Posts: 129
Life saver.

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

Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 27, 2008 8:47 pm 
Offline
Developer
User avatar

Joined: Tue May 14, 2002 5:08 pm
Posts: 14861
Location: MI, USA
I believe that if you look into /var/www/html/cacti/cli, you will find it there aleardy in 0.8.7b.

TheWitness

_________________
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of MacTrack, Boost, CLog, SpikeKill, Platform RTM, DSStats, maintainer of Spine, lot's of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
Gandalfs Official Debugging Help
Central Plugin Repository
Central Templates Repository


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: Exabot [Bot] and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Protected by Anti-Spam ACP Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group