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_disable_statements= @@global.log_slow_disabled_statements; 5SET @@session.log_slow_verbosity="explain,innodb,query_plan"; 6CREATE TABLE t1 (i INT PRIMARY KEY AUTO_INCREMENT, j blob) ENGINE=MyISAM; 7insert into t1 (j) values ('a'),('b'),('c'),('d'); 8create table t2 (a int auto_increment primary key) engine=myisam; 9SET GLOBAL log_output = 'file,table'; 10SET GLOBAL slow_query_log = on; 11CREATE PROCEDURE slow() 12BEGIN 13SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'; 14SELECT count(*) from t1 where j>'a'; 15insert into t2 () values(); 16END 17$ 18CREATE PROCEDURE slow2() 19BEGIN 20SELECT j,count(*) from t1 group by j; 21create temporary table t3 (a int); 22alter table t3 add column (b int); 23call slow(); 24drop temporary table t3; 25SELECT j,count(*)+1 from t1 group by j,i; 26END 27$ 28SET SESSION long_query_time = 0; 29SELECT @@log_slow_disabled_statements; 30@@log_slow_disabled_statements 31sp 32TRUNCATE TABLE mysql.slow_log; 33ALTER TABLE t1 add column extra int; 34CALL slow2(); 35j count(*) 36a 1 37b 1 38c 1 39d 1 40count(if(sleep(1) >= 0,0,NULL)) 412 42count(*) 433 44j count(*)+1 45a 2 46b 2 47c 2 48d 2 49SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2'; 50count(if(sleep(1) >= 0,0,NULL)) 512 52--> 53SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; 54sql_text 55TRUNCATE TABLE mysql.slow_log 56ALTER TABLE t1 add column extra int 57CALL slow2() 58SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2' 59<-- 60SET SESSION log_slow_disabled_statements="call,admin"; 61TRUNCATE TABLE mysql.slow_log; 62ALTER TABLE t1 add column extra2 int; 63CALL slow2(); 64j count(*) 65a 1 66b 1 67c 1 68d 1 69count(if(sleep(1) >= 0,0,NULL)) 702 71count(*) 723 73j count(*)+1 74a 2 75b 2 76c 2 77d 2 78SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3'; 79count(if(sleep(1) >= 0,0,NULL)) 802 81--> 82SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; 83sql_text 84TRUNCATE TABLE mysql.slow_log 85SELECT j,count(*) from t1 group by j 86create temporary table t3 (a int) 87SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b' 88SELECT count(*) from t1 where j>'a' 89insert into t2 () values() 90drop temporary table t3 91SELECT j,count(*)+1 from t1 group by j,i 92SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3' 93<-- 94SET SESSION log_slow_disabled_statements=""; 95TRUNCATE TABLE mysql.slow_log; 96ALTER TABLE t1 add column extra3 int; 97CALL slow2(); 98j count(*) 99a 1 100b 1 101c 1 102d 1 103count(if(sleep(1) >= 0,0,NULL)) 1042 105count(*) 1063 107j count(*)+1 108a 2 109b 2 110c 2 111d 2 112SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4'; 113count(if(sleep(1) >= 0,0,NULL)) 1142 115--> 116SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; 117sql_text 118TRUNCATE TABLE mysql.slow_log 119ALTER TABLE t1 add column extra3 int 120SELECT j,count(*) from t1 group by j 121create temporary table t3 (a int) 122alter table t3 add column (b int) 123SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b' 124SELECT count(*) from t1 where j>'a' 125insert into t2 () values() 126call slow() 127drop temporary table t3 128SELECT j,count(*)+1 from t1 group by j,i 129CALL slow2() 130SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4' 131<-- 132SET SESSION log_slow_disabled_statements="call,admin,slave,sp"; 133TRUNCATE TABLE mysql.slow_log; 134ALTER TABLE t1 add column extra4 int; 135CALL slow2(); 136j count(*) 137a 1 138b 1 139c 1 140d 1 141count(if(sleep(1) >= 0,0,NULL)) 1422 143count(*) 1443 145j count(*)+1 146a 2 147b 2 148c 2 149d 2 150SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5'; 151count(if(sleep(1) >= 0,0,NULL)) 1522 153--> 154SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; 155sql_text 156TRUNCATE TABLE mysql.slow_log 157SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5' 158<-- 159DROP TABLE t1,t2; 160DROP PROCEDURE slow; 161DROP PROCEDURE slow2; 162SET @@session.long_query_time= @old_long_query_time; 163TRUNCATE TABLE mysql.slow_log; 164SET @@global.log_output= @old_log_output; 165SET @@global.slow_query_log= @old_slow_query_log; 166SET @@global.log_slow_disabled_statements= @old_log_slow_disable_statements; 167