variables.test 18 KB
Newer Older
1 2 3
#
# test variables
#
4 5 6 7
--disable_warnings
drop table if exists t1,t2;
--enable_warnings

8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
#
# 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_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_max_write_lock_count      =@@global.max_write_lock_count;
set @my_myisam_data_pointer_size  =@@global.myisam_data_pointer_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_query_cache_limit         =@@global.query_cache_limit;
set @my_query_cache_type          =@@global.query_cache_type;
set @my_rpl_recovery_rank         =@@global.rpl_recovery_rank;
set @my_server_id                 =@@global.server_id;
set @my_slow_launch_time          =@@global.slow_launch_time;
set @my_storage_engine            =@@global.storage_engine;
set @my_thread_cache_size         =@@global.thread_cache_size;

39 40 41 42 43 44 45 46 47 48 49 50 51 52
# 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;
53
set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
54 55
# Expected result "1e-10", windows returns "1e-010"
--replace_result 1e-010 1e-10
56 57 58 59 60 61 62 63
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;
64
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
65 66
select @t5;

unknown's avatar
unknown committed
67 68 69 70 71 72 73 74 75 76 77 78
#
# 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;
79 80 81 82

#
# Test system variables
#
83
set GLOBAL max_join_size=10;
unknown's avatar
unknown committed
84 85
set max_join_size=100;
show variables like 'max_join_size';
86
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
87 88 89 90
show global variables like 'max_join_size';
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
set max_join_size=DEFAULT;
91
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
92 93
show variables like 'max_join_size';
set GLOBAL max_join_size=DEFAULT;
94
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
95 96 97 98
show global variables 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;
99 100
select @@VERSION=version();
select last_insert_id(345);
101
explain extended select last_insert_id(345);
unknown's avatar
unknown committed
102
select @@IDENTITY,last_insert_id(), @@identity;
103
explain extended select @@IDENTITY,last_insert_id(), @@identity;
unknown's avatar
unknown committed
104 105 106

set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";

107
set global concurrent_insert=2;
unknown's avatar
unknown committed
108 109 110 111 112 113
show variables like 'concurrent_insert';
set global concurrent_insert=1;
show variables like 'concurrent_insert';
set global concurrent_insert=0;
show variables like 'concurrent_insert';
set global concurrent_insert=DEFAULT;
114
select @@concurrent_insert;
unknown's avatar
unknown committed
115

116
set global timed_mutexes=ON;
unknown's avatar
unknown committed
117 118 119 120
show variables like 'timed_mutexes';
set global timed_mutexes=0;
show variables like 'timed_mutexes';

unknown's avatar
unknown committed
121
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
unknown's avatar
unknown committed
122 123
show local variables like 'storage_engine';
show global variables like 'storage_engine';
unknown's avatar
unknown committed
124 125
set GLOBAL query_cache_size=100000;

126
set GLOBAL myisam_max_sort_file_size=2000000;
unknown's avatar
unknown committed
127
show global variables like 'myisam_max_sort_file_size';
128
set GLOBAL myisam_max_sort_file_size=default;
129
--replace_result 2147483647 FILE_SIZE 9223372036854775807 FILE_SIZE
unknown's avatar
unknown committed
130 131
show variables like 'myisam_max_sort_file_size';

132
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
133 134 135 136 137 138 139 140 141 142 143 144 145
set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600;
show global variables like 'net_%';
show session variables like 'net_%';
set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
show global variables like 'net_%';
show session variables like 'net_%';
set net_buffer_length=1;
show variables like 'net_buffer_length';
set net_buffer_length=2000000000;
show variables like 'net_buffer_length';

set character set cp1251_koi8;
146
show variables like "character_set_client";
unknown's avatar
unknown committed
147 148
select @@timestamp>0;

149 150 151
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);

152 153 154 155 156 157 158 159 160 161 162 163
show variables like '%alloc%';
set @@range_alloc_block_size=1024*16;
set @@query_alloc_block_size=1024*17+2;
set @@query_prealloc_size=1024*18;
set @@transaction_alloc_block_size=1024*20-1;
set @@transaction_prealloc_size=1024*21-1;
select @@query_alloc_block_size;
show variables like '%alloc%';
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;
show variables like '%alloc%';
164

unknown's avatar
unknown committed
165 166 167 168 169 170 171
#
# 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';

unknown's avatar
unknown committed
172 173 174 175 176 177
# The following should give errors

