diff options
Diffstat (limited to 'mysql-test/main/variables.test')
| -rw-r--r-- | mysql-test/main/variables.test | 1549 |
1 files changed, 1549 insertions, 0 deletions
diff --git a/mysql-test/main/variables.test b/mysql-test/main/variables.test new file mode 100644 index 00000000000..4da3cac3a73 --- /dev/null +++ b/mysql-test/main/variables.test @@ -0,0 +1,1549 @@ +# +# test variables +# +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# +# Bug#19263: variables.test doesn't clean up after itself (I/II -- save) +# +set @my_binlog_cache_size =@@global.binlog_cache_size; +set @my_connect_timeout =@@global.connect_timeout; +set @my_delayed_insert_timeout =@@global.delayed_insert_timeout; +set @my_delayed_queue_size =@@global.delayed_queue_size; +set @my_flush =@@global.flush; +set @my_flush_time =@@global.flush_time; +set @my_key_buffer_size =@@global.key_buffer_size; +set @my_max_binlog_cache_size =@@global.max_binlog_cache_size; +set @my_max_binlog_size =@@global.max_binlog_size; +set @my_max_connect_errors =@@global.max_connect_errors; +set @my_max_connections =@@global.max_connections; +set @my_max_delayed_threads =@@global.max_delayed_threads; +set @my_max_heap_table_size =@@global.max_heap_table_size; +set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads; +set @my_max_join_size =@@global.max_join_size; +set @my_max_user_connections =@@global.max_user_connections; +set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size; +set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size; +set @my_net_buffer_length =@@global.net_buffer_length; +set @my_net_write_timeout =@@global.net_write_timeout; +set @my_net_read_timeout =@@global.net_read_timeout; +set @my_server_id =@@global.server_id; +set @my_slow_launch_time =@@global.slow_launch_time; +set @my_storage_engine =@@global.default_storage_engine; +set @my_thread_cache_size =@@global.thread_cache_size; +set @my_max_allowed_packet =@@global.max_allowed_packet; +set @my_delay_key_write =@@global.delay_key_write; +set @my_join_buffer_size =@@global.join_buffer_size; +set @my_log_warnings =@@global.log_warnings; +# case insensitivity tests (new in 5.0) +set @`test`=1; +select @test, @`test`, @TEST, @`TEST`, @"teSt"; +set @TEST=2; +select @test, @`test`, @TEST, @`TEST`, @"teSt"; +set @"tEST"=3; +select @test, @`test`, @TEST, @`TEST`, @"teSt"; +set @`TeST`=4; +select @test, @`test`, @TEST, @`TEST`, @"teSt"; +select @`teST`:=5; +select @test, @`test`, @TEST, @`TEST`, @"teSt"; + +set @select=2,@t5=1.23456; +select @`select`,@not_used; +set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL; +# Expected result "1e-10", windows returns "1e-010" +--replace_result 1e-010 1e-10 +select @test_int,@test_double,@test_string,@test_string2,@select; +set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello"; +select @test_int,@test_double,@test_string,@test_string2; +set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello"; +select @test_int,@test_double,@test_string,@test_string2; +set @test_int=null,@test_double=null,@test_string=null,@test_string2=null; +select @test_int,@test_double,@test_string,@test_string2; +select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +select @t5; + +# +# Test problem with WHERE and variables +# + +CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id)); +INSERT INTO t1 VALUES (1,'Bozo','USA'),(2,'Ronald','USA'),(3,'Kinko','IRE'),(4,'Mr. Floppy','GB'); +SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1; +SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid; +SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666; +ALTER TABLE t1 DROP PRIMARY KEY; +select * from t1 where c_id=@min_cid OR c_id=@max_cid; +drop table t1; + +# +# Test system variables +# +set GLOBAL max_join_size=10; +set max_join_size=100; +show variables like 'max_join_size'; +select * from information_schema.session_variables where variable_name like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +show global variables like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +select * from information_schema.global_variables where variable_name like 'max_join_size'; +set GLOBAL max_join_size=2000; +show global variables like 'max_join_size'; +select * from information_schema.global_variables where variable_name like 'max_join_size'; +set max_join_size=DEFAULT; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +show variables like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +select * from information_schema.session_variables where variable_name like 'max_join_size'; +set GLOBAL max_join_size=DEFAULT; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +show global variables like 'max_join_size'; +--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR +select * from information_schema.global_variables where variable_name like 'max_join_size'; +set @@max_join_size=1000, @@global.max_join_size=2000; +select @@local.max_join_size, @@global.max_join_size; +select @@identity, length(@@version)>0; +select @@VERSION=version(); +select last_insert_id(345); +explain extended select last_insert_id(345); +select @@IDENTITY,last_insert_id(), @@identity; +explain extended select @@IDENTITY,last_insert_id(), @@identity; + +set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; + +set global concurrent_insert=2; +show variables like 'concurrent_insert'; +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; +set global concurrent_insert=1; +show variables like 'concurrent_insert'; +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; +set global concurrent_insert=0; +show variables like 'concurrent_insert'; +select * from information_schema.session_variables where variable_name like 'concurrent_insert'; +set global concurrent_insert=DEFAULT; +select @@concurrent_insert; + +set global timed_mutexes=ON; +show variables like 'timed_mutexes'; +select * from information_schema.session_variables where variable_name like 'timed_mutexes'; +set global timed_mutexes=0; +show variables like 'timed_mutexes'; +select * from information_schema.session_variables where variable_name like 'timed_mutexes'; + +set default_storage_engine=MYISAM, default_storage_engine="HEAP", global default_storage_engine="MERGE"; +show local variables like 'default_storage_engine'; +select * from information_schema.session_variables where variable_name like 'default_storage_engine'; +show global variables like 'default_storage_engine'; +select * from information_schema.global_variables where variable_name like 'default_storage_engine'; + +set GLOBAL myisam_max_sort_file_size=2000000; +show global variables like 'myisam_max_sort_file_size'; +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; +set GLOBAL myisam_max_sort_file_size=default; +--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE +show global variables like 'myisam_max_sort_file_size'; +--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; + +# bug#22891: modified to take read-only SESSION net_buffer_length into account +set global net_retry_count=10, session net_retry_count=10; +set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; +show global variables like 'net_%'; +select * from information_schema.global_variables where variable_name like 'net_%' order by 1; +show session variables like 'net_%'; +select * from information_schema.session_variables where variable_name like 'net_%' order by 1; +set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +show global variables like 'net_%'; +select * from information_schema.global_variables where variable_name like 'net_%' order by 1; +set global net_buffer_length=1; +show global variables like 'net_buffer_length'; +select * from information_schema.global_variables where variable_name like 'net_buffer_length'; +#warning 1292 +set global net_buffer_length=2000000000; +show global variables like 'net_buffer_length'; +select * from information_schema.global_variables where variable_name like 'net_buffer_length'; + +set character set cp1251_koi8; +show variables like "character_set_client"; +select * from information_schema.session_variables where variable_name like 'character_set_client'; +select @@timestamp>0; + +set @@rand_seed1=10000000,@@rand_seed2=1000000; +select ROUND(RAND(),5); + + +--echo +--echo ==+ Testing %alloc% system variables +== +--echo ==+ NOTE: These values *must* be a multiple of 1024 +== +--echo ==+ Other values will be rounded down to nearest multiple +== +--echo +--echo ==+ Show initial values +== +SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size'); + +--echo ==+ information_schema data +== +SELECT * FROM information_schema.session_variables +WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1; +--echo Testing values that are multiples of 1024 +set @@range_alloc_block_size=1024*15+1024; +set @@query_alloc_block_size=1024*15+1024*2; +set @@query_prealloc_size=1024*18-1024; +set @@transaction_alloc_block_size=1024*21-1024*1; +set @@transaction_prealloc_size=1024*21-2048; +--echo ==+ Check manipulated values ==+ +select @@query_alloc_block_size; +SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size'); +--echo ==+ information_schema data +== +SELECT * FROM information_schema.session_variables +WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1; +--echo ==+ Manipulate variable values +== +--echo Testing values that are not 1024 multiples +set @@range_alloc_block_size=1024*16+1023; +set @@query_alloc_block_size=1024*17+2; +set @@query_prealloc_size=1024*18-1023; +set @@transaction_alloc_block_size=1024*20-1; +set @@transaction_prealloc_size=1024*21-1; +--echo ==+ Check manipulated values ==+ +SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size'); +--echo ==+ information_schema data +== +SELECT * FROM information_schema.session_variables +WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1; +--echo ==+ Set values back to the default values +== +set @@range_alloc_block_size=default; +set @@query_alloc_block_size=default, @@query_prealloc_size=default; +set transaction_alloc_block_size=default, @@transaction_prealloc_size=default; +--echo ==+ Check the values now that they are reset +== +SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', +'query_alloc_block_size', 'query_prealloc_size', +'transaction_alloc_block_size', 'transaction_prealloc_size'); + +# +# Bug #10904 Illegal mix of collations between +# a system variable and a constant +# +SELECT @@version LIKE 'non-existent'; +SELECT @@version_compile_os LIKE 'non-existent'; + +# The following should give errors + +--error ER_WRONG_VALUE_FOR_VAR +set big_tables=OFFF; +--error ER_WRONG_VALUE_FOR_VAR +set big_tables="OFFF"; +--error ER_UNKNOWN_SYSTEM_VARIABLE +set unknown_variable=1; +--error ER_WRONG_TYPE_FOR_VAR +set max_join_size="hello"; +--error ER_UNKNOWN_STORAGE_ENGINE +set default_storage_engine=UNKNOWN_TABLE_TYPE; +--error ER_WRONG_VALUE_FOR_VAR +set default_storage_engine=MERGE, big_tables=2; +show local variables like 'default_storage_engine'; +--error ER_UNKNOWN_CHARACTER_SET +set character_set_client=UNKNOWN_CHARACTER_SET; +--error ER_UNKNOWN_COLLATION +set collation_connection=UNKNOWN_COLLATION; +--error ER_WRONG_VALUE_FOR_VAR +set character_set_client=NULL; +--error ER_WRONG_VALUE_FOR_VAR +set collation_connection=NULL; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@global.timestamp; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set @@version=''; +--error ER_GLOBAL_VARIABLE +set @@concurrent_insert=1; +--error ER_GLOBAL_VARIABLE +set myisam_max_sort_file_size=100; +--error ER_WRONG_VALUE_FOR_VAR +set @@SQL_WARNINGS=NULL; + +# Test setting all variables + +set autocommit=1; +set big_tables=1; +select @@autocommit, @@big_tables; +set global binlog_cache_size=100; +set bulk_insert_buffer_size=100; +set character set cp1251_koi8; +set character set default; +set @@global.concurrent_insert=1; +set global connect_timeout=100; +select @@delay_key_write; +set global delay_key_write="OFF"; +select @@delay_key_write; +set global delay_key_write=ALL; +select @@delay_key_write; +set global delay_key_write=1; +select @@delay_key_write; +set global delayed_insert_limit=100; +set global delayed_insert_timeout=100; +set global delayed_queue_size=100; +set global flush=1; +set global flush_time=100; +set insert_id=1; +set interactive_timeout=100; +set join_buffer_size=100; +set last_insert_id=1; +set global local_infile=1; +set long_query_time=0.000001; +select @@long_query_time; +set long_query_time=100.000001; +select @@long_query_time; +set low_priority_updates=1; +set global max_allowed_packet=100; +set global max_binlog_cache_size=100; +set global max_binlog_size=100; +set global max_connect_errors=100; +set global max_connections=100; +set global max_delayed_threads=100; +set max_heap_table_size=100; +set max_join_size=100; +set max_sort_length=100; +set global max_user_connections=100; +select @@max_user_connections; +set global max_write_lock_count=100; +set myisam_sort_buffer_size=100; +set global net_buffer_length=100; +set net_read_timeout=100; +set net_write_timeout=100; +set read_buffer_size=100; +set read_rnd_buffer_size=100; +set global server_id=100; +set global slow_launch_time=100; +set sort_buffer_size=100; +set @@max_sp_recursion_depth=10; +select @@max_sp_recursion_depth; +set @@max_sp_recursion_depth=0; +select @@max_sp_recursion_depth; +set sql_auto_is_null=1; +select @@sql_auto_is_null; +set @@sql_auto_is_null=0; +select @@sql_auto_is_null; +set sql_big_selects=1; +set sql_buffer_result=1; +set sql_log_bin=1; +set sql_log_off=1; +set sql_quote_show_create=1; +set sql_safe_updates=1; +set sql_select_limit=1; +# reset it, so later tests don't get confused +set sql_select_limit=default; +set sql_warnings=1; +set global table_open_cache=100; +set default_storage_engine=myisam; +set global thread_cache_size=100; +set timestamp=1, timestamp=default; +set tmp_table_size=1024; +set tx_isolation="READ-COMMITTED"; +set wait_timeout=100; +set log_warnings=1; +set global log_warnings=1; + +# +# Bugs: #20392: INSERT_ID session variable has weird value +# +select @@session.insert_id; +set @save_insert_id=@@session.insert_id; +set session insert_id=20; +select @@session.insert_id; + +set session last_insert_id=100; +select @@session.insert_id; +select @@session.last_insert_id; +select @@session.insert_id; + +set @@session.insert_id=@save_insert_id; +select @@session.insert_id; + +# +# key buffer +# + +create table t1 (a int not null auto_increment, primary key(a)); +create table t2 (a int not null auto_increment, primary key(a)); +insert into t1 values(null),(null),(null); +insert into t2 values(null),(null),(null); +set global key_buffer_size=100000; +select @@key_buffer_size; +select * from t1 where a=2; +select * from t2 where a=3; +check table t1,t2; +select max(a) +1, max(a) +2 into @xx,@yy from t1; +drop table t1,t2; + +# +# error conditions +# + +--error ER_UNKNOWN_SYSTEM_VARIABLE +select @@xxxxxxxxxx; +select 1; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.key_buffer_size; + +--error ER_GLOBAL_VARIABLE +set ft_boolean_syntax = @@init_connect; +--error ER_WRONG_VALUE_FOR_VAR +set global ft_boolean_syntax = @@init_connect; +--error ER_GLOBAL_VARIABLE +set init_connect = NULL; +set global init_connect = NULL; +--error ER_GLOBAL_VARIABLE +set ft_boolean_syntax = @@init_connect; +--error ER_WRONG_VALUE_FOR_VAR +set global ft_boolean_syntax = @@init_connect; + +# Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as +# expected: check that there is no overflow when 64-bit unsigned +# variables are set + +set global myisam_max_sort_file_size=4294967296; +--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE +show global variables like 'myisam_max_sort_file_size'; +--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; +set global myisam_max_sort_file_size=default; + +# +# swap +# +select @@global.max_user_connections,@@local.max_join_size; +set @svc=@@global.max_user_connections, @svj=@@local.max_join_size; +select @@global.max_user_connections,@@local.max_join_size; +set @@global.max_user_connections=111,@@local.max_join_size=222; +select @@global.max_user_connections,@@local.max_join_size; +set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections; +select @@global.max_user_connections,@@local.max_join_size; +set @@global.max_user_connections=@svc, @@local.max_join_size=@svj; +select @@global.max_user_connections,@@local.max_join_size; +set @a=1, @b=2; +set @a=@b, @b=@a; +select @a, @b; + +# +# Bug#2586:Disallow global/session/local as structured var. instance names +# +--error ER_UNKNOWN_STRUCTURED_VARIABLE +set @@global.global.key_buffer_size= 1; +--error ER_UNKNOWN_STRUCTURED_VARIABLE +set GLOBAL global.key_buffer_size= 1; +--error ER_PARSE_ERROR +SELECT @@global.global.key_buffer_size; +--error ER_PARSE_ERROR +SELECT @@global.session.key_buffer_size; +--error ER_PARSE_ERROR +SELECT @@global.local.key_buffer_size; + +# BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0) +set @tstlw = @@log_warnings; +show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; +set global log_warnings = 0; +show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; +set global log_warnings = 42; +show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; +set global log_warnings = @tstlw; +show global variables like 'log_warnings'; +select * from information_schema.global_variables where variable_name like 'log_warnings'; + +# +# BUG#4788 show create table provides incorrect statement +# +# What default width have numeric types? +create table t1 ( + c1 tinyint, + c2 smallint, + c3 mediumint, + c4 int, + c5 bigint); +show create table t1; +drop table t1; +# +# What types and widths have variables? +set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0; +create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4; +show create table t1; +drop table t1; + + +# +# Bug #6993: myisam_data_pointer_size +# Wrong bug report, data pointer size must be restricted to 7, +# setting to 8 will not work on all computers, myisamchk and +# the server may see a wrong value, such as 0 or negative number +# if 8 bytes is set. +# + +SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7; +SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE'; + +# +# Bug #6958: negative arguments to integer options wrap around +# + +SET GLOBAL table_open_cache=-1; +SHOW VARIABLES LIKE 'table_open_cache'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache'; +SET GLOBAL table_open_cache=DEFAULT; + +# +# Bugs12363: character_set_results is nullable, +# but value_ptr returns string "NULL" +# +set character_set_results=NULL; +select ifnull(@@character_set_results,"really null"); +set names latin1; + +# +# Tests for lc_time_names +# Note, when adding new locales, please fix ID accordingly: +# - to test the last ID (currently 108) +# - and the next after the last (currently 109) +# +--echo *** Various tests with LC_TIME_NAMES +--echo *** LC_TIME_NAMES: testing case insensitivity +set @@lc_time_names='ru_ru'; +select @@lc_time_names; +--echo *** LC_TIME_NAMES: testing with a user variable +set @lc='JA_JP'; +set @@lc_time_names=@lc; +select @@lc_time_names; +--echo *** LC_TIME_NAMES: testing with string expressions +set lc_time_names=concat('de','_','DE'); +select @@lc_time_names; +--error ER_UNKNOWN_LOCALE +set lc_time_names=concat('de','+','DE'); +select @@lc_time_names; +--echo LC_TIME_NAMES: testing with numeric expressions +set @@lc_time_names=1+2; +select @@lc_time_names; +--error ER_WRONG_TYPE_FOR_VAR +set @@lc_time_names=1/0; +select @@lc_time_names; +set lc_time_names=en_US; +--echo LC_TIME_NAMES: testing NULL and a negative number: +--error ER_WRONG_VALUE_FOR_VAR +set lc_time_names=NULL; +--error ER_UNKNOWN_LOCALE +set lc_time_names=-1; +select @@lc_time_names; +--echo LC_TIME_NAMES: testing locale with the last ID: +set lc_time_names=110; +select @@lc_time_names; +--echo LC_TIME_NAMES: testing a number beyond the valid ID range: +--error ER_UNKNOWN_LOCALE +set lc_time_names=111; +select @@lc_time_names; +--echo LC_TIME_NAMES: testing that 0 is en_US: +set lc_time_names=0; +select @@lc_time_names; + +# +# Bug #22648 LC_TIME_NAMES: Setting GLOBAL has no effect +# +select @@global.lc_time_names, @@lc_time_names; +set @@global.lc_time_names=fr_FR; +select @@global.lc_time_names, @@lc_time_names; +connect (con1,localhost,root,,); +connection con1; +select @@global.lc_time_names, @@lc_time_names; +set @@lc_time_names=ru_RU; +select @@global.lc_time_names, @@lc_time_names; +disconnect con1; +connection default; +select @@global.lc_time_names, @@lc_time_names; +set lc_time_names=default; +select @@global.lc_time_names, @@lc_time_names; +set @@global.lc_time_names=default; +select @@global.lc_time_names, @@lc_time_names; +set @@lc_time_names=default; +select @@global.lc_time_names, @@lc_time_names; + + +# +# Bug #13334: query_prealloc_size default less than minimum +# +set @test = @@query_prealloc_size; +set @@query_prealloc_size = @test; +select @@query_prealloc_size = @test; + +# +# Bug#31588 buffer overrun when setting variables +# +# Buffer-size Off By One. Should throw valgrind-warning without fix #31588. +--error ER_WRONG_VALUE_FOR_VAR +set global sql_mode=repeat('a',80); + +--echo End of 4.1 tests + +# +# Bug#6282 Packet error with SELECT INTO +# + +create table t1 (a int); +select a into @x from t1; +show warnings; +drop table t1; + +# +# Bug #10339: read only variables. +# + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set @@warning_count=1; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set @@global.error_count=1; + +# +# Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform +# + +--disable_warnings +set @@max_heap_table_size= 4294967296; +select @@max_heap_table_size > 0; +set global max_heap_table_size= 4294967296; +select @@max_heap_table_size > 0; +set @@max_heap_table_size= 4294967296; +select @@max_heap_table_size > 0; +--enable_warnings + +# +# Bug #11775 Variable character_set_system does not exist (sometimes) +# +select @@character_set_system; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set global character_set_system = latin1; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set @@global.version_compile_os='234'; + +# +# Check character_set_filesystem variable +# +set character_set_filesystem=latin1; +select @@character_set_filesystem; +set @@global.character_set_filesystem=latin2; +set character_set_filesystem=latin1; +select @@character_set_filesystem; +set @@global.character_set_filesystem=latin2; +set character_set_filesystem=default; +select @@character_set_filesystem; +set @@global.character_set_filesystem=default; +select @@global.character_set_filesystem; + +# +# Bug #17849: Show sql_big_selects in SHOW VARIABLES +# +set @old_sql_big_selects = @@sql_big_selects; +set @@sql_big_selects = 1; +show variables like 'sql_big_selects'; +select * from information_schema.session_variables where variable_name like 'sql_big_selects'; +set @@sql_big_selects = @old_sql_big_selects; + +# +# Bug #16195: SHOW VARIABLES doesn't report correctly sql_warnings and +# sql_notes values +# +set @@sql_notes = 0, @@sql_warnings = 0; +show variables like 'sql_notes'; +select * from information_schema.session_variables where variable_name like 'sql_notes'; +show variables like 'sql_warnings'; +select * from information_schema.session_variables where variable_name like 'sql_warnings'; +set @@sql_notes = 1, @@sql_warnings = 1; +show variables like 'sql_notes'; +select * from information_schema.session_variables where variable_name like 'sql_notes'; +show variables like 'sql_warnings'; +select * from information_schema.session_variables where variable_name like 'sql_warnings'; + +# +# Bug #12792: @@system_time_zone is not SELECTable. +# +# Don't actually output, since it depends on the system +--replace_column 1 # +select @@system_time_zone; + +# +# Bug #15684: system variables cannot be SELECTed (e.g. @@version_comment) +# +# Don't actually output, since it depends on the system +--replace_column 1 # 2 # 3 # 4 # +select @@version, @@version_comment, @@version_compile_machine, + @@version_compile_os; + +# +# Bug #1039: make tmpdir and datadir available as @@variables (also included +# basedir) +# +# Don't actually output, since it depends on the system +--replace_column 1 # 2 # 3 # +select @@basedir, @@datadir, @@tmpdir; +--replace_column 2 # +show variables like 'basedir'; +--replace_column 2 # +select * from information_schema.session_variables where variable_name like 'basedir'; +--replace_column 2 # +show variables like 'datadir'; +--replace_column 2 # +select * from information_schema.session_variables where variable_name like 'datadir'; +--replace_column 2 # +show variables like 'tmpdir'; +--replace_column 2 # +select * from information_schema.session_variables where variable_name like 'tmpdir'; + +# +# Bug #19606: make ssl settings available via SHOW VARIABLES and @@variables +# +# Don't actually output, since it depends on the system +set sort_buffer_size=1024*8; +--replace_column 1 # 2 # 3 # 4 # 5 # +select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key; +--replace_column 2 # +show variables like 'ssl%'; +--replace_column 2 # +select * from information_schema.session_variables where variable_name like 'ssl%' order by 1; + +# +# Bug #19616: make log_queries_not_using_indexes available in SHOW VARIABLES +# and as @@log_queries_not_using_indexes +# +select @@log_queries_not_using_indexes; +show variables like 'log_queries_not_using_indexes'; +select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes'; + +# +# Bug#20908: Crash if select @@"" +# +--error ER_PARSE_ERROR +select @@""; +--error ER_PARSE_ERROR +select @@&; +--error ER_PARSE_ERROR +select @@@; + +# +# Bug#20166 mysql-test-run.pl does not test system privilege tables creation +# +# Don't actually output, since it depends on the system +--replace_column 1 # +select @@hostname; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set @@hostname= "anothername"; +--replace_column 2 # +show variables like 'hostname'; + +--echo # +--echo # BUG#37408 - Compressed MyISAM files should not require/use mmap() +--echo # +--echo # Test 'myisam_mmap_size' option is not dynamic +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@myisam_mmap_size= 500M; + + +--echo # +--echo # Bug #52315: utc_date() crashes when system time > year 2037 +--echo # + +--error 0, ER_WRONG_VALUE_FOR_VAR +SET TIMESTAMP=2*1024*1024*1024; +--echo #Should not crash +--disable_result_log +SELECT UTC_DATE(); +--enable_result_log +SET TIMESTAMP=DEFAULT; + + +--echo End of 5.0 tests + +# +# Bug#36446: Attempt to set @@join_buffer_size to its minimum value +# produces spurious warning +# + +# set to 1 so mysqld will correct to minimum (+ warn) +set join_buffer_size=1; +# save minimum +set @save_join_buffer_size=@@join_buffer_size; +# set minimum +set join_buffer_size=@save_join_buffer_size; + +# This is at the very after the versioned tests, since it involves doing +# cleanup +# +# Bug #19263: variables.test doesn't clean up after itself (II/II -- +# restore) +# +set global binlog_cache_size =@my_binlog_cache_size; +set global connect_timeout =@my_connect_timeout; +set global delayed_insert_timeout =@my_delayed_insert_timeout; +set global delayed_queue_size =@my_delayed_queue_size; +set global flush =@my_flush; +set global flush_time =@my_flush_time; +set global key_buffer_size =@my_key_buffer_size; +set global max_binlog_cache_size =@my_max_binlog_cache_size; +set global max_binlog_size =@my_max_binlog_size; +set global max_connect_errors =@my_max_connect_errors; +set global max_connections =@my_max_connections; +set global max_delayed_threads =@my_max_delayed_threads; +set global max_heap_table_size =@my_max_heap_table_size; +set global max_insert_delayed_threads=@my_max_insert_delayed_threads; +set global max_join_size =@my_max_join_size; +set global max_user_connections =@my_max_user_connections; +set global max_write_lock_count =default; +set global myisam_data_pointer_size =@my_myisam_data_pointer_size; +set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size; +set global net_buffer_length =@my_net_buffer_length; +set global net_write_timeout =@my_net_write_timeout; +set global net_read_timeout =@my_net_read_timeout; +set global server_id =@my_server_id; +set global slow_launch_time =@my_slow_launch_time; +set global default_storage_engine =@my_storage_engine; +set global thread_cache_size =@my_thread_cache_size; +set global max_allowed_packet = default; +set global delay_key_write =@my_delay_key_write; +set global join_buffer_size =@my_join_buffer_size; +set global log_warnings =@my_log_warnings; + +# +# Bug#28580 Repeatation of status variables +# +--replace_column 2 # +show global variables where Variable_name='table_definition_cache'; +# Note: table_lock_wait_timeout no longer exists. See bug#45225. + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#34820: log_output can be set to illegal value. +--echo # -- + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL log_output = ''; + +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL log_output = 0; + +--echo +--echo # -- End of Bug#34820. + +# +# Bug#28234 - global/session scope - documentation vs implementation +# +--echo +--echo # +SHOW VARIABLES like 'ft_max_word_len'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_max_word_len; +SELECT @@global.ft_max_word_len; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_max_word_len= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_max_word_len= 7; +# +--echo # +SHOW VARIABLES like 'ft_min_word_len'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_min_word_len; +SELECT @@global.ft_min_word_len; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_min_word_len= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_min_word_len= 7; +# +--echo # +SHOW VARIABLES like 'ft_query_expansion_limit'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_query_expansion_limit; +SELECT @@global.ft_query_expansion_limit; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_query_expansion_limit= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_query_expansion_limit= 7; +# +--echo # +SHOW VARIABLES like 'ft_stopword_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.ft_stopword_file; +SELECT @@global.ft_stopword_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.ft_stopword_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.ft_stopword_file= 'x'; +# +# Additional variables fixed. +# +--echo # +SHOW VARIABLES like 'back_log'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.back_log; +SELECT @@global.back_log; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.back_log= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.back_log= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'large_files_support'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.large_files_support; +--replace_column 1 # +SELECT @@global.large_files_support; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.large_files_support= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.large_files_support= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'character_sets_dir'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.character_sets_dir; +--replace_column 1 # +SELECT @@global.character_sets_dir; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.character_sets_dir= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.character_sets_dir= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'init_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.init_file; +--replace_column 1 # +SELECT @@global.init_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.init_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.init_file= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'lc_messages_dir'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.lc_messages_dir; +--replace_column 1 # +SELECT @@global.lc_messages_dir; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.lc_messages_dir= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.lc_messages_dir= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'large_page_size'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.large_page_size; +--replace_column 1 # +SELECT @@global.large_page_size; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.large_page_size= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.large_page_size= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'large_pages'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.large_pages; +--replace_column 1 # +SELECT @@global.large_pages; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.large_pages= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.large_pages= true; +# +--echo # +SHOW VARIABLES like 'log_bin'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.log_bin; +SELECT @@global.log_bin; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.log_bin= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.log_bin= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'log_error'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.log_error; +--replace_column 1 # +SELECT @@global.log_error; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.log_error= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.log_error= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'lower_case_file_system'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.lower_case_file_system; +--replace_column 1 # +SELECT @@global.lower_case_file_system; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.lower_case_file_system= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.lower_case_file_system= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'lower_case_table_names'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.lower_case_table_names; +--replace_column 1 # +SELECT @@global.lower_case_table_names; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.lower_case_table_names= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.lower_case_table_names= 7; +# +--echo # +SHOW VARIABLES like 'myisam_recover_options'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.myisam_recover_options; +SELECT @@global.myisam_recover_options; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.myisam_recover_options= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.myisam_recover_options= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'open_files_limit'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.open_files_limit; +--replace_column 1 # +SELECT @@global.open_files_limit; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.open_files_limit= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.open_files_limit= 7; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'pid_file'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.pid_file; +--replace_column 1 # +SELECT @@global.pid_file; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.pid_file= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.pid_file= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'plugin_dir'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.plugin_dir; +--replace_column 1 # +SELECT @@global.plugin_dir; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.plugin_dir= 'x'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.plugin_dir= 'x'; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'port'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.port; +--replace_column 1 # +SELECT @@global.port; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.port= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.port= 7; +# +--echo # +SHOW VARIABLES like 'protocol_version'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.protocol_version; +SELECT @@global.protocol_version; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.protocol_version= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.protocol_version= 7; +# +--echo # +SHOW VARIABLES like 'skip_external_locking'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.skip_external_locking; +SELECT @@global.skip_external_locking; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.skip_external_locking= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.skip_external_locking= true; +# +--echo # +SHOW VARIABLES like 'skip_networking'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.skip_networking; +SELECT @@global.skip_networking; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.skip_networking= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.skip_networking= true; +# +--echo # +SHOW VARIABLES like 'skip_show_database'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.skip_show_database; +SELECT @@global.skip_show_database; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.skip_show_database= true; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.skip_show_database= true; +# +--echo # +--replace_column 2 # +SHOW VARIABLES like 'thread_stack'; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.thread_stack; +--replace_column 1 # +SELECT @@global.thread_stack; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@session.thread_stack= 7; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@global.thread_stack= 7; +# + +# +# Bug #40657 - assertion with out of range variables and traditional sql_mode +# + +SELECT @@global.expire_logs_days INTO @old_eld; + +SET GLOBAL expire_logs_days = -1; +--echo needs to've been adjusted (0) +SELECT @@global.expire_logs_days; + +SET GLOBAL expire_logs_days = 11; +SET @old_mode=@@sql_mode; +SET SESSION sql_mode = 'TRADITIONAL'; +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL expire_logs_days = 100; +--echo needs to be unchanged (11) +SELECT @@global.expire_logs_days; +SET SESSION sql_mode = @old_mode; + +SET GLOBAL expire_logs_days = 100; +--echo needs to've been adjusted (99) +SELECT @@global.expire_logs_days; + +SET GLOBAL expire_logs_days = 11; +SET GLOBAL expire_logs_days = 99; +--echo needs to pass with no warnings (99) +SELECT @@global.expire_logs_days; + +# cleanup +SET GLOBAL expire_logs_days = @old_eld; + +# show that warning uses underscore (sysvar-name), not hyphens (option-name) +SET GLOBAL auto_increment_offset=-1; +SET GLOBAL auto_increment_offset=0; + + + +# +# Bug#41030 Wrong meta data (incorrect fieldlen) +# + +--enable_metadata +select @@default_storage_engine; +--disable_metadata + +# +# Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id +# + +SET @old_server_id = @@GLOBAL.server_id; +SET GLOBAL server_id = (1 << 32) - 1; +SELECT @@GLOBAL.server_id; +SET GLOBAL server_id = (1 << 32); +SELECT @@GLOBAL.server_id; +SET GLOBAL server_id = (1 << 60); +SELECT @@GLOBAL.server_id; +SET GLOBAL server_id = 0; +SELECT @@GLOBAL.server_id; +SET GLOBAL server_id = -1; +SELECT @@GLOBAL.server_id; +SET GLOBAL server_id = @old_server_id; + +--echo # +--echo # BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback +--echo # + +SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size; +--echo # Set the max_binlog_cache_size to size more than 4GB. +--disable_warnings +SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; +--enable_warnings +--replace_result 5368709120 max_size 4294963200 max_size +SELECT @@GLOBAL.max_binlog_cache_size; +SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; + +--echo # +--echo # Bug #37168 : Missing variable - skip_name_resolve +--echo # + +SELECT @@skip_name_resolve; +SHOW VARIABLES LIKE 'skip_name_resolve'; + +--echo # +--echo # Bug #43233 : Some server variables are clipped during "update," +--echo # not "check" stage +--echo # + +SET @kbs=@@global.key_buffer_size; +SET @kcbs=@@global.key_cache_block_size; + +--echo throw errors in STRICT mode +SET SQL_MODE=STRICT_ALL_TABLES; + +# sys_var_ulonglong_ptr: sys_max_binlog_cache_size +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.max_binlog_cache_size=-1; + +# sys_var_thd_ha_rows: "max_join_size" et al. +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.max_join_size=0; + +# sys_var_key_buffer_size: "key_buffer_size" +--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE +SET @@global.key_buffer_size=0; + +# sys_var_key_cache_long: "key_cache_block_size" et al. +--error ER_WRONG_VALUE_FOR_VAR +SET @@global.key_cache_block_size=0; + +--echo throw warnings in default mode +SET SQL_MODE=DEFAULT; + +SET @@global.max_binlog_cache_size=-1; +SET @@global.max_join_size=0; +# this is an exception. since this is a new error/warning, let's stay +# compatible with the upcoming 5.6. +--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE +SET @@global.key_buffer_size=0; +SET @@global.key_cache_block_size=0; + +# Restore variables +SET @@global.max_binlog_cache_size=DEFAULT; +SET @@global.max_join_size=DEFAULT; +SET @@global.key_buffer_size=@kbs; +SET @@global.key_cache_block_size=@kcbs; + +# +# Bug#56976: added new start-up parameter +# +select @@max_long_data_size; + +--echo # +--echo # Bug#11766424 59527: +--echo # Assert in DECIMAL_BIN_SIZE: +--echo # `SCALE >= 0 && PRECISION > 0 && SCALE <= PRE +--echo # This test also exposed a bug with sql_buffer_result +--echo # + +CREATE TABLE t1(f1 DECIMAL(1,1) UNSIGNED); +INSERT INTO t1 VALUES (0.2),(0.1); +set @a=NULL; +set sql_buffer_result=0; +SELECT 1 as 'one' FROM t1 GROUP BY @a:= ROUND(f1); + +explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); +SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); +SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE @a=f1); + +set sql_buffer_result=1; +explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); +SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); +SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE @a=f1); + +DROP TABLE t1; + +set sql_buffer_result=0; + +# +# Test of CREATE ... CAST +# + +CREATE TABLE t1 AS SELECT @a:= CAST(1 AS UNSIGNED) AS a; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo End of 5.1 tests + +########################################################################### + +--echo +--echo # +--echo # Bug#34828: OF is taken as OFF and a value of 0 is set for variable SQL_notes. +--echo # +--echo + +--echo # Checking sql_notes... +SET @sql_notes_saved = @@sql_notes; + +--echo +SET @@sql_notes = ON; +SELECT @@sql_notes; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_notes = OF; +SELECT @@sql_notes; + +--echo +SET @@sql_notes = OFF; +SELECT @@sql_notes; + +--echo +SET @@sql_notes = @sql_notes_saved; + +--echo +--echo # Checking delay_key_write... +SET @delay_key_write_saved = @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = ON; +SELECT @@delay_key_write; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL delay_key_write = OF; +SELECT @@delay_key_write; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET GLOBAL delay_key_write = AL; +SELECT @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = OFF; +SELECT @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = ALL; +SELECT @@delay_key_write; + +--echo +SET GLOBAL delay_key_write = @delay_key_write_saved; + +--echo +--echo # Checking sql_safe_updates... +SET @sql_safe_updates_saved = @@sql_safe_updates; + +--echo +SET @@sql_safe_updates = ON; +SELECT @@sql_safe_updates; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_safe_updates = OF; +SELECT @@sql_safe_updates; + +--echo +SET @@sql_safe_updates = OFF; +SELECT @@sql_safe_updates; + +--echo +SET @@sql_safe_updates = @sql_safe_updates_saved; + +--echo +--echo # Checking foreign_key_checks... +SET @foreign_key_checks_saved = @@foreign_key_checks; + +--echo +SET @@foreign_key_checks = ON; +SELECT @@foreign_key_checks; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@foreign_key_checks = OF; +SELECT @@foreign_key_checks; + +--echo +SET @@foreign_key_checks = OFF; +SELECT @@foreign_key_checks; + +--echo +SET @@foreign_key_checks = @foreign_key_checks_saved; + +--echo +--echo # Checking unique_checks... +SET @unique_checks_saved = @@unique_checks; + +--echo +SET @@unique_checks = ON; +SELECT @@unique_checks; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@unique_checks = OF; +SELECT @@unique_checks; + +--echo +SET @@unique_checks = OFF; +SELECT @@unique_checks; + +--echo +SET @@unique_checks = @unique_checks_saved; + +--echo +--echo # Checking sql_buffer_result... +SET @sql_buffer_result_saved = @@sql_buffer_result; + +--echo +SET @@sql_buffer_result = ON; +SELECT @@sql_buffer_result; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_buffer_result = OF; +SELECT @@sql_buffer_result; + +--echo +SET @@sql_buffer_result = OFF; +SELECT @@sql_buffer_result; + +--echo +SET @@sql_buffer_result = @sql_buffer_result_saved; + +--echo +--echo # Checking sql_quote_show_create... +SET @sql_quote_show_create_saved = @@sql_quote_show_create; + +--echo +SET @@sql_quote_show_create = ON; +SELECT @@sql_quote_show_create; + +--echo +--error ER_WRONG_VALUE_FOR_VAR +SET @@sql_quote_show_create = OF; +SELECT @@sql_quote_show_create; + +--echo +SET @@sql_quote_show_create = OFF; +SELECT @@sql_quote_show_create; + +--echo +SET @@sql_quote_show_create = @sql_quote_show_create_saved; + +--echo +--echo # End of Bug#34828. +--echo + +--echo # Make sure we can manipulate with autocommit in the +--echo # along with other variables. + + +--disable_warnings +drop table if exists t1; +drop function if exists t1_max; +drop function if exists t1_min; +--enable_warnings + +set sql_mode=""; +--disable_warnings +create table t1 (a int) engine=innodb; +--enable_warnings +set sql_mode=default; +insert into t1(a) values (0), (1); +create function t1_max() returns int return (select max(a) from t1); +create function t1_min() returns int return (select min(a) from t1); +select t1_min(); +select t1_max(); +set @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), + @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), + @@session.autocommit=t1_min(), @@session.autocommit=t1_max(); + +--echo # Cleanup. +drop table t1; +drop function t1_min; +drop function t1_max; + + +########################################################################### + + +--echo # +--echo # Bug #59884: setting charset to 2048 crashes +--echo # + +--error ER_UNKNOWN_CHARACTER_SET +set session character_set_results = 2048; +--error ER_UNKNOWN_CHARACTER_SET +set session character_set_client=2048; +--error ER_UNKNOWN_CHARACTER_SET +set session character_set_connection=2048; +--error ER_UNKNOWN_CHARACTER_SET +set session character_set_server=2048; +--error ER_UNKNOWN_COLLATION +set session collation_server=2048; +--error ER_UNKNOWN_CHARACTER_SET +set session character_set_filesystem=2048; +--error ER_UNKNOWN_CHARACTER_SET +set session character_set_database=2048; +--error ER_UNKNOWN_COLLATION +set session collation_connection=2048; +--error ER_UNKNOWN_COLLATION +set session collation_database=2048; + +# +# Bug #16044655 CRASH: SETTING DEFAULT VALUE FOR SOME VARIABLES +# +--error ER_NO_DEFAULT +set session rand_seed1=DEFAULT; + +# +# Bug #14211565 CRASH WHEN ATTEMPTING TO SET SYSTEM VARIABLE TO RESULT OF VALUES() +# +--error ER_BAD_FIELD_ERROR +set autocommit = value(v); + +# +# MDEV-6673 I_S.SESSION_VARIABLES shows global values +# +set session sql_mode=ansi_quotes; +select * from information_schema.session_variables where variable_name='sql_mode'; +show global status like 'foobar'; +select * from information_schema.session_variables where variable_name='sql_mode'; + +--echo End of 5.5 tests + +# +# test Item_func_get_system_var::print() +# +explain extended select @@VERsion from dual where rand() > @@verSION; +explain extended select @@SESsion.SQL_mode from dual where rand() > @@sesSION.sql_MODE; +explain extended select @@GLObal.COLLATION_connection from dual where rand() > @@gloBAL.collation_CONNECTION; +explain extended select @@FOObar.KEY_BUFfer_size from dual where rand() > @@fooBAR.key_bufFER_SIZE; + +--echo # +--echo # MDEV-15316 Assertion `(thd->lex)->var_list.is_empty()' failed in MYSQLparse +--echo # + +SET GLOBAL mc.key_cache_age_threshold=100, mc.key_cache_block_size=1024; +SET GLOBAL mc.key_buffer_size= 0 /*cleanup*/; |