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

8
#
9
# Bug#19263: variables.test doesn't clean up after itself (I/II -- save)
10 11 12 13 14 15 16 17 18 19 20
#
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;
21
set @my_max_connections           =@@global.max_connections;
22 23 24 25 26
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_myisam_data_pointer_size  =@@global.myisam_data_pointer_size;
27
set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
28 29 30 31 32 33 34 35
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;
36
set @my_storage_engine            =@@global.default_storage_engine;
37
set @my_thread_cache_size         =@@global.thread_cache_size;
38
set @my_max_allowed_packet        =@@global.max_allowed_packet;
39
set @my_join_buffer_size          =@@global.join_buffer_size;
40 41 42 43 44 45 46 47 48 49 50 51 52 53
# 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;
54
set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
55 56
# Expected result "1e-10", windows returns "1e-010"
--replace_result 1e-010 1e-10
57 58 59 60 61 62 63 64
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;
65
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
66 67
select @t5;

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

#
# Test system variables
#
84
set GLOBAL max_join_size=10;
unknown's avatar
unknown committed
85 86
set max_join_size=100;
show variables like 'max_join_size';
87
select * from information_schema.session_variables where variable_name like 'max_join_size';
88
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
89
show global variables like 'max_join_size';
90 91
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
select * from information_schema.global_variables where variable_name like 'max_join_size';
unknown's avatar
unknown committed
92 93
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
94
select * from information_schema.global_variables where variable_name like 'max_join_size';
unknown's avatar
unknown committed
95
set max_join_size=DEFAULT;
96
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
97
show variables like 'max_join_size';
98 99
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
select * from information_schema.session_variables where variable_name like 'max_join_size';
unknown's avatar
unknown committed
100
set GLOBAL max_join_size=DEFAULT;
101
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
102
show global variables like 'max_join_size';
103 104
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
select * from information_schema.global_variables where variable_name like 'max_join_size';
unknown's avatar
unknown committed
105 106 107
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
select @@identity,  length(@@version)>0;
108 109
select @@VERSION=version();
select last_insert_id(345);
110
explain extended select last_insert_id(345);
unknown's avatar
unknown committed
111
select @@IDENTITY,last_insert_id(), @@identity;
112
explain extended select @@IDENTITY,last_insert_id(), @@identity;
unknown's avatar
unknown committed
113 114 115

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

116
set global concurrent_insert=2;
unknown's avatar
unknown committed
117
show variables like 'concurrent_insert';
118
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
unknown's avatar
unknown committed
119 120
set global concurrent_insert=1;
show variables like 'concurrent_insert';
121
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
unknown's avatar
unknown committed
122 123
set global concurrent_insert=0;
show variables like 'concurrent_insert';
124
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
unknown's avatar
unknown committed
125
set global concurrent_insert=DEFAULT;
126
select @@concurrent_insert;
unknown's avatar
unknown committed
127

128
set global timed_mutexes=ON;
unknown's avatar
unknown committed
129
show variables like 'timed_mutexes';
130
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
unknown's avatar
unknown committed
131 132
set global timed_mutexes=0;
show variables like 'timed_mutexes';
133
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
unknown's avatar
unknown committed
134

135 136 137 138 139 140
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 query_cache_size=102400;
unknown's avatar
unknown committed
141

142
set GLOBAL myisam_max_sort_file_size=2000000;
unknown's avatar
unknown committed
143
show global variables like 'myisam_max_sort_file_size';
144
select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
145
set GLOBAL myisam_max_sort_file_size=default;
146
--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE
147
show global variables like 'myisam_max_sort_file_size';
148
--replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE
unknown's avatar
unknown committed
149
select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
unknown's avatar
unknown committed
150

151
# bug#22891: modified to take read-only SESSION net_buffer_length into account
152
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
153 154
set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
show global variables like 'net_%';
unknown's avatar
unknown committed
155
select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
unknown's avatar
unknown committed
156
show session variables like 'net_%';
unknown's avatar
unknown committed
157
select * from information_schema.session_variables where variable_name like 'net_%' order by 1;
158
set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
unknown's avatar
unknown committed
159
show global variables like 'net_%';
unknown's avatar
unknown committed
160
select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
161 162 163
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';
unknown's avatar
unknown committed
164
#warning 1292
165 166 167
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';
unknown's avatar
unknown committed
168 169

set character set cp1251_koi8;
170
show variables like "character_set_client";
171
select * from information_schema.session_variables where variable_name like 'character_set_client';
unknown's avatar
unknown committed
172 173
select @@timestamp>0;

