variables.result 50.3 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
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;
18
set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
19 20 21 22 23 24 25 26 27 28
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
set @my_max_allowed_packet        =@@global.max_allowed_packet;
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 55
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
56 57
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;
58
@test_int	@test_double	@test_string	@test_string2	@select
59
10	1e-10	abcdeghi	abcdefghij	NULL
unknown's avatar
unknown committed
60 61
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
62 63
@test_int	@test_double	@test_string	@test_string2
hello	hello	hello	hello
unknown's avatar
unknown committed
64 65
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
66 67
@test_int	@test_double	@test_string	@test_string2
hellohello	hellohello	hellohello	hellohello
unknown's avatar
unknown committed
68 69
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
70 71
@test_int	@test_double	@test_string	@test_string2
NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
72
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
73 74
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
75
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
76 77
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
78
Warnings:
79
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
80
select @t5;
81 82
@t5
1.23456
unknown's avatar
unknown committed
83 84 85
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
86 87
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
unknown's avatar
unknown committed
88
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
unknown's avatar
unknown committed
89 90 91
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
92
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
unknown's avatar
unknown committed
93 94 95
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
96 97
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
unknown's avatar
unknown committed
98 99 100
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
101
drop table t1;
102
set GLOBAL max_join_size=10;
unknown's avatar
unknown committed
103 104 105 106
set max_join_size=100;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	100
107 108 109
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
110 111
show global variables like 'max_join_size';
Variable_name	Value
112
max_join_size	10
113 114 115
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
116 117 118 119
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
120 121 122
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
123 124 125 126
set max_join_size=DEFAULT;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
127 128 129
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
130 131 132
set GLOBAL max_join_size=DEFAULT;
show global variables like 'max_join_size';
Variable_name	Value
133
max_join_size	HA_POS_ERROR
134 135 136
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
137 138
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
139
@@local.max_join_size	@@global.max_join_size
unknown's avatar
unknown committed
140 141 142 143
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
unknown's avatar
unknown committed
144 145 146 147 148 149
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
150
explain extended select last_insert_id(345);
151 152
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
153
Warnings:
154
Note	1003	select last_insert_id(345) AS `last_insert_id(345)`
unknown's avatar
unknown committed
155
select @@IDENTITY,last_insert_id(), @@identity;
156
@@IDENTITY	last_insert_id()	@@identity
unknown's avatar
unknown committed
157
345	345	345
158
explain extended select @@IDENTITY,last_insert_id(), @@identity;
159 160
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
161
Warnings:
162
Note	1003	select @@IDENTITY AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,@@identity AS `@@identity`
unknown's avatar
unknown committed
163
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
164
set global concurrent_insert=2;
unknown's avatar
unknown committed
165 166
show variables like 'concurrent_insert';
Variable_name	Value
167
concurrent_insert	2
168 169 170
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	2
unknown's avatar
unknown committed
171 172 173
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name	Value
174
concurrent_insert	1
175 176 177
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	1
unknown's avatar
unknown committed
178 179 180
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name	Value
181
concurrent_insert	0
182 183 184
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
VARIABLE_NAME	VARIABLE_VALUE
CONCURRENT_INSERT	0
unknown's avatar
unknown committed
185
set global concurrent_insert=DEFAULT;
186 187 188 189
select @@concurrent_insert;
@@concurrent_insert
1
set global timed_mutexes=ON;
unknown's avatar
unknown committed
190 191 192
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	ON
193 194 195
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
VARIABLE_NAME	VARIABLE_VALUE
TIMED_MUTEXES	ON
unknown's avatar
unknown committed
196 197 198 199
set global timed_mutexes=0;
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	OFF
200 201 202
select * from information_schema.session_variables where variable_name like 'timed_mutexes';
VARIABLE_NAME	VARIABLE_VALUE
TIMED_MUTEXES	OFF
unknown's avatar
unknown committed
203
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
unknown's avatar
unknown committed
204
show local variables like 'storage_engine';
unknown's avatar
unknown committed
205
Variable_name	Value
206
storage_engine	MEMORY
207 208 209
select * from information_schema.session_variables where variable_name like 'storage_engine';
VARIABLE_NAME	VARIABLE_VALUE
STORAGE_ENGINE	MEMORY
unknown's avatar
unknown committed
210
show global variables like 'storage_engine';
unknown's avatar
unknown committed
211
Variable_name	Value
212
storage_engine	MRG_MYISAM
213 214 215
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
216
set GLOBAL query_cache_size=100000;
217
set GLOBAL myisam_max_sort_file_size=2000000;
unknown's avatar
unknown committed
218 219
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
220
myisam_max_sort_file_size	1048576
221 222 223
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
224
set GLOBAL myisam_max_sort_file_size=default;
225
show global variables like 'myisam_max_sort_file_size';
unknown's avatar
unknown committed
226
Variable_name	Value
227
myisam_max_sort_file_size	FILE_SIZE
unknown's avatar
unknown committed
228
select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
229 230
VARIABLE_NAME	VARIABLE_VALUE
MYISAM_MAX_SORT_FILE_SIZE	FILE_SIZE
231
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
232 233 234 235 236 237 238
set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
show global variables like 'net_%';
Variable_name	Value
net_buffer_length	1024
net_read_timeout	300
net_retry_count	10
net_write_timeout	200
unknown's avatar
unknown committed
239
select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
240 241 242 243 244
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
show session variables like 'net_%';
Variable_name	Value
247 248
net_buffer_length	16384
net_read_timeout	30
unknown's avatar
unknown committed
249
net_retry_count	10
250
net_write_timeout	60
unknown's avatar
unknown committed
251
select * from information_schema.session_variables where variable_name like 'net_%' order by 1;
252
VARIABLE_NAME	VARIABLE_VALUE
253 254
NET_BUFFER_LENGTH	16384
NET_READ_TIMEOUT	30
255
NET_RETRY_COUNT	10
256 257
NET_WRITE_TIMEOUT	60
set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
unknown's avatar
unknown committed
258 259
show global variables like 'net_%';
Variable_name	Value
260
net_buffer_length	7168
unknown's avatar
unknown committed
261 262 263
net_read_timeout	900
net_retry_count	10
net_write_timeout	1000
unknown's avatar
unknown committed
264
select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
265
VARIABLE_NAME	VARIABLE_VALUE
266
NET_BUFFER_LENGTH	7168
267 268 269
NET_READ_TIMEOUT	900
NET_RETRY_COUNT	10
NET_WRITE_TIMEOUT	1000
270
set global net_buffer_length=1;
271 272
Warnings:
Warning	1292	Truncated incorrect net_buffer_length value: '1'
273
show global variables like 'net_buffer_length';
unknown's avatar
unknown committed
274 275
Variable_name	Value
net_buffer_length	1024
276
select * from information_schema.global_variables where variable_name like 'net_buffer_length';
277 278
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1024
279
set global net_buffer_length=2000000000;
280 281
Warnings:
Warning	1292	Truncated incorrect net_buffer_length value: '2000000000'
282
show global variables like 'net_buffer_length';
unknown's avatar
unknown committed
283 284
Variable_name	Value
net_buffer_length	1048576
285
select * from information_schema.global_variables where variable_name like 'net_buffer_length';
286 287
VARIABLE_NAME	VARIABLE_VALUE
NET_BUFFER_LENGTH	1048576
unknown's avatar
unknown committed
288
set character set cp1251_koi8;
289
show variables like "character_set_client";
unknown's avatar
unknown committed
290
Variable_name	Value
291
character_set_client	cp1251
292 293 294
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
295 296 297
select @@timestamp>0;
@@timestamp>0
1
298 299 300 301
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
302 303 304 305 306 307 308 309 310

