CACTI: CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL

Summary:

PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1194', SQL:

Problem or Goal:

Cacti graphs/weathermap stopped polling after power failure on the server.
When you "tail" the "cacti.log" you observe the errors below:

# cd /var/www/html/log
# tail -100 cacti.log

03/16/2018 02:51:02 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1194', SQL:"DELETE FROM plugin_hmib_hrSWRun WHERE host_id IN(SELECT id FROM host WHERE disabled='on' OR host.status=1)'
03/16/2018 02:51:02 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1194', SQL:"INSERT INTO plugin_hmib_hrSWRun_last_seen (host_id, name, total_time)  SELECT DISTINCT host_id, name, 300 AS `total_time`  FROM plugin_hmib_hrSWRun  WHERE host_id=1  ON DUPLICATE KEY UPDATE last_seen=NOW(),total_time=total_time+VALUES(total_time)'
03/16/2018 02:51:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT * FROM poller_item WHERE local_data_id IN (481) ORDER by host_id"
03/16/2018 02:51:03 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) FROM poller_item WHERE (action=2)"
03/16/2018 02:51:03 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) from poller_item WHERE action=2"
03/16/2018 02:51:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT  poller_output_rt.output,  poller_output_rt.time,  poller_output_rt.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  FROM (poller_output_rt,poller_item)  WHERE (poller_output_rt.local_data_id=poller_item.local_data_id  AND poller_output_rt.rrd_name=poller_item.rrd_name)  AND poller_output_rt.poller_id = 7362"
03/16/2018 02:51:07 PM - SYSTEM HMIB STATS: time:5.2000 processes:10 hosts:1
03/16/2018 02:51:15 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT * FROM poller_item WHERE local_data_id IN (481) ORDER by host_id"
03/16/2018 02:51:15 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) FROM poller_item WHERE (action=2)"
03/16/2018 02:51:15 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) from poller_item WHERE action=2"
03/16/2018 02:51:15 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT  poller_output_rt.output,  poller_output_rt.time,  poller_output_rt.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  FROM (poller_output_rt,poller_item)  WHERE (poller_output_rt.local_data_id=poller_item.local_data_id  AND poller_output_rt.rrd_name=poller_item.rrd_name)  AND poller_output_rt.poller_id = 28859"
03/16/2018 02:51:19 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT * FROM poller_item WHERE local_data_id IN (481) ORDER by host_id"
03/16/2018 02:51:19 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) FROM poller_item WHERE (action=2)"
03/16/2018 02:51:19 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) from poller_item WHERE action=2"
03/16/2018 02:51:19 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT  poller_output_rt.output,  poller_output_rt.time,  poller_output_rt.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  FROM (poller_output_rt,poller_item)  WHERE (poller_output_rt.local_data_id=poller_item.local_data_id  AND poller_output_rt.rrd_name=poller_item.rrd_name)  AND poller_output_rt.poller_id = 32133"
03/16/2018 02:51:31 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'145', SQL:"SELECT * FROM poller_item WHERE local_data_id IN (481) ORDER by host_id"
03/16/2018 02:51:31 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) FROM poller_item WHERE (action=2)"
03/16/2018 02:51:31 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'145', SQL:"SELECT count(*) from poller_item WHERE action=2"

Cause:

The errors above point to the cacti MySQL database.

Solution:

Time to repair your cacti database.
Thanks to the cacti developers, there is already a script developed for this purpose.

1. cd /var/www/html/cli - This is my root folder for my cacti installation, this might be different for other installations.

2. Look for the "repair_database.php" script and run it

[root@localhost cli]# ls -ltr
total 196
-rwxr-xr-x. 1 root root  5705 Jun  5  2012 poller_reindex_hosts.php
-rwxr-xr-x. 1 root root  5551 Jun  5  2012 upgrade_database.php
-rwxr-xr-x. 1 root root  8260 Jun  5  2012 structure_rra_paths.php
-rwxr-xr-x. 1 root root  5107 Jun  5  2012 repair_templates.php
-rwxr-xr-x. 1 root root  7963 Jun  5  2012 repair_database.php
-rwxr-xr-x. 1 root root  8778 Jun  5  2012 reorder_data_query.php
-rwxr-xr-x. 1 root root  4496 Jun  5  2012 rebuild_poller_cache.php
-rwxr-xr-x. 1 root root  3444 Jun  5  2012 poller_output_empty.php
-rwxr-xr-x. 1 root root  5350 Jun  5  2012 poller_graphs_reapply_names.php
-rwxr-xr-x. 1 root root  5759 Jun  5  2012 poller_data_sources_reapply_names.php
-rwxr-xr-x. 1 root root  5485 Jun  5  2012 import_template.php
-rwxr-xr-x. 1 root root  6116 Jun  5  2012 host_update_template.php
-rwxr-xr-x. 1 root root  5090 Jun  5  2012 data_template_associate_rra.php
-rwxr-xr-x. 1 root root  3065 Jun  5  2012 copy_user.php
-rwxr-xr-x. 1 root root  4139 Jun  5  2012 convert_innodb.php
-rwxr-xr-x. 1 root root  3370 Jun  5  2012 analyze_database.php
-rwxr-xr-x. 1 root root  9887 Jun  5  2012 add_tree.php
-rwxr-xr-x. 1 root root  6876 Jun  5  2012 add_perms.php
-rwxr-xr-x. 1 root root  5940 Jun  5  2012 add_graph_template.php
-rwxr-xr-x. 1 root root 18738 Jun  5  2012 add_graphs.php
-rwxr-xr-x. 1 root root 11403 Jun  5  2012 add_device.php
-rwxr-xr-x. 1 root root  7372 Jun  5  2012 add_data_query.php
-rwxr-xr-x. 1 root root  3916 Jun  5  2012 plugins.php

