收到一台MySQL服务器告警：MySQL的错误日志出现下面错误信息：
[ERROR] Failed to write to mysql.slow_log
mysql> show variables like '%slow%';
+---------------------------+----------------+
| Variable_name | Value |
+---------------------------+----------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | slow_query.log |
+---------------------------+----------------+
5 rows in set (0.00 sec)
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.01 sec)
mysql> select count(*) from mysql.slow_log;
ERROR 1194 (HY000): Table 'slow_log' is marked as crashed and should be repaired
mysql>
查询表mysql.slow_log时出现“ERROR 1194 (HY000): Table ‘slow_log’ is marked as crashed and should be repaired” 错误信息。也就是说表slow_log损坏了。查看这个表的相关文件和表的定义
# ls *slow_log*
slow_log.CSM slow_log.CSV slow_log.frm
# du -sh *slow_log*
4.0K slow_log.CSM
1.7G slow_log.CSV
12K slow_log.frm
mysql> show create table slow_log;
+----------+----------------------------------------------------------------+
| Table | Create Table
+----------+----------------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumblob NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
这个表的存储引擎是CSV类型，所以没法使用myisamchk命令修复表。
使用check table命令检查表是否有错误，如下所示：
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> check table slow_log;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| mysql.slow_log | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (2 min 9.68 sec)
mysql>
执行下面有些SQL正常，但是有些SQL语句依然报ERROR 1194 (HY000): Table ‘slow_log’ is marked as crashed and should be repaired错误
mysql> select * from slow_log limit 1,10;
--上面SQL有正常输出，下面SQL报错
mysql> select * from slow_log where start_time > '2021-10-22 08:00:00';
ERROR 1194 (HY000): Table 'slow_log' is marked as crashed and should be repaired
REPAIR TABLE
REPAIR TABLE命令是最简单的方法，但是只有当表被标记为崩溃的时才能运行命令。
The REPAIR TABLE method is only applicable to MyISAM, ARCHIVE, and CSV tables. You can use REPAIR TABLE if the table checking operation indicates that there is a corruption or that an upgrade is required.
mysql> repair table slow_log;
+----------------+--------+----------+------------------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+------------------------------------------------------------------------------------------------------+
| mysql.slow_log | repair | Warning | Incorrect integer value: 'xxx[xxx] @ [192.168.xxx.xxx]' for column 'thread_id' at row 29552 |
| mysql.slow_log | repair | status | OK |
+----------------+--------+----------+------------------------------------------------------------------------------------------------------+
2 rows in set (14.13 sec)
mysql>
mysql> check table slow_log;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| mysql.slow_log | check | status | OK |
+----------------+-------+----------+----------+
1 row in set (9.60 sec)
mysql>
报错的原因是因为“Incorrect integer value: ‘xxx[xxx] @ [192.168.xxx.xxx]’ for column ‘thread_id’ at row 29552”。
修复后测试验证，发现已经正常
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.00 sec)
mysql> select * from slow_log where start_time > '2021-10-22 08:00:00';
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
| 2021-10-22 08:36:43.334778 | root[root] @ localhost [] | 00:00:05.000219 | 00:00:00.000000 | 1 | 0 | mysql | 0 | 0 | 12311 | select sleep(5) | 289121 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+
1 row in set (0.00 sec)
mysql>
原文始发于微信公众号（DBA闲思杂想录）：Table 'slow_log' is marked as crashed and should be repaired
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/227413.html