==+ Testing %alloc% system variables +==
==+ NOTE:  These values *must* be a multiple of 1024 +==
==+ Other values will be rounded down to nearest multiple +==

==+ 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');
311 312 313
Variable_name	Value
query_alloc_block_size	8192
query_prealloc_size	8192
314
range_alloc_block_size	4096
315 316
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
Patrick Crews's avatar
Patrick Crews committed
317 318 319 320
==+ information_schema data +==
SELECT * FROM information_schema.session_variables 
WHERE variable_name IN ('range_alloc_block_size',
'query_alloc_block_size', 'query_prealloc_size',
321
'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
322 323 324
VARIABLE_NAME	VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE	8192
QUERY_PREALLOC_SIZE	8192
325
RANGE_ALLOC_BLOCK_SIZE	4096
326 327
TRANSACTION_ALLOC_BLOCK_SIZE	8192
TRANSACTION_PREALLOC_SIZE	4096
328 329 330 331 332 333 334
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;
==+ Check manipulated values ==+
Patrick Crews's avatar
Patrick Crews committed
335 336 337
select @@query_alloc_block_size;
@@query_alloc_block_size
17408
338 339 340 341 342 343 344 345 346
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
'query_alloc_block_size', 'query_prealloc_size',
'transaction_alloc_block_size', 'transaction_prealloc_size');
Variable_name	Value
query_alloc_block_size	17408
query_prealloc_size	17408
range_alloc_block_size	16384
transaction_alloc_block_size	20480
transaction_prealloc_size	19456
Patrick Crews's avatar
Patrick Crews committed
347 348 349 350 351 352 353 354 355 356 357
==+ 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;
VARIABLE_NAME	VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE	17408
QUERY_PREALLOC_SIZE	17408
RANGE_ALLOC_BLOCK_SIZE	16384
TRANSACTION_ALLOC_BLOCK_SIZE	20480
TRANSACTION_PREALLOC_SIZE	19456
358 359 360
==+ Manipulate variable values +==
Testing values that are not 1024 multiples
set @@range_alloc_block_size=1024*16+1023;
361
set @@query_alloc_block_size=1024*17+2;
362
set @@query_prealloc_size=1024*18-1023;
363 364 365 366 367
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
368 369 370 371
==+ 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');
372 373
Variable_name	Value
query_alloc_block_size	17408
374
query_prealloc_size	17408
375 376 377
range_alloc_block_size	16384
transaction_alloc_block_size	19456
transaction_prealloc_size	20480
Patrick Crews's avatar
Patrick Crews committed
378 379 380 381 382
==+ 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;
383 384
VARIABLE_NAME	VARIABLE_VALUE
QUERY_ALLOC_BLOCK_SIZE	17408
Patrick Crews's avatar
Patrick Crews committed
385
QUERY_PREALLOC_SIZE	17408
386 387 388
RANGE_ALLOC_BLOCK_SIZE	16384
TRANSACTION_ALLOC_BLOCK_SIZE	19456
TRANSACTION_PREALLOC_SIZE	20480
389
==+ Set values back to the default values +==
390 391 392
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;
Patrick Crews's avatar
Patrick Crews committed
393
==+ Check the values now that they are reset +==
394 395 396
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
'query_alloc_block_size', 'query_prealloc_size',
'transaction_alloc_block_size', 'transaction_prealloc_size');
397 398 399
Variable_name	Value
query_alloc_block_size	8192
query_prealloc_size	8192
400
range_alloc_block_size	4096
401 402
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
unknown's avatar
unknown committed
403 404 405 406 407 408
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
409
set big_tables=OFFF;
410
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
unknown's avatar
unknown committed
411
set big_tables="OFFF";
412
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
unknown's avatar
unknown committed
413
set unknown_variable=1;
414
ERROR HY000: Unknown system variable 'unknown_variable'
unknown's avatar
unknown committed
415
set max_join_size="hello";
416
ERROR 42000: Incorrect argument type to variable 'max_join_size'
unknown's avatar
unknown committed
417
set storage_engine=UNKNOWN_TABLE_TYPE;
418
ERROR 42000: Unknown table engine 'UNKNOWN_TABLE_TYPE'
unknown's avatar
unknown committed
419
set storage_engine=MERGE, big_tables=2;
420
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
unknown's avatar
unknown committed
421
show local variables like 'storage_engine';
unknown's avatar
unknown committed
422
Variable_name	Value
423
storage_engine	MEMORY
unknown's avatar
unknown committed
424
set SESSION query_cache_size=10000;
425
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
unknown's avatar
unknown committed
426 427
set GLOBAL storage_engine=DEFAULT;
ERROR 42000: Variable 'storage_engine' doesn't have a default value
428
set character_set_client=UNKNOWN_CHARACTER_SET;
429
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
430 431
set collation_connection=UNKNOWN_COLLATION;
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
432 433 434 435
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
436
set global autocommit=1;
437
ERROR HY000: Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
unknown's avatar
unknown committed
438
select @@global.timestamp;
439
ERROR HY000: Variable 'timestamp' is a SESSION variable
unknown's avatar
unknown committed
440
set @@version='';
441
ERROR HY000: Variable 'version' is a read only variable
unknown's avatar
unknown committed
442
set @@concurrent_insert=1;
443
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
unknown's avatar
unknown committed
444
set @@global.sql_auto_is_null=1;
445
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable and can't be used with SET GLOBAL
unknown's avatar
unknown committed
446
select @@global.sql_auto_is_null;
447
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
448
set myisam_max_sort_file_size=100;
449
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
450
set @@SQL_WARNINGS=NULL;
unknown's avatar
unknown committed
451
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
unknown's avatar
unknown committed
452 453 454 455 456 457
set autocommit=1;
set big_tables=1;
select @@autocommit, @@big_tables;
@@autocommit	@@big_tables
1	1
set global binlog_cache_size=100;
458 459
Warnings:
Warning	1292	Truncated incorrect binlog_cache_size value: '100'
unknown's avatar
unknown committed
460
set bulk_insert_buffer_size=100;
unknown's avatar
unknown committed
461 462
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
463 464
set @@global.concurrent_insert=1;
set global connect_timeout=100;
465 466 467 468 469 470 471 472 473 474 475
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
476
set global delay_key_write=1;
477 478 479
select @@delay_key_write;
@@delay_key_write
ON
unknown's avatar
unknown committed
480 481 482 483 484 485 486 487
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;
488 489
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '100'
unknown's avatar
unknown committed
490 491
set last_insert_id=1;
set global local_infile=1;
492 493 494 495 496 497 498 499
set long_query_time=0.000001;
select @@long_query_time;
@@long_query_time
0.000001
set long_query_time=100.000001;
select @@long_query_time;
@@long_query_time
100.000001
unknown's avatar
unknown committed
500
set low_priority_updates=1;
501
set global max_allowed_packet=100;
502 503
Warnings:
Warning	1292	Truncated incorrect max_allowed_packet value: '100'
unknown's avatar
unknown committed
504
set global max_binlog_cache_size=100;
505 506
Warnings:
Warning	1292	Truncated incorrect max_binlog_cache_size value: '100'
unknown's avatar
unknown committed
507
set global max_binlog_size=100;
508 509
Warnings:
Warning	1292	Truncated incorrect max_binlog_size value: '100'
unknown's avatar
unknown committed
510 511 512 513
set global max_connect_errors=100;
set global max_connections=100;
set global max_delayed_threads=100;
set max_heap_table_size=100;
514 515
Warnings:
Warning	1292	Truncated incorrect max_heap_table_size value: '100'
unknown's avatar
unknown committed
516 517 518 519 520 521 522 523 524
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;
525
set global net_buffer_length=100;
526 527
Warnings:
Warning	1292	Truncated incorrect net_buffer_length value: '100'
unknown's avatar
unknown committed
528 529 530 531 532 533
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;
534 535
Warnings:
Warning	1292	Truncated incorrect read_buffer_size value: '100'
unknown's avatar
unknown committed
536
set read_rnd_buffer_size=100;
537 538
Warnings:
Warning	1292	Truncated incorrect read_rnd_buffer_size value: '100'
unknown's avatar
unknown committed
539 540 541 542
set global rpl_recovery_rank=100;
set global server_id=100;
set global slow_launch_time=100;
set sort_buffer_size=100;
543 544
Warnings:
Warning	1292	Truncated incorrect sort_buffer_size value: '100'
545 546 547 548 549 550 551 552
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
553 554 555 556 557 558 559 560 561 562 563 564 565 566
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
567
Warnings:
568
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
569 570 571 572 573 574 575 576
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;
577
set sql_select_limit=default;
unknown's avatar
unknown committed
578
set sql_warnings=1;
unknown's avatar
unknown committed
579
set global table_open_cache=100;
unknown's avatar
unknown committed
580
set storage_engine=myisam;
unknown's avatar
unknown committed
581 582 583
set global thread_cache_size=100;
set timestamp=1, timestamp=default;
set tmp_table_size=100;
584 585
Warnings:
Warning	1292	Truncated incorrect tmp_table_size value: '100'
unknown's avatar
unknown committed
586 587 588
set tx_isolation="READ-COMMITTED";
set wait_timeout=100;
set log_warnings=1;
589
set global log_warnings=1;
590 591 592 593 594 595 596 597
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
598 599 600 601 602 603 604 605 606 607
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
608 609 610 611
set @@session.insert_id=@save_insert_id;
select @@session.insert_id;
@@session.insert_id
1
unknown's avatar
unknown committed
612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629
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
630
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
631
drop table t1,t2;
632
select @@xxxxxxxxxx;
633
ERROR HY000: Unknown system variable 'xxxxxxxxxx'
634 635 636
select 1;
1
1
637
select @@session.key_buffer_size;
unknown's avatar
unknown committed
638
ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable
639 640
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
641 642 643 644 645 646 647 648 649
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
650 651 652
set global myisam_max_sort_file_size=4294967296;
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
653
myisam_max_sort_file_size	MAX_FILE_SIZE
654 655 656
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
657
set global myisam_max_sort_file_size=default;
658
select @@global.max_user_connections,@@local.max_join_size;
659
@@global.max_user_connections	@@local.max_join_size
660 661 662
100	200
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
select @@global.max_user_connections,@@local.max_join_size;
663
@@global.max_user_connections	@@local.max_join_size
664 665 666
100	200
set @@global.max_user_connections=111,@@local.max_join_size=222;
select @@global.max_user_connections,@@local.max_join_size;
667
@@global.max_user_connections	@@local.max_join_size
668 669 670
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;
671
@@global.max_user_connections	@@local.max_join_size
672 673 674
222	111
set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
select @@global.max_user_connections,@@local.max_join_size;
675
@@global.max_user_connections	@@local.max_join_size
676 677 678 679 680 681
100	200
set @a=1, @b=2;
set @a=@b, @b=@a;
select @a, @b;
@a	@b
2	1
682
set @@global.global.key_buffer_size= 1;
683
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
684
set GLOBAL global.key_buffer_size= 1;
685
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
686
SELECT @@global.global.key_buffer_size;
687
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
688
SELECT @@global.session.key_buffer_size;
689
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
690
SELECT @@global.local.key_buffer_size;
691
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
692 693 694 695
set @tstlw = @@log_warnings;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
696 697 698
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	1
699 700 701 702
set global log_warnings = 0;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	0
703 704 705
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	0
706 707 708 709
set global log_warnings = 42;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	42
710 711 712
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	42
713 714 715 716
set global log_warnings = @tstlw;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
717 718 719
select * from information_schema.global_variables where variable_name like 'log_warnings';
VARIABLE_NAME	VARIABLE_VALUE
LOG_WARNINGS	1
720 721 722 723 724 725 726 727 728
create table t1 (
c1 tinyint,
c2 smallint,
c3 mediumint,
c4 int,
c5 bigint);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
729 730 731 732 733
  `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
734 735
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
unknown's avatar
unknown committed
736 737
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;
738 739 740
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
741 742
  `c1` bigint(20) DEFAULT NULL,
  `c2` decimal(65,30) DEFAULT NULL,
unknown's avatar
unknown committed
743
  `c3` longtext,
744
  `c4` double DEFAULT NULL
745 746
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
747
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
748 749
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
Variable_name	Value
750
myisam_data_pointer_size	7
751 752 753
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
754
SET GLOBAL table_open_cache=-1;
755
Warnings:
756
Warning	1292	Truncated incorrect table_open_cache value: '-1'
unknown's avatar
unknown committed
757
SHOW VARIABLES LIKE 'table_open_cache';
unknown's avatar
unknown committed
758
Variable_name	Value
unknown's avatar
unknown committed
759
table_open_cache	1
760 761 762
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
763
SET GLOBAL table_open_cache=DEFAULT;
unknown's avatar
unknown committed
764 765 766 767 768
set character_set_results=NULL;
select ifnull(@@character_set_results,"really null");
ifnull(@@character_set_results,"really null")
really null
set names latin1;
769 770 771
select @@have_innodb;
@@have_innodb
#
772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828
*** 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
829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859
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
860 861 862 863 864
set @test = @@query_prealloc_size;
set @@query_prealloc_size = @test;
select @@query_prealloc_size = @test;
@@query_prealloc_size = @test
1
865
set global sql_mode=repeat('a',80);
866
ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
867
End of 4.1 tests
868 869 870
create table t1 (a int);
select a into @x from t1;
Warnings:
871
Warning	1329	No data - zero rows fetched, selected, or processed
872 873
show warnings;
Level	Code	Message
874
Warning	1329	No data - zero rows fetched, selected, or processed
875
drop table t1;
876 877 878 879
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
880
set @@max_heap_table_size= 4294967296;
881 882 883
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
884
set global max_heap_table_size= 4294967296;
885 886 887
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
888
set @@max_heap_table_size= 4294967296;
889 890 891
select @@max_heap_table_size > 0;
@@max_heap_table_size > 0
1
892 893 894 895 896 897 898
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
899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916
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
917 918 919 920 921
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
922 923 924
select * from information_schema.session_variables where variable_name like 'sql_big_selects';
VARIABLE_NAME	VARIABLE_VALUE
SQL_BIG_SELECTS	ON
925
set @@sql_big_selects = @old_sql_big_selects;
926 927 928 929
set @@sql_notes = 0, @@sql_warnings = 0;
show variables like 'sql_notes';
Variable_name	Value
sql_notes	OFF
930 931 932
select * from information_schema.session_variables where variable_name like 'sql_notes';
VARIABLE_NAME	VARIABLE_VALUE
SQL_NOTES	OFF
933 934 935
show variables like 'sql_warnings';
Variable_name	Value
sql_warnings	OFF
936 937 938
select * from information_schema.session_variables where variable_name like 'sql_warnings';
VARIABLE_NAME	VARIABLE_VALUE
SQL_WARNINGS	OFF
939 940 941 942
set @@sql_notes = 1, @@sql_warnings = 1;
show variables like 'sql_notes';
Variable_name	Value
sql_notes	ON
943 944 945
select * from information_schema.session_variables where variable_name like 'sql_notes';
VARIABLE_NAME	VARIABLE_VALUE
SQL_NOTES	ON
946 947 948
show variables like 'sql_warnings';
Variable_name	Value
sql_warnings	ON
949 950 951
select * from information_schema.session_variables where variable_name like 'sql_warnings';
VARIABLE_NAME	VARIABLE_VALUE
SQL_WARNINGS	ON
952 953 954 955 956 957 958
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
#	#	#	#
959 960 961 962 963 964
select @@basedir, @@datadir, @@tmpdir;
@@basedir	@@datadir	@@tmpdir
#	#	#
show variables like 'basedir';
Variable_name	Value
basedir	#
965 966 967
select * from information_schema.session_variables where variable_name like 'basedir';
VARIABLE_NAME	VARIABLE_VALUE
BASEDIR	#
968 969 970
show variables like 'datadir';
Variable_name	Value
datadir	#
971 972 973
select * from information_schema.session_variables where variable_name like 'datadir';
VARIABLE_NAME	VARIABLE_VALUE
DATADIR	#
974 975 976
show variables like 'tmpdir';
Variable_name	Value
tmpdir	#
977 978 979
select * from information_schema.session_variables where variable_name like 'tmpdir';
VARIABLE_NAME	VARIABLE_VALUE
TMPDIR	#
980 981 982 983 984 985 986 987 988 989
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	#
unknown's avatar
unknown committed
990
select * from information_schema.session_variables where variable_name like 'ssl%' order by 1;
991 992 993 994 995 996
VARIABLE_NAME	VARIABLE_VALUE
SSL_CA	#
SSL_CAPATH	#
SSL_CERT	#
SSL_CIPHER	#
SSL_KEY	#
997 998 999 1000 1001 1002
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
1003 1004 1005
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
1006 1007 1008 1009 1010 1011
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
1012 1013 1014 1015 1016 1017 1018 1019
select @@hostname;
@@hostname
#
set @@hostname= "anothername";
ERROR HY000: Variable 'hostname' is a read only variable
show variables like 'hostname';
Variable_name	Value
hostname	#
1020
End of 5.0 tests
1021 1022 1023 1024 1025 1026 1027
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;
1028
set global max_binlog_cache_size     =@my_max_binlog_cache_size;
1029 1030
set global max_binlog_size           =@my_max_binlog_size;
set global max_connect_errors        =@my_max_connect_errors;
1031
set global max_connections           =@my_max_connections;
1032 1033 1034 1035
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;
1036 1037
set global max_user_connections      =default;
set global max_write_lock_count      =default;
1038
set global myisam_data_pointer_size  =@my_myisam_data_pointer_size;
1039
set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
1040 1041 1042 1043 1044 1045 1046 1047 1048 1049
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;
1050
set global max_allowed_packet        =@my_max_allowed_packet;
1051 1052 1053 1054 1055
show global variables where Variable_name='table_definition_cache' or
Variable_name='table_lock_wait_timeout';
Variable_name	Value
table_definition_cache	#
table_lock_wait_timeout	#
1056 1057 1058 1059 1060 1061 1062 1063 1064 1065

# --
# -- Bug#34820: log_output can be set to illegal value.
# --
SET GLOBAL log_output = '';
ERROR 42000: Variable 'log_output' can't be set to the value of ''
SET GLOBAL log_output = 0;
ERROR 42000: Variable 'log_output' can't be set to the value of '0'

# -- End of Bug#34820.
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 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 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 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

#
SHOW VARIABLES like 'ft_max_word_len';
Variable_name	Value
ft_max_word_len	84
SELECT @@session.ft_max_word_len;
ERROR HY000: Variable 'ft_max_word_len' is a GLOBAL variable
SELECT @@global.ft_max_word_len;
@@global.ft_max_word_len
84
SET @@session.ft_max_word_len= 7;
ERROR HY000: Variable 'ft_max_word_len' is a read only variable
SET @@global.ft_max_word_len= 7;
ERROR HY000: Variable 'ft_max_word_len' is a read only variable
#
SHOW VARIABLES like 'ft_min_word_len';
Variable_name	Value
ft_min_word_len	4
SELECT @@session.ft_min_word_len;
ERROR HY000: Variable 'ft_min_word_len' is a GLOBAL variable
SELECT @@global.ft_min_word_len;
@@global.ft_min_word_len
4
SET @@session.ft_min_word_len= 7;
ERROR HY000: Variable 'ft_min_word_len' is a read only variable
SET @@global.ft_min_word_len= 7;
ERROR HY000: Variable 'ft_min_word_len' is a read only variable
#
SHOW VARIABLES like 'ft_query_expansion_limit';
Variable_name	Value
ft_query_expansion_limit	20
SELECT @@session.ft_query_expansion_limit;
ERROR HY000: Variable 'ft_query_expansion_limit' is a GLOBAL variable
SELECT @@global.ft_query_expansion_limit;
@@global.ft_query_expansion_limit
20
SET @@session.ft_query_expansion_limit= 7;
ERROR HY000: Variable 'ft_query_expansion_limit' is a read only variable
SET @@global.ft_query_expansion_limit= 7;
ERROR HY000: Variable 'ft_query_expansion_limit' is a read only variable
#
SHOW VARIABLES like 'ft_stopword_file';
Variable_name	Value
ft_stopword_file	(built-in)
SELECT @@session.ft_stopword_file;
ERROR HY000: Variable 'ft_stopword_file' is a GLOBAL variable
SELECT @@global.ft_stopword_file;
@@global.ft_stopword_file
(built-in)
SET @@session.ft_stopword_file= 'x';
ERROR HY000: Variable 'ft_stopword_file' is a read only variable
SET @@global.ft_stopword_file= 'x';
ERROR HY000: Variable 'ft_stopword_file' is a read only variable
#
SHOW VARIABLES like 'back_log';
Variable_name	Value
back_log	50
SELECT @@session.back_log;
ERROR HY000: Variable 'back_log' is a GLOBAL variable
SELECT @@global.back_log;
@@global.back_log
50
SET @@session.back_log= 7;
ERROR HY000: Variable 'back_log' is a read only variable
SET @@global.back_log= 7;
ERROR HY000: Variable 'back_log' is a read only variable
#
SHOW VARIABLES like 'large_files_support';
Variable_name	Value
large_files_support	#
SELECT @@session.large_files_support;
ERROR HY000: Variable 'large_files_support' is a GLOBAL variable
SELECT @@global.large_files_support;
@@global.large_files_support
#
SET @@session.large_files_support= true;
ERROR HY000: Variable 'large_files_support' is a read only variable
SET @@global.large_files_support= true;
ERROR HY000: Variable 'large_files_support' is a read only variable
#
SHOW VARIABLES like 'character_sets_dir';
Variable_name	Value
character_sets_dir	#
SELECT @@session.character_sets_dir;
ERROR HY000: Variable 'character_sets_dir' is a GLOBAL variable
SELECT @@global.character_sets_dir;
@@global.character_sets_dir
#
SET @@session.character_sets_dir= 'x';
ERROR HY000: Variable 'character_sets_dir' is a read only variable
SET @@global.character_sets_dir= 'x';
ERROR HY000: Variable 'character_sets_dir' is a read only variable
#
SHOW VARIABLES like 'init_file';
Variable_name	Value
init_file	#
SELECT @@session.init_file;
ERROR HY000: Variable 'init_file' is a GLOBAL variable
SELECT @@global.init_file;
@@global.init_file
#
SET @@session.init_file= 'x';
ERROR HY000: Variable 'init_file' is a read only variable
SET @@global.init_file= 'x';
ERROR HY000: Variable 'init_file' is a read only variable
#
SHOW VARIABLES like 'language';
Variable_name	Value
language	#
SELECT @@session.language;
ERROR HY000: Variable 'language' is a GLOBAL variable
SELECT @@global.language;
@@global.language
#
SET @@session.language= 'x';
ERROR HY000: Variable 'language' is a read only variable
SET @@global.language= 'x';
ERROR HY000: Variable 'language' is a read only variable
#
SHOW VARIABLES like 'large_page_size';
Variable_name	Value
large_page_size	#
SELECT @@session.large_page_size;
ERROR HY000: Variable 'large_page_size' is a GLOBAL variable
SELECT @@global.large_page_size;
@@global.large_page_size
#
SET @@session.large_page_size= 7;
ERROR HY000: Variable 'large_page_size' is a read only variable
SET @@global.large_page_size= 7;
ERROR HY000: Variable 'large_page_size' is a read only variable
#
SHOW VARIABLES like 'large_pages';
Variable_name	Value
large_pages	#
SELECT @@session.large_pages;
ERROR HY000: Variable 'large_pages' is a GLOBAL variable
SELECT @@global.large_pages;
@@global.large_pages
#
SET @@session.large_pages= true;
ERROR HY000: Variable 'large_pages' is a read only variable
SET @@global.large_pages= true;
ERROR HY000: Variable 'large_pages' is a read only variable
#
SHOW VARIABLES like 'log_bin';
Variable_name	Value
log_bin	OFF
SELECT @@session.log_bin;
ERROR HY000: Variable 'log_bin' is a GLOBAL variable
SELECT @@global.log_bin;
@@global.log_bin
0
SET @@session.log_bin= true;
ERROR HY000: Variable 'log_bin' is a read only variable
SET @@global.log_bin= true;
ERROR HY000: Variable 'log_bin' is a read only variable
#
SHOW VARIABLES like 'log_error';
Variable_name	Value
log_error	#
SELECT @@session.log_error;
ERROR HY000: Variable 'log_error' is a GLOBAL variable
SELECT @@global.log_error;
@@global.log_error
#
SET @@session.log_error= 'x';
ERROR HY000: Variable 'log_error' is a read only variable
SET @@global.log_error= 'x';
ERROR HY000: Variable 'log_error' is a read only variable
#
SHOW VARIABLES like 'lower_case_file_system';
Variable_name	Value
lower_case_file_system	#
SELECT @@session.lower_case_file_system;
ERROR HY000: Variable 'lower_case_file_system' is a GLOBAL variable
SELECT @@global.lower_case_file_system;
@@global.lower_case_file_system
#
SET @@session.lower_case_file_system= true;
ERROR HY000: Variable 'lower_case_file_system' is a read only variable
SET @@global.lower_case_file_system= true;
ERROR HY000: Variable 'lower_case_file_system' is a read only variable
#
SHOW VARIABLES like 'lower_case_table_names';
Variable_name	Value
lower_case_table_names	#
SELECT @@session.lower_case_table_names;
ERROR HY000: Variable 'lower_case_table_names' is a GLOBAL variable
SELECT @@global.lower_case_table_names;
@@global.lower_case_table_names
#
SET @@session.lower_case_table_names= 7;
ERROR HY000: Variable 'lower_case_table_names' is a read only variable
SET @@global.lower_case_table_names= 7;
ERROR HY000: Variable 'lower_case_table_names' is a read only variable
#
SHOW VARIABLES like 'myisam_recover_options';
Variable_name	Value
myisam_recover_options	OFF
SELECT @@session.myisam_recover_options;
ERROR HY000: Variable 'myisam_recover_options' is a GLOBAL variable
SELECT @@global.myisam_recover_options;
@@global.myisam_recover_options
OFF
SET @@session.myisam_recover_options= 'x';
ERROR HY000: Variable 'myisam_recover_options' is a read only variable
SET @@global.myisam_recover_options= 'x';
ERROR HY000: Variable 'myisam_recover_options' is a read only variable
#
SHOW VARIABLES like 'open_files_limit';
Variable_name	Value
open_files_limit	#
SELECT @@session.open_files_limit;
ERROR HY000: Variable 'open_files_limit' is a GLOBAL variable
SELECT @@global.open_files_limit;
@@global.open_files_limit
#
SET @@session.open_files_limit= 7;
ERROR HY000: Variable 'open_files_limit' is a read only variable
SET @@global.open_files_limit= 7;
ERROR HY000: Variable 'open_files_limit' is a read only variable
#
SHOW VARIABLES like 'pid_file';
Variable_name	Value
pid_file	#
SELECT @@session.pid_file;
ERROR HY000: Variable 'pid_file' is a GLOBAL variable
SELECT @@global.pid_file;
@@global.pid_file
#
SET @@session.pid_file= 'x';
ERROR HY000: Variable 'pid_file' is a read only variable
SET @@global.pid_file= 'x';
ERROR HY000: Variable 'pid_file' is a read only variable
#
SHOW VARIABLES like 'plugin_dir';
Variable_name	Value
plugin_dir	#
SELECT @@session.plugin_dir;
ERROR HY000: Variable 'plugin_dir' is a GLOBAL variable
SELECT @@global.plugin_dir;
@@global.plugin_dir
#
SET @@session.plugin_dir= 'x';
ERROR HY000: Variable 'plugin_dir' is a read only variable
SET @@global.plugin_dir= 'x';
ERROR HY000: Variable 'plugin_dir' is a read only variable
#
SHOW VARIABLES like 'port';
Variable_name	Value
port	#
SELECT @@session.port;
ERROR HY000: Variable 'port' is a GLOBAL variable
SELECT @@global.port;
@@global.port
#
SET @@session.port= 7;
ERROR HY000: Variable 'port' is a read only variable
SET @@global.port= 7;
ERROR HY000: Variable 'port' is a read only variable
#
SHOW VARIABLES like 'protocol_version';
Variable_name	Value
protocol_version	10
SELECT @@session.protocol_version;
ERROR HY000: Variable 'protocol_version' is a GLOBAL variable
SELECT @@global.protocol_version;
@@global.protocol_version
10
SET @@session.protocol_version= 7;
ERROR HY000: Variable 'protocol_version' is a read only variable
SET @@global.protocol_version= 7;
ERROR HY000: Variable 'protocol_version' is a read only variable
#
SHOW VARIABLES like 'skip_external_locking';
Variable_name	Value
skip_external_locking	ON
SELECT @@session.skip_external_locking;
ERROR HY000: Variable 'skip_external_locking' is a GLOBAL variable
SELECT @@global.skip_external_locking;
@@global.skip_external_locking
1
SET @@session.skip_external_locking= true;
ERROR HY000: Variable 'skip_external_locking' is a read only variable
SET @@global.skip_external_locking= true;
ERROR HY000: Variable 'skip_external_locking' is a read only variable
#
SHOW VARIABLES like 'skip_networking';
Variable_name	Value
skip_networking	OFF
SELECT @@session.skip_networking;
ERROR HY000: Variable 'skip_networking' is a GLOBAL variable
SELECT @@global.skip_networking;
@@global.skip_networking
0
SET @@session.skip_networking= true;
ERROR HY000: Variable 'skip_networking' is a read only variable
SET @@global.skip_networking= true;
ERROR HY000: Variable 'skip_networking' is a read only variable
#
SHOW VARIABLES like 'skip_show_database';
Variable_name	Value
skip_show_database	OFF
SELECT @@session.skip_show_database;
ERROR HY000: Variable 'skip_show_database' is a GLOBAL variable
SELECT @@global.skip_show_database;
@@global.skip_show_database
0
SET @@session.skip_show_database= true;
ERROR HY000: Variable 'skip_show_database' is a read only variable
SET @@global.skip_show_database= true;
ERROR HY000: Variable 'skip_show_database' is a read only variable
#
SHOW VARIABLES like 'thread_stack';
Variable_name	Value
thread_stack	#
SELECT @@session.thread_stack;
ERROR HY000: Variable 'thread_stack' is a GLOBAL variable
SELECT @@global.thread_stack;
@@global.thread_stack
#
SET @@session.thread_stack= 7;
ERROR HY000: Variable 'thread_stack' is a read only variable
SET @@global.thread_stack= 7;
ERROR HY000: Variable 'thread_stack' is a read only variable
1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423
SELECT @@global.expire_logs_days INTO @old_eld;
SET GLOBAL expire_logs_days = -1;
Warnings:
Warning	1292	Truncated incorrect expire_logs_days value: '-1'
needs to've been adjusted (0)
SELECT @@global.expire_logs_days;
@@global.expire_logs_days
0
SET GLOBAL expire_logs_days = 11;
SET @old_mode=@@sql_mode;
SET SESSION sql_mode = 'TRADITIONAL';
SET GLOBAL expire_logs_days = 100;
ERROR 42000: Variable 'expire_logs_days' can't be set to the value of '100'
needs to be unchanged (11)
SELECT @@global.expire_logs_days;
@@global.expire_logs_days
11
SET SESSION sql_mode = @old_mode;
SET GLOBAL expire_logs_days = 100;
Warnings:
Warning	1292	Truncated incorrect expire_logs_days value: '100'
needs to've been adjusted (99)
SELECT @@global.expire_logs_days;
@@global.expire_logs_days
99
SET GLOBAL expire_logs_days = 11;
SET GLOBAL expire_logs_days = 99;
needs to pass with no warnings (99)
SELECT @@global.expire_logs_days;
@@global.expire_logs_days
99
SET GLOBAL expire_logs_days = @old_eld;
1424 1425 1426 1427 1428
select @@storage_engine;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					@@storage_engine	253	6	6	N	1	31	8
@@storage_engine
MyISAM
1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456
SET @old_server_id = @@GLOBAL.server_id;
SET GLOBAL server_id = (1 << 32) - 1;
SELECT @@GLOBAL.server_id;
@@GLOBAL.server_id
4294967295
SET GLOBAL server_id = (1 << 32);
Warnings:
Warning	1292	Truncated incorrect server-id value: '4294967296'
SELECT @@GLOBAL.server_id;
@@GLOBAL.server_id
4294967295
SET GLOBAL server_id = (1 << 60);
Warnings:
Warning	1292	Truncated incorrect server-id value: '1152921504606846976'
SELECT @@GLOBAL.server_id;
@@GLOBAL.server_id
4294967295
SET GLOBAL server_id = 0;
SELECT @@GLOBAL.server_id;
@@GLOBAL.server_id
0
SET GLOBAL server_id = -1;
Warnings:
Warning	1292	Truncated incorrect server_id value: '-1'
SELECT @@GLOBAL.server_id;
@@GLOBAL.server_id
0
SET GLOBAL server_id = @old_server_id;
1457
End of 5.1 tests