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

[Rael]

I'm running 0.8.7b and I don't see it in my cli directory.

[BSOD2600]

Then you have something seriously messed up. How did you install your cacti?

[scruboy01]

What exactly causes this issue and how can it be avoided?

[streaker69]

What exactly causes this issue and how can it be avoided?

A crashed database can be caused by the unexpected shutdown of the machine. To avoid it, properly shutdown your machines ensuring that all services are properly stopped first.

[aitm908]

All,

Good afternoon. Not sure if you guys get a lot of thank you emails, but thank you so much for these helpful guides!

[root@cacti cli]# php repair_database.php

That also saved my Cacti DB!! Thanks again, and have a great day!

George

[mp72@excite.com]

I saw similar messages in my log files and graphs weren't getting updated. This happened after the system ran out of disk space on the mysql partition, then space was cleared and system restarted.

I ran "mysqlcheck cacti" and it reported problems with one of the tables.

I ran "mysqlcheck --repair cacti" and the problem was fixed.

[ozzy-tr]

hi all,
if your db crashed you should be try in console:

mysqlcheck --auto-repair --databases cacti

mysqlcheck --repair cacti

php ../var/www/html/cli/repair_database.php

[nytro]

I run CactiEZ whit version 0.8.8a

[root@localhost]cd /var/www/html/cli/
[root@localhost cli]php repair_database.php
Repairing Table -> 'version' Successful
Repairing Table -> 'weathermap_auth' Successful
Repairing Table -> 'weathermap_data' Successful
Repairing Table -> 'weathermap_maps' Successful
Repairing Table -> 'weathermap_settings' Successful

Solved!


Thank.

[tekkitan]

Sorry for bumping an old thread, but I am getting this error 130 in our cacti 0.8.8a installation.

I tried repairing the database via mysqlcheck as well as running the database_repair.php script and neither seems to want to fix the corrupted database.

Also not sure why this happened. It stopped on Wednesday. Neither the server running Cacti nor the database server went down on these days.

Code: Select all

root@cacti:/usr/share/cacti/cli# php repair_database.php
Repairing All Cacti Database Tables
Repairing Table -> 'cdef' Successful
Repairing Table -> 'cdef_items' Successful
Repairing Table -> 'colors' Successful
Repairing Table -> 'copytable' Successful
Repairing Table -> 'data_input' Successful
Repairing Table -> 'data_input_data' Successful
Repairing Table -> 'data_input_fields' Successful
Repairing Table -> 'data_local' Successful
Repairing Table -> 'data_template' Successful
Repairing Table -> 'data_template_data' Successful
Repairing Table -> 'data_template_data_rra' Successful
Repairing Table -> 'data_template_rrd' Successful
Repairing Table -> 'graph_local' Successful
Repairing Table -> 'graph_template_input' Successful
Repairing Table -> 'graph_template_input_defs' Successful
Repairing Table -> 'graph_templates' Successful
Repairing Table -> 'graph_templates_gprint' Successful
Repairing Table -> 'graph_templates_graph' Successful
Repairing Table -> 'graph_templates_item' Successful
Repairing Table -> 'graph_tree' Successful
Repairing Table -> 'graph_tree_items' Successful
Repairing Table -> 'host' Successful
Repairing Table -> 'host_graph' Successful
Repairing Table -> 'host_snmp_cache' Successful
Repairing Table -> 'host_snmp_query' Successful
Repairing Table -> 'host_template' Successful
Repairing Table -> 'host_template_graph' Successful
Repairing Table -> 'host_template_snmp_query' Successful
Repairing Table -> 'plugin_config' Successful
Repairing Table -> 'plugin_db_changes' Successful
Repairing Table -> 'plugin_hooks' Successful
Repairing Table -> 'plugin_realms' Successful
Repairing Table -> 'plugin_thold_contacts' Successful
Repairing Table -> 'plugin_thold_log' Successful
Repairing Table -> 'plugin_thold_template_contact' Successful
Repairing Table -> 'plugin_thold_threshold_contact' Successful
Repairing Table -> 'poller' Successful
Repairing Table -> 'poller_command' Successful
Repairing Table -> 'poller_item' Successful
Repairing Table -> 'poller_output' Successful
Repairing Table -> 'poller_reindex' Successful
Repairing Table -> 'poller_time' Successful
Repairing Table -> 'rra' Successful
Repairing Table -> 'rra_cf' Successful
Repairing Table -> 'settings' Successful
Repairing Table -> 'settings_graphs' Successful
Repairing Table -> 'settings_tree' Successful
Repairing Table -> 'snmp_query' Successful
Repairing Table -> 'snmp_query_graph' Successful
Repairing Table -> 'snmp_query_graph_rrd' Successful
Repairing Table -> 'snmp_query_graph_rrd_sv' Successful
Repairing Table -> 'snmp_query_graph_sv' Successful
Repairing Table -> 'thold_data' Successful
Repairing Table -> 'thold_template' Successful
Repairing Table -> 'user_auth' Successful
Repairing Table -> 'user_auth_perms' Successful
Repairing Table -> 'user_auth_realm' Successful
Repairing Table -> 'user_log' Successful
Repairing Table -> 'version' Successful

NOTE: Checking for Invalid Cacti Templates
NOTE: No Invalid Cacti Template Records found in your Database