--error 1231
set big_tables=OFFF;
--error 1231
set big_tables="OFFF";
178
--error 1193
unknown's avatar
unknown committed
179 180 181
set unknown_variable=1;
--error 1232
set max_join_size="hello";
unknown's avatar
unknown committed
182
--error 1286
unknown's avatar
unknown committed
183
set storage_engine=UNKNOWN_TABLE_TYPE;
unknown's avatar
unknown committed
184
--error 1231
unknown's avatar
unknown committed
185
set storage_engine=MERGE, big_tables=2;
unknown's avatar
unknown committed
186
show local variables like 'storage_engine';
unknown's avatar
unknown committed
187 188 189
--error 1229
set SESSION query_cache_size=10000;
--error 1230
unknown's avatar
unknown committed
190
set GLOBAL storage_engine=DEFAULT;
unknown's avatar
unknown committed
191
--error 1115
192
set character_set_client=UNKNOWN_CHARACTER_SET;
unknown's avatar
unknown committed
193
--error 1273
194
set collation_connection=UNKNOWN_COLLATION;
195 196 197 198
--error 1231
set character_set_client=NULL;
--error 1231
set collation_connection=NULL;
unknown's avatar
unknown committed
199 200
--error 1228
set global autocommit=1;
201
--error 1238
unknown's avatar
unknown committed
202
select @@global.timestamp;
203
--error 1238 
unknown's avatar
unknown committed
204 205 206 207 208
set @@version='';
--error 1229
set @@concurrent_insert=1;
--error 1228
set @@global.sql_auto_is_null=1;
209
--error 1238
unknown's avatar
unknown committed
210
select @@global.sql_auto_is_null;
211 212
--error 1229
set myisam_max_sort_file_size=100;
213 214
--error 1231
set @@SQL_WARNINGS=NULL;
unknown's avatar
unknown committed
215 216 217 218 219 220 221 222

# 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;
unknown's avatar
unknown committed
223 224
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
225 226
set @@global.concurrent_insert=1;
set global connect_timeout=100;
227 228 229 230 231
select @@delay_key_write;
set global delay_key_write="OFF";
select @@delay_key_write;
set global delay_key_write=ALL;
select @@delay_key_write;
unknown's avatar
unknown committed
232
set global delay_key_write=1;
233
select @@delay_key_write;
unknown's avatar
unknown committed
234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271
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=100;
set low_priority_updates=1;
set 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 max_tmp_tables=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 net_buffer_length=100;
set net_read_timeout=100;
set net_write_timeout=100;
set global query_cache_limit=100;
set global query_cache_size=100;
set global query_cache_type=demand;
set read_buffer_size=100;
set read_rnd_buffer_size=100;
set global rpl_recovery_rank=100;
set global server_id=100;
set global slow_launch_time=100;
set sort_buffer_size=100;
272 273 274 275
set @@max_sp_recursion_depth=10;
select @@max_sp_recursion_depth;
set @@max_sp_recursion_depth=0;
select @@max_sp_recursion_depth;
unknown's avatar
unknown committed
276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291
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_big_tables=1;
set sql_buffer_result=1;
set sql_log_bin=1;
set sql_log_off=1;
set sql_log_update=1;
set sql_low_priority_updates=1;
set sql_max_join_size=200;
select @@sql_max_join_size,@@max_join_size;
set sql_quote_show_create=1;
set sql_safe_updates=1;
set sql_select_limit=1;
292 293
# reset it, so later tests don't get confused
set sql_select_limit=default;
unknown's avatar
unknown committed
294 295
set sql_warnings=1;
set global table_cache=100;
unknown's avatar
unknown committed
296
set storage_engine=myisam;
unknown's avatar
unknown committed
297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316
set global thread_cache_size=100;
set timestamp=1, timestamp=default;
set tmp_table_size=100;
set tx_isolation="READ-COMMITTED";
set wait_timeout=100;
set log_warnings=1;

#
# 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;
317
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
318 319
drop table t1,t2;

320 321 322 323 324 325 326
#
# error conditions
#

--error 1193
select @@xxxxxxxxxx;
select 1;
327

328
--error 1238
unknown's avatar
unknown committed
329 330
select @@session.key_buffer_size;

331 332
--error 1229
set ft_boolean_syntax = @@init_connect;
unknown's avatar
unknown committed
333 334 335 336 337 338 339 340 341
--error 1231
set global ft_boolean_syntax = @@init_connect;
--error 1229
set init_connect = NULL;
set global init_connect = NULL;
--error 1229
set ft_boolean_syntax = @@init_connect;
--error 1231
set global ft_boolean_syntax = @@init_connect;
342