3. Run the repair script as below

[root@localhost 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 -> 'data_input' Successful

Repairing Table -> 'data_input_data' Successful

Repairing Table -> 'data_input_fields' Successful

Repairing Table -> 'data_local' Successful

Repairing Table -> 'data_source_stats_daily' Successful

Repairing Table -> 'data_source_stats_hourly' Successful

Repairing Table -> 'data_source_stats_hourly_cache' Successful

Repairing Table -> 'data_source_stats_hourly_last' Successful

Repairing Table -> 'data_source_stats_monthly' Successful

Repairing Table -> 'data_source_stats_weekly' Successful

Repairing Table -> 'data_source_stats_yearly' 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 -> 'mac_track_aggregated_ports' Successful

Repairing Table -> 'mac_track_approved_macs' Successful

Repairing Table -> 'mac_track_device_types' Successful

Repairing Table -> 'mac_track_devices' Successful

Repairing Table -> 'mac_track_interface_graphs' Successful

Repairing Table -> 'mac_track_interfaces' Successful

Repairing Table -> 'mac_track_ip_ranges' Successful

Repairing Table -> 'mac_track_ips' Successful

Repairing Table -> 'mac_track_macauth' Successful

Repairing Table -> 'mac_track_macwatch' Successful

Repairing Table -> 'mac_track_oui_database' Successful

Repairing Table -> 'mac_track_ports' Successful

Repairing Table -> 'mac_track_processes' Successful

Repairing Table -> 'mac_track_scan_dates' Successful

Repairing Table -> 'mac_track_scanning_functions' Successful

Repairing Table -> 'mac_track_sites' Successful

Repairing Table -> 'mac_track_snmp' Successful

Repairing Table -> 'mac_track_snmp_items' Successful

Repairing Table -> 'mac_track_temp_ports' Successful

Repairing Table -> 'mac_track_vlans' Successful

Repairing Table -> 'plugin_aggregate_color_template_items' Successful

Repairing Table -> 'plugin_aggregate_color_templates' Successful

Repairing Table -> 'plugin_aggregate_graph_templates' Successful

Repairing Table -> 'plugin_aggregate_graph_templates_item' Successful

Repairing Table -> 'plugin_aggregate_graphs' Successful

Repairing Table -> 'plugin_aggregate_graphs_graph_item' Successful

Repairing Table -> 'plugin_aggregate_graphs_items' Successful

Repairing Table -> 'plugin_autom8_graph_rule_items' Successful

Repairing Table -> 'plugin_autom8_graph_rules' Successful

Repairing Table -> 'plugin_autom8_match_rule_items' Successful

Repairing Table -> 'plugin_autom8_tree_rule_items' Successful

Repairing Table -> 'plugin_autom8_tree_rules' Successful

Repairing Table -> 'plugin_config' Successful

Repairing Table -> 'plugin_db_changes' Successful

Repairing Table -> 'plugin_discover_hosts' Successful

Repairing Table -> 'plugin_discover_template' Successful

Repairing Table -> 'plugin_flowview_devices' Successful

Repairing Table -> 'plugin_flowview_dnscache' Successful

Repairing Table -> 'plugin_flowview_ports' Successful

Repairing Table -> 'plugin_flowview_queries' Successful

Repairing Table -> 'plugin_flowview_schedules' Successful

Repairing Table -> 'plugin_hmib_hrDevices' Successful

Repairing Table -> 'plugin_hmib_hrProcessor' Successful

Repairing Table -> 'plugin_hmib_hrSWInstalled' Successful

Repairing Table -> 'plugin_hmib_hrSWRun' Successful

Repairing Table -> 'plugin_hmib_hrSWRun_ignore' Successful

Repairing Table -> 'plugin_hmib_hrSWRun_last_seen' Successful

Repairing Table -> 'plugin_hmib_hrStorage' Successful

Repairing Table -> 'plugin_hmib_hrSystem' Successful

Repairing Table -> 'plugin_hmib_hrSystemTypes' Successful

Repairing Table -> 'plugin_hmib_processes' Successful

Repairing Table -> 'plugin_hmib_types' Successful

Repairing Table -> 'plugin_hooks' Successful

Repairing Table -> 'plugin_maint_hosts' Successful

Repairing Table -> 'plugin_maint_schedules' Successful

Repairing Table -> 'plugin_nectar' Successful

Repairing Table -> 'plugin_nectar_items' Successful

Repairing Table -> 'plugin_notification_lists' Successful

Repairing Table -> 'plugin_realms' Successful

Repairing Table -> 'plugin_thold_contacts' Successful

Repairing Table -> 'plugin_thold_host_failed' 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_output_boost' Successful

Repairing Table -> 'poller_output_boost_processes' Successful

Repairing Table -> 'poller_output_rt' 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

Repairing Table -> 'weathermap_auth' Successful

Repairing Table -> 'weathermap_data' Successful

Repairing Table -> 'weathermap_groups' Successful

Repairing Table -> 'weathermap_maps' Successful

Repairing Table -> 'weathermap_settings' Successful

NOTE: Checking for Invalid Cacti Templates

NOTE: 1 Invalid CDEF Item Rows Found in Graph Templates

WARNING: Serious Cacti Template Problems found in your Database.  Using the '--force' option will remove

the invalid records.  However, these changes can be catastrophic to existing data sources.  Therefore, you 

should contact your support organization prior to proceeding with that repair.

Problem Solved?

Yes, cacti graphs and weathermap will be restored