root@cacti:/usr/share/cacti/cli# mysqlcheck -u cacti -p -h database.server --auto-repair --databases cacti
Enter password:
cacti.cdef                                         OK
cacti.cdef_items                                   OK
cacti.colors                                       OK
cacti.copytable                                    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.plugin_config                                OK
cacti.plugin_db_changes                            OK
cacti.plugin_hooks                                 OK
cacti.plugin_realms                                OK
cacti.plugin_thold_contacts                        OK
cacti.plugin_thold_log                             OK
cacti.plugin_thold_template_contact                OK
cacti.plugin_thold_threshold_contact               OK
cacti.poller                                       OK
cacti.poller_command                               OK
cacti.poller_item                                  OK
cacti.poller_output
Error    : Incorrect file format 'poller_output'
error    : Corrupt
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.thold_data                                   OK
cacti.thold_template                               OK
cacti.user_auth                                    OK
cacti.user_auth_perms                              OK
cacti.user_auth_realm                              OK
cacti.user_log                                     OK
cacti.version                                      OK

Repairing tables
cacti.poller_output
Error    : Incorrect file format 'poller_output'
error    : Corrupt

(I also noticed this is in the Windows forum, whoops)

[BSOD2600]

I'd recommend dropping the cacti.poller_output table and recreating (extract the info from \cacti\cacti.sql) it.

[tekkitan]

I'd recommend dropping the cacti.poller_output table and recreating (extract the info from \cacti\cacti.sql) it.

Thanks for the reply. What will I lose by doing this? Sorry I am still learning the technical details of Cacti. I also see there is a poller_output_empty.php script, would that help in any way?

[BSOD2600]

poller_output holds data cacti collected from the last polling cycle. since its already corrupt, you wont loose anything additional.

poller_output_empty.php is an Empty Poller Output Table Utility, which wont help you in this situation.

[tekkitan]

poller_output holds data cacti collected from the last polling cycle. since its already corrupt, you wont loose anything additional.

poller_output_empty.php is an Empty Poller Output Table Utility, which wont help you in this situation.

Thanks. I did end up just dropping the table and recreating it. Everything seems to be working normally again. Thanks again for the suggestions!

[hammondo]

Sorry too about bumping a VERY old thread but hopefully BSOD2006 or someone can assist? Our Windows 2008R2 cacti server crashed early yesterday morning (12:04 a.m.) and I now see "#J" in all my graphs and earlier I was seeing dozens of "SQL Assoc Failed" errors in the cacti.log file however now I see nothing when I click in the UI to see the cacti.log file - probably because it is now 4.6 GB in size;-) In Windows Event Logs>Application Logs I see various errors such as "mysqld: Incorrect key file for table '.\cacti\poller_output.MYI\; try to repair it" and "mysql thread id 827, OS thread handle 0xf84, query id 12101056 localhost 127.0.0.1 cacti update insert into poller_output (local_data_id, rrd_name, time, output) values (2813, 'traffic_out', '2016-06-22 10:14:36', '0')". I tried mysqlcheck --auto-repair --databases cacti but it also now looks like my mysql root and mysql cacti passwords also got corrupted so right now I am attempting to change the mysql root password. I will report back but of course any assistance is certainly MOST APPRECIATED! You truly wise cacti guardians are thus (MOST APPRECIATED!).

Steve

[hammondo]

I tried the mysql-init.txt password reset mysqld --init-file=C:\mysql-init.txt --explicit_defaults_for_timestamp server option --console (I saw an error asking for the --explicit... so I added that and the --console to see what happens), with the MySql 5.6 service stopped.

The result is the service was not started and now will not start. When attempting to manually start the MySql service I see error 1067: The process terminated unexpectedly.

Here is the console output from the password reset attempt:

536 [Note] Plugin 'FEDERATED' is disabled.
536 [Note] InnoDB: The InnoDB memory heap is disabled
536 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
536 [Note] InnoDB: Compressed tables use Windows interlocked functions
536 [Note] InnoDB: Not using CPU crc32 instructions
536 [Note] InnoDB: Initializing buffer pool, size = 128.0M
536 [Note] InnoDB: Completed initialization of buffer pool
536 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
536 [ERROR] InnoDB: The system tablespace must be writable!
536 [ERROR] Plugin 'InnoDB' init functions returned error.
536 [ERROR] Plugin 'InnoDB' restration as a STORAGE ENGINE failed.
mysqld: Too many arguments (first extra is 'server').
Use --verbose --help to get a list of available options
536 [ERROR] Aborting

536 [Note] Binlog end
536 [Note] Shutting down plugin 'partition'
536 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
536 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
536 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
536 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
536 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
536 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
536 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
536 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
536 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
536 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
536 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
536 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
536 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
536 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
536 [Note] Shutting down plugin 'INNODB_FT_DELETED'
536 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
536 [Note] Shutting down plugin 'INNOBD_METRICS;
536 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
536 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LAW'
536 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
536 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
536 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
536 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
536 [Note] Shutting down plugin 'INNODB CMPMEM'
536 [Note] Shutting down plugin 'INNODB_CMP'
536 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
536 [Note] Shutting down plugin 'INNODB_LOCKS'
536 [Note] Shutting down plugin 'INNODB_TRX'
536 [Note] Shutting down plugin 'BLACKHOLE'
536 [Note] Shutting down plugin 'ARCHIVE'
536 [Note] Shutting down plugin 'MRG_MYISAN'
536 [Note] Shutting down plugin 'MySIAM'
536 [Note] Shutting down plugin 'MEMORY'
536 [Note] Shutting down plugin 'CSU'
536 [Note] Shutting down plugin 'sha256_password'
536 [Note] Shutting down plugin 'mysql_old_password'
536 [Note] Shutting down plugin 'mysql_native_password'
536 [Note] Shutting down plugin "binlog'
536 [Note] mysqld: Shutdown complete

mysql-init.txt content:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('******');

where ***** - my password for mysql root. As of right now I have no web UI access, here is the error when I attempt to access the UI: FATAL: Cannot connect to MySQL server on 'localhost'. Please make sure you have specified a valid MySQL database name in 'include/config.php'