背景 从MySQL.slow_log 获取慢查询日志很慢，该表是csv表，没有索引。 想添加索引来加速访问，而csv引擎不能添加索引（csv引擎存储是以逗号分割的文本来存储的），只能改存储引擎来添
背景
从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 repairedmysql>mysql> check table mysql.slow_log;+----------------+-------+----------+----------+| Table | Op | Msg_type | Msg_text |+----------------+-------+----------+----------+| mysql.slow_log | check | status | OK |+----------------+-------+----------+----------+
能正常访问其它记录
select * from mysql.slow_log limit 1G*************************** 1. row ***************************start_time: 2018-09-08 13:14:51.688722user_host: universe_op[universe_op] @ [127.0.0.1]query_time: 00:00:01.501843lock_time: 00:00:00.000000rows_sent: 0rows_examined: 0db:last_insert_id: 0insert_id: 0server_id: 2112034892sql_text: COMMITthread_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 1mysql>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 1mysql>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值
