1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
|
SET @org_slow_query_log= @@global.slow_query_log;
SET @org_log_output= @@global.log_output;
SET @org_log_slow_admin_statements= @@global.log_slow_admin_statements;
SET @@GLOBAL.slow_query_log=OFF;
SET @@GLOBAL.log_output='TABLE';
FLUSH SLOW LOGS;
SET @@GLOBAL.slow_query_log=ON;
SET @@GLOBAL.log_slow_admin_statements=ON;
SET @save_dbug = @@debug_dbug;
SET SESSION debug_dbug="+d,simulate_slow_query";
CREATE PROCEDURE show_slow_log()
BEGIN
SELECT CONCAT('[slow] ', sql_text) AS sql_text
FROM mysql.slow_log
WHERE sql_text NOT LIKE '%debug_dbug%';
END
$$
#
# Expect all admin statements in the slow log (ON,DEFAULT)
#
SET @@GLOBAL.log_slow_admin_statements=ON;
SET log_slow_filter=DEFAULT;
TRUNCATE TABLE mysql.slow_log;
CREATE TABLE t1 (a INT);
CREATE INDEX t1a ON t1 (a);
DROP INDEX t1a ON t1;
DROP TABLE t1;
CREATE TABLE t2 (a INT);
ALTER TABLE t2 RENAME t2;
RENAME TABLE t2 TO t3;
DROP TABLE t3;
CREATE TABLE t4 (a INT);
PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t4;
CALL show_slow_log();
sql_text
[slow] TRUNCATE TABLE mysql.slow_log
[slow] CREATE TABLE t1 (a INT)
[slow] CREATE INDEX t1a ON t1 (a)
[slow] DROP INDEX t1a ON t1
[slow] DROP TABLE t1
[slow] CREATE TABLE t2 (a INT)
[slow] ALTER TABLE t2 RENAME t2
[slow] RENAME TABLE t2 TO t3
[slow] DROP TABLE t3
[slow] CREATE TABLE t4 (a INT)
[slow] PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1'
[slow] ALTER TABLE t4 MODIFY a INT DEFAULT 1
[slow] DEALLOCATE PREPARE stmt
[slow] DROP TABLE t4
#
# Expect all admin statements in the slow log (ON,admin)
#
SET @@GLOBAL.log_slow_admin_statements=ON;
SET log_slow_filter=admin;
TRUNCATE TABLE mysql.slow_log;
CREATE TABLE t1 (a INT);
CREATE INDEX t1a ON t1 (a);
DROP INDEX t1a ON t1;
DROP TABLE t1;
CREATE TABLE t2 (a INT);
ALTER TABLE t2 RENAME t2;
RENAME TABLE t2 TO t3;
DROP TABLE t3;
CREATE TABLE t4 (a INT);
PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t4;
CALL show_slow_log();
sql_text
[slow] CREATE INDEX t1a ON t1 (a)
[slow] DROP INDEX t1a ON t1
[slow] ALTER TABLE t2 RENAME t2
[slow] RENAME TABLE t2 TO t3
[slow] ALTER TABLE t4 MODIFY a INT DEFAULT 1
#
# Expect none of admin DDL statements in the slow log (ON,filesort)
#
SET @@GLOBAL.log_slow_admin_statements=ON;
SET log_slow_filter=filesort;
TRUNCATE TABLE mysql.slow_log;
CREATE TABLE t1 (a INT);
CREATE INDEX t1a ON t1 (a);
DROP INDEX t1a ON t1;
DROP TABLE t1;
CREATE TABLE t2 (a INT);
ALTER TABLE t2 RENAME t2;
RENAME TABLE t2 TO t3;
DROP TABLE t3;
CREATE TABLE t4 (a INT);
PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t4;
CALL show_slow_log();
sql_text
#
# Expect none of admin statements in the slow log (OFF,DEFAULT)
#
SET @@GLOBAL.log_slow_admin_statements=OFF;
SET log_slow_filter=DEFAULT;
TRUNCATE TABLE mysql.slow_log;
CREATE TABLE t1 (a INT);
CREATE INDEX t1a ON t1 (a);
DROP INDEX t1a ON t1;
DROP TABLE t1;
CREATE TABLE t2 (a INT);
ALTER TABLE t2 RENAME t2;
RENAME TABLE t2 TO t3;
DROP TABLE t3;
CREATE TABLE t4 (a INT);
PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t4;
CALL show_slow_log();
sql_text
[slow] TRUNCATE TABLE mysql.slow_log
[slow] CREATE TABLE t1 (a INT)
[slow] DROP TABLE t1
[slow] CREATE TABLE t2 (a INT)
[slow] DROP TABLE t3
[slow] CREATE TABLE t4 (a INT)
[slow] PREPARE stmt FROM 'ALTER TABLE t4 MODIFY a INT DEFAULT 1'
[slow] DEALLOCATE PREPARE stmt
[slow] DROP TABLE t4
#
# Clean up
#
SET SESSION debug_dbug=@save_dbug;
TRUNCATE mysql.slow_log;
SET @@global.slow_query_log= @org_slow_query_log;
SET @@global.log_output= @org_log_output;
SET @@global.log_slow_admin_statements= @org_log_slow_admin_statements;
DROP PROCEDURE show_slow_log;
|