unknown's avatar
unknown committed
343 344 345 346 347
# 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;
348
--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
unknown's avatar
unknown committed
349 350 351
show global variables like 'myisam_max_sort_file_size';
set global myisam_max_sort_file_size=default;

352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367
#
# 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;

368 369 370
#
# Bug#2586:Disallow global/session/local as structured var. instance names
#
371
--error 1064
372
set @@global.global.key_buffer_size= 1;
373
--error 1064
374
set GLOBAL global.key_buffer_size= 1;
375
--error 1064
376
SELECT @@global.global.key_buffer_size;
377
--error 1064
378
SELECT @@global.session.key_buffer_size;
379
--error 1064
380
SELECT @@global.local.key_buffer_size;
381 382 383 384 385 386 387 388 389 390

# 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';
set global log_warnings = 0;
show global variables like 'log_warnings';
set global log_warnings = 42;
show global variables like 'log_warnings';
set global log_warnings = @tstlw;
show global variables like 'log_warnings';
unknown's avatar
unknown committed
391

392 393 394 395 396 397 398 399 400 401 402 403 404 405
#
# 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?
unknown's avatar
unknown committed
406 407
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;
408 409 410
show create table t1;
drop table t1;

411 412 413

#
# Bug #6993: myisam_data_pointer_size
414 415 416 417
# 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.
418 419
#

420
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
421
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
unknown's avatar
unknown committed
422 423 424 425 426 427 428 429

#
# Bug #6958: negative arguments to integer options wrap around
#

SET GLOBAL table_cache=-1;
SHOW VARIABLES LIKE 'table_cache';
SET GLOBAL table_cache=DEFAULT;
430

unknown's avatar
unknown committed
431 432 433 434 435 436 437 438
#
# 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;

439 440 441 442 443 444 445

#
# Bug #9613: @@have_innodb
#

--replace_column 1 #
select @@have_innodb;
446 447 448 449 450 451 452 453

#
# Bug #13334: query_prealloc_size default less than minimum
#
set @test = @@query_prealloc_size;
set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;

454
# End of 4.1 tests
unknown's avatar
unknown committed
455

456 457 458
#
# Bug#6282 Packet error with SELECT INTO
# 
459

460 461 462 463 464
create table t1 (a int);
select a into @x from t1;
show warnings;
drop table t1;

465 466 467 468 469 470 471 472
#
# Bug #10339: read only variables.
#

--error 1238
set @@warning_count=1;
--error 1238
set @@global.error_count=1;
473 474

#
475
# Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform
476
#
477

478
set @@max_heap_table_size= 4294967296;
479
select @@max_heap_table_size > 0;
480
set global max_heap_table_size= 4294967296;
481
select @@max_heap_table_size > 0;
482
set @@max_heap_table_size= 4294967296;
483
select @@max_heap_table_size > 0;
484

485 486 487 488 489 490 491 492
#
# Bug #11775 Variable character_set_system does not exist (sometimes)
#
select @@character_set_system;
--error 1238
set global character_set_system = latin1;
--error 1238
set @@global.version_compile_os='234';
493

unknown's avatar
unknown committed
494 495 496 497 498 499 500 501 502 503 504 505 506 507
#
# 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;

508 509 510 511 512 513 514 515
#
# 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';
set @@sql_big_selects = @old_sql_big_selects;

516 517 518 519 520 521 522 523 524 525 526
#
# 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';
show variables like 'sql_warnings';
set @@sql_notes = 1, @@sql_warnings = 1;
show variables like 'sql_notes';
show variables like 'sql_warnings';

527 528 529 530 531 532 533 534 535 536 537 538 539 540 541
#
# 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;

542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572
#
# 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     =default;                #@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_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      =@my_max_write_lock_count;
set global myisam_data_pointer_size  =@my_myisam_data_pointer_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 query_cache_limit         =@my_query_cache_limit;
set global query_cache_type          =@my_query_cache_type;
set global rpl_recovery_rank         =@my_rpl_recovery_rank;
set global server_id                 =@my_server_id;
set global slow_launch_time          =@my_slow_launch_time;
set global storage_engine            =@my_storage_engine;
set global thread_cache_size         =@my_thread_cache_size;

573
--echo End of 5.0 tests