174 175 176
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);

Patrick Crews's avatar
Patrick Crews committed
177

178 179 180 181 182 183 184 185 186
--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');
Patrick Crews's avatar
Patrick Crews committed
187 188 189 190 191

--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',
192
'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
193 194 195 196 197 198 199
--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 ==+
Patrick Crews's avatar
Patrick Crews committed
200
select @@query_alloc_block_size;
201 202 203
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
'query_alloc_block_size', 'query_prealloc_size',
'transaction_alloc_block_size', 'transaction_prealloc_size');
Patrick Crews's avatar
Patrick Crews committed
204 205 206 207 208
--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;
209 210 211
--echo ==+ Manipulate variable values +==
--echo Testing values that are not 1024 multiples
set @@range_alloc_block_size=1024*16+1023;
212
set @@query_alloc_block_size=1024*17+2;
213
set @@query_prealloc_size=1024*18-1023;
214 215
set @@transaction_alloc_block_size=1024*20-1;
set @@transaction_prealloc_size=1024*21-1;
216 217 218 219
--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');
Patrick Crews's avatar
Patrick Crews committed
220 221 222 223 224
--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;
225
--echo ==+ Set values back to the default values +==
226 227 228
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;
229
--echo ==+ Check the values now that they are reset +==
230 231 232
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
'query_alloc_block_size', 'query_prealloc_size',
'transaction_alloc_block_size', 'transaction_prealloc_size');
233

unknown's avatar
unknown committed
234 235 236 237 238 239 240
#
# 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
241 242
# The following should give errors

243
--error ER_WRONG_VALUE_FOR_VAR
unknown's avatar
unknown committed
244
set big_tables=OFFF;
245
--error ER_WRONG_VALUE_FOR_VAR
unknown's avatar
unknown committed
246
set big_tables="OFFF";
247
--error ER_UNKNOWN_SYSTEM_VARIABLE
unknown's avatar
unknown committed
248
set unknown_variable=1;
249
--error ER_WRONG_TYPE_FOR_VAR
unknown's avatar
unknown committed
250
set max_join_size="hello";
251
--error ER_UNKNOWN_STORAGE_ENGINE
252
set default_storage_engine=UNKNOWN_TABLE_TYPE;
253
--error ER_WRONG_VALUE_FOR_VAR
254 255
set default_storage_engine=MERGE, big_tables=2;
show local variables like 'default_storage_engine';
256
--error ER_GLOBAL_VARIABLE
unknown's avatar
unknown committed
257
set SESSION query_cache_size=10000;
258
--error ER_UNKNOWN_CHARACTER_SET
259
set character_set_client=UNKNOWN_CHARACTER_SET;
260
--error ER_UNKNOWN_COLLATION
261
set collation_connection=UNKNOWN_COLLATION;
262
--error ER_WRONG_VALUE_FOR_VAR
263
set character_set_client=NULL;
264
--error ER_WRONG_VALUE_FOR_VAR
265
set collation_connection=NULL;
266
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
unknown's avatar
unknown committed
267
select @@global.timestamp;
268
--error ER_INCORRECT_GLOBAL_LOCAL_VAR 
unknown's avatar
unknown committed
269
set @@version='';
270
--error ER_GLOBAL_VARIABLE
unknown's avatar
unknown committed
271
set @@concurrent_insert=1;
272
--error ER_GLOBAL_VARIABLE
273
set myisam_max_sort_file_size=100;
274
--error ER_WRONG_VALUE_FOR_VAR
275
set @@SQL_WARNINGS=NULL;
unknown's avatar
unknown committed
276 277 278 279 280 281 282 283

# 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
284 285
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
286 287
set @@global.concurrent_insert=1;
set global connect_timeout=100;
288 289 290 291 292
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
293
set global delay_key_write=1;
294
select @@delay_key_write;
unknown's avatar
unknown committed
295 296 297 298 299 300 301 302 303 304
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;
305 306 307 308
set long_query_time=0.000001;
select @@long_query_time;
set long_query_time=100.000001;
select @@long_query_time;
unknown's avatar
unknown committed
309
set low_priority_updates=1;
310
set global max_allowed_packet=100;
unknown's avatar
unknown committed
311 312 313 314 315 316 317 318 319 320 321 322 323
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;
324
set global net_buffer_length=100;
unknown's avatar
unknown committed
325 326 327 328 329 330 331 332 333 334 335
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;
336 337 338 339
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
340 341 342 343 344 345 346 347 348 349 350 351 352
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_low_priority_updates=1;
set sql_quote_show_create=1;
set sql_safe_updates=1;
set sql_select_limit=1;
353 354
# reset it, so later tests don't get confused
set sql_select_limit=default;
unknown's avatar
unknown committed
355
set sql_warnings=1;
unknown's avatar
unknown committed
356
set global table_open_cache=100;
357
set default_storage_engine=myisam;
unknown's avatar
unknown committed
358 359 360 361 362 363
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;
364
set global log_warnings=1;
unknown's avatar
unknown committed
365

