HQY 一个和谐有爱的空间

最近查看Cacti日志，出现大量红色告警，并且每五分钟出现1次，虽然不影响流量的查看，但是肯定会有问题。

错误日志如下：

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog_hosts` (host) (SELECT DISTINCT host FROM `cacti`.`syslog_incoming`) ON DUPLICATE KEY UPDATE host=VALUES(host), last_updated=NOW()'

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"DELETE FROM `cacti`.`syslog_statistics` WHERE insert_time<'2014-11-05 13:00:16''

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog` (logtime, priority_id, facility_id, host_id, message) SELECT TIMESTAMP(`date`, `time`), priority_id, facility_id, host_id, message FROM (SELECT date, time, priority_id, facility_id, host_id, message FROM syslog_incoming AS si INNER JOIN syslog_facilities AS sf ON sf.facility=si.facility INNER JOIN syslog_priorities AS sp ON sp.priority=si.priority INNER JOIN syslog_hosts AS sh ON sh.host=si.host WHERE status=5) AS merge'

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog_statistics` (host_id, facility_id, priority_id, insert_time, records) SELECT host_id, facility_id, priority_id, NOW(), sum(records) AS records FROM (SELECT host_id, facility_id, priority_id, count(*) AS records FROM syslog_incoming AS si INNER JOIN syslog_facilities AS sf ON sf.facility=si.facility INNER JOIN syslog_priorities AS sp ON sp.priority=si.priority INNER JOIN syslog_hosts AS sh ON sh.host=si.host WHERE status=5 GROUP BY host_id, priority_id, facility_id) AS merge GROUP BY host_id, priority_id, facility_id'

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog_host_facilities` (host_id, facility_id) SELECT host_id, facility_id FROM ((SELECT DISTINCT host, facility FROM `cacti`.`syslog_incoming`) AS s INNER JOIN `cacti`.`syslog_hosts` AS sh ON s.host=sh.host INNER JOIN `cacti`.`syslog_facilities` AS sf ON sf.facility=s.facility) ON DUPLICATE KEY UPDATE host_id=VALUES(host_id), last_updated=NOW()'

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog_hosts` (host) SELECT DISTINCT host FROM `cacti`.`syslog_incoming` ON DUPLICATE KEY UPDATE host=VALUES(host), last_updated=NOW()'

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog_priorities` (priority) SELECT DISTINCT priority FROM `cacti`.`syslog_incoming` ON DUPLICATE KEY UPDATE priority=VALUES(priority), last_updated=NOW()'

2014年12月05日 13:00:16 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"INSERT INTO `cacti`.`syslog_facilities` (facility) SELECT DISTINCT facility FROM `cacti`.`syslog_incoming` ON DUPLICATE KEY UPDATE facility=VALUES(facility), last_updated=NOW()'

错误信息显示：

数据库中已经存在这台主机，再插入就失败了。

解决办法：

1、登入后台服务器，进入数据库

    #mysql（我的后台数据库没有密码，直接进入）

2、查看数据库

    mysql>show databases;

   +--------------------+
| Database           |
+--------------------+
| information_schema |
| cacti              |
| mysql              |
+--------------------+
4 rows in set (0.00 sec)

3、进入cacti表

mysql> use cacti

4、显示数据库中的表

mysql> show tables；

+---------------------------------------+
| Tables_in_cacti                       |
+---------------------------------------+
| cdef                                  |
| cdef_items                            |
| colors                                |
| data_input                            |
| data_input_data                       |
| data_input_fields                     |
| data_local                            |
| data_template                         |
| data_template_data                    |
| data_template_data_rra                |
| data_template_rrd                     |
| graph_local                           |
| graph_template_input                  |
| graph_template_input_defs             |
| graph_templates                       |
| graph_templates_gprint                |
| graph_templates_graph                 |
| graph_templates_item                  |
| graph_tree                            |
| graph_tree_items                      |
| host                                  |
| host_graph                            |
| host_snmp_cache                       |
| host_snmp_query                       |
| host_template                         |
| host_template_graph                   |
| host_template_snmp_query              |
| plugin_aggregate_color_template_items |
| plugin_aggregate_color_templates      |
| plugin_config                         |
| plugin_db_changes                     |
| plugin_hooks                          |
| plugin_realms                         |
| plugin_thold_contacts                 |
| plugin_thold_log                      |
| plugin_thold_template_contact         |
| plugin_thold_threshold_contact        |
| poller                                |
| poller_command                        |
| poller_item                           |
| poller_output                         |
| poller_output_rt                      |
| poller_reindex                        |
| poller_time                           |
| rra                                   |
| rra_cf                                |
| settings                              |
| settings_graphs                       |
| settings_tree                         |
| snmp_query                            |
| snmp_query_graph                      |
| snmp_query_graph_rrd                  |
| snmp_query_graph_rrd_sv               |
| snmp_query_graph_sv                   |
| syslog                                |
| syslog_alarm_log                      |
| syslog_alert                          |
| syslog_facilities                     |
| syslog_host_facilities                |
| syslog_hosts                          |
| syslog_incoming                       |
| syslog_logs                           |
| syslog_priorities                     |
| syslog_remove                         |
| syslog_removed                        |
| syslog_reports                        |
| syslog_statistics                     |
| thold_data                            |
| thold_template                        |
| user_auth                             |
| user_auth_perms                       |
| user_auth_realm                       |
| user_log                              |
| version                               |
| weathermap_auth                       |
| weathermap_data                       |
| weathermap_groups                     |
| weathermap_maps                       |
| weathermap_settings                   |
+---------------------------------------+
79 rows in set (0.00 sec)

 

5、访问有问题的表

mysql> describe syslog_hosts;
ERROR 145 (HY000): Table './cacti/syslog_hosts' is marked as crashed and should be repaired

    提示该表损坏需要修复。

6、修复数据库中的所有表

#mysqlcheck -A -o -r  -p

7、修复完成后，再次进入数据库访问有错误的表

mysql> describe syslog_hosts;
+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| host_id      | int(10) unsigned | NO   | MUL | NULL              | auto_increment              |
| host         | varchar(128)     | NO   | PRI | NULL              |                             |
| last_updated | timestamp        | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

可以正常访问了，错误日志也不在出现了。

推荐本站淘宝优惠价购买喜欢的宝贝:

本文链接：https://hqyman.cn/post/1269.html 非本站原创文章欢迎转载，原创文章需保留本站地址！

休息一下~~