MySQL slow_log日誌表出現非法欄位值

背景

從mysql.slow_log 獲取慢查詢日誌很慢，該表是csv表，沒有索引。
想添加索引來加速訪問，而csv引擎不能添加索引（csv引擎存儲是以逗號分割的文本來存儲的），只能改存儲引擎來添加索引了

MySQL 中日誌表slow_log和general_log主要特點

• 日誌表只能是CSV和MYISAM存儲引擎
• 更改日誌表的存儲引擎必須先停止使用該日誌表
• 日誌表中的數據不記錄binlog
• 鎖表語句FTWRL和lock tables、read_only對日誌表無效
• 用戶不能對日誌表進行DML操作，只能被mysql自己寫入數據

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'

ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ERROR 1292 (22007) at line 1: Incorrect time value: '838:59:59.305999' for column 'query_time' at row 320264n

slow_log.csv 文件

"2019-03-05 15:29:56.102276","xxxx[xxxx] @ [10.230.123.134]","838:59:59.305999","00:00:00.000000",0,0,"",0,0,2112034892,"Binlog Dump GTID",413317

無法訪問該記錄

mysql> select * from mysql.slow_log where query_time like '838:59:59%';
ERROR 1194 (HY000): Table 'slow_log' is marked as crashed and should be repaired
mysql>
mysql> check table mysql.slow_log;
+----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| mysql.slow_log | check | status | OK |
+----------------+-------+----------+----------+

能正常訪問其它記錄

select * from mysql.slow_log limit 1\G
*************************** 1. row ***************************
start_time: 2018-09-08 13:14:51.688722
user_host: universe_op[universe_op] @ [127.0.0.1]
query_time: 00:00:01.501843
lock_time: 00:00:00.000000
rows_sent: 0
rows_examined: 0
db:
last_insert_id: 0
insert_id: 0
server_id: 2112034892
sql_text: COMMIT
thread_id: 6

嘗試復現
mysql> create table test_time6(time time(6) not null) engine=csv;
Query OK, 0 rows affected (0.23 sec)

mysql>
mysql>
mysql> insert into test_time6 values('838:59:59.305999');
ERROR 1292 (22007): Incorrect time value: '838:59:59.305999' for column 'time' at row 1
mysql>
mysql>
mysql>

無法復現，提示插入的該time值非法。mysql自身是怎麼把該值插入slow_log表中的了？sql_mode也沒有修改過

修改sql_mode後嘗試復現
mysql> show global variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

mysql>
mysql> set global sql_mode ="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> insert into test_time6 values('838:59:59.305999');
ERROR 1292 (22007): Incorrect time value: '838:59:59.305999' for column 'time' at row 1
mysql>
mysql> show global variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改sql_mode，去掉STRICT_TRANS_TABLES後，也無法插入該非法time值