Connecting the world…

auto-repair

Cacti: corrupt database

After rebooting a Cacti server, the customer complained that no new graphs were drawn by the server. I tried to run the poller.php script with the –-force option and noticed the following output:

06/16/2011 10:34:48 AM – SPINE: Poller[0] ERROR: SQL Failed! Error:’145′, Message:’Table ‘./cacti/poller_output’ is marked as crashed and should be repaired’, SQL Fragment:’INSERT INTO poller_output (local_data_id, rrd_name, time, output) VALUES (514,’traffic_in’,’2011-06-16 10:34:48′,’3446319166′),(357,’traffic_in’,’2011-06-16

This log entry was shown multiple time and it looks like the database got corrupted after the reboot. Mysql has an option to check and repair the database. So I gave that a try via the following command:

mysqlcheck –-auto-repair –-databases cacti

The command gives 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
warning  : 1 client is using or hasn’t closed the table properly
status   : OK
cacti.data_template_data_rra                       OK
cacti.data_template_rrd
warning  : 1 client is using or hasn’t closed the table properly
status   : 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
warning  : 2 clients are using or haven’t closed the table properly
status   : OK
cacti.host_graph                                   OK
cacti.host_snmp_cache
warning  : 1 client is using or hasn’t closed the table properly
status   : OK
cacti.host_snmp_query                              OK
cacti.host_template                                OK
cacti.host_template_graph                          OK
cacti.host_template_snmp_query                     OK
cacti.mac_track_approved_macs                      OK
cacti.mac_track_device_types                       OK
cacti.mac_track_devices                            OK
cacti.mac_track_ip_ranges                          OK
cacti.mac_track_ips
note     : The storage engine for the table doesn’t support check
cacti.mac_track_macauth                            OK
cacti.mac_track_macwatch                           OK
cacti.mac_track_oui_database                       OK
cacti.mac_track_ports                              OK
cacti.mac_track_processes                          OK
cacti.mac_track_scan_dates                         OK
cacti.mac_track_scanning_functions                 OK
cacti.mac_track_sites                              OK
cacti.mac_track_temp_ports
note     : The storage engine for the table doesn’t support check
cacti.plugin_color_templates                       OK
cacti.plugin_color_templates_item                  OK
cacti.plugin_config                                OK
cacti.plugin_db_changes
warning  : 2 clients are using or haven’t closed the table properly
status   : OK
cacti.plugin_discover_hosts                        OK
cacti.plugin_discover_template                     OK
cacti.plugin_flowview_devices                      OK
cacti.plugin_flowview_dnscache
note     : The storage engine for the table doesn’t support check
cacti.plugin_flowview_queries                      OK
cacti.plugin_flowview_schedules                    OK
cacti.plugin_hooks                                 OK
cacti.plugin_realms                                OK
cacti.plugin_routerconfigs_accounts                OK
cacti.plugin_routerconfigs_backups                 OK
cacti.plugin_routerconfigs_devices                 OK
cacti.plugin_routerconfigs_devicetypes             OK
cacti.plugin_thold_contacts                        OK
cacti.plugin_thold_log                             OK
cacti.plugin_thold_template_contact                OK
cacti.plugin_thold_threshold_contact               OK
cacti.plugin_update_info                           OK
cacti.plugin_wmi_accounts                          OK
cacti.plugin_wmi_queries                           OK
cacti.poller                                       OK
cacti.poller_command                               OK
cacti.poller_item
warning  : 1 client is using or hasn’t closed the table properly
status   : OK
cacti.poller_output
warning  : Table is marked as crashed
warning  : 1 client is using or hasn’t closed the table properly
error    : Invalid key block position: 107523441122544244  key block size: 1024  file_length: 25600
error    : key delete-link-chain corrupted
error    : Corrupt
cacti.poller_output_boost
note     : The storage engine for the table doesn’t support check
cacti.poller_output_boost_processes
note     : The storage engine for the table doesn’t support check
cacti.poller_output_rt                             OK
cacti.poller_reindex
warning  : 1 client is using or hasn’t closed the table properly
status   : OK
cacti.poller_time                                  OK
cacti.quicktree_graphs                             OK
cacti.reportit_cache_measurands                    OK
cacti.reportit_cache_reports                       OK
cacti.reportit_cache_variables                     OK
cacti.reportit_measurands                          OK
cacti.reportit_presets                             OK
cacti.reportit_recipients                          OK
cacti.reportit_reports                             OK
cacti.reportit_rvars                               OK
cacti.reportit_templates                           OK
cacti.reportit_variables                           OK
cacti.rra                                          OK
cacti.rra_cf                                       OK
cacti.settings
warning  : 1 client is using or hasn’t closed the table properly
status   : 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.superlinks_auth                              OK
cacti.superlinks_pages                             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
cacti.weathermap_auth                              OK
cacti.weathermap_data                              OK
cacti.weathermap_maps                              OK

Repairing tables
cacti.poller_output                                OK

After the repair I ran the poller.php script again with the –-force option and this time I didn’t receive any errors and the graphs were updated again.

Afterwards I noticed that Cacti has a script of its own to repair the database. This script is called repair_database.php and can be found in the directory /var/www/html/cli/.