1SET @old_log_output= @@global.log_output; 2SET @old_slow_query_log= @@global.slow_query_log; 3SET @old_long_query_time= @@session.long_query_time; 4SET @old_log_slow_admin_statements= @@global.log_slow_admin_statements; 5USE test; 6CREATE TABLE log_slow_admin_statements ( 7i INT PRIMARY KEY AUTO_INCREMENT, 8j VARCHAR(255) 9) ENGINE=InnoDB; 10SET GLOBAL log_output = 'file,table'; 11SET GLOBAL slow_query_log = on; 12SET SESSION long_query_time = 0; 13SET GLOBAL log_slow_admin_statements = off; 14ALTER TABLE log_slow_admin_statements ADD COLUMN k INT DEFAULT 17; 15CREATE PROCEDURE add_rows() 16BEGIN 17DECLARE count INT; 18SET count = 1; 19INSERT INTO log_slow_admin_statements(j) values (REPEAT('A', 255)); 20WHILE count <= 15 DO 21INSERT INTO log_slow_admin_statements(j) SELECT j FROM log_slow_admin_statements; 22SET count = count + 1; 23END WHILE; 24END 25$ 26CALL add_rows(); 27ANALYZE TABLE log_slow_admin_statements; 28Table Op Msg_type Msg_text 29test.log_slow_admin_statements analyze status OK 30OPTIMIZE TABLE log_slow_admin_statements; 31Table Op Msg_type Msg_text 32test.log_slow_admin_statements optimize note Table does not support optimize, doing recreate + analyze instead 33test.log_slow_admin_statements optimize status OK 34CHECK TABLE log_slow_admin_statements EXTENDED; 35Table Op Msg_type Msg_text 36test.log_slow_admin_statements check status OK 37Administrative sql statements must not be logged 38SELECT count(sql_text)= 0 FROM mysql.slow_log 39WHERE sql_text LIKE '%TABLE log_slow_admin_statements%'; 40count(sql_text)= 0 411 42SET GLOBAL log_slow_admin_statements = on; 43DROP TABLE log_slow_admin_statements; 44CREATE TABLE log_slow_admin_statements ( 45i INT PRIMARY KEY AUTO_INCREMENT, 46j VARCHAR(255) 47) ENGINE=InnoDB; 48CALL add_rows(); 49ANALYZE TABLE log_slow_admin_statements; 50Table Op Msg_type Msg_text 51test.log_slow_admin_statements analyze status OK 52OPTIMIZE TABLE log_slow_admin_statements; 53Table Op Msg_type Msg_text 54test.log_slow_admin_statements optimize note Table does not support optimize, doing recreate + analyze instead 55test.log_slow_admin_statements optimize status OK 56CHECK TABLE log_slow_admin_statements EXTENDED; 57Table Op Msg_type Msg_text 58test.log_slow_admin_statements check status OK 59At least one administrativ sql statement must be logged in slow query log 60SELECT count(sql_text)> 0 FROM mysql.slow_log 61WHERE sql_text LIKE '%TABLE log_slow_admin_statements%'; 62count(sql_text)> 0 631 64SET @@global.log_output= @old_log_output; 65SET @@global.slow_query_log= @old_slow_query_log; 66SET @@session.long_query_time= @old_long_query_time; 67SET @@global.log_slow_admin_statements= @old_log_slow_admin_statements; 68DROP PROCEDURE add_rows; 69DROP TABLE log_slow_admin_statements; 70TRUNCATE TABLE mysql.slow_log; 71