1# 2# Testing of slow log query options 3# 4 5set @@log_slow_verbosity=""; 6 7select @@log_slow_filter; 8select @@log_slow_rate_limit; 9select @@log_slow_verbosity; 10show variables like "log_slow%"; 11set @org_slow_query_log= @@global.slow_query_log; 12 13# Some simple test to set log_slow_filter 14set @@log_slow_filter= "filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk,admin"; 15select @@log_slow_filter; 16set @@log_slow_filter="admin,admin"; 17select @@log_slow_filter; 18set @@log_slow_filter=7; 19select @@log_slow_filter; 20 21# Test of wrong values 22--error 1231 23set @@log_slow_filter= "filesort,impossible,impossible2,admin"; 24--error 1231 25set @@log_slow_filter= "filesort, admin"; 26--error 1231 27set @@log_slow_filter= 1<<31; 28select @@log_slow_filter; 29 30# Some simple test to set log_slow_verbosity 31set @@log_slow_verbosity= "query_plan,innodb"; 32select @@log_slow_verbosity; 33set @@log_slow_verbosity=1; 34select @@log_slow_verbosity; 35 36# 37# Check which fields are in slow_log table 38# 39 40show fields from mysql.slow_log; 41 42# 43# Check flush command 44# 45 46flush slow logs; 47 48# MDEV-4206 (empty filter should be no filter) 49set long_query_time=0.1; 50set log_slow_filter=''; 51set global slow_query_log=1; 52set global log_output='TABLE'; 53select sleep(0.5); 54select count(*) FROM mysql.slow_log; 55 56# Reset used variables 57set @@long_query_time=default; 58set global slow_query_log= @org_slow_query_log; 59set @@log_slow_filter=default; 60set @@log_slow_verbosity=default; 61set global log_output= default; 62truncate mysql.slow_log; 63 64--echo # 65--echo # MDEV-18333 Slow_queries count doesn't increase when slow_query_log is turned off 66--echo # 67 68SET SESSION slow_query_log=OFF; 69SET GLOBAL slow_query_log=OFF; 70SET long_query_time=0.1; 71 72--echo # Although this query is disallowed by slow_query_log, it should still increment Slow_queries 73 74SELECT VARIABLE_VALUE INTO @global_slow_queries 75 FROM INFORMATION_SCHEMA.GLOBAL_STATUS 76 WHERE VARIABLE_NAME='SLOW_QUERIES'; 77SELECT sleep(0.2) INTO @tmp FROM DUAL; 78SELECT 79 CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment 80 FROM 81 INFORMATION_SCHEMA.GLOBAL_STATUS 82 WHERE 83 VARIABLE_NAME='SLOW_QUERIES'; 84 85--echo # Although this query is disallowed by log_slow_filter, it should still increment Slow_queries 86 87SET log_slow_filter=filesort; 88SELECT sleep(0.2) INTO @tmp FROM DUAL; 89SELECT VARIABLE_VALUE INTO @global_slow_queries 90 FROM INFORMATION_SCHEMA.GLOBAL_STATUS 91 WHERE VARIABLE_NAME='SLOW_QUERIES'; 92SELECT sleep(0.2) INTO @tmp FROM DUAL; 93SELECT 94 CAST(VARIABLE_VALUE AS UNSIGNED)-@global_slow_queries AS Slow_queries_increment 95 FROM 96 INFORMATION_SCHEMA.GLOBAL_STATUS 97 WHERE 98 VARIABLE_NAME='SLOW_QUERIES'; 99SET log_slow_filter=DEFAULT; 100 101SET @@long_query_time=default; 102SET GLOBAL slow_query_log= @org_slow_query_log; 103