diff options
Diffstat (limited to 'mysql-test')
24 files changed, 1065 insertions, 81 deletions
diff --git a/mysql-test/include/check-warnings.test b/mysql-test/include/check-warnings.test index b2f73c32e35..be347ba46ec 100644 --- a/mysql-test/include/check-warnings.test +++ b/mysql-test/include/check-warnings.test @@ -36,7 +36,7 @@ create temporary table error_log ( let $log_error= $MTR_LOG_ERROR; let $log_warning= $log_error.warnings; -# Try tload the warnings into a temporary table, +# Try to load the warnings into a temporary table, # it might fail with error saying "The MySQL server is # running with the --secure-file-priv" in which case # an attempt to load the file using LOAD DATA LOCAL is made diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 510bb929d84..47b8640c556 100644 --- a/mysql-test/r/log_slow.result +++ b/mysql-test/r/log_slow.result @@ -1,6 +1,6 @@ select @@log_slow_filter; @@log_slow_filter -admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk select @@log_slow_rate_limit; @@log_slow_rate_limit 1 @@ -10,7 +10,8 @@ select @@log_slow_verbosity; show variables like "log_slow%"; Variable_name Value log_slow_admin_statements ON -log_slow_filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +log_slow_disabled_statements sp +log_slow_filter admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk log_slow_rate_limit 1 log_slow_slave_statements ON log_slow_verbosity diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 45e9a670eb3..71397f6cc84 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -380,6 +380,9 @@ The following options may be given as the first argument: ALWAYS use row-based binary logging, the security issues do not exist and the binary logging cannot break, so you can safely set this to TRUE + --log-disabled-statements=name + Don't log certain types of statements to general log. Any + combination of: slave, sp --log-error[=name] Log errors to file (instead of stdout). If file name is not specified then 'datadir'/'log-basename'.err or the 'pid-file' path with extension .err is used @@ -388,7 +391,8 @@ The following options may be given as the first argument: FILE, TABLE --log-queries-not-using-indexes Log queries that are executed without benefit of any - index to the slow log if it is open + index to the slow log if it is open. Same as + log_slow_filter='not_using_index' --log-short-format Don't log extra information to update and slow-query logs. --log-slave-updates Tells the slave to log the updates from the slave thread @@ -396,12 +400,20 @@ The following options may be given as the first argument: plan to daisy-chain the slaves --log-slow-admin-statements Log slow OPTIMIZE, ANALYZE, ALTER and other - administrative statements to the slow log if it is open. - (Defaults to on; use --skip-log-slow-admin-statements to disable.) + administrative statements to the slow log if it is open. + Resets or sets the option 'admin' in + log_slow_disabled_statements + --log-slow-disabled-statements=name + Don't log certain types of statements to slow log. Any + combination of: admin, call, slave, sp --log-slow-filter=name - Log only certain types of queries. Any combination of: - admin, filesort, filesort_on_disk, full_join, full_scan, - query_cache, query_cache_miss, tmp_table, + Log only certain types of queries to the slow log. If + variable empty alll kind of queries are logged. All + types are bound by slow_query_time, except + 'not_using_index' which is always logged if enabled. Any + combination of: admin, filesort, filesort_on_disk, + filsort_priority_queue, full_join, full_scan, + not_using_index, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk --log-slow-rate-limit=# Write to slow log every #th slow query. Set to 1 to log @@ -409,8 +421,8 @@ The following options may be given as the first argument: the performance impact of slow logging --log-slow-slave-statements Log slow statements executed by slave thread to the slow - log if it is open. - (Defaults to on; use --skip-log-slow-slave-statements to disable.) + log if it is open. Resets or sets the option 'slave' in + log_slow_disabled_statements --log-slow-verbosity=name Verbosity level for the slow log. Any combination of: innodb, query_plan, explain @@ -1320,6 +1332,7 @@ log-bin-compress FALSE log-bin-compress-min-len 256 log-bin-index (No default value) log-bin-trust-function-creators FALSE +log-disabled-statements sp log-error log-isam myisam.log log-output FILE @@ -1327,7 +1340,8 @@ log-queries-not-using-indexes FALSE log-short-format FALSE log-slave-updates FALSE log-slow-admin-statements TRUE -log-slow-filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +log-slow-disabled-statements sp +log-slow-filter admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk log-slow-rate-limit 1 log-slow-slave-statements TRUE log-slow-verbosity diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result index 406ca99a5ea..11d8c6f931f 100644 --- a/mysql-test/r/set_statement.result +++ b/mysql-test/r/set_statement.result @@ -1047,7 +1047,7 @@ set statement long_query_time=default for select @@long_query_time; 10.000000 set statement log_slow_filter=default for select @@log_slow_filter; @@log_slow_filter -admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk set statement log_slow_verbosity=default for select @@log_slow_verbosity; @@log_slow_verbosity diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index bf4627f6f68..38a7bd8e1c2 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1236,7 +1236,7 @@ select 1 from information_schema.tables limit 1; show status like 'slow_queries'; Variable_name Value Slow_queries 1 -set global log_queries_not_using_indexes=OFF; +set session log_queries_not_using_indexes=OFF; show variables like "log_queries_not_using_indexes"; Variable_name Value log_queries_not_using_indexes OFF @@ -1246,7 +1246,7 @@ select 1 from information_schema.tables limit 1; show status like 'slow_queries'; Variable_name Value Slow_queries 1 -set global log_queries_not_using_indexes=ON; +set session log_queries_not_using_indexes=ON; show variables like "log_queries_not_using_indexes"; Variable_name Value log_queries_not_using_indexes ON diff --git a/mysql-test/suite/sys_vars/r/log_disabled_statements_basic.result b/mysql-test/suite/sys_vars/r/log_disabled_statements_basic.result new file mode 100644 index 00000000000..72bc8449f13 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/log_disabled_statements_basic.result @@ -0,0 +1,60 @@ +SET @start_global_value = @@global.log_disabled_statements; +select @@global.log_disabled_statements; +@@global.log_disabled_statements +sp +select @@session.log_disabled_statements; +@@session.log_disabled_statements +sp +show global variables like 'log_disabled_statements'; +Variable_name Value +log_disabled_statements sp +show session variables like 'log_disabled_statements'; +Variable_name Value +log_disabled_statements sp +select * from information_schema.global_variables where variable_name='log_disabled_statements'; +VARIABLE_NAME VARIABLE_VALUE +LOG_DISABLED_STATEMENTS sp +select * from information_schema.session_variables where variable_name='log_disabled_statements'; +VARIABLE_NAME VARIABLE_VALUE +LOG_DISABLED_STATEMENTS sp +set global log_disabled_statements=1; +select @@global.log_disabled_statements; +@@global.log_disabled_statements +slave +set session log_disabled_statements=1; +select @@session.log_disabled_statements; +@@session.log_disabled_statements +slave +set session log_disabled_statements=1; +select @@session.log_disabled_statements; +@@session.log_disabled_statements +slave +set session log_disabled_statements=2; +select @@session.log_disabled_statements; +@@session.log_disabled_statements +sp +set session log_disabled_statements='slave'; +select @@session.log_disabled_statements; +@@session.log_disabled_statements +slave +set session log_disabled_statements='sp'; +select @@session.log_disabled_statements; +@@session.log_disabled_statements +sp +set session log_disabled_statements='slave,sp'; +select @@session.log_disabled_statements; +@@session.log_disabled_statements +slave,sp +set session log_disabled_statements=''; +select @@session.log_disabled_statements; +@@session.log_disabled_statements + +set session log_disabled_statements=1.1; +ERROR 42000: Incorrect argument type to variable 'log_disabled_statements' +set session log_disabled_statements=1e1; +ERROR 42000: Incorrect argument type to variable 'log_disabled_statements' +set session log_disabled_statements="foo"; +ERROR 42000: Variable 'log_disabled_statements' can't be set to the value of 'foo' +set session log_disabled_statements=1024; +ERROR 42000: Variable 'log_disabled_statements' can't be set to the value of '1024' +SET @@global.log_disabled_statements = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/log_disabled_statements_func.result b/mysql-test/suite/sys_vars/r/log_disabled_statements_func.result new file mode 100644 index 00000000000..4cfb2adbaac --- /dev/null +++ b/mysql-test/suite/sys_vars/r/log_disabled_statements_func.result @@ -0,0 +1,82 @@ +SET @old_log_output= @@global.log_output; +SET @old_log_disable_statements= @@global.log_disabled_statements; +CREATE TABLE t1 (i INT PRIMARY KEY AUTO_INCREMENT, j VARCHAR(255) ) ENGINE=MyISAM; +insert into t1 (j) values ('a'),('b'),('c'),('d'); +SET GLOBAL log_output = 'file,table'; +CREATE PROCEDURE slow() +BEGIN +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'; +SELECT count(*) from t1 where j>'a'; +END +$ +select @@log_disabled_statements; +@@log_disabled_statements +sp +TRUNCATE TABLE mysql.general_log; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CALL slow(); +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT argument FROM mysql.general_log where command_type <> "Close stmt" and command_type <> "Prepare" and argument not like "%general_log%"; +argument +check table t1 +CALL slow() +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2' +<-- +TRUNCATE TABLE mysql.general_log; +SET SESSION log_disabled_statements=""; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CALL slow(); +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT argument FROM mysql.general_log where command_type <> "Close stmt" and command_type <> "Prepare" and argument not like "%general_log%"; +argument +SET SESSION log_disabled_statements="" +check table t1 +CALL slow() +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b' +SELECT count(*) from t1 where j>'a' +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3' +<-- +TRUNCATE TABLE mysql.general_log; +SET SESSION log_disabled_statements="sp,slave"; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CALL slow(); +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT argument FROM mysql.general_log where command_type <> "Close stmt" and command_type <> "Prepare" and argument not like "%general_log%"; +argument +SET SESSION log_disabled_statements="sp,slave" +check table t1 +CALL slow() +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4' +<-- +DROP TABLE t1; +DROP PROCEDURE slow; +TRUNCATE TABLE mysql.general_log; +SET @@global.log_output= @old_log_output; +SET @@global.log_disabled_statements= @old_log_disable_statements; diff --git a/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result b/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result index fcb5abbcf91..8952caac8b0 100644 --- a/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result +++ b/mysql-test/suite/sys_vars/r/log_queries_not_using_indexes_basic.result @@ -1,4 +1,9 @@ SET @start_value= @@global.log_queries_not_using_indexes; +SET @filter_start_value= @@global.log_slow_filter; +SET @@global.log_queries_not_using_indexes= TRUE; +SET @@session.log_queries_not_using_indexes= TRUE; +SET @@global.log_queries_not_using_indexes= FALSE; +SET @@session.log_queries_not_using_indexes= FALSE; SET @@global.log_queries_not_using_indexes= DEFAULT; SELECT @@global.log_queries_not_using_indexes; @@global.log_queries_not_using_indexes @@ -61,16 +66,41 @@ SET @@global.log_queries_not_using_indexes= @badvar; ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'true' SET GLOBAL log_queries_not_using_indexes= 'DEFAULT'; ERROR 42000: Variable 'log_queries_not_using_indexes' can't be set to the value of 'DEFAULT' -SET log_queries_not_using_indexes= TRUE; -ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL -SET SESSION log_queries_not_using_indexes= TRUE; -ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL -SET @@session.log_queries_not_using_indexes= TRUE; -ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL -SET LOCAL log_queries_not_using_indexes= TRUE; -ERROR HY000: Variable 'log_queries_not_using_indexes' is a GLOBAL variable and should be set with SET GLOBAL SET @@global log_queries_not_using_indexes= TRUE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'log_queries_not_using_indexes= TRUE' at line 1 SET @@SESSION log_queries_not_using_indexes= TRUE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'log_queries_not_using_indexes= TRUE' at line 1 +SET @@SESSION.log_queries_not_using_indexes= TRUE; +select @@SESSION.log_slow_filter; +@@SESSION.log_slow_filter +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,not_using_index,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +SET @@SESSION.log_queries_not_using_indexes= FALSE; +select @@SESSION.log_slow_filter; +@@SESSION.log_slow_filter +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +SET @@GLOBAL.log_queries_not_using_indexes= TRUE; +select @@GLOBAL.log_slow_filter; +@@GLOBAL.log_slow_filter +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,not_using_index,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +SET @@GLOBAL.log_queries_not_using_indexes= FALSE; +select @@GLOBAL.log_slow_filter; +@@GLOBAL.log_slow_filter +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +SET @@SESSION.log_slow_filter=""; +select @@SESSION.log_queries_not_using_indexes; +@@SESSION.log_queries_not_using_indexes +0 +SET @@SESSION.log_slow_filter="not_using_index"; +select @@SESSION.log_queries_not_using_indexes; +@@SESSION.log_queries_not_using_indexes +1 +SET @@GLOBAL.log_slow_filter=""; +select @@GLOBAL.log_queries_not_using_indexes; +@@GLOBAL.log_queries_not_using_indexes +0 +SET @@GLOBAL.log_slow_filter="not_using_index"; +select @@GLOBAL.log_queries_not_using_indexes; +@@GLOBAL.log_queries_not_using_indexes +1 SET @@global.log_queries_not_using_indexes= @start_value; +SET @@global.log_slow_filter= @filter_start_value; diff --git a/mysql-test/suite/sys_vars/r/log_result_errors_basic.result b/mysql-test/suite/sys_vars/r/log_result_errors_basic.result new file mode 100644 index 00000000000..5d3696901a6 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/log_result_errors_basic.result @@ -0,0 +1,148 @@ +SET @start_global_value = @@global.log_result_errors; +SELECT @start_global_value; +@start_global_value +0 +SET @start_session_value = @@session.log_result_errors; +SELECT @start_session_value; +@start_session_value +0 +'#--------------------FN_DYNVARS_067_01-------------------------#' +SET @@global.log_result_errors = 1; +SET @@global.log_result_errors = DEFAULT; +SELECT @@global.log_result_errors; +@@global.log_result_errors +0 +SET @@session.log_result_errors = 2; +SET @@session.log_result_errors = DEFAULT; +SELECT @@session.log_result_errors; +@@session.log_result_errors +0 +'#--------------------FN_DYNVARS_067_02-------------------------#' +SET @@global.log_result_errors = DEFAULT; +SELECT @@global.log_result_errors = 0; +@@global.log_result_errors = 0 +1 +SET @@session.log_result_errors = DEFAULT; +SELECT @@session.log_result_errors = 0; +@@session.log_result_errors = 0 +1 +'#--------------------FN_DYNVARS_067_03-------------------------#' +SET @@global.log_result_errors = 0; +SELECT @@global.log_result_errors; +@@global.log_result_errors +0 +SET @@global.log_result_errors = 1; +SELECT @@global.log_result_errors; +@@global.log_result_errors +1 +SET @@global.log_result_errors = 3; +SELECT @@global.log_result_errors; +@@global.log_result_errors +3 +'#--------------------FN_DYNVARS_067_04-------------------------#' +SET @@session.log_result_errors = 0; +SELECT @@session.log_result_errors; +@@session.log_result_errors +0 +SET @@session.log_result_errors = 1; +SELECT @@session.log_result_errors; +@@session.log_result_errors +1 +SET @@session.log_result_errors = 3; +SELECT @@session.log_result_errors; +@@session.log_result_errors +3 +'#------------------FN_DYNVARS_067_05-----------------------#' +SET @@global.log_result_errors = 4; +Warnings: +Warning 1292 Truncated incorrect log_result_errors value: '4' +SELECT @@global.log_result_errors; +@@global.log_result_errors +3 +SET @@global.log_result_errors = 65530.34; +ERROR 42000: Incorrect argument type to variable 'log_result_errors' +SELECT @@global.log_result_errors; +@@global.log_result_errors +3 +SET @@global.log_result_errors = test; +ERROR 42000: Incorrect argument type to variable 'log_result_errors' +SELECT @@global.log_result_errors; +@@global.log_result_errors +3 +SET @@session.log_result_errors = -2; +Warnings: +Warning 1292 Truncated incorrect log_result_errors value: '-2' +SELECT @@session.log_result_errors; +@@session.log_result_errors +0 +SET @@session.log_result_errors = 65530.34; +ERROR 42000: Incorrect argument type to variable 'log_result_errors' +SET @@session.log_result_errors = test; +ERROR 42000: Incorrect argument type to variable 'log_result_errors' +SELECT @@session.log_result_errors; +@@session.log_result_errors +0 +'#------------------FN_DYNVARS_067_06-----------------------#' +SELECT @@global.log_result_errors = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='log_result_errors'; +@@global.log_result_errors = VARIABLE_VALUE +1 +'#------------------FN_DYNVARS_067_07-----------------------#' +SELECT @@session.log_result_errors = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='log_result_errors'; +@@session.log_result_errors = VARIABLE_VALUE +1 +'#------------------FN_DYNVARS_067_08-----------------------#' +SET @@global.log_result_errors = TRUE; +SELECT @@global.log_result_errors; +@@global.log_result_errors +1 +SET @@global.log_result_errors = FALSE; +SELECT @@global.log_result_errors; +@@global.log_result_errors +0 +'#---------------------FN_DYNVARS_067_09----------------------#' +SET @@session.log_result_errors = 1; +SET @@global.log_result_errors = 2; +SELECT @@log_result_errors = @@global.log_result_errors; +@@log_result_errors = @@global.log_result_errors +0 +'#---------------------FN_DYNVARS_067_10----------------------#' +SET @@session.log_result_errors = 1; +SET @@global.log_result_errors = 2; +SELECT @@log_result_errors = @@local.log_result_errors; +@@log_result_errors = @@local.log_result_errors +1 +SELECT @@local.log_result_errors = @@session.log_result_errors; +@@local.log_result_errors = @@session.log_result_errors +1 +SET @@session.log_result_errors = 1; +create table t1(a int primary key); +insert into t1 values(1); +insert into t1 values(1); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +insert ignore into t1 values(1); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +SET @@session.log_result_errors = 3; +insert ignore into t1 values(1); +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +drop table t1; +SET @@global.log_result_errors = @start_global_value; +SELECT @@global.log_result_errors; +@@global.log_result_errors +0 +SET @@session.log_result_errors = @start_session_value; +SELECT @@session.log_result_errors; +@@session.log_result_errors +0 +[WARN RESULT] affected_rows: 0 status: 2 warning_count: 1 +[WARN RESULT] Level: Warning Code: 1292 'Truncated incorrect log_result_errors value: '4'' +[ERROR RESULT] Code: 1232 'Incorrect argument type to variable 'log_result_errors'' +[ERROR RESULT] Code: 1232 'Incorrect argument type to variable 'log_result_errors'' +[ERROR RESULT] Code: 1062 'Duplicate entry '1' for key 'PRIMARY'' +[WARN RESULT] affected_rows: 0 status: 2 warning_count: 1 +[WARN RESULT] Level: Warning Code: 1062 'Duplicate entry '1' for key 'PRIMARY'' diff --git a/mysql-test/suite/sys_vars/r/log_slow_admin_statements_func.result b/mysql-test/suite/sys_vars/r/log_slow_admin_statements_func.result index daddc4af627..95916d8ce15 100644 --- a/mysql-test/suite/sys_vars/r/log_slow_admin_statements_func.result +++ b/mysql-test/suite/sys_vars/r/log_slow_admin_statements_func.result @@ -10,7 +10,7 @@ j VARCHAR(255) SET GLOBAL log_output = 'file,table'; SET GLOBAL slow_query_log = on; SET SESSION long_query_time = 0; -SET GLOBAL log_slow_admin_statements = on; +SET SESSION log_slow_admin_statements = on; ALTER TABLE log_slow_admin_statements ADD COLUMN k INT DEFAULT 17; CREATE PROCEDURE add_rows() BEGIN @@ -44,3 +44,37 @@ SET @@session.long_query_time= @old_long_query_time; SET @@global.log_slow_admin_statements= @old_log_slow_admin_statements; DROP PROCEDURE add_rows; TRUNCATE TABLE mysql.slow_log; +SET @save_log_slow_disabled_statements= @@global.log_slow_disabled_statements; +SET @@SESSION.log_slow_admin_statements= TRUE; +select @@SESSION.log_slow_disabled_statements; +@@SESSION.log_slow_disabled_statements +sp +SET @@SESSION.log_slow_admin_statements= FALSE; +select @@SESSION.log_slow_disabled_statements; +@@SESSION.log_slow_disabled_statements +admin,sp +SET @@GLOBAL.log_slow_admin_statements= TRUE; +select @@GLOBAL.log_slow_disabled_statements; +@@GLOBAL.log_slow_disabled_statements +sp +SET @@GLOBAL.log_slow_admin_statements= FALSE; +select @@GLOBAL.log_slow_disabled_statements; +@@GLOBAL.log_slow_disabled_statements +admin,sp +SET @@SESSION.log_slow_disabled_statements=""; +select @@SESSION.log_slow_admin_statements; +@@SESSION.log_slow_admin_statements +1 +SET @@SESSION.log_slow_disabled_statements="admin"; +select @@SESSION.log_slow_admin_statements; +@@SESSION.log_slow_admin_statements +0 +SET @@GLOBAL.log_slow_disabled_statements=""; +select @@GLOBAL.log_slow_admin_statements; +@@GLOBAL.log_slow_admin_statements +1 +SET @@GLOBAL.log_slow_disabled_statements="admin"; +select @@GLOBAL.log_slow_admin_statements; +@@GLOBAL.log_slow_admin_statements +0 +SET @@global.log_slow_disabled_statements= @save_log_slow_disabled_statements; diff --git a/mysql-test/suite/sys_vars/r/log_slow_disabled_statements_basic.result b/mysql-test/suite/sys_vars/r/log_slow_disabled_statements_basic.result new file mode 100644 index 00000000000..7ef4931a288 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/log_slow_disabled_statements_basic.result @@ -0,0 +1,72 @@ +SET @start_global_value = @@global.log_slow_disabled_statements; +select @@global.log_slow_disabled_statements; +@@global.log_slow_disabled_statements +sp +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +sp +show global variables like 'log_slow_disabled_statements'; +Variable_name Value +log_slow_disabled_statements sp +show session variables like 'log_slow_disabled_statements'; +Variable_name Value +log_slow_disabled_statements sp +select * from information_schema.global_variables where variable_name='log_slow_disabled_statements'; +VARIABLE_NAME VARIABLE_VALUE +LOG_SLOW_DISABLED_STATEMENTS sp +select * from information_schema.session_variables where variable_name='log_slow_disabled_statements'; +VARIABLE_NAME VARIABLE_VALUE +LOG_SLOW_DISABLED_STATEMENTS sp +set global log_slow_disabled_statements=1; +select @@global.log_slow_disabled_statements; +@@global.log_slow_disabled_statements +admin +set session log_slow_disabled_statements=1; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +admin +set session log_slow_disabled_statements=1; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +admin +set session log_slow_disabled_statements=2; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +call +set session log_slow_disabled_statements=5; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +admin,slave +set session log_slow_disabled_statements='admin'; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +admin +set session log_slow_disabled_statements='slave'; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +slave +set session log_slow_disabled_statements='call'; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +call +set session log_slow_disabled_statements='sp'; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +sp +set session log_slow_disabled_statements='admin,sp,call,slave'; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements +admin,call,slave,sp +set session log_slow_disabled_statements=''; +select @@session.log_slow_disabled_statements; +@@session.log_slow_disabled_statements + +set session log_slow_disabled_statements=1.1; +ERROR 42000: Incorrect argument type to variable 'log_slow_disabled_statements' +set session log_slow_disabled_statements=1e1; +ERROR 42000: Incorrect argument type to variable 'log_slow_disabled_statements' +set session log_slow_disabled_statements="foo"; +ERROR 42000: Variable 'log_slow_disabled_statements' can't be set to the value of 'foo' +set session log_slow_disabled_statements=1024; +ERROR 42000: Variable 'log_slow_disabled_statements' can't be set to the value of '1024' +SET @@global.log_slow_disabled_statements = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/log_slow_disabled_statements_func.result b/mysql-test/suite/sys_vars/r/log_slow_disabled_statements_func.result new file mode 100644 index 00000000000..80a723a305d --- /dev/null +++ b/mysql-test/suite/sys_vars/r/log_slow_disabled_statements_func.result @@ -0,0 +1,166 @@ +SET @old_log_output= @@global.log_output; +SET @old_slow_query_log= @@global.slow_query_log; +SET @old_long_query_time= @@session.long_query_time; +SET @old_log_slow_disable_statements= @@global.log_slow_disabled_statements; +SET @@session.log_slow_verbosity="explain,innodb,query_plan"; +CREATE TABLE t1 (i INT PRIMARY KEY AUTO_INCREMENT, j blob) ENGINE=MyISAM; +insert into t1 (j) values ('a'),('b'),('c'),('d'); +create table t2 (a int auto_increment primary key) engine=myisam; +SET GLOBAL log_output = 'file,table'; +SET GLOBAL slow_query_log = on; +CREATE PROCEDURE slow() +BEGIN +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'; +SELECT count(*) from t1 where j>'a'; +insert into t2 () values(); +END +$ +CREATE PROCEDURE slow2() +BEGIN +SELECT j,count(*) from t1 group by j; +create temporary table t3 (a int); +alter table t3 add column (b int); +call slow(); +drop temporary table t3; +SELECT j,count(*)+1 from t1 group by j,i; +END +$ +SET SESSION long_query_time = 0; +SELECT @@log_slow_disabled_statements; +@@log_slow_disabled_statements +sp +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra int; +CALL slow2(); +j count(*) +a 1 +b 1 +c 1 +d 1 +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +j count(*)+1 +a 2 +b 2 +c 2 +d 2 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +sql_text +TRUNCATE TABLE mysql.slow_log +ALTER TABLE t1 add column extra int +CALL slow2() +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2' +<-- +SET SESSION log_slow_disabled_statements="call,admin"; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra2 int; +CALL slow2(); +j count(*) +a 1 +b 1 +c 1 +d 1 +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +j count(*)+1 +a 2 +b 2 +c 2 +d 2 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +sql_text +TRUNCATE TABLE mysql.slow_log +SELECT j,count(*) from t1 group by j +create temporary table t3 (a int) +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b' +SELECT count(*) from t1 where j>'a' +insert into t2 () values() +drop temporary table t3 +SELECT j,count(*)+1 from t1 group by j,i +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3' +<-- +SET SESSION log_slow_disabled_statements=""; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra3 int; +CALL slow2(); +j count(*) +a 1 +b 1 +c 1 +d 1 +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +j count(*)+1 +a 2 +b 2 +c 2 +d 2 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +sql_text +TRUNCATE TABLE mysql.slow_log +ALTER TABLE t1 add column extra3 int +SELECT j,count(*) from t1 group by j +create temporary table t3 (a int) +alter table t3 add column (b int) +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b' +SELECT count(*) from t1 where j>'a' +insert into t2 () values() +call slow() +drop temporary table t3 +SELECT j,count(*)+1 from t1 group by j,i +CALL slow2() +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4' +<-- +SET SESSION log_slow_disabled_statements="call,admin,slave,sp"; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra4 int; +CALL slow2(); +j count(*) +a 1 +b 1 +c 1 +d 1 +count(if(sleep(1) >= 0,0,NULL)) +2 +count(*) +3 +j count(*)+1 +a 2 +b 2 +c 2 +d 2 +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5'; +count(if(sleep(1) >= 0,0,NULL)) +2 +--> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +sql_text +TRUNCATE TABLE mysql.slow_log +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5' +<-- +DROP TABLE t1,t2; +DROP PROCEDURE slow; +DROP PROCEDURE slow2; +SET @@session.long_query_time= @old_long_query_time; +TRUNCATE TABLE mysql.slow_log; +SET @@global.log_output= @old_log_output; +SET @@global.slow_query_log= @old_slow_query_log; +SET @@global.log_slow_disabled_statements= @old_log_slow_disable_statements; diff --git a/mysql-test/suite/sys_vars/r/log_slow_filter_basic.result b/mysql-test/suite/sys_vars/r/log_slow_filter_basic.result index 5b6eccf2185..0d2549bdbf1 100644 --- a/mysql-test/suite/sys_vars/r/log_slow_filter_basic.result +++ b/mysql-test/suite/sys_vars/r/log_slow_filter_basic.result @@ -1,22 +1,22 @@ SET @start_global_value = @@global.log_slow_filter; select @@global.log_slow_filter; @@global.log_slow_filter -admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk select @@session.log_slow_filter; @@session.log_slow_filter -admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk show global variables like 'log_slow_filter'; Variable_name Value -log_slow_filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +log_slow_filter admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk show session variables like 'log_slow_filter'; Variable_name Value -log_slow_filter admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +log_slow_filter admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk select * from information_schema.global_variables where variable_name='log_slow_filter'; VARIABLE_NAME VARIABLE_VALUE -LOG_SLOW_FILTER admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +LOG_SLOW_FILTER admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk select * from information_schema.session_variables where variable_name='log_slow_filter'; VARIABLE_NAME VARIABLE_VALUE -LOG_SLOW_FILTER admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +LOG_SLOW_FILTER admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk set global log_slow_filter=1; select @@global.log_slow_filter; @@global.log_slow_filter @@ -57,6 +57,10 @@ set session log_slow_filter='full_scan'; select @@session.log_slow_filter; @@session.log_slow_filter full_scan +set session log_slow_filter='not_using_index'; +select @@session.log_slow_filter; +@@session.log_slow_filter +not_using_index set session log_slow_filter='query_cache'; select @@session.log_slow_filter; @@session.log_slow_filter @@ -77,10 +81,10 @@ set session log_slow_filter='filesort,query_cache'; select @@session.log_slow_filter; @@session.log_slow_filter filesort,query_cache -set session log_slow_filter='admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk'; +set session log_slow_filter='admin,not_using_index,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk'; select @@session.log_slow_filter; @@session.log_slow_filter -admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +admin,filesort,filesort_on_disk,full_join,full_scan,not_using_index,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk set session log_slow_filter=''; select @@session.log_slow_filter; @@session.log_slow_filter @@ -91,6 +95,6 @@ set session log_slow_filter=1e1; ERROR 42000: Incorrect argument type to variable 'log_slow_filter' set session log_slow_filter="foo"; ERROR 42000: Variable 'log_slow_filter' can't be set to the value of 'foo' -set session log_slow_filter=1024; -ERROR 42000: Variable 'log_slow_filter' can't be set to the value of '1024' +set session log_slow_filter=16384; +ERROR 42000: Variable 'log_slow_filter' can't be set to the value of '16384' SET @@global.log_slow_filter = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 7be656b2b05..20ece183b86 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -1703,6 +1703,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME LOG_DISABLED_STATEMENTS +SESSION_VALUE sp +GLOBAL_VALUE sp +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE sp +VARIABLE_SCOPE SESSION +VARIABLE_TYPE SET +VARIABLE_COMMENT Don't log certain types of statements to general log +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST slave,sp +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_ERROR SESSION_VALUE NULL GLOBAL_VALUE @@ -1732,13 +1746,13 @@ ENUM_VALUE_LIST NONE,FILE,TABLE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_QUERIES_NOT_USING_INDEXES -SESSION_VALUE NULL +SESSION_VALUE OFF GLOBAL_VALUE OFF GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE OFF -VARIABLE_SCOPE GLOBAL +VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Log queries that are executed without benefit of any index to the slow log if it is open +VARIABLE_COMMENT Log queries that are executed without benefit of any index to the slow log if it is open. Same as log_slow_filter='not_using_index' NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL @@ -1746,31 +1760,45 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME LOG_SLOW_ADMIN_STATEMENTS -SESSION_VALUE NULL +SESSION_VALUE ON GLOBAL_VALUE ON GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE ON -VARIABLE_SCOPE GLOBAL +VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. +VARIABLE_COMMENT Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. Resets or sets the option 'admin' in log_slow_disabled_statements NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME LOG_SLOW_DISABLED_STATEMENTS +SESSION_VALUE sp +GLOBAL_VALUE sp +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE sp +VARIABLE_SCOPE SESSION +VARIABLE_TYPE SET +VARIABLE_COMMENT Don't log certain types of statements to slow log +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST admin,call,slave,sp +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_SLOW_FILTER -SESSION_VALUE admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk -GLOBAL_VALUE admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +SESSION_VALUE admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +GLOBAL_VALUE admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +DEFAULT_VALUE admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk VARIABLE_SCOPE SESSION VARIABLE_TYPE SET -VARIABLE_COMMENT Log only certain types of queries +VARIABLE_COMMENT Log only certain types of queries to the slow log. If variable empty alll kind of queries are logged. All types are bound by slow_query_time, except 'not_using_index' which is always logged if enabled NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +ENUM_VALUE_LIST admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,not_using_index,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_SLOW_RATE_LIMIT @@ -1788,13 +1816,13 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_SLOW_SLAVE_STATEMENTS -SESSION_VALUE NULL +SESSION_VALUE ON GLOBAL_VALUE ON GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE ON -VARIABLE_SCOPE GLOBAL +VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Log slow statements executed by slave thread to the slow log if it is open. +VARIABLE_COMMENT Log slow statements executed by slave thread to the slow log if it is open. Resets or sets the option 'slave' in log_slow_disabled_statements NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index a94b4f720c6..0867139f37d 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -1871,6 +1871,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME LOG_DISABLED_STATEMENTS +SESSION_VALUE sp +GLOBAL_VALUE sp +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE sp +VARIABLE_SCOPE SESSION +VARIABLE_TYPE SET +VARIABLE_COMMENT Don't log certain types of statements to general log +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST slave,sp +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_ERROR SESSION_VALUE NULL GLOBAL_VALUE PATH @@ -1900,13 +1914,13 @@ ENUM_VALUE_LIST NONE,FILE,TABLE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_QUERIES_NOT_USING_INDEXES -SESSION_VALUE NULL +SESSION_VALUE OFF GLOBAL_VALUE OFF GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE OFF -VARIABLE_SCOPE GLOBAL +VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Log queries that are executed without benefit of any index to the slow log if it is open +VARIABLE_COMMENT Log queries that are executed without benefit of any index to the slow log if it is open. Same as log_slow_filter='not_using_index' NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL @@ -1928,31 +1942,45 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME LOG_SLOW_ADMIN_STATEMENTS -SESSION_VALUE NULL +SESSION_VALUE ON GLOBAL_VALUE ON GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE ON -VARIABLE_SCOPE GLOBAL +VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. +VARIABLE_COMMENT Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. Resets or sets the option 'admin' in log_slow_disabled_statements NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME LOG_SLOW_DISABLED_STATEMENTS +SESSION_VALUE sp +GLOBAL_VALUE sp +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE sp +VARIABLE_SCOPE SESSION +VARIABLE_TYPE SET +VARIABLE_COMMENT Don't log certain types of statements to slow log +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST admin,call,slave,sp +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_SLOW_FILTER -SESSION_VALUE admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk -GLOBAL_VALUE admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +SESSION_VALUE admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +GLOBAL_VALUE admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +DEFAULT_VALUE admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk VARIABLE_SCOPE SESSION VARIABLE_TYPE SET -VARIABLE_COMMENT Log only certain types of queries +VARIABLE_COMMENT Log only certain types of queries to the slow log. If variable empty alll kind of queries are logged. All types are bound by slow_query_time, except 'not_using_index' which is always logged if enabled NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk +ENUM_VALUE_LIST admin,filesort,filesort_on_disk,filsort_priority_queue,full_join,full_scan,not_using_index,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_SLOW_RATE_LIMIT @@ -1970,13 +1998,13 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME LOG_SLOW_SLAVE_STATEMENTS -SESSION_VALUE NULL +SESSION_VALUE ON GLOBAL_VALUE ON GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE ON -VARIABLE_SCOPE GLOBAL +VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Log slow statements executed by slave thread to the slow log if it is open. +VARIABLE_COMMENT Log slow statements executed by slave thread to the slow log if it is open. Resets or sets the option 'slave' in log_slow_disabled_statements NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL diff --git a/mysql-test/suite/sys_vars/t/log_disabled_statements_basic.test b/mysql-test/suite/sys_vars/t/log_disabled_statements_basic.test new file mode 100644 index 00000000000..f6191629f91 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/log_disabled_statements_basic.test @@ -0,0 +1,51 @@ +# set session + +SET @start_global_value = @@global.log_disabled_statements; + +# +# exists as global only +# +select @@global.log_disabled_statements; +select @@session.log_disabled_statements; +show global variables like 'log_disabled_statements'; +show session variables like 'log_disabled_statements'; +select * from information_schema.global_variables where variable_name='log_disabled_statements'; +select * from information_schema.session_variables where variable_name='log_disabled_statements'; + +# +# show that it's writable +# +set global log_disabled_statements=1; +select @@global.log_disabled_statements; +set session log_disabled_statements=1; +select @@session.log_disabled_statements; + +# +# all valid values +# +set session log_disabled_statements=1; +select @@session.log_disabled_statements; +set session log_disabled_statements=2; +select @@session.log_disabled_statements; +set session log_disabled_statements='slave'; +select @@session.log_disabled_statements; +set session log_disabled_statements='sp'; +select @@session.log_disabled_statements; +set session log_disabled_statements='slave,sp'; +select @@session.log_disabled_statements; +set session log_disabled_statements=''; +select @@session.log_disabled_statements; + +# +# incorrect types/values +# +--error ER_WRONG_TYPE_FOR_VAR +set session log_disabled_statements=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set session log_disabled_statements=1e1; +--error ER_WRONG_VALUE_FOR_VAR +set session log_disabled_statements="foo"; +--error ER_WRONG_VALUE_FOR_VAR +set session log_disabled_statements=1024; + +SET @@global.log_disabled_statements = @start_global_value; diff --git a/mysql-test/suite/sys_vars/t/log_disabled_statements_func-master.opt b/mysql-test/suite/sys_vars/t/log_disabled_statements_func-master.opt new file mode 100644 index 00000000000..c9f62c1ab8a --- /dev/null +++ b/mysql-test/suite/sys_vars/t/log_disabled_statements_func-master.opt @@ -0,0 +1,2 @@ +--general-log +--general-log-file=test.log diff --git a/mysql-test/suite/sys_vars/t/log_disabled_statements_func.test b/mysql-test/suite/sys_vars/t/log_disabled_statements_func.test new file mode 100644 index 00000000000..a7a088cdcd9 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/log_disabled_statements_func.test @@ -0,0 +1,61 @@ +SET @old_log_output= @@global.log_output; +SET @old_log_disable_statements= @@global.log_disabled_statements; + + +CREATE TABLE t1 (i INT PRIMARY KEY AUTO_INCREMENT, j VARCHAR(255) ) ENGINE=MyISAM; +insert into t1 (j) values ('a'),('b'),('c'),('d'); + +# enable slow logging to table +SET GLOBAL log_output = 'file,table'; + +DELIMITER $; +CREATE PROCEDURE slow() +BEGIN + SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'; + SELECT count(*) from t1 where j>'a'; +END +$ + +DELIMITER ;$ + +select @@log_disabled_statements; +TRUNCATE TABLE mysql.general_log; +check table t1; +CALL slow(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2'; + +--echo --> +SELECT argument FROM mysql.general_log where command_type <> "Close stmt" and command_type <> "Prepare" and argument not like "%general_log%"; +--echo <-- + +TRUNCATE TABLE mysql.general_log; +SET SESSION log_disabled_statements=""; +check table t1; +CALL slow(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3'; + +--echo --> +SELECT argument FROM mysql.general_log where command_type <> "Close stmt" and command_type <> "Prepare" and argument not like "%general_log%"; +--echo <-- + +TRUNCATE TABLE mysql.general_log; +SET SESSION log_disabled_statements="sp,slave"; +check table t1; +CALL slow(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4'; + +--echo --> +SELECT argument FROM mysql.general_log where command_type <> "Close stmt" and command_type <> "Prepare" and argument not like "%general_log%"; +--echo <-- + +DROP TABLE t1; +DROP PROCEDURE slow; + +# +# Restore setup +# +TRUNCATE TABLE mysql.general_log; +SET @@global.log_output= @old_log_output; +SET @@global.log_disabled_statements= @old_log_disable_statements; + + diff --git a/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test b/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test index a726bff08d7..5710920464f 100644 --- a/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test +++ b/mysql-test/suite/sys_vars/t/log_queries_not_using_indexes_basic.test @@ -21,6 +21,16 @@ ############################################################################### SET @start_value= @@global.log_queries_not_using_indexes; +SET @filter_start_value= @@global.log_slow_filter; + +#################################################################### +# Check that variable is both session and global # +#################################################################### + +SET @@global.log_queries_not_using_indexes= TRUE; +SET @@session.log_queries_not_using_indexes= TRUE; +SET @@global.log_queries_not_using_indexes= FALSE; +SET @@session.log_queries_not_using_indexes= FALSE; #################################################################### # Valid values for boolean # @@ -63,7 +73,7 @@ SET GLOBAL log_queries_not_using_indexes= +0; SELECT @@global.log_queries_not_using_indexes; ################################################################# -# Check if the value in GLOBAL Table matches value in variable # +# Not valid values for the variable # ################################################################# --error ER_WRONG_VALUE_FOR_VAR @@ -88,23 +98,38 @@ SET @@global.log_queries_not_using_indexes= @badvar; --error ER_WRONG_VALUE_FOR_VAR SET GLOBAL log_queries_not_using_indexes= 'DEFAULT'; ---error ER_GLOBAL_VARIABLE -SET log_queries_not_using_indexes= TRUE; - ---error ER_GLOBAL_VARIABLE -SET SESSION log_queries_not_using_indexes= TRUE; - ---error ER_GLOBAL_VARIABLE -SET @@session.log_queries_not_using_indexes= TRUE; - ---error ER_GLOBAL_VARIABLE -SET LOCAL log_queries_not_using_indexes= TRUE; - --error ER_PARSE_ERROR SET @@global log_queries_not_using_indexes= TRUE; --error ER_PARSE_ERROR SET @@SESSION log_queries_not_using_indexes= TRUE; -SET @@global.log_queries_not_using_indexes= @start_value; +#################################################################### +# Ensure that the value matches log_slow_filter # +#################################################################### + +SET @@SESSION.log_queries_not_using_indexes= TRUE; +select @@SESSION.log_slow_filter; +SET @@SESSION.log_queries_not_using_indexes= FALSE; +select @@SESSION.log_slow_filter; +SET @@GLOBAL.log_queries_not_using_indexes= TRUE; +select @@GLOBAL.log_slow_filter; +SET @@GLOBAL.log_queries_not_using_indexes= FALSE; +select @@GLOBAL.log_slow_filter; + +SET @@SESSION.log_slow_filter=""; +select @@SESSION.log_queries_not_using_indexes; +SET @@SESSION.log_slow_filter="not_using_index"; +select @@SESSION.log_queries_not_using_indexes; + +SET @@GLOBAL.log_slow_filter=""; +select @@GLOBAL.log_queries_not_using_indexes; +SET @@GLOBAL.log_slow_filter="not_using_index"; +select @@GLOBAL.log_queries_not_using_indexes; +#################################################################### +# Restore original value # +#################################################################### + +SET @@global.log_queries_not_using_indexes= @start_value; +SET @@global.log_slow_filter= @filter_start_value; diff --git a/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test b/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test index 78e4d35f69a..97822a0f23a 100644 --- a/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test +++ b/mysql-test/suite/sys_vars/t/log_slow_admin_statements_func.test @@ -16,7 +16,7 @@ CREATE TABLE log_slow_admin_statements ( SET GLOBAL log_output = 'file,table'; SET GLOBAL slow_query_log = on; SET SESSION long_query_time = 0; -SET GLOBAL log_slow_admin_statements = on; +SET SESSION log_slow_admin_statements = on; # test ALTER, OPTIMIZE and CHECK against the table shown up ALTER TABLE log_slow_admin_statements ADD COLUMN k INT DEFAULT 17; @@ -59,3 +59,30 @@ SET @@global.log_slow_admin_statements= @old_log_slow_admin_statements; DROP PROCEDURE add_rows; TRUNCATE TABLE mysql.slow_log; + +# +# Ensure that log_slow_admin_statements maps to log_slow_disable_statements +# + +SET @save_log_slow_disabled_statements= @@global.log_slow_disabled_statements; +SET @@SESSION.log_slow_admin_statements= TRUE; +select @@SESSION.log_slow_disabled_statements; +SET @@SESSION.log_slow_admin_statements= FALSE; +select @@SESSION.log_slow_disabled_statements; +SET @@GLOBAL.log_slow_admin_statements= TRUE; +select @@GLOBAL.log_slow_disabled_statements; +SET @@GLOBAL.log_slow_admin_statements= FALSE; +select @@GLOBAL.log_slow_disabled_statements; + +SET @@SESSION.log_slow_disabled_statements=""; +select @@SESSION.log_slow_admin_statements; +SET @@SESSION.log_slow_disabled_statements="admin"; +select @@SESSION.log_slow_admin_statements; + +SET @@GLOBAL.log_slow_disabled_statements=""; +select @@GLOBAL.log_slow_admin_statements; +SET @@GLOBAL.log_slow_disabled_statements="admin"; +select @@GLOBAL.log_slow_admin_statements; + +SET @@global.log_slow_disabled_statements= @save_log_slow_disabled_statements; + diff --git a/mysql-test/suite/sys_vars/t/log_slow_disabled_statements_basic.test b/mysql-test/suite/sys_vars/t/log_slow_disabled_statements_basic.test new file mode 100644 index 00000000000..77391df6b3c --- /dev/null +++ b/mysql-test/suite/sys_vars/t/log_slow_disabled_statements_basic.test @@ -0,0 +1,57 @@ +# set session + +SET @start_global_value = @@global.log_slow_disabled_statements; + +# +# exists as global only +# +select @@global.log_slow_disabled_statements; +select @@session.log_slow_disabled_statements; +show global variables like 'log_slow_disabled_statements'; +show session variables like 'log_slow_disabled_statements'; +select * from information_schema.global_variables where variable_name='log_slow_disabled_statements'; +select * from information_schema.session_variables where variable_name='log_slow_disabled_statements'; + +# +# show that it's writable +# +set global log_slow_disabled_statements=1; +select @@global.log_slow_disabled_statements; +set session log_slow_disabled_statements=1; +select @@session.log_slow_disabled_statements; + +# +# all valid values +# +set session log_slow_disabled_statements=1; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements=2; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements=5; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements='admin'; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements='slave'; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements='call'; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements='sp'; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements='admin,sp,call,slave'; +select @@session.log_slow_disabled_statements; +set session log_slow_disabled_statements=''; +select @@session.log_slow_disabled_statements; + +# +# incorrect types/values +# +--error ER_WRONG_TYPE_FOR_VAR +set session log_slow_disabled_statements=1.1; +--error ER_WRONG_TYPE_FOR_VAR +set session log_slow_disabled_statements=1e1; +--error ER_WRONG_VALUE_FOR_VAR +set session log_slow_disabled_statements="foo"; +--error ER_WRONG_VALUE_FOR_VAR +set session log_slow_disabled_statements=1024; + +SET @@global.log_slow_disabled_statements = @start_global_value; diff --git a/mysql-test/suite/sys_vars/t/log_slow_disabled_statements_func.test b/mysql-test/suite/sys_vars/t/log_slow_disabled_statements_func.test new file mode 100644 index 00000000000..483948c840e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/log_slow_disabled_statements_func.test @@ -0,0 +1,92 @@ +SET @old_log_output= @@global.log_output; +SET @old_slow_query_log= @@global.slow_query_log; +SET @old_long_query_time= @@session.long_query_time; +SET @old_log_slow_disable_statements= @@global.log_slow_disabled_statements; + +# Log everything to slow log +SET @@session.log_slow_verbosity="explain,innodb,query_plan"; + +CREATE TABLE t1 (i INT PRIMARY KEY AUTO_INCREMENT, j blob) ENGINE=MyISAM; +insert into t1 (j) values ('a'),('b'),('c'),('d'); +create table t2 (a int auto_increment primary key) engine=myisam; + +# enable slow logging to table +SET GLOBAL log_output = 'file,table'; +SET GLOBAL slow_query_log = on; + +DELIMITER $; +CREATE PROCEDURE slow() +BEGIN + SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b'; + SELECT count(*) from t1 where j>'a'; + insert into t2 () values(); +END +$ + +CREATE PROCEDURE slow2() +BEGIN + SELECT j,count(*) from t1 group by j; + create temporary table t3 (a int); + alter table t3 add column (b int); + call slow(); + drop temporary table t3; + SELECT j,count(*)+1 from t1 group by j,i; +END +$ + +DELIMITER ;$ + +SET SESSION long_query_time = 0; + +SELECT @@log_slow_disabled_statements; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra int; +CALL slow2(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part2'; + +--echo --> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +--echo <-- + +SET SESSION log_slow_disabled_statements="call,admin"; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra2 int; +CALL slow2(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part3'; + +--echo --> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +--echo <-- + +SET SESSION log_slow_disabled_statements=""; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra3 int; +CALL slow2(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part4'; + +--echo --> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +--echo <-- + +SET SESSION log_slow_disabled_statements="call,admin,slave,sp"; +TRUNCATE TABLE mysql.slow_log; +ALTER TABLE t1 add column extra4 int; +CALL slow2(); +SELECT count(if(sleep(1) >= 0,0,NULL)) from t1 where j>'b and part5'; + +--echo --> +SELECT sql_text FROM mysql.slow_log where sql_text <> "Close stmt" and sql_text <> "Prepare"; +--echo <-- + +DROP TABLE t1,t2; +DROP PROCEDURE slow; +DROP PROCEDURE slow2; + +# +# Restore setup +# +SET @@session.long_query_time= @old_long_query_time; +TRUNCATE TABLE mysql.slow_log; +SET @@global.log_output= @old_log_output; +SET @@global.slow_query_log= @old_slow_query_log; +SET @@global.log_slow_disabled_statements= @old_log_slow_disable_statements; diff --git a/mysql-test/suite/sys_vars/t/log_slow_filter_basic.test b/mysql-test/suite/sys_vars/t/log_slow_filter_basic.test index 2891b7ded8b..5309456b3ef 100644 --- a/mysql-test/suite/sys_vars/t/log_slow_filter_basic.test +++ b/mysql-test/suite/sys_vars/t/log_slow_filter_basic.test @@ -39,6 +39,8 @@ set session log_slow_filter='full_join'; select @@session.log_slow_filter; set session log_slow_filter='full_scan'; select @@session.log_slow_filter; +set session log_slow_filter='not_using_index'; +select @@session.log_slow_filter; set session log_slow_filter='query_cache'; select @@session.log_slow_filter; set session log_slow_filter='query_cache_miss'; @@ -49,7 +51,7 @@ set session log_slow_filter='tmp_table_on_disk'; select @@session.log_slow_filter; set session log_slow_filter='filesort,query_cache'; select @@session.log_slow_filter; -set session log_slow_filter='admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk'; +set session log_slow_filter='admin,not_using_index,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk'; select @@session.log_slow_filter; set session log_slow_filter=''; select @@session.log_slow_filter; @@ -64,6 +66,6 @@ set session log_slow_filter=1e1; --error ER_WRONG_VALUE_FOR_VAR set session log_slow_filter="foo"; --error ER_WRONG_VALUE_FOR_VAR -set session log_slow_filter=1024; +set session log_slow_filter=16384; SET @@global.log_slow_filter = @start_global_value; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 13ca9a528c6..262013e0d2c 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -954,11 +954,11 @@ flush status; show variables like "log_queries_not_using_indexes"; select 1 from information_schema.tables limit 1; show status like 'slow_queries'; -set global log_queries_not_using_indexes=OFF; +set session log_queries_not_using_indexes=OFF; show variables like "log_queries_not_using_indexes"; select 1 from information_schema.tables limit 1; show status like 'slow_queries'; -set global log_queries_not_using_indexes=ON; +set session log_queries_not_using_indexes=ON; show variables like "log_queries_not_using_indexes"; select 1 from information_schema.tables limit 1; show status like 'slow_queries'; |