diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2020-08-03 19:08:11 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2020-08-03 19:08:11 +0300 |
commit | 859dbfea7716a83dd155392361006126b055568d (patch) | |
tree | 270410572a60be79d865d16209ae18be4c345254 /mysql-test | |
parent | c12d24e29162f35f4fb751b761a0fbd84db19498 (diff) | |
download | mariadb-git-bb-10.5-MDEV-23379.tar.gz |
MDEV-23379 Deprecate&ignore InnoDB concurrency throttling parametersbb-10.5-MDEV-23379
The parameters innodb_thread_concurrency and innodb_commit_concurrency
were useful years ago when both computing resources and the implementation
of some shared data structures were limited, but nowadays enabling
any concurrency throttling for InnoDB actually makes things worse,
because queries have to wait outside InnoDB.
We have seen many customers mistakenly setting this to a small value
like 16 or 64 and then complaining the server was slow.
The parameters used to be useful a long time ago, when MySQL 5.0 or 5.1
had trouble scaling beyond 8 concurrent connections. Most
of the scalability bottlenecks have been removed since then, and
the transactions per second delivered by MariaDB Server 10.5 should
not dramatically drop upon exceeding the 'optimal' number of connections.
Ignoring the parameters allows us to remove some normally unused code
and data structures, which could slightly improve performance.
innodb_thread_concurrency, innodb_commit_concurrency,
innodb_replication_delay, innodb_concurrency_tickets,
innodb_thread_sleep_delay, innodb_adaptive_max_sleep_delay:
Deprecate and ignore; hard-wire to 0.
The column INFORMATION_SCHEMA.INNODB_TRX.trx_concurrency_tickets
will always report 0.
Diffstat (limited to 'mysql-test')
26 files changed, 58 insertions, 1627 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index 30b17c2e063..2ec0868c39e 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1183,10 +1183,6 @@ set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; set global innodb_autoextend_increment=8; set global innodb_autoextend_increment=@my_innodb_autoextend_increment; -set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; -set global innodb_commit_concurrency=0; -set global innodb_commit_concurrency=@my_innodb_commit_concurrency; - # # Bug #37830: ORDER BY ASC/DESC - no difference # diff --git a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_debug.result b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_debug.result index f1faeb1d554..0dea25f6bf1 100644 --- a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_debug.result +++ b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_debug.result @@ -1,5 +1,4 @@ set global innodb_file_per_table=ON; -set global innodb_thread_concurrency=20; connect con1,localhost,root,,; connect con2,localhost,root,,; connect con3,localhost,root,,; diff --git a/mysql-test/suite/innodb/r/innodb_bug42101-nonzero.result b/mysql-test/suite/innodb/r/innodb_bug42101-nonzero.result deleted file mode 100644 index f43cb9da239..00000000000 --- a/mysql-test/suite/innodb/r/innodb_bug42101-nonzero.result +++ /dev/null @@ -1,26 +0,0 @@ -set global innodb_commit_concurrency=0; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '0' -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -1 -set global innodb_commit_concurrency=1; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -1 -set global innodb_commit_concurrency=42; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -42 -set global innodb_commit_concurrency=DEFAULT; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -1 -set global innodb_commit_concurrency=0; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '0' -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -1 -set global innodb_commit_concurrency=1; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -1 diff --git a/mysql-test/suite/innodb/r/innodb_bug42101.result b/mysql-test/suite/innodb/r/innodb_bug42101.result deleted file mode 100644 index 4e3367d5a54..00000000000 --- a/mysql-test/suite/innodb/r/innodb_bug42101.result +++ /dev/null @@ -1,22 +0,0 @@ -set global innodb_commit_concurrency=0; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -0 -set global innodb_commit_concurrency=1; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '1' -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -0 -set global innodb_commit_concurrency=42; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '42' -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -0 -set global innodb_commit_concurrency=0; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -0 -set global innodb_commit_concurrency=DEFAULT; -select @@innodb_commit_concurrency; -@@innodb_commit_concurrency -0 diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 31db0622fe1..595a56a8bef 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -1797,9 +1797,6 @@ DROP TABLE t1; set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; set global innodb_autoextend_increment=8; set global innodb_autoextend_increment=@my_innodb_autoextend_increment; -set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; -set global innodb_commit_concurrency=0; -set global innodb_commit_concurrency=@my_innodb_commit_concurrency; CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) ENGINE=InnoDB; INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 6fd2cdc74d2..dfb4da3a63d 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -24,10 +24,6 @@ call mtr.add_suppression("Cannot add field .* in table .* because after adding i call mtr.add_suppression("\\[ERROR\\] InnoDB: in ALTER TABLE `test`.`t1`"); call mtr.add_suppression("\\[ERROR\\] InnoDB: in RENAME TABLE table `test`.`t1`"); -SET @innodb_thread_sleep_delay_orig = @@innodb_thread_sleep_delay; - -SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency; - --disable_warnings SET @innodb_rows_deleted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'); SET @innodb_rows_inserted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'); @@ -2503,9 +2499,6 @@ DROP TABLE bug35537; DISCONNECT c1; CONNECTION default; -SET GLOBAL innodb_thread_sleep_delay = @innodb_thread_sleep_delay_orig; -SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig; - -- enable_query_log # Clean up after the Bug#55284/Bug#58912 test case. diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test index 4f5ee089852..ddac64c17c9 100644 --- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test +++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test @@ -9,8 +9,6 @@ --disable_query_log set @old_innodb_buffer_pool_size = @@innodb_buffer_pool_size; set @old_innodb_file_per_table = @@innodb_file_per_table; -set @old_innodb_thread_concurrency = @@innodb_thread_concurrency; -set @old_innodb_thread_sleep_delay = @@innodb_thread_sleep_delay; set @old_innodb_disable_resize = @@innodb_disable_resize_buffer_pool_debug; set global innodb_disable_resize_buffer_pool_debug = OFF; call mtr.add_suppression("\\[ERROR\\] InnoDB: failed to allocate memory for buffer pool chunk"); @@ -22,7 +20,6 @@ call mtr.add_suppression("\\[ERROR\\] InnoDB: failed to allocate memory for buff let $wait_timeout = 180; set global innodb_file_per_table=ON; -set global innodb_thread_concurrency=20; connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -178,8 +175,6 @@ drop table t6; --disable_query_log set global innodb_buffer_pool_size = @old_innodb_buffer_pool_size; set global innodb_file_per_table = @old_innodb_file_per_table; -set global innodb_thread_concurrency = @old_innodb_thread_concurrency; -set global innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; set global innodb_disable_resize_buffer_pool_debug = @old_innodb_disable_resize; --enable_query_log diff --git a/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt b/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt deleted file mode 100644 index e82f614c897..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-innodb-commit-concurrency=1 diff --git a/mysql-test/suite/innodb/t/innodb_bug42101-nonzero.test b/mysql-test/suite/innodb/t/innodb_bug42101-nonzero.test deleted file mode 100644 index 3ee3f1e6a8b..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug42101-nonzero.test +++ /dev/null @@ -1,20 +0,0 @@ ---source include/have_innodb.inc -# -# Bug#42101 Race condition in innodb_commit_concurrency -# http://bugs.mysql.com/42101 -# - ---error ER_WRONG_VALUE_FOR_VAR -set global innodb_commit_concurrency=0; -select @@innodb_commit_concurrency; -set global innodb_commit_concurrency=1; -select @@innodb_commit_concurrency; -set global innodb_commit_concurrency=42; -select @@innodb_commit_concurrency; -set global innodb_commit_concurrency=DEFAULT; -select @@innodb_commit_concurrency; ---error ER_WRONG_VALUE_FOR_VAR -set global innodb_commit_concurrency=0; -select @@innodb_commit_concurrency; -set global innodb_commit_concurrency=1; -select @@innodb_commit_concurrency; diff --git a/mysql-test/suite/innodb/t/innodb_bug42101.test b/mysql-test/suite/innodb/t/innodb_bug42101.test deleted file mode 100644 index 374d3e6b5f5..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug42101.test +++ /dev/null @@ -1,18 +0,0 @@ ---source include/have_innodb.inc -# -# Bug#42101 Race condition in innodb_commit_concurrency -# http://bugs.mysql.com/42101 -# - -set global innodb_commit_concurrency=0; -select @@innodb_commit_concurrency; ---error ER_WRONG_VALUE_FOR_VAR -set global innodb_commit_concurrency=1; -select @@innodb_commit_concurrency; ---error ER_WRONG_VALUE_FOR_VAR -set global innodb_commit_concurrency=42; -select @@innodb_commit_concurrency; -set global innodb_commit_concurrency=0; -select @@innodb_commit_concurrency; -set global innodb_commit_concurrency=DEFAULT; -select @@innodb_commit_concurrency; diff --git a/mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result b/mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result deleted file mode 100644 index cd9ac1db9b1..00000000000 --- a/mysql-test/suite/rpl/r/rpl_sync_with_innodb_thd_conc.result +++ /dev/null @@ -1,18 +0,0 @@ -include/master-slave.inc -[connection master] -connection slave; -SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; -SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; -SET GLOBAL innodb_thread_concurrency = 100; -connection master; -CREATE TABLE t(f INT) ENGINE=INNODB; -INSERT INTO t VALUES (10); -connection slave; -include/diff_tables.inc [master:t, slave:t] -"===== Clean up=======" -connection master; -DROP TABLE t; -connection slave; -SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency; -SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; -include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test b/mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test deleted file mode 100644 index b4c2971d2fb..00000000000 --- a/mysql-test/suite/rpl/t/rpl_sync_with_innodb_thd_conc.test +++ /dev/null @@ -1,41 +0,0 @@ -# ==== Purpose ==== -# -# Test verifies that replication shouldn't hang when number of active threads -# on the slave server are less than the allowed innodb_thread_concurrency value. -# -# ==== Implementation ==== -# -# Steps: -# 0 - Have master slave replication setup with engine being Innodb. -# 1 - Configure innodb_thread_concurrency = 100. -# 2 - Do some DML on master and sync the slave with master. -# 3 - Ensure replication doesn't hang. -# -# ==== References ==== -# -# MDEV-20247: Replication hangs with "preparing" and never starts -# - ---source include/master-slave.inc ---source include/have_innodb.inc - ---connection slave -SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; -SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; -SET GLOBAL innodb_thread_concurrency = 100; - ---connection master -CREATE TABLE t(f INT) ENGINE=INNODB; -INSERT INTO t VALUES (10); ---sync_slave_with_master - ---let $diff_tables=master:t, slave:t ---source include/diff_tables.inc - ---echo "===== Clean up=======" ---connection master -DROP TABLE t; ---sync_slave_with_master -SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency; -SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; ---source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/innodb_adaptive_max_sleep_delay_basic.result b/mysql-test/suite/sys_vars/r/innodb_adaptive_max_sleep_delay_basic.result deleted file mode 100644 index b3ba28a4411..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_adaptive_max_sleep_delay_basic.result +++ /dev/null @@ -1,72 +0,0 @@ -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -150000 -150000 Expected -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=100; -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=0; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -0 -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=100000; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -100000 -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=1000000; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -1000000 -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=1000001; -Warnings: -Warning 1292 Truncated incorrect innodb_adaptive_max_sleep_delay value: '1000001' -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -1000000 -1000000 Expected -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=4294967295; -Warnings: -Warning 1292 Truncated incorrect innodb_adaptive_max_sleep_delay value: '4294967295' -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -1000000 -1000000 Expected -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=-1; -Warnings: -Warning 1292 Truncated incorrect innodb_adaptive_max_sleep_delay value: '-1' -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -0 -0 Expected -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=-1024; -Warnings: -Warning 1292 Truncated incorrect innodb_adaptive_max_sleep_delay value: '-1024' -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@GLOBAL.innodb_adaptive_max_sleep_delay -0 -0 Expected -SELECT COUNT(@@GLOBAL.innodb_adaptive_max_sleep_delay); -COUNT(@@GLOBAL.innodb_adaptive_max_sleep_delay) -1 -1 Expected -SELECT VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_adaptive_max_sleep_delay'; -VARIABLE_VALUE -0 -100 Expected -SELECT @@innodb_adaptive_max_sleep_delay = @@GLOBAL.innodb_adaptive_max_sleep_delay; -@@innodb_adaptive_max_sleep_delay = @@GLOBAL.innodb_adaptive_max_sleep_delay -1 -1000000 Expected -SELECT COUNT(@@innodb_adaptive_max_sleep_delay); -COUNT(@@innodb_adaptive_max_sleep_delay) -1 -1 Expected -SELECT COUNT(@@local.innodb_adaptive_max_sleep_delay); -ERROR HY000: Variable 'innodb_adaptive_max_sleep_delay' is a GLOBAL variable -Expected error 'Variable is a GLOBAL variable' -SELECT COUNT(@@SESSION.innodb_adaptive_max_sleep_delay); -ERROR HY000: Variable 'innodb_adaptive_max_sleep_delay' is a GLOBAL variable -Expected error 'Variable is a GLOBAL variable' -SELECT innodb_adaptive_max_sleep_delay = @@SESSION.innodb_adaptive_max_sleep_delay; -ERROR 42S22: Unknown column 'innodb_adaptive_max_sleep_delay' in 'field list' -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=150000; diff --git a/mysql-test/suite/sys_vars/r/innodb_commit_concurrency_basic.result b/mysql-test/suite/sys_vars/r/innodb_commit_concurrency_basic.result deleted file mode 100644 index 474818829c5..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_commit_concurrency_basic.result +++ /dev/null @@ -1,87 +0,0 @@ -SET @global_start_value = @@global.innodb_commit_concurrency; -SELECT @global_start_value; -@global_start_value -0 -'#--------------------FN_DYNVARS_046_01------------------------#' -SET @@global.innodb_commit_concurrency = 0; -SET @@global.innodb_commit_concurrency = DEFAULT; -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -'#---------------------FN_DYNVARS_046_02-------------------------#' -SET innodb_commit_concurrency = 1; -ERROR HY000: Variable 'innodb_commit_concurrency' is a GLOBAL variable and should be set with SET GLOBAL -SELECT @@innodb_commit_concurrency; -@@innodb_commit_concurrency -0 -SELECT local.innodb_commit_concurrency; -ERROR 42S02: Unknown table 'local' in field list -SET global innodb_commit_concurrency = 0; -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -'#--------------------FN_DYNVARS_046_03------------------------#' -SET @@global.innodb_commit_concurrency = 0; -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -'#--------------------FN_DYNVARS_046_04-------------------------#' -SET @@global.innodb_commit_concurrency = 1; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '1' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = -1; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '-1' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = "T"; -ERROR 42000: Incorrect argument type to variable 'innodb_commit_concurrency' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = "Y"; -ERROR 42000: Incorrect argument type to variable 'innodb_commit_concurrency' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = 1.1; -ERROR 42000: Incorrect argument type to variable 'innodb_commit_concurrency' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = 1001; -ERROR 42000: Variable 'innodb_commit_concurrency' can't be set to the value of '1001' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -'#----------------------FN_DYNVARS_046_05------------------------#' -SELECT @@global.innodb_commit_concurrency = -VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_commit_concurrency'; -@@global.innodb_commit_concurrency = -VARIABLE_VALUE -1 -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_commit_concurrency'; -VARIABLE_VALUE -0 -'#---------------------FN_DYNVARS_046_06-------------------------#' -SET @@global.innodb_commit_concurrency = OFF; -ERROR 42000: Incorrect argument type to variable 'innodb_commit_concurrency' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = ON; -ERROR 42000: Incorrect argument type to variable 'innodb_commit_concurrency' -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 -SET @@global.innodb_commit_concurrency = @global_start_value; -SELECT @@global.innodb_commit_concurrency; -@@global.innodb_commit_concurrency -0 diff --git a/mysql-test/suite/sys_vars/r/innodb_concurrency_tickets_basic.result b/mysql-test/suite/sys_vars/r/innodb_concurrency_tickets_basic.result deleted file mode 100644 index dd4488a97b3..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_concurrency_tickets_basic.result +++ /dev/null @@ -1,130 +0,0 @@ -SET @global_start_value = @@global.innodb_concurrency_tickets; -SELECT @global_start_value; -@global_start_value -5000 -'#--------------------FN_DYNVARS_046_01------------------------#' -SET @@global.innodb_concurrency_tickets = 0; -Warnings: -Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0' -SET @@global.innodb_concurrency_tickets = DEFAULT; -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -5000 -'#---------------------FN_DYNVARS_046_02-------------------------#' -SET innodb_concurrency_tickets = 1; -ERROR HY000: Variable 'innodb_concurrency_tickets' is a GLOBAL variable and should be set with SET GLOBAL -SELECT @@innodb_concurrency_tickets; -@@innodb_concurrency_tickets -5000 -SELECT local.innodb_concurrency_tickets; -ERROR 42S02: Unknown table 'local' in field list -SET global innodb_concurrency_tickets = 0; -Warnings: -Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -'#--------------------FN_DYNVARS_046_03------------------------#' -SET @@global.innodb_concurrency_tickets = 1; -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = 1000; -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1000 -SET @@global.innodb_concurrency_tickets = 4294967295; -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -4294967295 -'#--------------------FN_DYNVARS_046_04-------------------------#' -SET @@global.innodb_concurrency_tickets = 4294967296; -SELECT @@global.innodb_concurrency_tickets IN (4294967296,4294967295); -@@global.innodb_concurrency_tickets IN (4294967296,4294967295) -1 -SET @@global.innodb_concurrency_tickets = 12345678901; -SELECT @@global.innodb_concurrency_tickets IN (12345678901,4294967295); -@@global.innodb_concurrency_tickets IN (12345678901,4294967295) -1 -SET @@global.innodb_concurrency_tickets = 18446744073709551615; -SELECT @@global.innodb_concurrency_tickets IN (18446744073709551615,4294967295); -@@global.innodb_concurrency_tickets IN (18446744073709551615,4294967295) -1 -'#--------------------FN_DYNVARS_046_05-------------------------#' -SET @@global.innodb_concurrency_tickets = -1; -Warnings: -Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '-1' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = -1024; -Warnings: -Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '-1024' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = "T"; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = "Y"; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = 1.1; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = " "; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = ' '; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -'#----------------------FN_DYNVARS_046_06------------------------#' -SELECT @@global.innodb_concurrency_tickets = -VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_concurrency_tickets'; -@@global.innodb_concurrency_tickets = -VARIABLE_VALUE -1 -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_concurrency_tickets'; -VARIABLE_VALUE -1 -'#---------------------FN_DYNVARS_046_07-------------------------#' -SET @@global.innodb_concurrency_tickets = OFF; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = ON; -ERROR 42000: Incorrect argument type to variable 'innodb_concurrency_tickets' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -'#---------------------FN_DYNVARS_046_08----------------------#' -SET @@global.innodb_concurrency_tickets = TRUE; -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = FALSE; -Warnings: -Warning 1292 Truncated incorrect innodb_concurrency_tickets value: '0' -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -1 -SET @@global.innodb_concurrency_tickets = @global_start_value; -SELECT @@global.innodb_concurrency_tickets; -@@global.innodb_concurrency_tickets -5000 diff --git a/mysql-test/suite/sys_vars/r/innodb_replication_delay_basic.result b/mysql-test/suite/sys_vars/r/innodb_replication_delay_basic.result deleted file mode 100644 index 5e0fb425f6b..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_replication_delay_basic.result +++ /dev/null @@ -1,100 +0,0 @@ -SET @start_global_value = @@global.innodb_replication_delay; -SELECT @start_global_value; -@start_global_value -0 -Valid values are zero or above -select @@global.innodb_replication_delay >=0; -@@global.innodb_replication_delay >=0 -1 -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -0 -select @@session.innodb_replication_delay; -ERROR HY000: Variable 'innodb_replication_delay' is a GLOBAL variable -show global variables like 'innodb_replication_delay'; -Variable_name Value -innodb_replication_delay 0 -show session variables like 'innodb_replication_delay'; -Variable_name Value -innodb_replication_delay 0 -select * from information_schema.global_variables where variable_name='innodb_replication_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_REPLICATION_DELAY 0 -select * from information_schema.session_variables where variable_name='innodb_replication_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_REPLICATION_DELAY 0 -set global innodb_replication_delay=10; -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -10 -select * from information_schema.global_variables where variable_name='innodb_replication_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_REPLICATION_DELAY 10 -select * from information_schema.session_variables where variable_name='innodb_replication_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_REPLICATION_DELAY 10 -set session innodb_replication_delay=1; -ERROR HY000: Variable 'innodb_replication_delay' is a GLOBAL variable and should be set with SET GLOBAL -set global innodb_replication_delay=DEFAULT; -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -0 -set global innodb_replication_delay=0; -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -0 -set global innodb_replication_delay=65535; -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -65535 -set global innodb_replication_delay=4294967295; -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -4294967295 -set global innodb_replication_delay=1.1; -ERROR 42000: Incorrect argument type to variable 'innodb_replication_delay' -set global innodb_replication_delay=1e1; -ERROR 42000: Incorrect argument type to variable 'innodb_replication_delay' -set global innodb_replication_delay="foo"; -ERROR 42000: Incorrect argument type to variable 'innodb_replication_delay' -set global innodb_replication_delay=' '; -ERROR 42000: Incorrect argument type to variable 'innodb_replication_delay' -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -4294967295 -set global innodb_replication_delay=" "; -ERROR 42000: Incorrect argument type to variable 'innodb_replication_delay' -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -4294967295 -set global innodb_replication_delay=-7; -Warnings: -Warning 1292 Truncated incorrect innodb_replication_delay value: '-7' -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -0 -set global innodb_replication_delay=-1024; -Warnings: -Warning 1292 Truncated incorrect innodb_replication_delay value: '-1024' -select @@global.innodb_replication_delay; -@@global.innodb_replication_delay -0 -select * from information_schema.global_variables where variable_name='innodb_replication_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_REPLICATION_DELAY 0 -SET @@global.innodb_replication_delay = 4294967296; -SELECT @@global.innodb_replication_delay IN (4294967296,4294967295); -@@global.innodb_replication_delay IN (4294967296,4294967295) -1 -SET @@global.innodb_replication_delay = 12345678901; -SELECT @@global.innodb_replication_delay IN (12345678901,4294967295); -@@global.innodb_replication_delay IN (12345678901,4294967295) -1 -SET @@global.innodb_replication_delay = 18446744073709551615; -SELECT @@global.innodb_replication_delay IN (18446744073709551615,4294967295); -@@global.innodb_replication_delay IN (18446744073709551615,4294967295) -1 -SET @@global.innodb_replication_delay = @start_global_value; -SELECT @@global.innodb_replication_delay; -@@global.innodb_replication_delay -0 diff --git a/mysql-test/suite/sys_vars/r/innodb_thread_concurrency_basic.result b/mysql-test/suite/sys_vars/r/innodb_thread_concurrency_basic.result deleted file mode 100644 index ca3c253604a..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_thread_concurrency_basic.result +++ /dev/null @@ -1,111 +0,0 @@ -SET @global_start_value = @@global.innodb_thread_concurrency; -SELECT @global_start_value; -@global_start_value -0 -'#--------------------FN_DYNVARS_046_01------------------------#' -SET @@global.innodb_thread_concurrency = 0; -SET @@global.innodb_thread_concurrency = DEFAULT; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -'#---------------------FN_DYNVARS_046_02-------------------------#' -SET innodb_thread_concurrency = 1; -ERROR HY000: Variable 'innodb_thread_concurrency' is a GLOBAL variable and should be set with SET GLOBAL -SELECT @@innodb_thread_concurrency; -@@innodb_thread_concurrency -0 -SELECT local.innodb_thread_concurrency; -ERROR 42S02: Unknown table 'local' in field list -SET global innodb_thread_concurrency = 0; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -'#--------------------FN_DYNVARS_046_03------------------------#' -SET @@global.innodb_thread_concurrency = 0; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = 1; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1 -SET @@global.innodb_thread_concurrency = 1000; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1000 -'#--------------------FN_DYNVARS_046_04-------------------------#' -SET @@global.innodb_thread_concurrency = -1; -Warnings: -Warning 1292 Truncated incorrect innodb_thread_concurrency value: '-1' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = "T"; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = "Y"; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = ' '; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = " "; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = 1001; -Warnings: -Warning 1292 Truncated incorrect innodb_thread_concurrency value: '1001' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1000 -SET @@global.innodb_thread_concurrency = 255.01; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1000 -'#----------------------FN_DYNVARS_046_05------------------------#' -SELECT @@global.innodb_thread_concurrency = -VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_thread_concurrency'; -@@global.innodb_thread_concurrency = -VARIABLE_VALUE -1 -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1000 -SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_thread_concurrency'; -VARIABLE_VALUE -1000 -'#---------------------FN_DYNVARS_046_06-------------------------#' -SET @@global.innodb_thread_concurrency = OFF; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1000 -SET @@global.innodb_thread_concurrency = ON; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_concurrency' -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1000 -'#---------------------FN_DYNVARS_046_07----------------------#' -SET @@global.innodb_thread_concurrency = TRUE; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -1 -SET @@global.innodb_thread_concurrency = FALSE; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 -SET @@global.innodb_thread_concurrency = @global_start_value; -SELECT @@global.innodb_thread_concurrency; -@@global.innodb_thread_concurrency -0 diff --git a/mysql-test/suite/sys_vars/r/innodb_thread_sleep_delay_basic.result b/mysql-test/suite/sys_vars/r/innodb_thread_sleep_delay_basic.result deleted file mode 100644 index c2017ee0b66..00000000000 --- a/mysql-test/suite/sys_vars/r/innodb_thread_sleep_delay_basic.result +++ /dev/null @@ -1,88 +0,0 @@ -SET @start_global_value = @@global.innodb_thread_sleep_delay; -SELECT @start_global_value; -@start_global_value -10000 -Valid values are zero or above -select @@global.innodb_thread_sleep_delay >=0; -@@global.innodb_thread_sleep_delay >=0 -1 -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -10000 -select @@session.innodb_thread_sleep_delay; -ERROR HY000: Variable 'innodb_thread_sleep_delay' is a GLOBAL variable -show global variables like 'innodb_thread_sleep_delay'; -Variable_name Value -innodb_thread_sleep_delay 10000 -show session variables like 'innodb_thread_sleep_delay'; -Variable_name Value -innodb_thread_sleep_delay 10000 -select * from information_schema.global_variables where variable_name='innodb_thread_sleep_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_THREAD_SLEEP_DELAY 10000 -select * from information_schema.session_variables where variable_name='innodb_thread_sleep_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_THREAD_SLEEP_DELAY 10000 -set global innodb_thread_sleep_delay=10; -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -10 -select * from information_schema.global_variables where variable_name='innodb_thread_sleep_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_THREAD_SLEEP_DELAY 10 -select * from information_schema.session_variables where variable_name='innodb_thread_sleep_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_THREAD_SLEEP_DELAY 10 -set session innodb_thread_sleep_delay=1; -ERROR HY000: Variable 'innodb_thread_sleep_delay' is a GLOBAL variable and should be set with SET GLOBAL -set global innodb_thread_sleep_delay=1.1; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_sleep_delay' -set global innodb_thread_sleep_delay=1e1; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_sleep_delay' -set global innodb_thread_sleep_delay="foo"; -ERROR 42000: Incorrect argument type to variable 'innodb_thread_sleep_delay' -set global innodb_thread_sleep_delay=18446744073709551616; -Warnings: -Warning 1916 Got overflow when converting '18446744073709551616' to INT. Value truncated -Warning 1292 Truncated incorrect innodb_thread_sleep_delay value: '9223372036854775807' -set global innodb_thread_sleep_delay=-7; -Warnings: -Warning 1292 Truncated incorrect innodb_thread_sleep_delay value: '-7' -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -0 -select * from information_schema.global_variables where variable_name='innodb_thread_sleep_delay'; -VARIABLE_NAME VARIABLE_VALUE -INNODB_THREAD_SLEEP_DELAY 0 -set global innodb_thread_sleep_delay=0; -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -0 -set global innodb_thread_sleep_delay=1000; -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -1000 -set global innodb_thread_sleep_delay=1000000; -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -1000000 -set global innodb_thread_sleep_delay=1000001; -Warnings: -Warning 1292 Truncated incorrect innodb_thread_sleep_delay value: '1000001' -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -1000000 -set global innodb_thread_sleep_delay=4294967295; -Warnings: -Warning 1292 Truncated incorrect innodb_thread_sleep_delay value: '4294967295' -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -1000000 -set global innodb_thread_sleep_delay=555; -select @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -555 -SET @@global.innodb_thread_sleep_delay = @start_global_value; -SELECT @@global.innodb_thread_sleep_delay; -@@global.innodb_thread_sleep_delay -10000 diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff index 84aecf2ce36..2f39a472b99 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb,32bit.rdiff @@ -9,15 +9,6 @@ VARIABLE_COMMENT Number of InnoDB Adaptive Hash Index Partitions (default 8) NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 512 -@@ -61,7 +61,7 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 150000 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT The upper limit of the sleep delay in usec. Value of 0 disables it. - NUMERIC_MIN_VALUE 0 - NUMERIC_MAX_VALUE 1000000 @@ -73,7 +73,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 64 @@ -76,16 +67,7 @@ VARIABLE_COMMENT A number between [0, 100] that tells how oftern buffer pool dump status in percentages should be printed. E.g. 10 means that buffer pool dump status is printed when every 10% of number of buffer pool pages are dumped. Default is 0 (only start and end status is printed). NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -373,7 +373,7 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 0 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT Helps in performance tuning in heavily concurrent environments. - NUMERIC_MIN_VALUE 0 - NUMERIC_MAX_VALUE 1000 -@@ -409,7 +409,7 @@ +@@ -421,7 +421,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 5 VARIABLE_SCOPE GLOBAL @@ -94,7 +76,7 @@ VARIABLE_COMMENT If the compression failure rate of a table is greater than this number more padding is added to the pages to reduce the failures. A value of zero implies no padding NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 100 -@@ -433,7 +433,7 @@ +@@ -445,7 +445,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 50 VARIABLE_SCOPE GLOBAL @@ -103,20 +85,7 @@ VARIABLE_COMMENT Percentage of empty space on a data page that can be reserved to make the page compressible. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 75 -@@ -445,10 +445,10 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 5000 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket - NUMERIC_MIN_VALUE 1 --NUMERIC_MAX_VALUE 18446744073709551615 -+NUMERIC_MAX_VALUE 4294967295 - NUMERIC_BLOCK_SIZE 0 - ENUM_VALUE_LIST NULL - READ_ONLY NO -@@ -673,7 +673,7 @@ +@@ -661,7 +661,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 120 VARIABLE_SCOPE GLOBAL @@ -125,7 +94,7 @@ VARIABLE_COMMENT Number of pages reserved in doublewrite buffer for batch flushing NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 127 -@@ -769,7 +769,7 @@ +@@ -757,7 +757,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 600 VARIABLE_SCOPE GLOBAL @@ -134,7 +103,7 @@ VARIABLE_COMMENT Maximum number of seconds that semaphore times out in InnoDB. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 4294967295 -@@ -817,7 +817,7 @@ +@@ -805,7 +805,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@ -143,7 +112,7 @@ VARIABLE_COMMENT Make the first page of the given tablespace dirty. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -829,7 +829,7 @@ +@@ -817,7 +817,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 30 VARIABLE_SCOPE GLOBAL @@ -152,7 +121,7 @@ VARIABLE_COMMENT Number of iterations over which the background flushing is averaged. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 1000 -@@ -853,7 +853,7 @@ +@@ -841,7 +841,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1 VARIABLE_SCOPE GLOBAL @@ -161,7 +130,7 @@ VARIABLE_COMMENT Controls the durability/speed trade-off for commits. Set to 0 (write and flush redo log to disk only once per second), 1 (flush to disk at each commit), 2 (write to log at commit but flush to disk only once per second) or 3 (flush to disk at prepare and at commit, slower and usually redundant). 1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash. 0 and 2 can be faster than 1 or 3. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 3 -@@ -877,7 +877,7 @@ +@@ -865,7 +865,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1 VARIABLE_SCOPE GLOBAL @@ -170,7 +139,7 @@ VARIABLE_COMMENT Set to 0 (don't flush neighbors from buffer pool), 1 (flush contiguous neighbors from buffer pool) or 2 (flush neighbors from buffer pool), when flushing a block NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 2 -@@ -925,7 +925,7 @@ +@@ -913,7 +913,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@ -179,7 +148,7 @@ VARIABLE_COMMENT Helps to save your data in case the disk image of the database becomes corrupt. Value 5 can return bogus data, and 6 can permanently corrupt data. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 6 -@@ -949,7 +949,7 @@ +@@ -937,7 +937,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 8000000 VARIABLE_SCOPE GLOBAL @@ -188,7 +157,7 @@ VARIABLE_COMMENT InnoDB Fulltext search cache size in bytes NUMERIC_MIN_VALUE 1600000 NUMERIC_MAX_VALUE 80000000 -@@ -985,7 +985,7 @@ +@@ -973,7 +973,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 84 VARIABLE_SCOPE GLOBAL @@ -197,7 +166,7 @@ VARIABLE_COMMENT InnoDB Fulltext search maximum token size in characters NUMERIC_MIN_VALUE 10 NUMERIC_MAX_VALUE 84 -@@ -997,7 +997,7 @@ +@@ -985,7 +985,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 3 VARIABLE_SCOPE GLOBAL @@ -206,7 +175,7 @@ VARIABLE_COMMENT InnoDB Fulltext search minimum token size in characters NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 16 -@@ -1009,7 +1009,7 @@ +@@ -997,7 +997,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 2000 VARIABLE_SCOPE GLOBAL @@ -215,7 +184,7 @@ VARIABLE_COMMENT InnoDB Fulltext search number of words to optimize for each optimize table call NUMERIC_MIN_VALUE 1000 NUMERIC_MAX_VALUE 10000 -@@ -1021,10 +1021,10 @@ +@@ -1009,10 +1009,10 @@ SESSION_VALUE NULL DEFAULT_VALUE 2000000000 VARIABLE_SCOPE GLOBAL @@ -228,7 +197,7 @@ NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -1045,7 +1045,7 @@ +@@ -1033,7 +1033,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 2 VARIABLE_SCOPE GLOBAL @@ -237,7 +206,7 @@ VARIABLE_COMMENT InnoDB Fulltext search parallel sort degree, will round up to nearest power of 2 number NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 16 -@@ -1057,7 +1057,7 @@ +@@ -1045,7 +1045,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 640000000 VARIABLE_SCOPE GLOBAL @@ -246,7 +215,7 @@ VARIABLE_COMMENT Total memory allocated for InnoDB Fulltext Search cache NUMERIC_MIN_VALUE 32000000 NUMERIC_MAX_VALUE 1600000000 -@@ -1081,7 +1081,7 @@ +@@ -1069,7 +1069,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 100 VARIABLE_SCOPE GLOBAL @@ -373,14 +342,14 @@ READ_ONLY YES @@ -1489,7 +1489,7 @@ SESSION_VALUE NULL - DEFAULT_VALUE 4 + DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_TYPE INT UNSIGNED VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 64 -@@ -1525,7 +1525,7 @@ +@@ -1513,7 +1513,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 16384 VARIABLE_SCOPE GLOBAL @@ -389,7 +358,7 @@ VARIABLE_COMMENT Page size to use for all InnoDB tablespaces. NUMERIC_MIN_VALUE 4096 NUMERIC_MAX_VALUE 65536 -@@ -1561,7 +1561,7 @@ +@@ -1549,7 +1549,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 300 VARIABLE_SCOPE GLOBAL @@ -398,7 +367,7 @@ VARIABLE_COMMENT Number of UNDO log pages to purge in one batch from the history list. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 5000 -@@ -1573,7 +1573,7 @@ +@@ -1561,7 +1561,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 128 VARIABLE_SCOPE GLOBAL @@ -407,7 +376,7 @@ VARIABLE_COMMENT Dictates rate at which UNDO records are purged. Value N means purge rollback segment(s) on every Nth iteration of purge invocation NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 128 -@@ -1609,7 +1609,7 @@ +@@ -1597,7 +1597,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 56 VARIABLE_SCOPE GLOBAL @@ -416,7 +385,7 @@ VARIABLE_COMMENT Number of pages that must be accessed sequentially for InnoDB to trigger a readahead. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 64 -@@ -1621,7 +1621,7 @@ +@@ -1609,7 +1609,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 4 VARIABLE_SCOPE GLOBAL @@ -425,20 +394,7 @@ VARIABLE_COMMENT Number of background read I/O threads in InnoDB. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 64 -@@ -1645,10 +1645,10 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 0 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default) - NUMERIC_MIN_VALUE 0 --NUMERIC_MAX_VALUE 18446744073709551615 -+NUMERIC_MAX_VALUE 4294967295 - NUMERIC_BLOCK_SIZE 0 - ENUM_VALUE_LIST NULL - READ_ONLY NO -@@ -1669,7 +1669,7 @@ +@@ -1657,7 +1657,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@ -447,7 +403,7 @@ VARIABLE_COMMENT An InnoDB page number. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 4294967295 -@@ -1717,7 +1717,7 @@ +@@ -1705,7 +1705,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1048576 VARIABLE_SCOPE GLOBAL @@ -456,7 +412,7 @@ VARIABLE_COMMENT Memory buffer size for index creation NUMERIC_MIN_VALUE 65536 NUMERIC_MAX_VALUE 67108864 -@@ -1885,7 +1885,7 @@ +@@ -1873,7 +1873,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 1 VARIABLE_SCOPE GLOBAL @@ -465,7 +421,7 @@ VARIABLE_COMMENT Size of the mutex/lock wait array. NUMERIC_MIN_VALUE 1 NUMERIC_MAX_VALUE 1024 -@@ -1909,10 +1909,10 @@ +@@ -1897,10 +1897,10 @@ SESSION_VALUE NULL DEFAULT_VALUE 30 VARIABLE_SCOPE GLOBAL @@ -478,25 +434,7 @@ NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO -@@ -1945,7 +1945,7 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 0 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling. - NUMERIC_MIN_VALUE 0 - NUMERIC_MAX_VALUE 1000 -@@ -1957,7 +1957,7 @@ - SESSION_VALUE NULL - DEFAULT_VALUE 10000 - VARIABLE_SCOPE GLOBAL --VARIABLE_TYPE BIGINT UNSIGNED -+VARIABLE_TYPE INT UNSIGNED - VARIABLE_COMMENT Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep - NUMERIC_MIN_VALUE 0 - NUMERIC_MAX_VALUE 1000000 -@@ -2017,7 +2017,7 @@ +@@ -2005,7 +2005,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 128 VARIABLE_SCOPE GLOBAL @@ -505,7 +443,7 @@ VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 128 -@@ -2041,7 +2041,7 @@ +@@ -2029,7 +2029,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL @@ -514,7 +452,7 @@ VARIABLE_COMMENT Number of undo tablespaces to use. NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 127 -@@ -2065,7 +2065,7 @@ +@@ -2053,7 +2053,7 @@ SESSION_VALUE NULL DEFAULT_VALUE 4 VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 4d062995b1e..aca20cee96a 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -59,15 +59,15 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_ADAPTIVE_MAX_SLEEP_DELAY SESSION_VALUE NULL -DEFAULT_VALUE 150000 +DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT The upper limit of the sleep delay in usec. Value of 0 disables it. +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 1000000 +NUMERIC_MAX_VALUE 0 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL -READ_ONLY NO +READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_AUTOEXTEND_INCREMENT SESSION_VALUE NULL @@ -385,13 +385,13 @@ VARIABLE_NAME INNODB_COMMIT_CONCURRENCY SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Helps in performance tuning in heavily concurrent environments. +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 1000 +NUMERIC_MAX_VALUE 0 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL -READ_ONLY NO +READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_COMPRESSION_ALGORITHM SESSION_VALUE NULL @@ -455,15 +455,15 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_CONCURRENCY_TICKETS SESSION_VALUE NULL -DEFAULT_VALUE 5000 +DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket -NUMERIC_MIN_VALUE 1 -NUMERIC_MAX_VALUE 18446744073709551615 +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Deprecated parameter with no effect. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 0 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL -READ_ONLY NO +READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_DATA_FILE_PATH SESSION_VALUE NULL @@ -1633,13 +1633,13 @@ VARIABLE_NAME INNODB_REPLICATION_DELAY SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Replication thread delay (ms) on the slave server if innodb_thread_concurrency is reached (0 by default) +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 0 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL -READ_ONLY NO +READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_ROLLBACK_ON_TIMEOUT SESSION_VALUE NULL @@ -1933,25 +1933,25 @@ VARIABLE_NAME INNODB_THREAD_CONCURRENCY SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Helps in performance tuning in heavily concurrent environments. Sets the maximum number of threads allowed inside InnoDB. Value 0 will disable the thread throttling. +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 1000 +NUMERIC_MAX_VALUE 0 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL -READ_ONLY NO +READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_THREAD_SLEEP_DELAY SESSION_VALUE NULL -DEFAULT_VALUE 10000 +DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT Time of innodb thread sleeping before joining InnoDB queue (usec). Value 0 disable a sleep +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Deprecated parameter with no effect. NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 1000000 +NUMERIC_MAX_VALUE 0 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL -READ_ONLY NO +READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME INNODB_TMPDIR SESSION_VALUE diff --git a/mysql-test/suite/sys_vars/t/innodb_adaptive_max_sleep_delay_basic.test b/mysql-test/suite/sys_vars/t/innodb_adaptive_max_sleep_delay_basic.test deleted file mode 100644 index 49349d86713..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_adaptive_max_sleep_delay_basic.test +++ /dev/null @@ -1,93 +0,0 @@ -################## mysql-test/t/innodb_adaptive_max_sleep_delay.test ########## -# # -# Variable Name: innodb_adaptive_max_sleep_delay # -# Scope: Global # -# Access Type: Dynamic # -# Data Type: numeric # -# # -# Note: This variable is only defined if innodb_have_atomic_builtins=ON # -# # -# Creation Date: 2011-08-17 # -# Author : Sunny Bains # -# # -# # -# Description: Dynamic config global variable innodb_adaptive_max_sleep_delay # -# * Value check # -# * Scope check # -# # -############################################################################### - ---source include/have_innodb.inc - -# Check if builtins are enabled -if (`SELECT LOWER(VARIABLE_VALUE)='off' FROM INFORMATION_SCHEMA.GLOBAL_STATUS - WHERE LOWER(variable_name) = 'innodb_have_atomic_builtins'`) { - --skip Test requires InnoDB atomic builtins -} - -# Display default value -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; ---echo 150000 Expected - -# Check if value can be set -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=100; - -# Check for valid values -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=0; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=100000; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=1000000; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; - -# Check for out of bounds -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=1000001; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; ---echo 1000000 Expected - -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=4294967295; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; ---echo 1000000 Expected - -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=-1; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; ---echo 0 Expected - -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=-1024; -SELECT @@GLOBAL.innodb_adaptive_max_sleep_delay; ---echo 0 Expected - -SELECT COUNT(@@GLOBAL.innodb_adaptive_max_sleep_delay); ---echo 1 Expected - -# Check if the value in GLOBAL table matches value in variable ---disable_warnings -SELECT VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='innodb_adaptive_max_sleep_delay'; ---enable_warnings ---echo 100 Expected - -# Check if accessing variable with and without GLOBAL point to same -# variable -SELECT @@innodb_adaptive_max_sleep_delay = @@GLOBAL.innodb_adaptive_max_sleep_delay; ---echo 1000000 Expected - -# Check if innodb_adaptive_max_sleep_delay can be accessed with and -# without @@ sign. -SELECT COUNT(@@innodb_adaptive_max_sleep_delay); ---echo 1 Expected - ---Error ER_INCORRECT_GLOBAL_LOCAL_VAR -SELECT COUNT(@@local.innodb_adaptive_max_sleep_delay); ---echo Expected error 'Variable is a GLOBAL variable' - ---Error ER_INCORRECT_GLOBAL_LOCAL_VAR -SELECT COUNT(@@SESSION.innodb_adaptive_max_sleep_delay); ---echo Expected error 'Variable is a GLOBAL variable' - ---Error ER_BAD_FIELD_ERROR -SELECT innodb_adaptive_max_sleep_delay = @@SESSION.innodb_adaptive_max_sleep_delay; - -# Reset the default -SET @@GLOBAL.innodb_adaptive_max_sleep_delay=150000; diff --git a/mysql-test/suite/sys_vars/t/innodb_commit_concurrency_basic.test b/mysql-test/suite/sys_vars/t/innodb_commit_concurrency_basic.test deleted file mode 100644 index 4ed706b372b..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_commit_concurrency_basic.test +++ /dev/null @@ -1,144 +0,0 @@ -################# mysql-test\t\innodb_commit_concurrency_basic.test ########### -# # -# Variable Name: innodb_commit_concurrency # -# Scope: GLOBAL # -# Access Type: Dynamic # -# Data Type: Numeric # -# Default Value: 0 # -# Range: 0-1000 # -# # -# # -# Creation Date: 2008-02-07 # -# Author: Rizwan # -# # -#Description:Test Cases of Dynamic System Variable innodb_commit_concurrency # -# that checks the behavior of this variable in the following ways # -# * Default Value # -# * Valid & Invalid values # -# * Scope & Access method # -# * Data Integrity # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # -# # -############################################################################### - ---source include/have_innodb.inc ---source include/load_sysvars.inc - -######################################################################## -# START OF innodb_commit_concurrency TESTS # -######################################################################## - - -############################################################################## -# Saving initial value of innodb_commit_concurrency in a temporary variable# -############################################################################## - -SET @global_start_value = @@global.innodb_commit_concurrency; -SELECT @global_start_value; - ---echo '#--------------------FN_DYNVARS_046_01------------------------#' -######################################################################## -# Display the DEFAULT value of innodb_commit_concurrency # -######################################################################## - -SET @@global.innodb_commit_concurrency = 0; -SET @@global.innodb_commit_concurrency = DEFAULT; -SELECT @@global.innodb_commit_concurrency; - ---echo '#---------------------FN_DYNVARS_046_02-------------------------#' -################################################################################ -# Check if innodb_commit_concurrency can be accessed with and without @@ sign# -################################################################################ - ---Error ER_GLOBAL_VARIABLE -SET innodb_commit_concurrency = 1; -SELECT @@innodb_commit_concurrency; - - ---Error ER_UNKNOWN_TABLE -SELECT local.innodb_commit_concurrency; - -SET global innodb_commit_concurrency = 0; - -SELECT @@global.innodb_commit_concurrency; - - ---echo '#--------------------FN_DYNVARS_046_03------------------------#' -########################################################################## -# change the value of innodb_commit_concurrency to a valid value # -########################################################################## - - -SET @@global.innodb_commit_concurrency = 0; -SELECT @@global.innodb_commit_concurrency; - ---echo '#--------------------FN_DYNVARS_046_04-------------------------#' -########################################################################### -# Change the value of innodb_commit_concurrency to invalid value # -########################################################################### - -# -# InnoDB doesn't allow innodb_commit_concurrency to change from -# zero to non-zero or vice versa -# ---Error ER_WRONG_VALUE_FOR_VAR -SET @@global.innodb_commit_concurrency = 1; -SELECT @@global.innodb_commit_concurrency; ---Error ER_WRONG_VALUE_FOR_VAR -SET @@global.innodb_commit_concurrency = -1; -SELECT @@global.innodb_commit_concurrency; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_commit_concurrency = "T"; -SELECT @@global.innodb_commit_concurrency; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_commit_concurrency = "Y"; -SELECT @@global.innodb_commit_concurrency; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_commit_concurrency = 1.1; -SELECT @@global.innodb_commit_concurrency; ---Error ER_WRONG_VALUE_FOR_VAR -SET @@global.innodb_commit_concurrency = 1001; -SELECT @@global.innodb_commit_concurrency; - - - ---echo '#----------------------FN_DYNVARS_046_05------------------------#' -######################################################################### -# Check if the value in GLOBAL Table matches value in variable # -######################################################################### - ---disable_warnings -SELECT @@global.innodb_commit_concurrency = - VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES - WHERE VARIABLE_NAME='innodb_commit_concurrency'; -SELECT @@global.innodb_commit_concurrency; -SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES - WHERE VARIABLE_NAME='innodb_commit_concurrency'; ---enable_warnings - ---echo '#---------------------FN_DYNVARS_046_06-------------------------#' -################################################################### -# Check if ON and OFF values can be used on variable # -################################################################### - ---ERROR ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_commit_concurrency = OFF; -SELECT @@global.innodb_commit_concurrency; - ---ERROR ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_commit_concurrency = ON; -SELECT @@global.innodb_commit_concurrency; - -############################## -# Restore initial value # -############################## - - -SET @@global.innodb_commit_concurrency = @global_start_value; -SELECT @@global.innodb_commit_concurrency; - -############################################################### -# END OF innodb_commit_concurrency TESTS # -############################################################### diff --git a/mysql-test/suite/sys_vars/t/innodb_concurrency_tickets_basic.test b/mysql-test/suite/sys_vars/t/innodb_concurrency_tickets_basic.test deleted file mode 100644 index d753b8bc344..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_concurrency_tickets_basic.test +++ /dev/null @@ -1,181 +0,0 @@ -################# mysql-test\t\innodb_concurrency_tickets_basic.test ########## -# # -# Variable Name: innodb_concurrency_tickets # -# Scope: GLOBAL # -# Access Type: Dynamic # -# Data Type: Numeric # -# Default Value: 5000 # -# Range: 1-4294967295 # -# # -# # -# Creation Date: 2008-02-07 # -# Author: Rizwan # -# # -#Description:Test Cases of Dynamic System Variable innodb_concurrency_tickets # -# that checks the behavior of this variable in the following ways # -# * Default Value # -# * Valid & Invalid values # -# * Scope & Access method # -# * Data Integrity # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # -# # -############################################################################### - ---source include/have_innodb.inc ---source include/load_sysvars.inc - -######################################################################## -# START OF innodb_concurrency_tickets TESTS # -######################################################################## - - -################################################################################ -# Saving initial value of innodb_concurrency_tickets in a temporary variable # -################################################################################ - -SET @global_start_value = @@global.innodb_concurrency_tickets; -SELECT @global_start_value; - ---echo '#--------------------FN_DYNVARS_046_01------------------------#' -######################################################################## -# Display the DEFAULT value of innodb_concurrency_tickets # -######################################################################## - -SET @@global.innodb_concurrency_tickets = 0; -SET @@global.innodb_concurrency_tickets = DEFAULT; -SELECT @@global.innodb_concurrency_tickets; - ---echo '#---------------------FN_DYNVARS_046_02-------------------------#' -################################################################################ -# Check if innodb_concurrency_tickets can be accessed with and without @@ sign # -################################################################################ - ---Error ER_GLOBAL_VARIABLE -SET innodb_concurrency_tickets = 1; -SELECT @@innodb_concurrency_tickets; - ---Error ER_UNKNOWN_TABLE -SELECT local.innodb_concurrency_tickets; - -SET global innodb_concurrency_tickets = 0; -SELECT @@global.innodb_concurrency_tickets; - - ---echo '#--------------------FN_DYNVARS_046_03------------------------#' -########################################################################## -# change the value of innodb_concurrency_tickets to a valid value # -########################################################################## - -SET @@global.innodb_concurrency_tickets = 1; -SELECT @@global.innodb_concurrency_tickets; - -SET @@global.innodb_concurrency_tickets = 1000; -SELECT @@global.innodb_concurrency_tickets; - -SET @@global.innodb_concurrency_tickets = 4294967295; -SELECT @@global.innodb_concurrency_tickets; - ---echo '#--------------------FN_DYNVARS_046_04-------------------------#' -########################################################################### -# Check the value of innodb_concurrency_tickets for out of bounds # -########################################################################### - -# With a 64 bit mysqld:18446744073709551615,with a 32 bit mysqld: 4294967295 ---disable_warnings -SET @@global.innodb_concurrency_tickets = 4294967296; ---enable_warnings -SELECT @@global.innodb_concurrency_tickets IN (4294967296,4294967295); - ---disable_warnings -SET @@global.innodb_concurrency_tickets = 12345678901; ---enable_warnings -SELECT @@global.innodb_concurrency_tickets IN (12345678901,4294967295); - ---disable_warnings -SET @@global.innodb_concurrency_tickets = 18446744073709551615; ---enable_warnings -SELECT @@global.innodb_concurrency_tickets IN (18446744073709551615,4294967295); - ---echo '#--------------------FN_DYNVARS_046_05-------------------------#' -########################################################################### -# Change the value of innodb_concurrency_tickets to invalid value # -########################################################################### - -SET @@global.innodb_concurrency_tickets = -1; -SELECT @@global.innodb_concurrency_tickets; - -SET @@global.innodb_concurrency_tickets = -1024; -SELECT @@global.innodb_concurrency_tickets; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = "T"; -SELECT @@global.innodb_concurrency_tickets; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = "Y"; -SELECT @@global.innodb_concurrency_tickets; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = 1.1; -SELECT @@global.innodb_concurrency_tickets; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = " "; -SELECT @@global.innodb_concurrency_tickets; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = ' '; -SELECT @@global.innodb_concurrency_tickets; - ---echo '#----------------------FN_DYNVARS_046_06------------------------#' -######################################################################### -# Check if the value in GLOBAL Table matches value in variable # -######################################################################### - ---disable_warnings -SELECT @@global.innodb_concurrency_tickets = - VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES - WHERE VARIABLE_NAME='innodb_concurrency_tickets'; ---enable_warnings -SELECT @@global.innodb_concurrency_tickets; ---disable_warnings -SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES - WHERE VARIABLE_NAME='innodb_concurrency_tickets'; ---enable_warnings - ---echo '#---------------------FN_DYNVARS_046_07-------------------------#' -################################################################### -# Check if ON and OFF values can be used on variable # -################################################################### - ---ERROR ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = OFF; -SELECT @@global.innodb_concurrency_tickets; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_concurrency_tickets = ON; -SELECT @@global.innodb_concurrency_tickets; - ---echo '#---------------------FN_DYNVARS_046_08----------------------#' -################################################################### -# Check if TRUE and FALSE values can be used on variable # -################################################################### - -SET @@global.innodb_concurrency_tickets = TRUE; -SELECT @@global.innodb_concurrency_tickets; -SET @@global.innodb_concurrency_tickets = FALSE; -SELECT @@global.innodb_concurrency_tickets; - -############################## -# Restore initial value # -############################## - -SET @@global.innodb_concurrency_tickets = @global_start_value; -SELECT @@global.innodb_concurrency_tickets; - -############################################################### -# END OF innodb_concurrency_tickets TESTS # -############################################################### - diff --git a/mysql-test/suite/sys_vars/t/innodb_replication_delay_basic.test b/mysql-test/suite/sys_vars/t/innodb_replication_delay_basic.test deleted file mode 100644 index c85cc85e78c..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_replication_delay_basic.test +++ /dev/null @@ -1,102 +0,0 @@ - -# -# 2010-01-27 - Added -# - ---source include/have_innodb.inc - -SET @start_global_value = @@global.innodb_replication_delay; -SELECT @start_global_value; - -# -# exists as global only -# ---echo Valid values are zero or above -select @@global.innodb_replication_delay >=0; -select @@global.innodb_replication_delay; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -select @@session.innodb_replication_delay; -show global variables like 'innodb_replication_delay'; -show session variables like 'innodb_replication_delay'; ---disable_warnings -select * from information_schema.global_variables where variable_name='innodb_replication_delay'; -select * from information_schema.session_variables where variable_name='innodb_replication_delay'; ---enable_warnings - -# -# show that it's writable -# -set global innodb_replication_delay=10; -select @@global.innodb_replication_delay; ---disable_warnings -select * from information_schema.global_variables where variable_name='innodb_replication_delay'; -select * from information_schema.session_variables where variable_name='innodb_replication_delay'; ---enable_warnings ---error ER_GLOBAL_VARIABLE -set session innodb_replication_delay=1; - -# -# check the default value -# -set global innodb_replication_delay=DEFAULT; -select @@global.innodb_replication_delay; - -# -# valid values -# - -set global innodb_replication_delay=0; -select @@global.innodb_replication_delay; -set global innodb_replication_delay=65535; -select @@global.innodb_replication_delay; -set global innodb_replication_delay=4294967295; -select @@global.innodb_replication_delay; - -# -# incorrect types -# ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_replication_delay=1.1; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_replication_delay=1e1; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_replication_delay="foo"; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_replication_delay=' '; -select @@global.innodb_replication_delay; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_replication_delay=" "; -select @@global.innodb_replication_delay; -set global innodb_replication_delay=-7; -select @@global.innodb_replication_delay; -set global innodb_replication_delay=-1024; -select @@global.innodb_replication_delay; ---disable_warnings -select * from information_schema.global_variables where variable_name='innodb_replication_delay'; ---enable_warnings - -# -# Check for out of bounds -# - -# With a 64 bit mysqld:18446744073709551615,with a 32 bit mysqld: 4294967295 ---disable_warnings -SET @@global.innodb_replication_delay = 4294967296; ---enable_warnings -SELECT @@global.innodb_replication_delay IN (4294967296,4294967295); - ---disable_warnings -SET @@global.innodb_replication_delay = 12345678901; ---enable_warnings -SELECT @@global.innodb_replication_delay IN (12345678901,4294967295); - ---disable_warnings -SET @@global.innodb_replication_delay = 18446744073709551615; ---enable_warnings -SELECT @@global.innodb_replication_delay IN (18446744073709551615,4294967295); - -# -# cleanup -# -SET @@global.innodb_replication_delay = @start_global_value; -SELECT @@global.innodb_replication_delay; diff --git a/mysql-test/suite/sys_vars/t/innodb_thread_concurrency_basic.test b/mysql-test/suite/sys_vars/t/innodb_thread_concurrency_basic.test deleted file mode 100644 index 0be32543d26..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_thread_concurrency_basic.test +++ /dev/null @@ -1,164 +0,0 @@ -################# mysql-test\t\innodb_thread_concurrency_basic.test ########### -# # -# Variable Name: innodb_thread_concurrency # -# Scope: GLOBAL # -# Access Type: Dynamic # -# Data Type: Numeric # -# Default Value: 0 # -# Range: 0-1000 # -# # -# # -# Creation Date: 2008-02-07 # -# Author: Rizwan # -# # -#Description:Test Cases of Dynamic System Variable innodb_thread_concurrency # -# that checks the behavior of this variable in the following ways # -# * Default Value # -# * Valid & Invalid values # -# * Scope & Access method # -# * Data Integrity # -# # -# Reference: http://dev.mysql.com/doc/refman/5.1/en/ # -# server-system-variables.html # -# # -############################################################################### - ---source include/have_innodb.inc ---source include/load_sysvars.inc - -######################################################################## -# START OF innodb_thread_concurrency TESTS # -######################################################################## - - -################################################################################ -# Saving initial value of innodb_thread_concurrency in a temporary variable # -################################################################################ - - -SET @global_start_value = @@global.innodb_thread_concurrency; -SELECT @global_start_value; - - - ---echo '#--------------------FN_DYNVARS_046_01------------------------#' -######################################################################## -# Display the DEFAULT value of innodb_thread_concurrency # -######################################################################## - - - -SET @@global.innodb_thread_concurrency = 0; -SET @@global.innodb_thread_concurrency = DEFAULT; -SELECT @@global.innodb_thread_concurrency; - ---echo '#---------------------FN_DYNVARS_046_02-------------------------#' -############################################################################### -# Check if innodb_thread_concurrency can be accessed with and without @@ sign # -############################################################################### - ---Error ER_GLOBAL_VARIABLE -SET innodb_thread_concurrency = 1; -SELECT @@innodb_thread_concurrency; - - ---Error ER_UNKNOWN_TABLE -SELECT local.innodb_thread_concurrency; - - -SET global innodb_thread_concurrency = 0; -SELECT @@global.innodb_thread_concurrency; - - ---echo '#--------------------FN_DYNVARS_046_03------------------------#' -########################################################################## -# change the value of innodb_thread_concurrency to a valid value # -########################################################################## - - -SET @@global.innodb_thread_concurrency = 0; -SELECT @@global.innodb_thread_concurrency; - -SET @@global.innodb_thread_concurrency = 1; -SELECT @@global.innodb_thread_concurrency; -SET @@global.innodb_thread_concurrency = 1000; -SELECT @@global.innodb_thread_concurrency; - ---echo '#--------------------FN_DYNVARS_046_04-------------------------#' -########################################################################### -# Change the value of innodb_thread_concurrency to invalid value # -########################################################################### - -SET @@global.innodb_thread_concurrency = -1; -SELECT @@global.innodb_thread_concurrency; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = "T"; -SELECT @@global.innodb_thread_concurrency; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = "Y"; -SELECT @@global.innodb_thread_concurrency; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = ' '; -SELECT @@global.innodb_thread_concurrency; ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = " "; -SELECT @@global.innodb_thread_concurrency; - -SET @@global.innodb_thread_concurrency = 1001; -SELECT @@global.innodb_thread_concurrency; - ---Error ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = 255.01; -SELECT @@global.innodb_thread_concurrency; - ---echo '#----------------------FN_DYNVARS_046_05------------------------#' -######################################################################### -# Check if the value in GLOBAL Table matches value in variable # -######################################################################### - ---disable_warnings -SELECT @@global.innodb_thread_concurrency = - VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES - WHERE VARIABLE_NAME='innodb_thread_concurrency'; ---enable_warnings -SELECT @@global.innodb_thread_concurrency; ---disable_warnings -SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES - WHERE VARIABLE_NAME='innodb_thread_concurrency'; ---enable_warnings - ---echo '#---------------------FN_DYNVARS_046_06-------------------------#' -################################################################### -# Check if ON and OFF values can be used on variable # -################################################################### - ---ERROR ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = OFF; -SELECT @@global.innodb_thread_concurrency; - ---ERROR ER_WRONG_TYPE_FOR_VAR -SET @@global.innodb_thread_concurrency = ON; -SELECT @@global.innodb_thread_concurrency; - ---echo '#---------------------FN_DYNVARS_046_07----------------------#' -################################################################### -# Check if TRUE and FALSE values can be used on variable # -################################################################### - -SET @@global.innodb_thread_concurrency = TRUE; -SELECT @@global.innodb_thread_concurrency; -SET @@global.innodb_thread_concurrency = FALSE; -SELECT @@global.innodb_thread_concurrency; - -############################## -# Restore initial value # -############################## - -SET @@global.innodb_thread_concurrency = @global_start_value; -SELECT @@global.innodb_thread_concurrency; - -############################################################### -# END OF innodb_thread_concurrency TESTS # -############################################################### diff --git a/mysql-test/suite/sys_vars/t/innodb_thread_sleep_delay_basic.test b/mysql-test/suite/sys_vars/t/innodb_thread_sleep_delay_basic.test deleted file mode 100644 index bc4efdd1d6f..00000000000 --- a/mysql-test/suite/sys_vars/t/innodb_thread_sleep_delay_basic.test +++ /dev/null @@ -1,69 +0,0 @@ -# -# 2010-01-27 - Added -# - ---source include/have_innodb.inc - -SET @start_global_value = @@global.innodb_thread_sleep_delay; -SELECT @start_global_value; - -# -# exists as global only -# ---echo Valid values are zero or above -select @@global.innodb_thread_sleep_delay >=0; -select @@global.innodb_thread_sleep_delay; ---error ER_INCORRECT_GLOBAL_LOCAL_VAR -select @@session.innodb_thread_sleep_delay; -show global variables like 'innodb_thread_sleep_delay'; -show session variables like 'innodb_thread_sleep_delay'; -select * from information_schema.global_variables where variable_name='innodb_thread_sleep_delay'; -select * from information_schema.session_variables where variable_name='innodb_thread_sleep_delay'; - -# -# show that it's writable -# -set global innodb_thread_sleep_delay=10; -select @@global.innodb_thread_sleep_delay; -select * from information_schema.global_variables where variable_name='innodb_thread_sleep_delay'; -select * from information_schema.session_variables where variable_name='innodb_thread_sleep_delay'; ---error ER_GLOBAL_VARIABLE -set session innodb_thread_sleep_delay=1; - -# -# incorrect types -# ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_thread_sleep_delay=1.1; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_thread_sleep_delay=1e1; ---error ER_WRONG_TYPE_FOR_VAR -set global innodb_thread_sleep_delay="foo"; - -set global innodb_thread_sleep_delay=18446744073709551616; - -set global innodb_thread_sleep_delay=-7; -select @@global.innodb_thread_sleep_delay; -select * from information_schema.global_variables where variable_name='innodb_thread_sleep_delay'; - -# -# correct/overflowed values -# -set global innodb_thread_sleep_delay=0; -select @@global.innodb_thread_sleep_delay; -set global innodb_thread_sleep_delay=1000; -select @@global.innodb_thread_sleep_delay; -set global innodb_thread_sleep_delay=1000000; -select @@global.innodb_thread_sleep_delay; -set global innodb_thread_sleep_delay=1000001; -select @@global.innodb_thread_sleep_delay; -set global innodb_thread_sleep_delay=4294967295; -select @@global.innodb_thread_sleep_delay; -set global innodb_thread_sleep_delay=555; -select @@global.innodb_thread_sleep_delay; - -# -# cleanup -# -SET @@global.innodb_thread_sleep_delay = @start_global_value; -SELECT @@global.innodb_thread_sleep_delay; |