366 367 368 369 370 371 372
#
# 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;
373 374 375 376 377 378

set session last_insert_id=100;
select @@session.insert_id;
select @@session.last_insert_id;
select @@session.insert_id;

379 380 381
set @@session.insert_id=@save_insert_id;
select @@session.insert_id;

unknown's avatar
unknown committed
382 383 384 385 386 387 388 389 390 391 392 393 394
#
# 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;
395
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
396 397
drop table t1,t2;

398 399 400 401
#
# error conditions
#

402
--error ER_UNKNOWN_SYSTEM_VARIABLE
403 404
select @@xxxxxxxxxx;
select 1;
405

406
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
unknown's avatar
unknown committed
407 408
select @@session.key_buffer_size;

409
--error ER_GLOBAL_VARIABLE
410
set ft_boolean_syntax = @@init_connect;
411
--error ER_WRONG_VALUE_FOR_VAR
unknown's avatar
unknown committed
412
set global ft_boolean_syntax = @@init_connect;
413
--error ER_GLOBAL_VARIABLE
unknown's avatar
unknown committed
414 415
set init_connect = NULL;
set global init_connect = NULL;
416
--error ER_GLOBAL_VARIABLE
unknown's avatar
unknown committed
417
set ft_boolean_syntax = @@init_connect;
418
--error ER_WRONG_VALUE_FOR_VAR
unknown's avatar
unknown committed
419
set global ft_boolean_syntax = @@init_connect;
420

unknown's avatar
unknown committed
421 422 423 424 425
# 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;
426
--replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
unknown's avatar
unknown committed
427
show global variables like 'myisam_max_sort_file_size';
428 429
--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';
unknown's avatar
unknown committed
430 431
set global myisam_max_sort_file_size=default;

432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447
#
# 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;

448 449 450
#
# Bug#2586:Disallow global/session/local as structured var. instance names
#
451
--error ER_PARSE_ERROR
452
set @@global.global.key_buffer_size= 1;
453
--error ER_PARSE_ERROR
454
set GLOBAL global.key_buffer_size= 1;
455
--error ER_PARSE_ERROR
456
SELECT @@global.global.key_buffer_size;
457
--error ER_PARSE_ERROR
458
SELECT @@global.session.key_buffer_size;
459
--error ER_PARSE_ERROR
460
SELECT @@global.local.key_buffer_size;
461 462 463 464

# 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';
465
select * from information_schema.global_variables where variable_name like 'log_warnings';
466 467
set global log_warnings = 0;
show global variables like 'log_warnings';
468
select * from information_schema.global_variables where variable_name like 'log_warnings';
469 470
set global log_warnings = 42;
show global variables like 'log_warnings';
471
select * from information_schema.global_variables where variable_name like 'log_warnings';
472 473
set global log_warnings = @tstlw;
show global variables like 'log_warnings';
474
select * from information_schema.global_variables where variable_name like 'log_warnings';
unknown's avatar
unknown committed
475

476 477 478 479 480 481 482 483 484 485 486 487 488 489
#
# 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
490 491
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;
492 493 494
show create table t1;
drop table t1;

495 496 497

#
# Bug #6993: myisam_data_pointer_size
498 499 500 501
# 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.
502 503
#

504
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
505
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
506
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
unknown's avatar
unknown committed
507 508 509 510 511

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

unknown's avatar
unknown committed
512 513
SET GLOBAL table_open_cache=-1;
SHOW VARIABLES LIKE 'table_open_cache';
514
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
unknown's avatar
unknown committed
515
SET GLOBAL table_open_cache=DEFAULT;
516

unknown's avatar
unknown committed
517 518 519 520 521 522 523 524
#
# 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;

525 526 527 528 529 530 531

#
# Bug #9613: @@have_innodb
#

--replace_column 1 #
select @@have_innodb;
532

