cacti 錯誤 ERROR: SQL Assoc Failed!, Error:\'145\'

錯誤描述：打開cacti監控，發現已經運作的轉碼機不産生流量，即cacti監控所顯示已經運作的轉碼機為當機，檢視cacti日志發現error，查資料解決此問題、

cacit監控無資料了，檢視日志：

2014年05月03日 09:05:51 AM - 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年05月03日 09:05:51 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'145', SQL:"DELETE FROM `cacti`.`syslog_incoming` WHERE status=90'

2014年05月03日 09:05:51 AM - 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=90) AS merge'

查mysql日志：

140504 12:20:45 [ERROR] /usr/libexec/mysqld: Table './cacti/syslog_incoming' is marked as crashed and should be repaired

140504 12:22:00 [ERROR] /usr/libexec/mysqld: Table './cacti/syslog_incoming' is marked as crashed and should be repaired

#連接配接損壞表所在的資料庫：

mysql> use cacti;

Database changed

# REPAIR TABLE修複資料表

mysql> REPAIR TABLE poller_item;

+——————-+——–+———-+———-+

| Table             | Op     | Msg_type | Msg_text |

| cacti.poller_item | repair | status   | OK

|

1 row in set (0.02 sec)

mysql>

修複成功。

如果修複失敗，可以嘗試用USE_FRM選項，例：

mysql> REPAIR TABLE poller_item USE_FRM;

由于表數量比較多，使用腳本進行檢查和修複

#!/bin/bash

#This script used by repair tables

mysql_host=localhost

mysql_user=root 

database=cacti

tables=$(mysql -h$mysql_host -u$mysql_user $database -A -Bse "show tables")

for arg in $tables

do

check_status=$(mysql -h$mysql_host -u$mysql_user $database -A -Bse "check table $arg" | awk '{ print $4 }')

if [ "$check_status" = "OK" ]

then

echo "$arg is ok"

else

echo $(mysql -h$mysql_host -u$mysql_user $database -A -Bse "repair table $arg")

fi

echo $(mysql -h$mysql_host -u$mysql_user $database -A -Bse "optimize table $arg")

done

      本文轉自crazy_charles 51CTO部落格，原文連結：http://blog.51cto.com/douya/1405653，如需轉載請自行聯系原作者