variables.result 34.2 KB
Newer Older
1
drop table if exists t1,t2;
2 3 4 5 6 7 8 9 10 11
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;
12
set @my_max_connections           =@@global.max_connections;
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
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;
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;
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
set @`test`=1;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
1	1	1	1	1
set @TEST=2;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
2	2	2	2	2
set @"tEST"=3;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
3	3	3	3	3
set @`TeST`=4;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
4	4	4	4	4
select @`teST`:=5;
@`teST`:=5
5
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
5	5	5	5	5
set @select=2,@t5=1.23456;
select @`select`,@not_used;
@`select`	@not_used
2	NULL
54 55
set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
select @test_int,@test_double,@test_string,@test_string2,@select;
56
@test_int	@test_double	@test_string	@test_string2	@select
57
10	1e-10	abcdeghi	abcdefghij	NULL
58 59
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
60 61
@test_int	@test_double	@test_string	@test_string2
hello	hello	hello	hello
62 63
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
64 65
@test_int	@test_double	@test_string	@test_string2
hellohello	hellohello	hellohello	hellohello
66 67
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
68 69
@test_int	@test_double	@test_string	@test_string2
NULL	NULL	NULL	NULL
70
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
71 72
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
73
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
74 75
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
76
Warnings:
77
Note	1003	select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
78
select @t5;
79 80
@t5
1.23456
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
81 82 83
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;
84 85
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
86
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
87 88 89
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
90
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
91 92 93
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
94 95
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
96 97 98
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
99
drop table t1;
100
set GLOBAL max_join_size=10;
101 102 103 104
set max_join_size=100;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	100
105 106 107
select * from information_schema.session_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	100
108 109
show global variables like 'max_join_size';
Variable_name	Value
110
max_join_size	10
111 112 113
select * from information_schema.global_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	10
114 115 116 117
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
118 119 120
select * from information_schema.global_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	2000
121 122 123 124
set max_join_size=DEFAULT;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
125 126 127
select * from information_schema.session_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	2000
128 129 130
set GLOBAL max_join_size=DEFAULT;
show global variables like 'max_join_size';
Variable_name	Value
131
max_join_size	HA_POS_ERROR
132 133 134
select * from information_schema.global_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	HA_POS_ERROR
135 136
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
137
@@local.max_join_size	@@global.max_join_size
138 139 140 141
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
142 143 144 145 146 147
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
148
explain extended select last_insert_id(345);
149 150
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
151
Warnings:
152
Note	1003	select last_insert_id(345) AS `last_insert_id(345)`
153
select @@IDENTITY,last_insert_id(), @@identity;
154
@@IDENTITY	last_insert_id()	@@identity
155
345	345	345
156
explain extended select @@IDENTITY,last_insert_id(), @@identity;
157 158
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
159
Warnings:
160
Note	1003	select 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
161
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
162
set global concurrent_insert=2;
163 164
show variables like 'concurrent_insert';
Variable_name	Value
165
concurrent_insert	2
166 167 168
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	2
169 170 171
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name	Value
172
concurrent_insert	1
173 174 175
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	1
176 177 178
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name	Value
179
concurrent_insert	0
180 181 182
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	0
183
set global concurrent_insert=DEFAULT;
184 185 186 187
select @@concurrent_insert;
@@concurrent_insert
1
set global timed_mutexes=ON;
vtkachenko@intelp4d.mysql.com's avatar
vtkachenko@intelp4d.mysql.com committed
188 189 190
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	ON
191 192 193
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
VARIABLE_NAME	VARIABLE_VALUE
TIMED_MUTEXES	ON
vtkachenko@intelp4d.mysql.com's avatar
vtkachenko@intelp4d.mysql.com committed
194 195 196 197
set global timed_mutexes=0;
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	OFF
198 199 200
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
VARIABLE_NAME	VARIABLE_VALUE
TIMED_MUTEXES	OFF
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
201
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
202
show local variables like 'storage_engine';
203
Variable_name	Value
204
storage_engine	MEMORY
205 206 207
select * from information_schema.session_variables where variable_name like 'storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
STORAGE_ENGINE	MEMORY
208
show global variables like 'storage_engine';
209
Variable_name	Value
210
storage_engine	MRG_MYISAM
211 212 213
select * from information_schema.global_variables where variable_name like 'storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
STORAGE_ENGINE	MRG_MYISAM
214
set GLOBAL query_cache_size=100000;
215
set GLOBAL myisam_max_sort_file_size=2000000;
216 217
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
218
myisam_max_sort_file_size	1048576
219 220 221
select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
VARIABLE_NAME	VARIABLE_VALUE
MYISAM_MAX_SORT_FILE_SIZE	1048576
222
set GLOBAL myisam_max_sort_file_size=default;
223 224
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
225
myisam_max_sort_file_size	FILE_SIZE
226 227 228
select * from information_schema.session_variables where variable_name like 'myisam_max_sort_file_size';
VARIABLE_NAME	VARIABLE_VALUE
MYISAM_MAX_SORT_FILE_SIZE	FILE_SIZE
229
set global net_retry_count=10, session net_retry_count=10;
230 231 232 233 234 235 236 237
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_%';
Variable_name	Value
net_buffer_length	1024
net_read_timeout	300
net_retry_count	10
net_write_timeout	200
238 239 240 241 242 243
select * from information_schema.global_variables where variable_name like 'net_%';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1024
NET_READ_TIMEOUT	300
NET_RETRY_COUNT	10
NET_WRITE_TIMEOUT	200
244 245 246 247 248 249
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	2048
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
250 251 252 253 254 255
select * from information_schema.session_variables where variable_name like 'net_%';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	2048
NET_READ_TIMEOUT	600
NET_RETRY_COUNT	10
NET_WRITE_TIMEOUT	500
256 257 258 259 260 261 262
set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
show global variables like 'net_%';
Variable_name	Value
net_buffer_length	1024
net_read_timeout	900
net_retry_count	10
net_write_timeout	1000
263 264 265 266 267 268
select * from information_schema.global_variables where variable_name like 'net_%';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1024
NET_READ_TIMEOUT	900
NET_RETRY_COUNT	10
NET_WRITE_TIMEOUT	1000
269 270 271 272 273 274
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	7168
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
275 276 277 278 279 280
select * from information_schema.session_variables where variable_name like 'net_%';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	7168
NET_READ_TIMEOUT	600
NET_RETRY_COUNT	10
NET_WRITE_TIMEOUT	500
281 282 283 284
set net_buffer_length=1;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1024
285 286 287
select * from information_schema.session_variables where variable_name like 'net_buffer_length';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1024
288 289 290 291
set net_buffer_length=2000000000;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1048576
292 293 294
select * from information_schema.session_variables where variable_name like 'net_buffer_length';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1048576
295
set character set cp1251_koi8;
296
show variables like "character_set_client";
297
Variable_name	Value
298
character_set_client	cp1251
299 300 301
select * from information_schema.session_variables where variable_name like 'character_set_client';
VARIABLE_NAME	VARIABLE_VALUE
CHARACTER_SET_CLIENT	cp1251
302 303 304
select @@timestamp>0;
@@timestamp>0
1
305 306 307 308
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
309 310 311 312 313 314 315
show variables like '%alloc%';
Variable_name	Value
query_alloc_block_size	8192
query_prealloc_size	8192
range_alloc_block_size	2048
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
316 317 318 319 320 321 322
select * from information_schema.session_variables where variable_name like '%alloc%';
VARIABLE_NAME	VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE	8192
QUERY_PREALLOC_SIZE	8192
RANGE_ALLOC_BLOCK_SIZE	2048
TRANSACTION_ALLOC_BLOCK_SIZE	8192
TRANSACTION_PREALLOC_SIZE	4096
323 324 325 326 327 328 329 330 331 332 333 334 335 336 337
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;
@@query_alloc_block_size
17408
show variables like '%alloc%';
Variable_name	Value
query_alloc_block_size	17408
query_prealloc_size	18432
range_alloc_block_size	16384
transaction_alloc_block_size	19456
transaction_prealloc_size	20480
338 339 340 341 342 343 344
select * from information_schema.session_variables where variable_name like '%alloc%';
VARIABLE_NAME	VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE	17408
QUERY_PREALLOC_SIZE	18432
RANGE_ALLOC_BLOCK_SIZE	16384
TRANSACTION_ALLOC_BLOCK_SIZE	19456
TRANSACTION_PREALLOC_SIZE	20480
345 346 347 348 349 350 351 352 353 354
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%';
Variable_name	Value
query_alloc_block_size	8192
query_prealloc_size	8192
range_alloc_block_size	2048
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
355 356 357 358 359 360 361
select * from information_schema.session_variables where variable_name like '%alloc%';
VARIABLE_NAME	VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE	8192
QUERY_PREALLOC_SIZE	8192
RANGE_ALLOC_BLOCK_SIZE	2048
TRANSACTION_ALLOC_BLOCK_SIZE	8192
TRANSACTION_PREALLOC_SIZE	4096
bar@mysql.com's avatar
bar@mysql.com committed
362 363 364 365 366 367
SELECT @@version LIKE 'non-existent';
@@version LIKE 'non-existent'
0
SELECT @@version_compile_os LIKE 'non-existent';
@@version_compile_os LIKE 'non-existent'
0
368
set big_tables=OFFF;
369
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
370
set big_tables="OFFF";
371
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
372
set unknown_variable=1;
373
ERROR HY000: Unknown system variable 'unknown_variable'
374
set max_join_size="hello";
375
ERROR 42000: Incorrect argument type to variable 'max_join_size'
376
set storage_engine=UNKNOWN_TABLE_TYPE;
377
ERROR 42000: Unknown table engine 'UNKNOWN_TABLE_TYPE'
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
378
set storage_engine=MERGE, big_tables=2;
379
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
380
show local variables like 'storage_engine';
381
Variable_name	Value
382
storage_engine	MEMORY
383
set SESSION query_cache_size=10000;
384
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
385 386
set GLOBAL storage_engine=DEFAULT;
ERROR 42000: Variable 'storage_engine' doesn't have a default value
387
set character_set_client=UNKNOWN_CHARACTER_SET;
388
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
389 390
set collation_connection=UNKNOWN_COLLATION;
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
391 392 393 394
set character_set_client=NULL;
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'NULL'
set collation_connection=NULL;
ERROR 42000: Variable 'collation_connection' can't be set to the value of 'NULL'
395
set global autocommit=1;
396
ERROR HY000: Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
397
select @@global.timestamp;
398
ERROR HY000: Variable 'timestamp' is a SESSION variable
399
set @@version='';
400
ERROR HY000: Variable 'version' is a read only variable
401
set @@concurrent_insert=1;
402
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
403
set @@global.sql_auto_is_null=1;
404
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable and can't be used with SET GLOBAL
405
select @@global.sql_auto_is_null;
406
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
407
set myisam_max_sort_file_size=100;
408
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
409
set @@SQL_WARNINGS=NULL;
410
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
411 412 413 414 415 416 417
set autocommit=1;
set big_tables=1;
select @@autocommit, @@big_tables;
@@autocommit	@@big_tables
1	1
set global binlog_cache_size=100;
set bulk_insert_buffer_size=100;
418 419
set character set cp1251_koi8;
set character set default;
420 421
set @@global.concurrent_insert=1;
set global connect_timeout=100;
422 423 424 425 426 427 428 429 430 431 432
select @@delay_key_write;
@@delay_key_write
ON
set global delay_key_write="OFF";
select @@delay_key_write;
@@delay_key_write
OFF
set global delay_key_write=ALL;
select @@delay_key_write;
@@delay_key_write
ALL
433
set global delay_key_write=1;
434 435 436
select @@delay_key_write;
@@delay_key_write
ON
437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476
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;
@@max_user_connections
100
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;
477 478 479 480 481 482 483 484
set @@max_sp_recursion_depth=10;
select @@max_sp_recursion_depth;
@@max_sp_recursion_depth
10
set @@max_sp_recursion_depth=0;
select @@max_sp_recursion_depth;
@@max_sp_recursion_depth
0
485 486 487 488 489 490 491 492 493 494 495 496 497 498
set sql_auto_is_null=1;
select @@sql_auto_is_null;
@@sql_auto_is_null
1
set @@sql_auto_is_null=0;
select @@sql_auto_is_null;
@@sql_auto_is_null
0
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;
pem@mysql.com's avatar
pem@mysql.com committed
499
Warnings:
500
Note	1315	The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored
501 502 503 504 505 506 507 508
set sql_low_priority_updates=1;
set sql_max_join_size=200;
select @@sql_max_join_size,@@max_join_size;
@@sql_max_join_size	@@max_join_size
200	200
set sql_quote_show_create=1;
set sql_safe_updates=1;
set sql_select_limit=1;
509
set sql_select_limit=default;
510
set sql_warnings=1;
511
set global table_open_cache=100;
512
set storage_engine=myisam;
513 514 515 516 517 518
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;
519 520 521 522 523 524 525 526
select @@session.insert_id;
@@session.insert_id
1
set @save_insert_id=@@session.insert_id;
set session insert_id=20;
select @@session.insert_id;
@@session.insert_id
20
527 528 529 530 531 532 533 534 535 536
set session last_insert_id=100;
select @@session.insert_id;
@@session.insert_id
20
select @@session.last_insert_id;
@@session.last_insert_id
100
select @@session.insert_id;
@@session.insert_id
20
537 538 539 540
set @@session.insert_id=@save_insert_id;
select @@session.insert_id;
@@session.insert_id
1
541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558
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;
@@key_buffer_size
98304
select * from t1 where a=2;
a
2
select * from t2 where a=3;
a
3
check table t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
559
select max(a) +1, max(a) +2 into @xx,@yy from t1;
560
drop table t1,t2;
561
select @@xxxxxxxxxx;
562
ERROR HY000: Unknown system variable 'xxxxxxxxxx'
563 564 565
select 1;
1
1
566
select @@session.key_buffer_size;
monty@mysql.com's avatar
monty@mysql.com committed
567
ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable
568 569
set ft_boolean_syntax = @@init_connect;
ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
serg@serg.mylan's avatar
serg@serg.mylan committed
570 571 572 573 574 575 576 577 578
set global ft_boolean_syntax = @@init_connect;
ERROR 42000: Variable 'ft_boolean_syntax' can't be set to the value of ''
set init_connect = NULL;
ERROR HY000: Variable 'init_connect' is a GLOBAL variable and should be set with SET GLOBAL
set global init_connect = NULL;
set ft_boolean_syntax = @@init_connect;
ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
set global ft_boolean_syntax = @@init_connect;
ERROR 42000: Variable 'ft_boolean_syntax' can't be set to the value of ''
monty@mysql.com's avatar
monty@mysql.com committed
579 580 581
set global myisam_max_sort_file_size=4294967296;
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
582
myisam_max_sort_file_size	MAX_FILE_SIZE
583 584 585
select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
VARIABLE_NAME	VARIABLE_VALUE
MYISAM_MAX_SORT_FILE_SIZE	MAX_FILE_SIZE
monty@mysql.com's avatar
monty@mysql.com committed
586
set global myisam_max_sort_file_size=default;
587
select @@global.max_user_connections,@@local.max_join_size;
588
@@global.max_user_connections	@@local.max_join_size
589 590 591
100	200
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
select @@global.max_user_connections,@@local.max_join_size;
592
@@global.max_user_connections	@@local.max_join_size
593 594 595
100	200
set @@global.max_user_connections=111,@@local.max_join_size=222;
select @@global.max_user_connections,@@local.max_join_size;
596
@@global.max_user_connections	@@local.max_join_size
597 598 599
111	222
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;
600
@@global.max_user_connections	@@local.max_join_size
601 602 603
222	111
set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
select @@global.max_user_connections,@@local.max_join_size;
604
@@global.max_user_connections	@@local.max_join_size
605 606 607 608 609 610
100	200
set @a=1, @b=2;
set @a=@b, @b=@a;
select @a, @b;
@a	@b
2	1
611
set @@global.global.key_buffer_size= 1;
612
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size= 1' at line 1
613
set GLOBAL global.key_buffer_size= 1;
614
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size= 1' at line 1
615
SELECT @@global.global.key_buffer_size;
616
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1
617
SELECT @@global.session.key_buffer_size;
618
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1
619
SELECT @@global.local.key_buffer_size;
620
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_buffer_size' at line 1
621 622 623 624
set @tstlw = @@log_warnings;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
625 626 627
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	1
628 629 630 631
set global log_warnings = 0;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	0
632 633 634
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	0
635 636 637 638
set global log_warnings = 42;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	42
639 640 641
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	42
642 643 644 645
set global log_warnings = @tstlw;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
646 647 648
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	1
649 650 651 652 653 654 655 656 657
create table t1 (
c1 tinyint,
c2 smallint,
c3 mediumint,
c4 int,
c5 bigint);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
658 659 660 661 662
  `c1` tinyint(4) DEFAULT NULL,
  `c2` smallint(6) DEFAULT NULL,
  `c3` mediumint(9) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  `c5` bigint(20) DEFAULT NULL