533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549
#
# 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;
550
--error ER_UNKNOWN_LOCALE
551 552 553 554 555
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;
556
--error ER_WRONG_TYPE_FOR_VAR
557 558 559 560
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:
561
--error ER_WRONG_VALUE_FOR_VAR
562
set lc_time_names=NULL;
563
--error ER_UNKNOWN_LOCALE
564 565 566
set lc_time_names=-1;
select @@lc_time_names;
--echo LC_TIME_NAMES: testing locale with the last ID:
567
set lc_time_names=109;
568 569
select @@lc_time_names;
--echo LC_TIME_NAMES: testing a number beyond the valid ID range:
570
--error ER_UNKNOWN_LOCALE
571
set lc_time_names=110;
572 573 574 575 576
select @@lc_time_names;
--echo LC_TIME_NAMES: testing that 0 is en_US:
set lc_time_names=0;
select @@lc_time_names;

577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600
#
# 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;
--echo New connection
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;
--echo Returnung to default connection
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;


601 602 603 604 605 606 607
#
# Bug #13334: query_prealloc_size default less than minimum
#
set @test = @@query_prealloc_size;
set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;

608 609 610 611
#
# Bug#31588 buffer overrun when setting variables
#
# Buffer-size Off By One. Should throw valgrind-warning without fix #31588.
612
--error ER_WRONG_VALUE_FOR_VAR
613 614 615
set global sql_mode=repeat('a',80);

--echo End of 4.1 tests
unknown's avatar
unknown committed
616

617 618 619
#
# Bug#6282 Packet error with SELECT INTO
# 
620

621 622 623 624 625
create table t1 (a int);
select a into @x from t1;
show warnings;
drop table t1;

626 627 628 629
#
# Bug #10339: read only variables.
#

630
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
631
set @@warning_count=1;
632
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
633
set @@global.error_count=1;
634 635

#
636
# Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform
637
#
638

639
--disable_warnings
640
set @@max_heap_table_size= 4294967296;
641
select @@max_heap_table_size > 0;
642
set global max_heap_table_size= 4294967296;
643
select @@max_heap_table_size > 0;
644
set @@max_heap_table_size= 4294967296;
645
select @@max_heap_table_size > 0;
646
--enable_warnings
647

648 649 650 651
#
# Bug #11775 Variable character_set_system does not exist (sometimes)
#
select @@character_set_system;
652
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
653
set global character_set_system = latin1;
654
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
655
set @@global.version_compile_os='234';
656

unknown's avatar
unknown committed
657 658 659 660 661 662 663 664 665 666 667 668 669 670
#
# 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;

671 672 673 674 675 676
#
# 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';
677
select * from information_schema.session_variables where variable_name like 'sql_big_selects';
678 679
set @@sql_big_selects = @old_sql_big_selects;

680 681 682 683 684 685
#
# 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';
686
select * from information_schema.session_variables where variable_name like 'sql_notes';
687
show variables like 'sql_warnings';
688
select * from information_schema.session_variables where variable_name like 'sql_warnings';
689 690
set @@sql_notes = 1, @@sql_warnings = 1;
show variables like 'sql_notes';
691
select * from information_schema.session_variables where variable_name like 'sql_notes';
692
show variables like 'sql_warnings';
693
select * from information_schema.session_variables where variable_name like 'sql_warnings';
694

695 696 697 698 699 700 701 702 703 704 705 706 707 708 709
#
# 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;

710
#
711 712 713 714 715 716 717 718 719
# 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 #
720 721
select * from information_schema.session_variables where variable_name like 'basedir';
--replace_column 2 #
722 723
show variables like 'datadir';
--replace_column 2 #
724 725
select * from information_schema.session_variables where variable_name like 'datadir';
--replace_column 2 #
726
show variables like 'tmpdir';
727 728
--replace_column 2 #
select * from information_schema.session_variables where variable_name like 'tmpdir';
729 730 731 732 733 734 735 736 737

#
# Bug #19606: make ssl settings available via SHOW VARIABLES and @@variables
# 
# Don't actually output, since it depends on the system
--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%';
738
--replace_column 2 #
unknown's avatar
unknown committed
739
select * from information_schema.session_variables where variable_name like 'ssl%' order by 1;
740 741 742 743 744 745 746

#
# 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';
747
select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
748

unknown's avatar
unknown committed
749 750 751 752 753 754 755 756 757 758
#
# Bug#20908: Crash if select @@""
#
--error ER_PARSE_ERROR
select @@"";
--error ER_PARSE_ERROR
select @@&;
--error ER_PARSE_ERROR
select @@@;

