1# 2# test variables 3# 4--disable_warnings 5drop table if exists t1,t2; 6--enable_warnings 7 8# 9# Bug#19263: variables.test doesn't clean up after itself (I/II -- save) 10# 11set @my_binlog_cache_size =@@global.binlog_cache_size; 12set @my_connect_timeout =@@global.connect_timeout; 13set @my_delayed_insert_timeout =@@global.delayed_insert_timeout; 14set @my_delayed_queue_size =@@global.delayed_queue_size; 15set @my_flush =@@global.flush; 16set @my_flush_time =@@global.flush_time; 17set @my_key_buffer_size =@@global.key_buffer_size; 18set @my_max_binlog_cache_size =@@global.max_binlog_cache_size; 19set @my_max_binlog_size =@@global.max_binlog_size; 20set @my_max_connect_errors =@@global.max_connect_errors; 21set @my_max_connections =@@global.max_connections; 22set @my_max_delayed_threads =@@global.max_delayed_threads; 23set @my_max_heap_table_size =@@global.max_heap_table_size; 24set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads; 25set @my_max_join_size =@@global.max_join_size; 26set @my_max_user_connections =@@global.max_user_connections; 27set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size; 28set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size; 29set @my_net_buffer_length =@@global.net_buffer_length; 30set @my_net_write_timeout =@@global.net_write_timeout; 31set @my_net_read_timeout =@@global.net_read_timeout; 32set @my_server_id =@@global.server_id; 33set @my_slow_launch_time =@@global.slow_launch_time; 34set @my_storage_engine =@@global.default_storage_engine; 35set @my_thread_cache_size =@@global.thread_cache_size; 36set @my_max_allowed_packet =@@global.max_allowed_packet; 37set @my_delay_key_write =@@global.delay_key_write; 38set @my_join_buffer_size =@@global.join_buffer_size; 39set @my_log_warnings =@@global.log_warnings; 40set @my_local_infile =@@global.local_infile; 41 42# case insensitivity tests (new in 5.0) 43set @`test`=1; 44select @test, @`test`, @TEST, @`TEST`, @"teSt"; 45set @TEST=2; 46select @test, @`test`, @TEST, @`TEST`, @"teSt"; 47set @"tEST"=3; 48select @test, @`test`, @TEST, @`TEST`, @"teSt"; 49set @`TeST`=4; 50select @test, @`test`, @TEST, @`TEST`, @"teSt"; 51select @`teST`:=5; 52select @test, @`test`, @TEST, @`TEST`, @"teSt"; 53 54set @select=2,@t5=1.23456; 55select @`select`,@not_used; 56set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL; 57# Expected result "1e-10", windows returns "1e-010" 58--replace_result 1e-010 1e-10 59select @test_int,@test_double,@test_string,@test_string2,@select; 60set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello"; 61select @test_int,@test_double,@test_string,@test_string2; 62set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello"; 63select @test_int,@test_double,@test_string,@test_string2; 64set @test_int=null,@test_double=null,@test_string=null,@test_string2=null; 65select @test_int,@test_double,@test_string,@test_string2; 66select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; 67explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; 68select @t5; 69 70# 71# Test problem with WHERE and variables 72# 73 74CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id)); 75INSERT INTO t1 VALUES (1,'Bozo','USA'),(2,'Ronald','USA'),(3,'Kinko','IRE'),(4,'Mr. Floppy','GB'); 76SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1; 77SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid; 78SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666; 79ALTER TABLE t1 DROP PRIMARY KEY; 80select * from t1 where c_id=@min_cid OR c_id=@max_cid; 81drop table t1; 82 83# 84# Test system variables 85# 86set GLOBAL max_join_size=10; 87set max_join_size=100; 88show variables like 'max_join_size'; 89select * from information_schema.session_variables where variable_name like 'max_join_size'; 90--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR 91show global variables like 'max_join_size'; 92--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR 93select * from information_schema.global_variables where variable_name like 'max_join_size'; 94set GLOBAL max_join_size=2000; 95show global variables like 'max_join_size'; 96select * from information_schema.global_variables where variable_name like 'max_join_size'; 97set max_join_size=DEFAULT; 98--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR 99show variables like 'max_join_size'; 100--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR 101select * from information_schema.session_variables where variable_name like 'max_join_size'; 102set GLOBAL max_join_size=DEFAULT; 103--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR 104show global variables like 'max_join_size'; 105--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR 106select * from information_schema.global_variables where variable_name like 'max_join_size'; 107set @@max_join_size=1000, @@global.max_join_size=2000; 108select @@local.max_join_size, @@global.max_join_size; 109select @@identity, length(@@version)>0; 110select @@VERSION=version(); 111select last_insert_id(345); 112explain extended select last_insert_id(345); 113select @@IDENTITY,last_insert_id(), @@identity; 114explain extended select @@IDENTITY,last_insert_id(), @@identity; 115 116set global concurrent_insert=2; 117show variables like 'concurrent_insert'; 118select * from information_schema.session_variables where variable_name like 'concurrent_insert'; 119set global concurrent_insert=1; 120show variables like 'concurrent_insert'; 121select * from information_schema.session_variables where variable_name like 'concurrent_insert'; 122set global concurrent_insert=0; 123show variables like 'concurrent_insert'; 124select * from information_schema.session_variables where variable_name like 'concurrent_insert'; 125set global concurrent_insert=DEFAULT; 126select @@concurrent_insert; 127 128set default_storage_engine=MYISAM, default_storage_engine="HEAP", global default_storage_engine="MERGE"; 129show local variables like 'default_storage_engine'; 130select * from information_schema.session_variables where variable_name like 'default_storage_engine'; 131show global variables like 'default_storage_engine'; 132select * from information_schema.global_variables where variable_name like 'default_storage_engine'; 133 134set GLOBAL myisam_max_sort_file_size=2000000; 135show global variables like 'myisam_max_sort_file_size'; 136select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; 137set GLOBAL myisam_max_sort_file_size=default; 138--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE 139show global variables like 'myisam_max_sort_file_size'; 140--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE 141select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; 142 143# bug#22891: modified to take read-only SESSION net_buffer_length into account 144set global net_retry_count=10, session net_retry_count=10; 145set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; 146show global variables like 'net_%'; 147select * from information_schema.global_variables where variable_name like 'net_%' order by 1; 148show session variables like 'net_%'; 149select * from information_schema.session_variables where variable_name like 'net_%' order by 1; 150set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; 151show global variables like 'net_%'; 152select * from information_schema.global_variables where variable_name like 'net_%' order by 1; 153set global net_buffer_length=1; 154show global variables like 'net_buffer_length'; 155select * from information_schema.global_variables where variable_name like 'net_buffer_length'; 156#warning 1292 157set global net_buffer_length=2000000000; 158show global variables like 'net_buffer_length'; 159select * from information_schema.global_variables where variable_name like 'net_buffer_length'; 160 161set character set cp1251_koi8; 162show variables like "character_set_client"; 163select * from information_schema.session_variables where variable_name like 'character_set_client'; 164select @@timestamp>0; 165 166set @@rand_seed1=10000000,@@rand_seed2=1000000; 167select ROUND(RAND(),5); 168 169 170--echo 171--echo ==+ Testing %alloc% system variables +== 172--echo ==+ NOTE: These values *must* be a multiple of 1024 +== 173--echo ==+ Other values will be rounded down to nearest multiple +== 174--echo 175--echo ==+ Show initial values +== 176SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', 177'query_alloc_block_size', 'query_prealloc_size', 178'transaction_alloc_block_size', 'transaction_prealloc_size'); 179 180--echo ==+ information_schema data +== 181SELECT * FROM information_schema.session_variables 182WHERE variable_name IN ('range_alloc_block_size', 183'query_alloc_block_size', 'query_prealloc_size', 184'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1; 185--echo Testing values that are multiples of 1024 186set @@range_alloc_block_size=1024*15+1024; 187set @@query_alloc_block_size=1024*15+1024*2; 188set @@query_prealloc_size=1024*18-1024; 189set @@transaction_alloc_block_size=1024*21-1024*1; 190set @@transaction_prealloc_size=1024*21-2048; 191--echo ==+ Check manipulated values ==+ 192select @@query_alloc_block_size; 193SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', 194'query_alloc_block_size', 'query_prealloc_size', 195'transaction_alloc_block_size', 'transaction_prealloc_size'); 196--echo ==+ information_schema data +== 197SELECT * FROM information_schema.session_variables 198WHERE variable_name IN ('range_alloc_block_size', 199'query_alloc_block_size', 'query_prealloc_size', 200'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1; 201--echo ==+ Manipulate variable values +== 202--echo Testing values that are not 1024 multiples 203set @@range_alloc_block_size=1024*16+1023; 204set @@query_alloc_block_size=1024*17+2; 205set @@query_prealloc_size=1024*18-1023; 206set @@transaction_alloc_block_size=1024*20-1; 207set @@transaction_prealloc_size=1024*21-1; 208--echo ==+ Check manipulated values ==+ 209SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', 210'query_alloc_block_size', 'query_prealloc_size', 211'transaction_alloc_block_size', 'transaction_prealloc_size'); 212--echo ==+ information_schema data +== 213SELECT * FROM information_schema.session_variables 214WHERE variable_name IN ('range_alloc_block_size', 215'query_alloc_block_size', 'query_prealloc_size', 216'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1; 217--echo ==+ Set values back to the default values +== 218set @@range_alloc_block_size=default; 219set @@query_alloc_block_size=default, @@query_prealloc_size=default; 220set transaction_alloc_block_size=default, @@transaction_prealloc_size=default; 221--echo ==+ Check the values now that they are reset +== 222SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size', 223'query_alloc_block_size', 'query_prealloc_size', 224'transaction_alloc_block_size', 'transaction_prealloc_size'); 225 226# 227# Bug #10904 Illegal mix of collations between 228# a system variable and a constant 229# 230SELECT @@version LIKE 'non-existent'; 231SELECT @@version_compile_os LIKE 'non-existent'; 232 233# The following should give errors 234 235--error ER_UNKNOWN_SYSTEM_VARIABLE 236set unknown_variable=1; 237--error ER_WRONG_TYPE_FOR_VAR 238set max_join_size="hello"; 239--error ER_UNKNOWN_STORAGE_ENGINE 240set default_storage_engine=UNKNOWN_TABLE_TYPE; 241--error ER_WRONG_VALUE_FOR_VAR 242set default_storage_engine=MERGE, sql_warnings=NULL; 243show local variables like 'default_storage_engine'; 244--error ER_UNKNOWN_CHARACTER_SET 245set character_set_client=UNKNOWN_CHARACTER_SET; 246--error ER_UNKNOWN_COLLATION 247set collation_connection=UNKNOWN_COLLATION; 248--error ER_WRONG_VALUE_FOR_VAR 249set character_set_client=NULL; 250--error ER_WRONG_VALUE_FOR_VAR 251set collation_connection=NULL; 252--error ER_INCORRECT_GLOBAL_LOCAL_VAR 253select @@global.timestamp; 254--error ER_INCORRECT_GLOBAL_LOCAL_VAR 255set @@version=''; 256--error ER_GLOBAL_VARIABLE 257set @@concurrent_insert=1; 258--error ER_GLOBAL_VARIABLE 259set myisam_max_sort_file_size=100; 260--error ER_WRONG_VALUE_FOR_VAR 261set @@SQL_WARNINGS=NULL; 262 263# Test setting all variables 264 265set autocommit=1; 266select @@autocommit; 267set global binlog_cache_size=100; 268set bulk_insert_buffer_size=100; 269set character set cp1251_koi8; 270set character set default; 271set @@global.concurrent_insert=1; 272set global connect_timeout=100; 273select @@delay_key_write; 274set global delay_key_write="OFF"; 275select @@delay_key_write; 276set global delay_key_write=ALL; 277select @@delay_key_write; 278set global delay_key_write=1; 279select @@delay_key_write; 280set global delayed_insert_limit=100; 281set global delayed_insert_timeout=100; 282set global delayed_queue_size=100; 283set global flush=1; 284set global flush_time=100; 285set insert_id=1; 286set interactive_timeout=100; 287set join_buffer_size=100; 288set last_insert_id=1; 289set global local_infile=1; 290set long_query_time=0.000001; 291select @@long_query_time; 292set long_query_time=100.000001; 293select @@long_query_time; 294set low_priority_updates=1; 295set global max_allowed_packet=100; 296set global max_binlog_cache_size=100; 297set global max_binlog_size=100; 298set global max_connect_errors=100; 299set global max_connections=100; 300set global max_delayed_threads=100; 301set max_heap_table_size=100; 302set max_join_size=100; 303set max_sort_length=100; 304set global max_user_connections=100; 305set global max_write_lock_count=100; 306set myisam_sort_buffer_size=100; 307set global net_buffer_length=100; 308set net_read_timeout=100; 309set net_write_timeout=100; 310set read_buffer_size=100; 311set read_rnd_buffer_size=100; 312set global server_id=100; 313set global slow_launch_time=100; 314set sort_buffer_size=100; 315set @@max_sp_recursion_depth=10; 316select @@max_sp_recursion_depth; 317set @@max_sp_recursion_depth=0; 318select @@max_sp_recursion_depth; 319set sql_auto_is_null=1; 320select @@sql_auto_is_null; 321set @@sql_auto_is_null=0; 322select @@sql_auto_is_null; 323set sql_big_selects=1; 324set sql_buffer_result=1; 325set sql_log_bin=1; 326set sql_log_off=1; 327set sql_quote_show_create=1; 328set sql_safe_updates=1; 329set sql_select_limit=1; 330# reset it, so later tests don't get confused 331set sql_select_limit=default; 332set sql_warnings=1; 333set @save_table_open_cache=@@table_open_cache; 334set global table_open_cache=100; 335set default_storage_engine=myisam; 336set global thread_cache_size=100; 337set timestamp=1, timestamp=default; 338set tmp_table_size=1024; 339set tx_isolation="READ-COMMITTED"; 340set wait_timeout=100; 341set log_warnings=1; 342set global log_warnings=1; 343 344# 345# Bugs: #20392: INSERT_ID session variable has weird value 346# 347select @@session.insert_id; 348set @save_insert_id=@@session.insert_id; 349set session insert_id=20; 350select @@session.insert_id; 351 352set session last_insert_id=100; 353select @@session.insert_id; 354select @@session.last_insert_id; 355select @@session.insert_id; 356 357set @@session.insert_id=@save_insert_id; 358select @@session.insert_id; 359 360# 361# key buffer 362# 363 364create table t1 (a int not null auto_increment, primary key(a)); 365create table t2 (a int not null auto_increment, primary key(a)); 366insert into t1 values(null),(null),(null); 367insert into t2 values(null),(null),(null); 368set global key_buffer_size=100000; 369select @@key_buffer_size; 370select * from t1 where a=2; 371select * from t2 where a=3; 372check table t1,t2; 373select max(a) +1, max(a) +2 into @xx,@yy from t1; 374drop table t1,t2; 375 376# 377# error conditions 378# 379 380--error ER_UNKNOWN_SYSTEM_VARIABLE 381select @@xxxxxxxxxx; 382select 1; 383 384--error ER_INCORRECT_GLOBAL_LOCAL_VAR 385select @@session.key_buffer_size; 386 387--error ER_GLOBAL_VARIABLE 388set ft_boolean_syntax = @@init_connect; 389--error ER_WRONG_VALUE_FOR_VAR 390set global ft_boolean_syntax = @@init_connect; 391--error ER_GLOBAL_VARIABLE 392set init_connect = NULL; 393set global init_connect = NULL; 394--error ER_GLOBAL_VARIABLE 395set ft_boolean_syntax = @@init_connect; 396--error ER_WRONG_VALUE_FOR_VAR 397set global ft_boolean_syntax = @@init_connect; 398 399# Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as 400# expected: check that there is no overflow when 64-bit unsigned 401# variables are set 402 403set global myisam_max_sort_file_size=4294967296; 404--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE 405show global variables like 'myisam_max_sort_file_size'; 406--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE 407select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; 408set global myisam_max_sort_file_size=default; 409 410# 411# swap 412# 413 414# set some variables to play with 415set @@global.max_user_connections=10, @@local.max_join_size=1000; 416 417select @@global.max_user_connections,@@local.max_join_size; 418set @svc=@@global.max_user_connections, @svj=@@local.max_join_size; 419select @@global.max_user_connections,@@local.max_join_size; 420set @@global.max_user_connections=111,@@local.max_join_size=222; 421select @@global.max_user_connections,@@local.max_join_size; 422set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections; 423select @@global.max_user_connections,@@local.max_join_size; 424set @@global.max_user_connections=@svc, @@local.max_join_size=@svj; 425select @@global.max_user_connections,@@local.max_join_size; 426set @a=1, @b=2; 427set @a=@b, @b=@a; 428select @a, @b; 429set @@global.max_user_connections=@my_max_user_connections; 430 431# 432# Bug#2586:Disallow global/session/local as structured var. instance names 433# 434--error ER_UNKNOWN_STRUCTURED_VARIABLE 435set @@global.global.key_buffer_size= 1; 436--error ER_UNKNOWN_STRUCTURED_VARIABLE 437set GLOBAL global.key_buffer_size= 1; 438--error ER_PARSE_ERROR 439SELECT @@global.global.key_buffer_size; 440--error ER_PARSE_ERROR 441SELECT @@global.session.key_buffer_size; 442--error ER_PARSE_ERROR 443SELECT @@global.local.key_buffer_size; 444 445# BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0) 446set @tstlw = @@log_warnings; 447show global variables like 'log_warnings'; 448select * from information_schema.global_variables where variable_name like 'log_warnings'; 449set global log_warnings = 0; 450show global variables like 'log_warnings'; 451select * from information_schema.global_variables where variable_name like 'log_warnings'; 452set global log_warnings = 42; 453show global variables like 'log_warnings'; 454select * from information_schema.global_variables where variable_name like 'log_warnings'; 455set global log_warnings = @tstlw; 456show global variables like 'log_warnings'; 457select * from information_schema.global_variables where variable_name like 'log_warnings'; 458 459# 460# BUG#4788 show create table provides incorrect statement 461# 462# What default width have numeric types? 463create table t1 ( 464 c1 tinyint, 465 c2 smallint, 466 c3 mediumint, 467 c4 int, 468 c5 bigint); 469show create table t1; 470drop table t1; 471# 472# What types and widths have variables? 473set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0; 474create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4; 475show create table t1; 476drop table t1; 477 478 479# 480# Bug #6993: myisam_data_pointer_size 481# Wrong bug report, data pointer size must be restricted to 7, 482# setting to 8 will not work on all computers, myisamchk and 483# the server may see a wrong value, such as 0 or negative number 484# if 8 bytes is set. 485# 486 487SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7; 488SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE'; 489SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE'; 490 491# 492# Bug #6958: negative arguments to integer options wrap around 493# 494 495SET GLOBAL table_open_cache=-1; 496SHOW VARIABLES LIKE 'table_open_cache'; 497SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache'; 498SET GLOBAL table_open_cache=@save_table_open_cache; 499 500# 501# Bugs12363: character_set_results is nullable, 502# but value_ptr returns string "NULL" 503# 504set character_set_results=NULL; 505select ifnull(@@character_set_results,"really null"); 506set names latin1; 507 508# 509# Tests for lc_time_names 510# Note, when adding new locales, please fix ID accordingly: 511# - to test the last ID (currently 108) 512# - and the next after the last (currently 109) 513# 514--echo *** Various tests with LC_TIME_NAMES 515--echo *** LC_TIME_NAMES: testing case insensitivity 516set @@lc_time_names='ru_ru'; 517select @@lc_time_names; 518--echo *** LC_TIME_NAMES: testing with a user variable 519set @lc='JA_JP'; 520set @@lc_time_names=@lc; 521select @@lc_time_names; 522--echo *** LC_TIME_NAMES: testing with string expressions 523set lc_time_names=concat('de','_','DE'); 524select @@lc_time_names; 525--error ER_UNKNOWN_LOCALE 526set lc_time_names=concat('de','+','DE'); 527select @@lc_time_names; 528--echo LC_TIME_NAMES: testing with numeric expressions 529set @@lc_time_names=1+2; 530select @@lc_time_names; 531--error ER_WRONG_TYPE_FOR_VAR 532set @@lc_time_names=1/0; 533select @@lc_time_names; 534set lc_time_names=en_US; 535--echo LC_TIME_NAMES: testing NULL and a negative number: 536--error ER_WRONG_VALUE_FOR_VAR 537set lc_time_names=NULL; 538--error ER_UNKNOWN_LOCALE 539set lc_time_names=-1; 540select @@lc_time_names; 541--echo LC_TIME_NAMES: testing locale with the last ID: 542set lc_time_names=110; 543select @@lc_time_names; 544--echo LC_TIME_NAMES: testing a number beyond the valid ID range: 545--error ER_UNKNOWN_LOCALE 546set lc_time_names=111; 547select @@lc_time_names; 548--echo LC_TIME_NAMES: testing that 0 is en_US: 549set lc_time_names=0; 550select @@lc_time_names; 551 552# 553# Bug #22648 LC_TIME_NAMES: Setting GLOBAL has no effect 554# 555select @@global.lc_time_names, @@lc_time_names; 556set @@global.lc_time_names=fr_FR; 557select @@global.lc_time_names, @@lc_time_names; 558connect (con1,localhost,root,,); 559connection con1; 560select @@global.lc_time_names, @@lc_time_names; 561set @@lc_time_names=ru_RU; 562select @@global.lc_time_names, @@lc_time_names; 563disconnect con1; 564connection default; 565select @@global.lc_time_names, @@lc_time_names; 566set lc_time_names=default; 567select @@global.lc_time_names, @@lc_time_names; 568set @@global.lc_time_names=default; 569select @@global.lc_time_names, @@lc_time_names; 570set @@lc_time_names=default; 571select @@global.lc_time_names, @@lc_time_names; 572 573 574# 575# Bug #13334: query_prealloc_size default less than minimum 576# 577set @test = @@query_prealloc_size; 578set @@query_prealloc_size = @test; 579select @@query_prealloc_size = @test; 580 581# 582# Bug#31588 buffer overrun when setting variables 583# 584# Buffer-size Off By One. Should throw valgrind-warning without fix #31588. 585--error ER_WRONG_VALUE_FOR_VAR 586set global sql_mode=repeat('a',80); 587 588--echo End of 4.1 tests 589 590# 591# Bug#6282 Packet error with SELECT INTO 592# 593 594create table t1 (a int); 595select a into @x from t1; 596show warnings; 597drop table t1; 598 599# 600# Bug #10339: read only variables. 601# 602 603--error ER_INCORRECT_GLOBAL_LOCAL_VAR 604set @@warning_count=1; 605--error ER_INCORRECT_GLOBAL_LOCAL_VAR 606set @@global.error_count=1; 607 608# 609# Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform 610# 611 612--disable_warnings 613set @@max_heap_table_size= 4294967296; 614select @@max_heap_table_size > 0; 615set global max_heap_table_size= 4294967296; 616select @@max_heap_table_size > 0; 617set @@max_heap_table_size= 4294967296; 618select @@max_heap_table_size > 0; 619--enable_warnings 620 621# 622# Bug #11775 Variable character_set_system does not exist (sometimes) 623# 624select @@character_set_system; 625--error ER_INCORRECT_GLOBAL_LOCAL_VAR 626set global character_set_system = latin1; 627--error ER_INCORRECT_GLOBAL_LOCAL_VAR 628set @@global.version_compile_os='234'; 629 630# 631# Check character_set_filesystem variable 632# 633set character_set_filesystem=latin1; 634select @@character_set_filesystem; 635set @@global.character_set_filesystem=latin2; 636set character_set_filesystem=latin1; 637select @@character_set_filesystem; 638set @@global.character_set_filesystem=latin2; 639set character_set_filesystem=default; 640select @@character_set_filesystem; 641set @@global.character_set_filesystem=default; 642select @@global.character_set_filesystem; 643 644# 645# Bug #17849: Show sql_big_selects in SHOW VARIABLES 646# 647set @old_sql_big_selects = @@sql_big_selects; 648set @@sql_big_selects = 1; 649show variables like 'sql_big_selects'; 650select * from information_schema.session_variables where variable_name like 'sql_big_selects'; 651set @@sql_big_selects = @old_sql_big_selects; 652 653# 654# Bug #16195: SHOW VARIABLES doesn't report correctly sql_warnings and 655# sql_notes values 656# 657set @@sql_notes = 0, @@sql_warnings = 0; 658show variables like 'sql_notes'; 659select * from information_schema.session_variables where variable_name like 'sql_notes'; 660show variables like 'sql_warnings'; 661select * from information_schema.session_variables where variable_name like 'sql_warnings'; 662set @@sql_notes = 1, @@sql_warnings = 1; 663show variables like 'sql_notes'; 664select * from information_schema.session_variables where variable_name like 'sql_notes'; 665show variables like 'sql_warnings'; 666select * from information_schema.session_variables where variable_name like 'sql_warnings'; 667 668# 669# Bug #12792: @@system_time_zone is not SELECTable. 670# 671# Don't actually output, since it depends on the system 672--replace_column 1 # 673select @@system_time_zone; 674 675# 676# Bug #15684: system variables cannot be SELECTed (e.g. @@version_comment) 677# 678# Don't actually output, since it depends on the system 679--replace_column 1 # 2 # 3 # 4 # 680select @@version, @@version_comment, @@version_compile_machine, 681 @@version_compile_os; 682 683# 684# Bug #1039: make tmpdir and datadir available as @@variables (also included 685# basedir) 686# 687# Don't actually output, since it depends on the system 688--replace_column 1 # 2 # 3 # 689select @@basedir, @@datadir, @@tmpdir; 690--replace_column 2 # 691show variables like 'basedir'; 692--replace_column 2 # 693select * from information_schema.session_variables where variable_name like 'basedir'; 694--replace_column 2 # 695show variables like 'datadir'; 696--replace_column 2 # 697select * from information_schema.session_variables where variable_name like 'datadir'; 698--replace_column 2 # 699show variables like 'tmpdir'; 700--replace_column 2 # 701select * from information_schema.session_variables where variable_name like 'tmpdir'; 702 703# 704# Bug #19606: make ssl settings available via SHOW VARIABLES and @@variables 705# 706# Don't actually output, since it depends on the system 707set sort_buffer_size=1024*8; 708--replace_column 1 # 2 # 3 # 4 # 5 # 709select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key; 710--replace_column 2 # 711show variables like 'ssl%'; 712--replace_column 2 # 713select * from information_schema.session_variables where variable_name like 'ssl%' order by 1; 714 715# 716# Bug #19616: make log_queries_not_using_indexes available in SHOW VARIABLES 717# and as @@log_queries_not_using_indexes 718# 719select @@log_queries_not_using_indexes; 720show variables like 'log_queries_not_using_indexes'; 721select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes'; 722 723# 724# Bug#20908: Crash if select @@"" 725# 726--error ER_PARSE_ERROR 727select @@""; 728--error ER_PARSE_ERROR 729select @@&; 730--error ER_PARSE_ERROR 731select @@@; 732 733# 734# Bug#20166 mysql-test-run.pl does not test system privilege tables creation 735# 736# Don't actually output, since it depends on the system 737--replace_column 1 # 738select @@hostname; 739--error ER_INCORRECT_GLOBAL_LOCAL_VAR 740set @@hostname= "anothername"; 741--replace_column 2 # 742show variables like 'hostname'; 743 744--echo # 745--echo # BUG#37408 - Compressed MyISAM files should not require/use mmap() 746--echo # 747--echo # Test 'myisam_mmap_size' option is not dynamic 748--error ER_INCORRECT_GLOBAL_LOCAL_VAR 749SET @@myisam_mmap_size= 500M; 750 751 752--echo # 753--echo # Bug #52315: utc_date() crashes when system time > year 2037 754--echo # 755 756--error 0, ER_WRONG_VALUE_FOR_VAR 757SET TIMESTAMP=2*1024*1024*1024; 758--echo #Should not crash 759--disable_result_log 760SELECT UTC_DATE(); 761--enable_result_log 762SET TIMESTAMP=DEFAULT; 763 764 765--echo End of 5.0 tests 766 767# 768# Bug#36446: Attempt to set @@join_buffer_size to its minimum value 769# produces spurious warning 770# 771 772# set to 1 so mysqld will correct to minimum (+ warn) 773set join_buffer_size=1; 774# save minimum 775set @save_join_buffer_size=@@join_buffer_size; 776# set minimum 777set join_buffer_size=@save_join_buffer_size; 778 779# This is at the very after the versioned tests, since it involves doing 780# cleanup 781# 782# Bug #19263: variables.test doesn't clean up after itself (II/II -- 783# restore) 784# 785set global binlog_cache_size =@my_binlog_cache_size; 786set global connect_timeout =@my_connect_timeout; 787set global delayed_insert_timeout =@my_delayed_insert_timeout; 788set global delayed_queue_size =@my_delayed_queue_size; 789set global flush =@my_flush; 790set global flush_time =@my_flush_time; 791set global key_buffer_size =@my_key_buffer_size; 792set global max_binlog_cache_size =@my_max_binlog_cache_size; 793set global max_binlog_size =@my_max_binlog_size; 794set global max_connect_errors =@my_max_connect_errors; 795set global max_connections =@my_max_connections; 796set global max_delayed_threads =@my_max_delayed_threads; 797set global max_heap_table_size =@my_max_heap_table_size; 798set global max_insert_delayed_threads=@my_max_insert_delayed_threads; 799set global max_join_size =@my_max_join_size; 800set global max_user_connections =@my_max_user_connections; 801set global max_write_lock_count =default; 802set global myisam_data_pointer_size =@my_myisam_data_pointer_size; 803set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size; 804set global net_buffer_length =@my_net_buffer_length; 805set global net_write_timeout =@my_net_write_timeout; 806set global net_read_timeout =@my_net_read_timeout; 807set global server_id =@my_server_id; 808set global slow_launch_time =@my_slow_launch_time; 809set global default_storage_engine =@my_storage_engine; 810set global thread_cache_size =@my_thread_cache_size; 811set global max_allowed_packet =@my_max_allowed_packet; 812set global delay_key_write =@my_delay_key_write; 813set global join_buffer_size =@my_join_buffer_size; 814set global log_warnings =@my_log_warnings; 815set global local_infile =@my_local_infile; 816 817# 818# Bug#28580 Repeatation of status variables 819# 820--replace_column 2 # 821show global variables where Variable_name='table_definition_cache'; 822# Note: table_lock_wait_timeout no longer exists. See bug#45225. 823 824########################################################################### 825 826--echo 827--echo # -- 828--echo # -- Bug#34820: log_output can be set to illegal value. 829--echo # -- 830 831--error ER_WRONG_VALUE_FOR_VAR 832SET GLOBAL log_output = ''; 833 834--error ER_WRONG_VALUE_FOR_VAR 835SET GLOBAL log_output = 0; 836 837--echo 838--echo # -- End of Bug#34820. 839 840# 841# Bug#28234 - global/session scope - documentation vs implementation 842# 843--echo 844--echo # 845SHOW VARIABLES like 'ft_max_word_len'; 846--error ER_INCORRECT_GLOBAL_LOCAL_VAR 847SELECT @@session.ft_max_word_len; 848SELECT @@global.ft_max_word_len; 849--error ER_INCORRECT_GLOBAL_LOCAL_VAR 850SET @@session.ft_max_word_len= 7; 851--error ER_INCORRECT_GLOBAL_LOCAL_VAR 852SET @@global.ft_max_word_len= 7; 853# 854--echo # 855SHOW VARIABLES like 'ft_min_word_len'; 856--error ER_INCORRECT_GLOBAL_LOCAL_VAR 857SELECT @@session.ft_min_word_len; 858SELECT @@global.ft_min_word_len; 859--error ER_INCORRECT_GLOBAL_LOCAL_VAR 860SET @@session.ft_min_word_len= 7; 861--error ER_INCORRECT_GLOBAL_LOCAL_VAR 862SET @@global.ft_min_word_len= 7; 863# 864--echo # 865SHOW VARIABLES like 'ft_query_expansion_limit'; 866--error ER_INCORRECT_GLOBAL_LOCAL_VAR 867SELECT @@session.ft_query_expansion_limit; 868SELECT @@global.ft_query_expansion_limit; 869--error ER_INCORRECT_GLOBAL_LOCAL_VAR 870SET @@session.ft_query_expansion_limit= 7; 871--error ER_INCORRECT_GLOBAL_LOCAL_VAR 872SET @@global.ft_query_expansion_limit= 7; 873# 874--echo # 875SHOW VARIABLES like 'ft_stopword_file'; 876--error ER_INCORRECT_GLOBAL_LOCAL_VAR 877SELECT @@session.ft_stopword_file; 878SELECT @@global.ft_stopword_file; 879--error ER_INCORRECT_GLOBAL_LOCAL_VAR 880SET @@session.ft_stopword_file= 'x'; 881--error ER_INCORRECT_GLOBAL_LOCAL_VAR 882SET @@global.ft_stopword_file= 'x'; 883# 884# Additional variables fixed. 885# 886--echo # 887--replace_column 2 # 888SHOW VARIABLES like 'back_log'; 889--error ER_INCORRECT_GLOBAL_LOCAL_VAR 890SELECT @@session.back_log; 891SELECT @@global.back_log > 0; 892--error ER_INCORRECT_GLOBAL_LOCAL_VAR 893SET @@session.back_log= 7; 894--error ER_INCORRECT_GLOBAL_LOCAL_VAR 895SET @@global.back_log= 7; 896# 897--echo # 898--replace_column 2 # 899SHOW VARIABLES like 'large_files_support'; 900--error ER_INCORRECT_GLOBAL_LOCAL_VAR 901SELECT @@session.large_files_support; 902--replace_column 1 # 903SELECT @@global.large_files_support; 904--error ER_INCORRECT_GLOBAL_LOCAL_VAR 905SET @@session.large_files_support= true; 906--error ER_INCORRECT_GLOBAL_LOCAL_VAR 907SET @@global.large_files_support= true; 908# 909--echo # 910--replace_column 2 # 911SHOW VARIABLES like 'character_sets_dir'; 912--error ER_INCORRECT_GLOBAL_LOCAL_VAR 913SELECT @@session.character_sets_dir; 914--replace_column 1 # 915SELECT @@global.character_sets_dir; 916--error ER_INCORRECT_GLOBAL_LOCAL_VAR 917SET @@session.character_sets_dir= 'x'; 918--error ER_INCORRECT_GLOBAL_LOCAL_VAR 919SET @@global.character_sets_dir= 'x'; 920# 921--echo # 922--replace_column 2 # 923SHOW VARIABLES like 'init_file'; 924--error ER_INCORRECT_GLOBAL_LOCAL_VAR 925SELECT @@session.init_file; 926--replace_column 1 # 927SELECT @@global.init_file; 928--error ER_INCORRECT_GLOBAL_LOCAL_VAR 929SET @@session.init_file= 'x'; 930--error ER_INCORRECT_GLOBAL_LOCAL_VAR 931SET @@global.init_file= 'x'; 932# 933--echo # 934--replace_column 2 # 935SHOW VARIABLES like 'lc_messages_dir'; 936--error ER_INCORRECT_GLOBAL_LOCAL_VAR 937SELECT @@session.lc_messages_dir; 938--replace_column 1 # 939SELECT @@global.lc_messages_dir; 940--error ER_INCORRECT_GLOBAL_LOCAL_VAR 941SET @@session.lc_messages_dir= 'x'; 942--error ER_INCORRECT_GLOBAL_LOCAL_VAR 943SET @@global.lc_messages_dir= 'x'; 944# 945--echo # 946--replace_column 2 # 947SHOW VARIABLES like 'large_page_size'; 948--error ER_INCORRECT_GLOBAL_LOCAL_VAR 949SELECT @@session.large_page_size; 950--replace_column 1 # 951SELECT @@global.large_page_size; 952--error ER_INCORRECT_GLOBAL_LOCAL_VAR 953SET @@session.large_page_size= 7; 954--error ER_INCORRECT_GLOBAL_LOCAL_VAR 955SET @@global.large_page_size= 7; 956# 957--echo # 958--replace_column 2 # 959SHOW VARIABLES like 'large_pages'; 960--error ER_INCORRECT_GLOBAL_LOCAL_VAR 961SELECT @@session.large_pages; 962--replace_column 1 # 963SELECT @@global.large_pages; 964--error ER_INCORRECT_GLOBAL_LOCAL_VAR 965SET @@session.large_pages= true; 966--error ER_INCORRECT_GLOBAL_LOCAL_VAR 967SET @@global.large_pages= true; 968# 969--echo # 970SHOW VARIABLES like 'log_bin'; 971--error ER_INCORRECT_GLOBAL_LOCAL_VAR 972SELECT @@session.log_bin; 973SELECT @@global.log_bin; 974--error ER_INCORRECT_GLOBAL_LOCAL_VAR 975SET @@session.log_bin= true; 976--error ER_INCORRECT_GLOBAL_LOCAL_VAR 977SET @@global.log_bin= true; 978# 979--echo # 980--replace_column 2 # 981SHOW VARIABLES like 'log_error'; 982--error ER_INCORRECT_GLOBAL_LOCAL_VAR 983SELECT @@session.log_error; 984--replace_column 1 # 985SELECT @@global.log_error; 986--error ER_INCORRECT_GLOBAL_LOCAL_VAR 987SET @@session.log_error= 'x'; 988--error ER_INCORRECT_GLOBAL_LOCAL_VAR 989SET @@global.log_error= 'x'; 990# 991--echo # 992--replace_column 2 # 993SHOW VARIABLES like 'lower_case_file_system'; 994--error ER_INCORRECT_GLOBAL_LOCAL_VAR 995SELECT @@session.lower_case_file_system; 996--replace_column 1 # 997SELECT @@global.lower_case_file_system; 998--error ER_INCORRECT_GLOBAL_LOCAL_VAR 999SET @@session.lower_case_file_system= true; 1000--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1001SET @@global.lower_case_file_system= true; 1002# 1003--echo # 1004--replace_column 2 # 1005SHOW VARIABLES like 'lower_case_table_names'; 1006--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1007SELECT @@session.lower_case_table_names; 1008--replace_column 1 # 1009SELECT @@global.lower_case_table_names; 1010--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1011SET @@session.lower_case_table_names= 7; 1012--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1013SET @@global.lower_case_table_names= 7; 1014# 1015--echo # 1016SHOW VARIABLES like 'myisam_recover_options'; 1017--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1018SELECT @@session.myisam_recover_options; 1019SELECT @@global.myisam_recover_options; 1020--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1021SET @@session.myisam_recover_options= 'x'; 1022--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1023SET @@global.myisam_recover_options= 'x'; 1024# 1025--echo # 1026--replace_column 2 # 1027SHOW VARIABLES like 'open_files_limit'; 1028--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1029SELECT @@session.open_files_limit; 1030--replace_column 1 # 1031SELECT @@global.open_files_limit; 1032--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1033SET @@session.open_files_limit= 7; 1034--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1035SET @@global.open_files_limit= 7; 1036# 1037--echo # 1038--replace_column 2 # 1039SHOW VARIABLES like 'pid_file'; 1040--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1041SELECT @@session.pid_file; 1042--replace_column 1 # 1043SELECT @@global.pid_file; 1044--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1045SET @@session.pid_file= 'x'; 1046--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1047SET @@global.pid_file= 'x'; 1048# 1049--echo # 1050--replace_column 2 # 1051SHOW VARIABLES like 'plugin_dir'; 1052--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1053SELECT @@session.plugin_dir; 1054--replace_column 1 # 1055SELECT @@global.plugin_dir; 1056--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1057SET @@session.plugin_dir= 'x'; 1058--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1059SET @@global.plugin_dir= 'x'; 1060# 1061--echo # 1062--replace_column 2 # 1063SHOW VARIABLES like 'port'; 1064--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1065SELECT @@session.port; 1066--replace_column 1 # 1067SELECT @@global.port; 1068--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1069SET @@session.port= 7; 1070--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1071SET @@global.port= 7; 1072# 1073--echo # 1074SHOW VARIABLES like 'protocol_version'; 1075--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1076SELECT @@session.protocol_version; 1077SELECT @@global.protocol_version; 1078--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1079SET @@session.protocol_version= 7; 1080--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1081SET @@global.protocol_version= 7; 1082# 1083--echo # 1084SHOW VARIABLES like 'skip_external_locking'; 1085--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1086SELECT @@session.skip_external_locking; 1087SELECT @@global.skip_external_locking; 1088--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1089SET @@session.skip_external_locking= true; 1090--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1091SET @@global.skip_external_locking= true; 1092# 1093--echo # 1094SHOW VARIABLES like 'skip_networking'; 1095--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1096SELECT @@session.skip_networking; 1097SELECT @@global.skip_networking; 1098--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1099SET @@session.skip_networking= true; 1100--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1101SET @@global.skip_networking= true; 1102# 1103--echo # 1104SHOW VARIABLES like 'skip_show_database'; 1105--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1106SELECT @@session.skip_show_database; 1107SELECT @@global.skip_show_database; 1108--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1109SET @@session.skip_show_database= true; 1110--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1111SET @@global.skip_show_database= true; 1112# 1113--echo # 1114--replace_column 2 # 1115SHOW VARIABLES like 'thread_stack'; 1116--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1117SELECT @@session.thread_stack; 1118--replace_column 1 # 1119SELECT @@global.thread_stack; 1120--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1121SET @@session.thread_stack= 7; 1122--error ER_INCORRECT_GLOBAL_LOCAL_VAR 1123SET @@global.thread_stack= 7; 1124# 1125 1126# 1127# Bug #40657 - assertion with out of range variables and traditional sql_mode 1128# 1129 1130SELECT @@global.expire_logs_days INTO @old_eld; 1131 1132SET GLOBAL expire_logs_days = -1; 1133--echo needs to've been adjusted (0) 1134SELECT @@global.expire_logs_days; 1135 1136SET GLOBAL expire_logs_days = 11; 1137SET @old_mode=@@sql_mode; 1138SET SESSION sql_mode = 'TRADITIONAL'; 1139--error ER_WRONG_VALUE_FOR_VAR 1140SET GLOBAL expire_logs_days = 100; 1141--echo needs to be unchanged (11) 1142SELECT @@global.expire_logs_days; 1143SET SESSION sql_mode = @old_mode; 1144 1145SET GLOBAL expire_logs_days = 100; 1146--echo needs to've been adjusted (99) 1147SELECT @@global.expire_logs_days; 1148 1149SET GLOBAL expire_logs_days = 11; 1150SET GLOBAL expire_logs_days = 99; 1151--echo needs to pass with no warnings (99) 1152SELECT @@global.expire_logs_days; 1153 1154# cleanup 1155SET GLOBAL expire_logs_days = @old_eld; 1156 1157# show that warning uses underscore (sysvar-name), not hyphens (option-name) 1158SET GLOBAL auto_increment_offset=-1; 1159SET GLOBAL auto_increment_offset=0; 1160 1161 1162 1163# 1164# Bug#41030 Wrong meta data (incorrect fieldlen) 1165# 1166 1167--enable_metadata 1168select @@default_storage_engine; 1169--disable_metadata 1170 1171# 1172# Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id 1173# 1174 1175SET @old_server_id = @@GLOBAL.server_id; 1176SET GLOBAL server_id = (1 << 32) - 1; 1177SELECT @@GLOBAL.server_id; 1178SET GLOBAL server_id = (1 << 32); 1179SELECT @@GLOBAL.server_id; 1180SET GLOBAL server_id = (1 << 60); 1181SELECT @@GLOBAL.server_id; 1182SET GLOBAL server_id = 0; 1183SELECT @@GLOBAL.server_id; 1184SET GLOBAL server_id = -1; 1185SELECT @@GLOBAL.server_id; 1186SET GLOBAL server_id = @old_server_id; 1187 1188--echo # 1189--echo # BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback 1190--echo # 1191 1192SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size; 1193--echo # Set the max_binlog_cache_size to size more than 4GB. 1194--disable_warnings 1195SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; 1196--enable_warnings 1197--replace_result 5368709120 max_size 4294963200 max_size 1198SELECT @@GLOBAL.max_binlog_cache_size; 1199SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; 1200 1201--echo # 1202--echo # Bug #37168 : Missing variable - skip_name_resolve 1203--echo # 1204 1205SELECT @@skip_name_resolve >= 0; 1206--replace_column 2 # 1207SHOW VARIABLES LIKE 'skip_name_resolve'; 1208 1209--echo # 1210--echo # Bug #43233 : Some server variables are clipped during "update," 1211--echo # not "check" stage 1212--echo # 1213 1214SET @kbs=@@global.key_buffer_size; 1215SET @kcbs=@@global.key_cache_block_size; 1216 1217--echo throw errors in STRICT mode 1218SET SQL_MODE=STRICT_ALL_TABLES; 1219 1220# sys_var_ulonglong_ptr: sys_max_binlog_cache_size 1221--error ER_WRONG_VALUE_FOR_VAR 1222SET @@global.max_binlog_cache_size=-1; 1223 1224# sys_var_thd_ha_rows: "max_join_size" et al. 1225--error ER_WRONG_VALUE_FOR_VAR 1226SET @@global.max_join_size=0; 1227 1228# sys_var_key_buffer_size: "key_buffer_size" 1229--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE 1230SET @@global.key_buffer_size=0; 1231 1232# sys_var_key_cache_long: "key_cache_block_size" et al. 1233--error ER_WRONG_VALUE_FOR_VAR 1234SET @@global.key_cache_block_size=0; 1235 1236--echo throw warnings in default mode 1237SET SQL_MODE=DEFAULT; 1238 1239SET @@global.max_binlog_cache_size=-1; 1240SET @@global.max_join_size=0; 1241# this is an exception. since this is a new error/warning, let's stay 1242# compatible with the upcoming 5.6. 1243--error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE 1244SET @@global.key_buffer_size=0; 1245SET @@global.key_cache_block_size=0; 1246 1247# Restore variables 1248SET @@global.max_binlog_cache_size=DEFAULT; 1249SET @@global.max_join_size=DEFAULT; 1250SET @@global.key_buffer_size=@kbs; 1251SET @@global.key_cache_block_size=@kcbs; 1252 1253--echo # 1254--echo # Bug#11766424 59527: 1255--echo # Assert in DECIMAL_BIN_SIZE: 1256--echo # `SCALE >= 0 && PRECISION > 0 && SCALE <= PRE 1257--echo # This test also exposed a bug with sql_buffer_result 1258--echo # 1259 1260CREATE TABLE t1(f1 DECIMAL(1,1) UNSIGNED); 1261INSERT INTO t1 VALUES (0.2),(0.1); 1262set @a=NULL; 1263set sql_buffer_result=0; 1264SELECT 1 as 'one' FROM t1 GROUP BY @a:= ROUND(f1); 1265 1266explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); 1267SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); 1268SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE @a=f1); 1269 1270set sql_buffer_result=1; 1271explain SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); 1272SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE f1 = 0); 1273SELECT 1 as 'one' FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE @a=f1); 1274 1275DROP TABLE t1; 1276 1277set sql_buffer_result=0; 1278 1279# 1280# Test of CREATE ... CAST 1281# 1282 1283CREATE TABLE t1 AS SELECT @a:= CAST(1 AS UNSIGNED) AS a; 1284SHOW CREATE TABLE t1; 1285DROP TABLE t1; 1286 1287--echo End of 5.1 tests 1288 1289########################################################################### 1290 1291--echo 1292--echo # 1293--echo # Bug#34828: OF is taken as OFF and a value of 0 is set for variable SQL_notes. 1294--echo # 1295--echo 1296 1297--echo # Checking sql_notes... 1298SET @sql_notes_saved = @@sql_notes; 1299 1300--echo 1301SET @@sql_notes = ON; 1302SELECT @@sql_notes; 1303 1304--echo 1305--error ER_WRONG_VALUE_FOR_VAR 1306SET @@sql_notes = OF; 1307SELECT @@sql_notes; 1308 1309--echo 1310SET @@sql_notes = OFF; 1311SELECT @@sql_notes; 1312 1313--echo 1314SET @@sql_notes = @sql_notes_saved; 1315 1316--echo 1317--echo # Checking delay_key_write... 1318SET @delay_key_write_saved = @@delay_key_write; 1319 1320--echo 1321SET GLOBAL delay_key_write = ON; 1322SELECT @@delay_key_write; 1323 1324--echo 1325--error ER_WRONG_VALUE_FOR_VAR 1326SET GLOBAL delay_key_write = OF; 1327SELECT @@delay_key_write; 1328 1329--echo 1330--error ER_WRONG_VALUE_FOR_VAR 1331SET GLOBAL delay_key_write = AL; 1332SELECT @@delay_key_write; 1333 1334--echo 1335SET GLOBAL delay_key_write = OFF; 1336SELECT @@delay_key_write; 1337 1338--echo 1339SET GLOBAL delay_key_write = ALL; 1340SELECT @@delay_key_write; 1341 1342--echo 1343SET GLOBAL delay_key_write = @delay_key_write_saved; 1344 1345--echo 1346--echo # Checking sql_safe_updates... 1347SET @sql_safe_updates_saved = @@sql_safe_updates; 1348 1349--echo 1350SET @@sql_safe_updates = ON; 1351SELECT @@sql_safe_updates; 1352 1353--echo 1354--error ER_WRONG_VALUE_FOR_VAR 1355SET @@sql_safe_updates = OF; 1356SELECT @@sql_safe_updates; 1357 1358--echo 1359SET @@sql_safe_updates = OFF; 1360SELECT @@sql_safe_updates; 1361 1362--echo 1363SET @@sql_safe_updates = @sql_safe_updates_saved; 1364 1365--echo 1366--echo # Checking foreign_key_checks... 1367SET @foreign_key_checks_saved = @@foreign_key_checks; 1368 1369--echo 1370SET @@foreign_key_checks = ON; 1371SELECT @@foreign_key_checks; 1372 1373--echo 1374--error ER_WRONG_VALUE_FOR_VAR 1375SET @@foreign_key_checks = OF; 1376SELECT @@foreign_key_checks; 1377 1378--echo 1379SET @@foreign_key_checks = OFF; 1380SELECT @@foreign_key_checks; 1381 1382--echo 1383SET @@foreign_key_checks = @foreign_key_checks_saved; 1384 1385--echo 1386--echo # Checking unique_checks... 1387SET @unique_checks_saved = @@unique_checks; 1388 1389--echo 1390SET @@unique_checks = ON; 1391SELECT @@unique_checks; 1392 1393--echo 1394--error ER_WRONG_VALUE_FOR_VAR 1395SET @@unique_checks = OF; 1396SELECT @@unique_checks; 1397 1398--echo 1399SET @@unique_checks = OFF; 1400SELECT @@unique_checks; 1401 1402--echo 1403SET @@unique_checks = @unique_checks_saved; 1404 1405--echo 1406--echo # Checking sql_buffer_result... 1407SET @sql_buffer_result_saved = @@sql_buffer_result; 1408 1409--echo 1410SET @@sql_buffer_result = ON; 1411SELECT @@sql_buffer_result; 1412 1413--echo 1414--error ER_WRONG_VALUE_FOR_VAR 1415SET @@sql_buffer_result = OF; 1416SELECT @@sql_buffer_result; 1417 1418--echo 1419SET @@sql_buffer_result = OFF; 1420SELECT @@sql_buffer_result; 1421 1422--echo 1423SET @@sql_buffer_result = @sql_buffer_result_saved; 1424 1425--echo 1426--echo # Checking sql_quote_show_create... 1427SET @sql_quote_show_create_saved = @@sql_quote_show_create; 1428 1429--echo 1430SET @@sql_quote_show_create = ON; 1431SELECT @@sql_quote_show_create; 1432 1433--echo 1434--error ER_WRONG_VALUE_FOR_VAR 1435SET @@sql_quote_show_create = OF; 1436SELECT @@sql_quote_show_create; 1437 1438--echo 1439SET @@sql_quote_show_create = OFF; 1440SELECT @@sql_quote_show_create; 1441 1442--echo 1443SET @@sql_quote_show_create = @sql_quote_show_create_saved; 1444 1445--echo 1446--echo # End of Bug#34828. 1447--echo 1448 1449--echo # Make sure we can manipulate with autocommit in the 1450--echo # along with other variables. 1451 1452 1453--disable_warnings 1454drop table if exists t1; 1455drop function if exists t1_max; 1456drop function if exists t1_min; 1457--enable_warnings 1458 1459set sql_mode=""; 1460--disable_warnings 1461create table t1 (a int) engine=innodb; 1462--enable_warnings 1463set sql_mode=default; 1464insert into t1(a) values (0), (1); 1465create function t1_max() returns int return (select max(a) from t1); 1466create function t1_min() returns int return (select min(a) from t1); 1467select t1_min(); 1468select t1_max(); 1469set @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), 1470 @@session.autocommit=t1_min(), @@session.autocommit=t1_max(), 1471 @@session.autocommit=t1_min(), @@session.autocommit=t1_max(); 1472 1473--echo # Cleanup. 1474drop table t1; 1475drop function t1_min; 1476drop function t1_max; 1477 1478 1479########################################################################### 1480 1481 1482--echo # 1483--echo # Bug #59884: setting charset to 2048 crashes 1484--echo # 1485 1486--error ER_UNKNOWN_CHARACTER_SET 1487set session character_set_results = 2048; 1488--error ER_UNKNOWN_CHARACTER_SET 1489set session character_set_client=2048; 1490--error ER_UNKNOWN_CHARACTER_SET 1491set session character_set_connection=2048; 1492--error ER_UNKNOWN_CHARACTER_SET 1493set session character_set_server=2048; 1494--error ER_UNKNOWN_COLLATION 1495set session collation_server=2048; 1496--error ER_UNKNOWN_CHARACTER_SET 1497set session character_set_filesystem=2048; 1498--error ER_UNKNOWN_CHARACTER_SET 1499set session character_set_database=2048; 1500--error ER_UNKNOWN_COLLATION 1501set session collation_connection=2048; 1502--error ER_UNKNOWN_COLLATION 1503set session collation_database=2048; 1504 1505# 1506# Bug #16044655 CRASH: SETTING DEFAULT VALUE FOR SOME VARIABLES 1507# 1508--error ER_NO_DEFAULT 1509set session rand_seed1=DEFAULT; 1510 1511# 1512# Bug #14211565 CRASH WHEN ATTEMPTING TO SET SYSTEM VARIABLE TO RESULT OF VALUES() 1513# 1514--error ER_BAD_FIELD_ERROR 1515set autocommit = value(v); 1516 1517# 1518# MDEV-6673 I_S.SESSION_VARIABLES shows global values 1519# 1520set session sql_mode=ansi_quotes; 1521select * from information_schema.session_variables where variable_name='sql_mode'; 1522show global status like 'foobar'; 1523select * from information_schema.session_variables where variable_name='sql_mode'; 1524 1525--echo End of 5.5 tests 1526 1527# 1528# test Item_func_get_system_var::print() 1529# 1530explain extended select @@VERsion from dual where rand() > @@verSION; 1531explain extended select @@SESsion.SQL_mode from dual where rand() > @@sesSION.sql_MODE; 1532explain extended select @@GLObal.COLLATION_connection from dual where rand() > @@gloBAL.collation_CONNECTION; 1533explain extended select @@FOObar.KEY_BUFfer_size from dual where rand() > @@fooBAR.key_bufFER_SIZE; 1534 1535--echo # 1536--echo # MDEV-15316 Assertion `(thd->lex)->var_list.is_empty()' failed in MYSQLparse 1537--echo # 1538 1539SET GLOBAL mc.key_cache_age_threshold=100, mc.key_cache_block_size=1024; 1540SET GLOBAL mc.key_buffer_size= 0 /*cleanup*/; 1541