variables.result 33.1 KB
Newer Older
1
drop table if exists t1,t2;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
set @my_binlog_cache_size         =@@global.binlog_cache_size;
set @my_connect_timeout           =@@global.connect_timeout;
set @my_delayed_insert_timeout    =@@global.delayed_insert_timeout;
set @my_delayed_queue_size        =@@global.delayed_queue_size;
set @my_flush                     =@@global.flush;
set @my_flush_time                =@@global.flush_time;
set @my_key_buffer_size           =@@global.key_buffer_size;
set @my_max_binlog_cache_size     =@@global.max_binlog_cache_size;
set @my_max_binlog_size           =@@global.max_binlog_size;
set @my_max_connect_errors        =@@global.max_connect_errors;
set @my_max_delayed_threads       =@@global.max_delayed_threads;
set @my_max_heap_table_size       =@@global.max_heap_table_size;
set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads;
set @my_max_join_size             =@@global.max_join_size;
set @my_max_user_connections      =@@global.max_user_connections;
set @my_max_write_lock_count      =@@global.max_write_lock_count;
set @my_myisam_data_pointer_size  =@@global.myisam_data_pointer_size;
set @my_net_buffer_length         =@@global.net_buffer_length;
set @my_net_write_timeout         =@@global.net_write_timeout;
set @my_net_read_timeout          =@@global.net_read_timeout;
set @my_query_cache_limit         =@@global.query_cache_limit;
set @my_query_cache_type          =@@global.query_cache_type;
set @my_rpl_recovery_rank         =@@global.rpl_recovery_rank;
set @my_server_id                 =@@global.server_id;
set @my_slow_launch_time          =@@global.slow_launch_time;
set @my_storage_engine            =@@global.storage_engine;
set @my_thread_cache_size         =@@global.thread_cache_size;
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 54
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
unknown's avatar
unknown committed
55 56
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;
57
@test_int	@test_double	@test_string	@test_string2	@select
58
10	1e-10	abcdeghi	abcdefghij	NULL
unknown's avatar
unknown committed
59 60
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
61 62
@test_int	@test_double	@test_string	@test_string2
hello	hello	hello	hello
unknown's avatar
unknown committed
63 64
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
65 66
@test_int	@test_double	@test_string	@test_string2
hellohello	hellohello	hellohello	hellohello
unknown's avatar
unknown committed
67 68
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
69 70
@test_int	@test_double	@test_string	@test_string2
NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
71
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
72 73
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
74
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
75 76
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
77
Warnings:
78
Note	1003	select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
unknown's avatar
unknown committed
79
select @t5;
80 81
@t5
1.23456
unknown's avatar
unknown committed
82 83 84
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;
unknown's avatar
unknown committed
85 86
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
unknown's avatar
unknown committed
87
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
unknown's avatar
unknown committed
88 89 90
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
91
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
unknown's avatar
unknown committed
92 93 94
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
95 96
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
unknown's avatar
unknown committed
97 98 99
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
100
drop table t1;
101
set GLOBAL max_join_size=10;
unknown's avatar
unknown committed
102 103 104 105
set max_join_size=100;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	100
106 107 108
select * from information_schema.session_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	100
unknown's avatar
unknown committed
109 110
show global variables like 'max_join_size';
Variable_name	Value
111
max_join_size	10
112 113 114
select * from information_schema.global_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	10
unknown's avatar
unknown committed
115 116 117 118
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
119 120 121
select * from information_schema.global_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	2000
unknown's avatar
unknown committed
122 123 124 125
set max_join_size=DEFAULT;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
126 127 128
select * from information_schema.session_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	2000
unknown's avatar
unknown committed
129 130 131
set GLOBAL max_join_size=DEFAULT;
show global variables like 'max_join_size';
Variable_name	Value
132
max_join_size	HA_POS_ERROR
133 134 135
select * from information_schema.global_variables where variable_name like 'max_join_size';
VARIABLE_NAME	VARIABLE_VALUE
MAX_JOIN_SIZE	HA_POS_ERROR
unknown's avatar
unknown committed
136 137
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
138
@@local.max_join_size	@@global.max_join_size
unknown's avatar
unknown committed
139 140 141 142
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
unknown's avatar
unknown committed
143 144 145 146 147 148
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
149
explain extended select last_insert_id(345);
150 151
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
152
Warnings:
153
Note	1003	select last_insert_id(345) AS `last_insert_id(345)`
unknown's avatar
unknown committed
154
select @@IDENTITY,last_insert_id(), @@identity;
155
@@IDENTITY	last_insert_id()	@@identity
unknown's avatar
unknown committed
156
345	345	345
157
explain extended select @@IDENTITY,last_insert_id(), @@identity;
158 159
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
160
Warnings:
161
Note	1003	select 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
unknown's avatar
unknown committed
162
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
163
set global concurrent_insert=2;
unknown's avatar
unknown committed
164 165
show variables like 'concurrent_insert';
Variable_name	Value
166
concurrent_insert	2
167 168 169
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	2
unknown's avatar
unknown committed
170 171 172
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name	Value
173
concurrent_insert	1
174 175 176
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	1
unknown's avatar
unknown committed
177 178 179
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name	Value
180
concurrent_insert	0
181 182 183
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	0
unknown's avatar
unknown committed
184
set global concurrent_insert=DEFAULT;
185 186 187 188
select @@concurrent_insert;
@@concurrent_insert
1
set global timed_mutexes=ON;
unknown's avatar
unknown committed
189 190 191
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	ON
192 193 194
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
VARIABLE_NAME	VARIABLE_VALUE
TIMED_MUTEXES	ON
unknown's avatar
unknown committed
195 196 197 198
set global timed_mutexes=0;
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	OFF
199 200 201
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
VARIABLE_NAME	VARIABLE_VALUE
TIMED_MUTEXES	OFF
unknown's avatar
unknown committed
202
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
unknown's avatar
unknown committed
203
show local variables like 'storage_engine';
unknown's avatar
unknown committed
204
Variable_name	Value
205
storage_engine	MEMORY
206 207 208
select * from information_schema.session_variables where variable_name like 'storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
STORAGE_ENGINE	MEMORY
unknown's avatar
unknown committed
209
show global variables like 'storage_engine';
unknown's avatar
unknown committed
210
Variable_name	Value
211
storage_engine	MRG_MYISAM
212 213 214
select * from information_schema.global_variables where variable_name like 'storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
STORAGE_ENGINE	MRG_MYISAM
unknown's avatar
unknown committed
215
set GLOBAL query_cache_size=100000;
216
set GLOBAL myisam_max_sort_file_size=2000000;
unknown's avatar
unknown committed
217 218
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
219
myisam_max_sort_file_size	1048576
220 221 222
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
223
set GLOBAL myisam_max_sort_file_size=default;
unknown's avatar
unknown committed
224 225
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
226
myisam_max_sort_file_size	FILE_SIZE
227 228 229
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
230
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
231 232 233 234 235 236 237 238
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
239 240 241 242 243 244
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
unknown's avatar
unknown committed
245 246 247 248 249 250
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	2048
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
251 252 253 254 255 256
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
unknown's avatar
unknown committed
257 258 259 260 261 262 263
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
264 265 266 267 268 269
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
unknown's avatar
unknown committed
270 271 272 273 274 275
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	7168
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
276 277 278 279 280 281
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
unknown's avatar
unknown committed
282 283 284 285
set net_buffer_length=1;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1024
286 287 288
select * from information_schema.session_variables where variable_name like 'net_buffer_length';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1024
unknown's avatar
unknown committed
289 290 291 292
set net_buffer_length=2000000000;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1048576
293 294 295
select * from information_schema.session_variables where variable_name like 'net_buffer_length';
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1048576
unknown's avatar
unknown committed
296
set character set cp1251_koi8;
297
show variables like "character_set_client";
unknown's avatar
unknown committed
298
Variable_name	Value
299
character_set_client	cp1251
300 301 302
select * from information_schema.session_variables where variable_name like 'character_set_client';
VARIABLE_NAME	VARIABLE_VALUE
CHARACTER_SET_CLIENT	cp1251
unknown's avatar
unknown committed
303 304 305
select @@timestamp>0;
@@timestamp>0
1
306 307 308 309
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
310 311 312 313 314 315 316
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
317 318 319 320 321 322 323
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
324 325 326 327 328 329 330 331 332 333 334 335 336 337 338
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
339 340 341 342 343 344 345
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
346 347 348 349 350 351 352 353 354 355
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
356 357 358 359 360 361 362
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
unknown's avatar
unknown committed
363 364 365 366 367 368
SELECT @@version LIKE 'non-existent';
@@version LIKE 'non-existent'
0
SELECT @@version_compile_os LIKE 'non-existent';
@@version_compile_os LIKE 'non-existent'
0
unknown's avatar
unknown committed
369
set big_tables=OFFF;
370
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
unknown's avatar
unknown committed
371
set big_tables="OFFF";
372
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
unknown's avatar
unknown committed
373
set unknown_variable=1;
374
ERROR HY000: Unknown system variable 'unknown_variable'
unknown's avatar
unknown committed
375
set max_join_size="hello";
376
ERROR 42000: Incorrect argument type to variable 'max_join_size'
unknown's avatar
unknown committed
377
set storage_engine=UNKNOWN_TABLE_TYPE;
378
ERROR 42000: Unknown table engine 'UNKNOWN_TABLE_TYPE'
unknown's avatar
unknown committed
379
set storage_engine=MERGE, big_tables=2;
380
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
unknown's avatar
unknown committed
381
show local variables like 'storage_engine';
unknown's avatar
unknown committed
382
Variable_name	Value
383
storage_engine	MEMORY
unknown's avatar
unknown committed
384
set SESSION query_cache_size=10000;
385
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
unknown's avatar
unknown committed
386 387
set GLOBAL storage_engine=DEFAULT;
ERROR 42000: Variable 'storage_engine' doesn't have a default value
388
set character_set_client=UNKNOWN_CHARACTER_SET;
389
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
390 391
set collation_connection=UNKNOWN_COLLATION;
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
392 393 394 395
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'
unknown's avatar
unknown committed
396
set global autocommit=1;
397
ERROR HY000: Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
unknown's avatar
unknown committed
398
select @@global.timestamp;
399
ERROR HY000: Variable 'timestamp' is a SESSION variable
unknown's avatar
unknown committed
400
set @@version='';
401
ERROR HY000: Variable 'version' is a read only variable
unknown's avatar
unknown committed
402
set @@concurrent_insert=1;
403
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
unknown's avatar
unknown committed
404
set @@global.sql_auto_is_null=1;
405
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable and can't be used with SET GLOBAL
unknown's avatar
unknown committed
406
select @@global.sql_auto_is_null;
407
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
408
set myisam_max_sort_file_size=100;
409
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
410
set @@SQL_WARNINGS=NULL;
unknown's avatar
unknown committed
411
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
unknown's avatar
unknown committed
412 413 414 415 416 417 418
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;
unknown's avatar
unknown committed
419 420
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
421 422
set @@global.concurrent_insert=1;
set global connect_timeout=100;
423 424 425 426 427 428 429 430 431 432 433
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
unknown's avatar
unknown committed
434
set global delay_key_write=1;
435 436 437
select @@delay_key_write;
@@delay_key_write
ON
unknown's avatar
unknown committed
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 477
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;
478 479 480 481 482 483 484 485
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
unknown's avatar
unknown committed
486 487 488 489 490 491 492 493 494 495 496 497 498 499
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;
unknown's avatar
unknown committed
500
Warnings:
501
Note	1315	The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored
unknown's avatar
unknown committed
502 503 504 505 506 507 508 509
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;
510
set sql_select_limit=default;
unknown's avatar
unknown committed
511
set sql_warnings=1;
unknown's avatar
unknown committed
512
set global table_open_cache=100;
unknown's avatar
unknown committed
513
set storage_engine=myisam;
unknown's avatar
unknown committed
514 515 516 517 518 519
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;
520 521 522 523 524 525 526 527
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
528 529 530 531 532 533 534 535 536 537
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
538 539 540 541
set @@session.insert_id=@save_insert_id;
select @@session.insert_id;
@@session.insert_id
1
unknown's avatar
unknown committed
542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559
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
560
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
561
drop table t1,t2;
562
select @@xxxxxxxxxx;
563
ERROR HY000: Unknown system variable 'xxxxxxxxxx'
564 565 566
select 1;
1
1
567
select @@session.key_buffer_size;
unknown's avatar
unknown committed
568
ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable
569 570
set ft_boolean_syntax = @@init_connect;
ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
unknown's avatar
unknown committed
571 572 573 574 575 576 577 578 579
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 ''
unknown's avatar
unknown committed
580 581 582
set global myisam_max_sort_file_size=4294967296;
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
583
myisam_max_sort_file_size	MAX_FILE_SIZE
584 585 586
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
unknown's avatar
unknown committed
587
set global myisam_max_sort_file_size=default;
588
select @@global.max_user_connections,@@local.max_join_size;
589
@@global.max_user_connections	@@local.max_join_size
590 591 592
100	200
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
select @@global.max_user_connections,@@local.max_join_size;
593
@@global.max_user_connections	@@local.max_join_size
594 595 596
100	200
set @@global.max_user_connections=111,@@local.max_join_size=222;
select @@global.max_user_connections,@@local.max_join_size;
597
@@global.max_user_connections	@@local.max_join_size
598 599 600
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;
601
@@global.max_user_connections	@@local.max_join_size
602 603 604
222	111
set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
select @@global.max_user_connections,@@local.max_join_size;
605
@@global.max_user_connections	@@local.max_join_size
606 607 608 609 610 611
100	200
set @a=1, @b=2;
set @a=@b, @b=@a;
select @a, @b;
@a	@b
2	1
612
set @@global.global.key_buffer_size= 1;
613
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
614
set GLOBAL global.key_buffer_size= 1;
615
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
616
SELECT @@global.global.key_buffer_size;
617
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
618
SELECT @@global.session.key_buffer_size;
619
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
620
SELECT @@global.local.key_buffer_size;
621
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
622 623 624 625
set @tstlw = @@log_warnings;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
626 627 628
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	1
629 630 631 632
set global log_warnings = 0;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	0
633 634 635
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	0
636 637 638 639
set global log_warnings = 42;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	42
640 641 642
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	42
643 644 645 646
set global log_warnings = @tstlw;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
647 648 649
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	1
650 651 652 653 654 655 656 657 658
create table t1 (
c1 tinyint,
c2 smallint,
c3 mediumint,
c4 int,
c5 bigint);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
659 660 661 662 663
  `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
664 665
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
unknown's avatar
unknown committed
666 667
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;
668 669 670
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
671 672
  `c1` bigint(20) DEFAULT NULL,
  `c2` decimal(65,30) DEFAULT NULL,
unknown's avatar
unknown committed
673
  `c3` longtext,
674
  `c4` double DEFAULT NULL
675 676
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
677
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
678 679
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
Variable_name	Value
680
myisam_data_pointer_size	7
681 682 683
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
VARIABLE_NAME	VARIABLE_VALUE
MYISAM_DATA_POINTER_SIZE	7
unknown's avatar
unknown committed
684 685
SET GLOBAL table_open_cache=-1;
SHOW VARIABLES LIKE 'table_open_cache';
unknown's avatar
unknown committed
686
Variable_name	Value
unknown's avatar
unknown committed
687
table_open_cache	1
688 689 690
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
VARIABLE_NAME	VARIABLE_VALUE
TABLE_OPEN_CACHE	1
unknown's avatar
unknown committed
691
SET GLOBAL table_open_cache=DEFAULT;
unknown's avatar
unknown committed
692 693 694 695 696
set character_set_results=NULL;
select ifnull(@@character_set_results,"really null");
ifnull(@@character_set_results,"really null")
really null
set names latin1;
697 698 699
select @@have_innodb;
@@have_innodb
#
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 756
*** 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
757 758 759 760 761
set @test = @@query_prealloc_size;
set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;
@@query_prealloc_size = @test
1
762 763 764
create table t1 (a int);
select a into @x from t1;
Warnings:
765
Warning	1329	No data - zero rows fetched, selected, or processed
766 767
show warnings;
Level	Code	Message
768
Warning	1329	No data - zero rows fetched, selected, or processed
769
drop table t1;
770 771 772 773
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
774
set @@max_heap_table_size= 4294967296;
775 776 777
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
778
set global max_heap_table_size= 4294967296;
779 780 781
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
782
set @@max_heap_table_size= 4294967296;
783 784 785
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
786 787 788 789 790 791 792
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
unknown's avatar
unknown committed
793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810
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
811 812 813 814 815
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
816 817 818
select * from information_schema.session_variables where variable_name like 'sql_big_selects';
VARIABLE_NAME	VARIABLE_VALUE
SQL_BIG_SELECTS	ON
819
set @@sql_big_selects = @old_sql_big_selects;
820 821 822 823
set @@sql_notes = 0, @@sql_warnings = 0;
show variables like 'sql_notes';
Variable_name	Value
sql_notes	OFF
824 825 826
select * from information_schema.session_variables where variable_name like 'sql_notes';
VARIABLE_NAME	VARIABLE_VALUE
SQL_NOTES	OFF
827 828 829
show variables like 'sql_warnings';
Variable_name	Value
sql_warnings	OFF
830 831 832
select * from information_schema.session_variables where variable_name like 'sql_warnings';
VARIABLE_NAME	VARIABLE_VALUE
SQL_WARNINGS	OFF
833 834 835 836
set @@sql_notes = 1, @@sql_warnings = 1;
show variables like 'sql_notes';
Variable_name	Value
sql_notes	ON
837 838 839
select * from information_schema.session_variables where variable_name like 'sql_notes';
VARIABLE_NAME	VARIABLE_VALUE
SQL_NOTES	ON
840 841 842
show variables like 'sql_warnings';
Variable_name	Value
sql_warnings	ON
843 844 845
select * from information_schema.session_variables where variable_name like 'sql_warnings';
VARIABLE_NAME	VARIABLE_VALUE
SQL_WARNINGS	ON
846 847 848 849 850 851 852
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
#	#	#	#
853 854 855 856 857 858
select @@basedir, @@datadir, @@tmpdir;
@@basedir	@@datadir	@@tmpdir
#	#	#
show variables like 'basedir';
Variable_name	Value
basedir	#
859 860 861
select * from information_schema.session_variables where variable_name like 'basedir';
VARIABLE_NAME	VARIABLE_VALUE
BASEDIR	#
862 863 864
show variables like 'datadir';
Variable_name	Value
datadir	#
865 866 867
select * from information_schema.session_variables where variable_name like 'datadir';
VARIABLE_NAME	VARIABLE_VALUE
DATADIR	#
868 869 870
show variables like 'tmpdir';
Variable_name	Value
tmpdir	#
871 872 873
select * from information_schema.session_variables where variable_name like 'tmpdir';
VARIABLE_NAME	VARIABLE_VALUE
TMPDIR	#
874 875 876 877 878 879 880 881 882 883
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	#
884 885 886 887 888 889 890
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	#
891 892 893 894 895 896
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
897 898 899
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
unknown's avatar
unknown committed
900 901 902 903 904 905
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
906
End of 5.0 tests
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
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;
set global max_delayed_threads       =@my_max_delayed_threads;
set global max_heap_table_size       =@my_max_heap_table_size;
set global max_insert_delayed_threads=@my_max_insert_delayed_threads;
set global max_join_size             =@my_max_join_size;
set global max_user_connections      =@my_max_user_connections;
set global max_write_lock_count      =@my_max_write_lock_count;
set global myisam_data_pointer_size  =@my_myisam_data_pointer_size;
set global net_buffer_length         =@my_net_buffer_length;
set global net_write_timeout         =@my_net_write_timeout;
set global net_read_timeout          =@my_net_read_timeout;
set global query_cache_limit         =@my_query_cache_limit;
set global query_cache_type          =@my_query_cache_type;
set global rpl_recovery_rank         =@my_rpl_recovery_rank;
set global server_id                 =@my_server_id;
set global slow_launch_time          =@my_slow_launch_time;
set global storage_engine            =@my_storage_engine;
set global thread_cache_size         =@my_thread_cache_size;