759 760 761 762 763 764
#
# 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;
765
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
766 767 768 769
set @@hostname= "anothername";
--replace_column 2 #
show variables like 'hostname';

770 771 772 773
--echo #
--echo # BUG#37408 - Compressed MyISAM files should not require/use mmap()
--echo #
--echo # Test 'myisam_mmap_size' option is not dynamic
774
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
775
SET @@myisam_mmap_size= 500M;
Georgi Kodinov's avatar
merge  
Georgi Kodinov committed
776 777 778 779 780 781 782 783 784 785 786 787 788


--echo #
--echo # Bug #52315: utc_date() crashes when system time > year 2037
--echo #

--error 0, ER_UNKNOWN_ERROR
SET TIMESTAMP=2*1024*1024*1024;
--echo #Should not crash
--disable_result_log
SELECT UTC_DATE();
--enable_result_log
SET TIMESTAMP=DEFAULT;
789 790


791
--echo End of 5.0 tests
792

793 794 795 796 797 798 799 800 801 802 803 804
#
# 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;

805 806 807 808 809
# 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)
810 811 812 813 814 815 816 817
#
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;
818
set global max_binlog_cache_size     =@my_max_binlog_cache_size;
819 820
set global max_binlog_size           =@my_max_binlog_size;
set global max_connect_errors        =@my_max_connect_errors;
821
set global max_connections           =@my_max_connections;
822 823 824 825
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;
826 827
set global max_user_connections      =default;
set global max_write_lock_count      =default;
828
set global myisam_data_pointer_size  =@my_myisam_data_pointer_size;
829
set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
830 831 832 833 834 835 836 837
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;
838
set global default_storage_engine            =@my_storage_engine;
839
set global thread_cache_size         =@my_thread_cache_size;
840
set global max_allowed_packet        =@my_max_allowed_packet;
841 842
set global join_buffer_size          =@my_join_buffer_size;

843 844 845 846
#
# Bug#28580 Repeatation of status variables
#
--replace_column 2 #
847 848
show global variables where Variable_name='table_definition_cache';
# Note: table_lock_wait_timeout no longer exists. See bug#45225.
849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865

###########################################################################

--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.

866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959
#
# 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 #
960
SHOW VARIABLES like 'lc_messages_dir';
961
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
962
SELECT @@session.lc_messages_dir;
963
--replace_column 1 #
964
SELECT @@global.lc_messages_dir;
965
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
966
SET @@session.lc_messages_dir= 'x';
967
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
968
SET @@global.lc_messages_dir= 'x';
969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149
#
--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;
#
1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181

#
# 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;

1182 1183 1184 1185 1186
# show that warning uses underscore (sysvar-name), not hyphens (option-name)
SET GLOBAL auto_increment_offset=-1;
SET GLOBAL auto_increment_offset=0;


1187

1188 1189 1190
#
# Bug#41030 Wrong meta data (incorrect fieldlen)
#
Tatiana A. Nurnberg's avatar
Tatiana A. Nurnberg committed
1191

1192
--enable_metadata
1193
select @@default_storage_engine;
1194
--disable_metadata
Tatiana A. Nurnberg's avatar
Tatiana A. Nurnberg committed
1195

1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211
#
# 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;
1212

1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228
#
# Bug #42778: delete order by null global variable causes 
#             assertion .\filesort.cc, line 797
#

SELECT @@GLOBAL.INIT_FILE, @@GLOBAL.INIT_FILE IS NULL;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES ();
SET @bug42778= @@sql_safe_updates;
SET @@sql_safe_updates= 0;
DELETE FROM t1 ORDER BY (@@GLOBAL.INIT_FILE) ASC LIMIT 10;
SET @@sql_safe_updates= @bug42778;

DROP TABLE t1;

1229 1230 1231 1232 1233 1234 1235 1236 1237
--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. 
SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024;
SELECT @@GLOBAL.max_binlog_cache_size;
SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size;
1238 1239 1240 1241 1242 1243 1244 1245

--echo #
--echo # Bug #37168 : Missing variable - skip_name_resolve
--echo #

SELECT @@skip_name_resolve;
SHOW VARIABLES LIKE 'skip_name_resolve';

1246
--echo End of 5.1 tests
1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407

###########################################################################

--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

1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433
--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

create table t1 (a int) engine=innodb;
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;


1434
###########################################################################