MySQL slow_log table cannot be modified to innodb engine detailed explanation

background

Getting the slow query log from mysql.slow_log is slow, the table is a csv table and has no index.

I want to add an index to speed up access, but the csv engine cannot add indexes (the csv engine stores text separated by commas), so I can only change the storage engine to add indexes.

mysql.slow_log table can be changed to myisam, but not to innodb

mysql> set global slow_query_log=off;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mysql.slow_log engine=innodb;
ERROR 1579 (HY000): This storage engine cannot be used for log tables"
mysql> alter table mysql.slow_log engine=myisam;
Query OK, 33760 rows affected (0.37 sec)
Records: 33760 Duplicates: 0 Warnings: 0

mysql.general_log cannot be changed to innodb

mysql> alter table mysql.general_log engine=myisam;
Query OK, 242956 rows affected (2.41 sec)
Records: 242956 Duplicates: 0 Warnings: 0

mysql> alter table mysql.general_log engine=innodb;
ERROR 1579 (HY000): This storage engine cannot be used for log tables"

Official Documentation

According to the official documentation, the log table only supports the CSV engine and the MyISAM engine.

Why does it not support the innodb engine?

On what basis do you consider not supporting innodb tables?

Asked a friend

I guess the log table engine consumes a lot of redo and undo resources.

This is not necessary. . . These data are not important. .

Change log table storage engine

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

Archive log table

USE mysql;
DROP TABLE IF EXISTS general_log2;
CREATE TABLE general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;

refer to

Selecting General Query Log and Slow Query Log Output Destinations

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.