663 664
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
665 666
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;
667 668 669
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
670 671
  `c1` bigint(20) DEFAULT NULL,
  `c2` decimal(65,30) DEFAULT NULL,
672
  `c3` longtext,
673
  `c4` double DEFAULT NULL
674 675
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
676
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
677 678
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
Variable_name	Value
679
myisam_data_pointer_size	7
680 681 682
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
VARIABLE_NAME	VARIABLE_VALUE
MYISAM_DATA_POINTER_SIZE	7
683 684
SET GLOBAL table_open_cache=-1;
SHOW VARIABLES LIKE 'table_open_cache';
svoj@mysql.com's avatar
svoj@mysql.com committed
685
Variable_name	Value
686
table_open_cache	1
687 688 689
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
VARIABLE_NAME	VARIABLE_VALUE
TABLE_OPEN_CACHE	1
690
SET GLOBAL table_open_cache=DEFAULT;
bar@mysql.com's avatar
bar@mysql.com committed
691 692 693 694 695
set character_set_results=NULL;
select ifnull(@@character_set_results,"really null");
ifnull(@@character_set_results,"really null")
really null
set names latin1;
696 697 698
select @@have_innodb;
@@have_innodb
#
699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755
*** Various tests with LC_TIME_NAMES
*** LC_TIME_NAMES: testing case insensitivity
set @@lc_time_names='ru_ru';
select @@lc_time_names;
@@lc_time_names
ru_RU
*** LC_TIME_NAMES: testing with a user variable
set @lc='JA_JP';
set @@lc_time_names=@lc;
select @@lc_time_names;
@@lc_time_names
ja_JP
*** LC_TIME_NAMES: testing with string expressions
set lc_time_names=concat('de','_','DE');
select @@lc_time_names;
@@lc_time_names
de_DE
set lc_time_names=concat('de','+','DE');
ERROR HY000: Unknown locale: 'de+DE'
select @@lc_time_names;
@@lc_time_names
de_DE
LC_TIME_NAMES: testing with numeric expressions
set @@lc_time_names=1+2;
select @@lc_time_names;
@@lc_time_names
sv_SE
set @@lc_time_names=1/0;
ERROR 42000: Incorrect argument type to variable 'lc_time_names'
select @@lc_time_names;
@@lc_time_names
sv_SE
set lc_time_names=en_US;
LC_TIME_NAMES: testing NULL and a negative number:
set lc_time_names=NULL;
ERROR 42000: Variable 'lc_time_names' can't be set to the value of 'NULL'
set lc_time_names=-1;
ERROR HY000: Unknown locale: '-1'
select @@lc_time_names;
@@lc_time_names
en_US
LC_TIME_NAMES: testing locale with the last ID:
set lc_time_names=108;
select @@lc_time_names;
@@lc_time_names
zh_HK
LC_TIME_NAMES: testing a number beyond the valid ID range:
set lc_time_names=109;
ERROR HY000: Unknown locale: '109'
select @@lc_time_names;
@@lc_time_names
zh_HK
LC_TIME_NAMES: testing that 0 is en_US:
set lc_time_names=0;
select @@lc_time_names;
@@lc_time_names
en_US
756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
en_US	en_US
set @@global.lc_time_names=fr_FR;
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
fr_FR	en_US
New connection
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
fr_FR	fr_FR
set @@lc_time_names=ru_RU;
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
fr_FR	ru_RU
Returnung to default connection
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
fr_FR	en_US
set lc_time_names=default;
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
fr_FR	fr_FR
set @@global.lc_time_names=default;
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
en_US	fr_FR
set @@lc_time_names=default;
select @@global.lc_time_names, @@lc_time_names;
@@global.lc_time_names	@@lc_time_names
en_US	en_US
787 788 789 790 791
set @test = @@query_prealloc_size;
set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;
@@query_prealloc_size = @test
1
792 793 794
create table t1 (a int);
select a into @x from t1;
Warnings:
795
Warning	1329	No data - zero rows fetched, selected, or processed
796 797
show warnings;
Level	Code	Message
798
Warning	1329	No data - zero rows fetched, selected, or processed
799
drop table t1;
800 801 802 803
set @@warning_count=1;
ERROR HY000: Variable 'warning_count' is a read only variable
set @@global.error_count=1;
ERROR HY000: Variable 'error_count' is a read only variable
804
set @@max_heap_table_size= 4294967296;
805 806 807
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
808
set global max_heap_table_size= 4294967296;
809 810 811
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
812
set @@max_heap_table_size= 4294967296;
813 814 815
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
816 817 818 819 820 821 822
select @@character_set_system;
@@character_set_system
utf8
set global character_set_system = latin1;
ERROR HY000: Variable 'character_set_system' is a read only variable
set @@global.version_compile_os='234';
ERROR HY000: Variable 'version_compile_os' is a read only variable
bar@mysql.com's avatar
bar@mysql.com committed
823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840
set character_set_filesystem=latin1;
select @@character_set_filesystem;
@@character_set_filesystem
latin1
set @@global.character_set_filesystem=latin2;
set character_set_filesystem=latin1;
select @@character_set_filesystem;
@@character_set_filesystem
latin1
set @@global.character_set_filesystem=latin2;
set character_set_filesystem=default;
select @@character_set_filesystem;
@@character_set_filesystem
latin2
set @@global.character_set_filesystem=default;
select @@global.character_set_filesystem;
@@global.character_set_filesystem
binary
841 842 843 844 845
set @old_sql_big_selects = @@sql_big_selects;
set @@sql_big_selects = 1;
show variables like 'sql_big_selects';
Variable_name	Value
sql_big_selects	ON
846 847 848
select * from information_schema.session_variables where variable_name like 'sql_big_selects';
VARIABLE_NAME	VARIABLE_VALUE
SQL_BIG_SELECTS	ON
849
set @@sql_big_selects = @old_sql_big_selects;
850 851 852 853
set @@sql_notes = 0, @@sql_warnings = 0;
show variables like 'sql_notes';
Variable_name	Value
sql_notes	OFF
854 855 856
select * from information_schema.session_variables where variable_name like 'sql_notes';
VARIABLE_NAME	VARIABLE_VALUE
SQL_NOTES	OFF
857 858 859
show variables like 'sql_warnings';
Variable_name	Value
sql_warnings	OFF
860 861 862
select * from information_schema.session_variables where variable_name like 'sql_warnings';
VARIABLE_NAME	VARIABLE_VALUE
SQL_WARNINGS	OFF
863 864 865 866
set @@sql_notes = 1, @@sql_warnings = 1;
show variables like 'sql_notes';
Variable_name	Value
sql_notes	ON
867 868 869
select * from information_schema.session_variables where variable_name like 'sql_notes';
VARIABLE_NAME	VARIABLE_VALUE
SQL_NOTES	ON
870 871 872
show variables like 'sql_warnings';
Variable_name	Value
sql_warnings	ON
873 874 875
select * from information_schema.session_variables where variable_name like 'sql_warnings';
VARIABLE_NAME	VARIABLE_VALUE
SQL_WARNINGS	ON
876 877 878 879 880 881 882
select @@system_time_zone;
@@system_time_zone
#
select @@version, @@version_comment, @@version_compile_machine,
@@version_compile_os;
@@version	@@version_comment	@@version_compile_machine	@@version_compile_os
#	#	#	#
883 884 885 886 887 888
select @@basedir, @@datadir, @@tmpdir;
@@basedir	@@datadir	@@tmpdir
#	#	#
show variables like 'basedir';
Variable_name	Value
basedir	#
889 890 891
select * from information_schema.session_variables where variable_name like 'basedir';
VARIABLE_NAME	VARIABLE_VALUE
BASEDIR	#
892 893 894
show variables like 'datadir';
Variable_name	Value
datadir	#
895 896 897
select * from information_schema.session_variables where variable_name like 'datadir';
VARIABLE_NAME	VARIABLE_VALUE
DATADIR	#
898 899 900
show variables like 'tmpdir';
Variable_name	Value
tmpdir	#
901 902 903
select * from information_schema.session_variables where variable_name like 'tmpdir';
VARIABLE_NAME	VARIABLE_VALUE
TMPDIR	#
904 905 906 907 908 909 910 911 912 913
select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key;
@@ssl_ca	@@ssl_capath	@@ssl_cert	@@ssl_cipher	@@ssl_key
#	#	#	#	#
show variables like 'ssl%';
Variable_name	Value
ssl_ca	#
ssl_capath	#
ssl_cert	#
ssl_cipher	#
ssl_key	#
914 915 916 917 918 919 920
select * from information_schema.session_variables where variable_name like 'ssl%';
VARIABLE_NAME	VARIABLE_VALUE
SSL_CA	#
SSL_CAPATH	#
SSL_CERT	#
SSL_CIPHER	#
SSL_KEY	#
921 922 923 924 925 926
select @@log_queries_not_using_indexes;
@@log_queries_not_using_indexes
0
show variables like 'log_queries_not_using_indexes';
Variable_name	Value
log_queries_not_using_indexes	OFF
927 928 929
select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
VARIABLE_NAME	VARIABLE_VALUE
LOG_QUERIES_NOT_USING_INDEXES	OFF
930 931 932 933 934 935
select @@"";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '""' at line 1
select @@&;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&' at line 1
select @@@;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@' at line 1
936 937 938 939 940 941 942 943
select @@hostname;
@@hostname
#
set @@hostname= "anothername";
ERROR HY000: Variable 'hostname' is a read only variable
show variables like 'hostname';
Variable_name	Value
hostname	#
944
End of 5.0 tests
945 946 947 948 949 950 951 952 953 954
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;
set global max_binlog_size           =@my_max_binlog_size;
set global max_connect_errors        =@my_max_connect_errors;
955
set global max_connections           =@my_max_connections;
956 957 958 959
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;
960 961
set global max_user_connections      =default;
set global max_write_lock_count      =default;
962 963 964 965 966 967 968 969 970 971 972
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;