variables.result 16.1 KB
Newer Older
1
drop table if exists t1,t2;
2 3
set @`test`=1,@TEST=3,@select=2,@t5=1.23456;
select @test,@`select`,@TEST,@not_used;
4 5
@test	@`select`	@TEST	@not_used
1	2	3	NULL
6 7
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;
8
@test_int	@test_double	@test_string	@test_string2	@select
9
10	1e-10	abcdeghi	abcdefghij	NULL
10 11
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
12 13
@test_int	@test_double	@test_string	@test_string2
hello	hello	hello	hello
14 15
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
16 17
@test_int	@test_double	@test_string	@test_string2
hellohello	hellohello	hellohello	hellohello
18 19
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
20 21
@test_int	@test_double	@test_string	@test_string2
NULL	NULL	NULL	NULL
22
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
23 24
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
25 26 27 28
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
29
Note	1003	select sql_no_cache (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
30
select @t5;
31 32
@t5
1.23456
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
33 34 35
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;
36 37
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
38
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
39 40 41
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
42
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
43 44 45
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
46 47
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
48 49 50
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
51
drop table t1;
52 53 54 55 56 57
set max_join_size=100;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	100
show global variables like 'max_join_size';
Variable_name	Value
58
max_join_size	10
59 60 61 62 63 64 65 66 67 68 69
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
set max_join_size=DEFAULT;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
set GLOBAL max_join_size=DEFAULT;
show global variables like 'max_join_size';
Variable_name	Value
70
max_join_size	HA_POS_ERROR
71 72
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
73
@@session.max_join_size	@@global.max_join_size
74 75 76 77
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
78 79 80 81 82 83
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
84 85 86 87
explain extended select last_insert_id(345);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
88
Note	1003	select sql_no_cache last_insert_id(345) AS `last_insert_id(345)`
89
select @@IDENTITY,last_insert_id(), @@identity;
90
@@IDENTITY	last_insert_id()	@@identity
91
345	345	345
92 93 94 95
explain extended select @@IDENTITY,last_insert_id(), @@identity;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
96
Note	1003	select sql_no_cache 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
set global concurrent_insert=ON;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	ON
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	ON
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	OFF
set global concurrent_insert=OFF;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	OFF
set global concurrent_insert=DEFAULT;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	ON
118 119
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="INNODB";
show local variables like 'storage_engine';
120
Variable_name	Value
121 122
storage_engine	HEAP
show global variables like 'storage_engine';
123
Variable_name	Value
124
storage_engine	InnoDB
125
set GLOBAL query_cache_size=100000;
126
set GLOBAL myisam_max_sort_file_size=2000000;
127 128
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
129 130
myisam_max_sort_file_size	1048576
set GLOBAL myisam_max_sort_file_size=default;
131 132
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
133
myisam_max_sort_file_size	FILE_SIZE
134
set global net_retry_count=10, session net_retry_count=10;
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
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
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	2048
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
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
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	7168
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
set net_buffer_length=1;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1024
set net_buffer_length=2000000000;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1048576
set character set cp1251_koi8;
171
show variables like "character_set_client";
172
Variable_name	Value
173
character_set_client	cp1251
174 175 176
select @@timestamp>0;
@@timestamp>0
1
177 178 179 180
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212
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
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
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
213
set big_tables=OFFF;
214
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
215
set big_tables="OFFF";
216
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
217
set unknown_variable=1;
218
ERROR HY000: Unknown system variable 'unknown_variable'
219
set max_join_size="hello";
220
ERROR 42000: Incorrect argument type to variable 'max_join_size'
221
set storage_engine=UNKNOWN_TABLE_TYPE;
222
ERROR 42000: Unknown table engine 'UNKNOWN_TABLE_TYPE'
223
set storage_engine=INNODB, big_tables=2;
224
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
225
show local variables like 'storage_engine';
226
Variable_name	Value
227
storage_engine	HEAP
228
set SESSION query_cache_size=10000;
229
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
230 231
set GLOBAL storage_engine=DEFAULT;
ERROR 42000: Variable 'storage_engine' doesn't have a default value
232
set character_set_client=UNKNOWN_CHARACTER_SET;
233
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
234 235
set collation_connection=UNKNOWN_COLLATION;
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
236 237 238 239
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'
240
set global autocommit=1;
241
ERROR HY000: Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
242
select @@global.timestamp;
243
ERROR HY000: Variable 'timestamp' is a SESSION variable
244
set @@version='';
245
ERROR HY000: Unknown system variable 'version'
246
set @@concurrent_insert=1;
247
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
248
set @@global.sql_auto_is_null=1;
249
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable and can't be used with SET GLOBAL
250
select @@global.sql_auto_is_null;
251
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
252
set myisam_max_sort_file_size=100;
253
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
254
set myisam_max_extra_sort_file_size=100;
255
ERROR HY000: Variable 'myisam_max_extra_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
256
set @@SQL_WARNINGS=NULL;
257
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
258 259 260 261 262 263 264
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;
265 266
set character set cp1251_koi8;
set character set default;
267 268
set @@global.concurrent_insert=1;
set global connect_timeout=100;
269 270 271 272 273 274 275 276 277 278 279
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
280
set global delay_key_write=1;
281 282 283
select @@delay_key_write;
@@delay_key_write
ON
284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310
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;
311
set global myisam_max_extra_sort_file_size=100;
312 313 314
select @@myisam_max_extra_sort_file_size;
@@myisam_max_extra_sort_file_size
100
315
set global myisam_max_sort_file_size=100;
316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354
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 slave_net_timeout=100;
set global slow_launch_time=100;
set sort_buffer_size=100;
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;
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;
set global sql_slave_skip_counter=100;
set sql_warnings=1;
set global table_cache=100;
355
set storage_engine=myisam;
356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
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;
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
380
select max(a) +1, max(a) +2 into @xx,@yy from t1;
381
drop table t1,t2;
382
select @@xxxxxxxxxx;
383
ERROR HY000: Unknown system variable 'xxxxxxxxxx'
384 385 386
select 1;
1
1
387
select @@session.key_buffer_size;
388
ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable
389 390
set ft_boolean_syntax = @@init_connect;
ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
serg@serg.mylan's avatar
serg@serg.mylan committed
391 392 393 394 395 396 397 398 399
set global ft_boolean_syntax = @@init_connect;
ERROR 42000: Variable 'ft_boolean_syntax' can't be set to the value of ''
set init_connect = NULL;
ERROR HY000: Variable 'init_connect' is a GLOBAL variable and should be set with SET GLOBAL
set global init_connect = NULL;
set ft_boolean_syntax = @@init_connect;
ERROR HY000: Variable 'ft_boolean_syntax' is a GLOBAL variable and should be set with SET GLOBAL
set global ft_boolean_syntax = @@init_connect;
ERROR 42000: Variable 'ft_boolean_syntax' can't be set to the value of ''
monty@mysql.com's avatar
monty@mysql.com committed
400 401 402
set global myisam_max_sort_file_size=4294967296;
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
403
myisam_max_sort_file_size	MAX_FILE_SIZE
monty@mysql.com's avatar
monty@mysql.com committed
404
set global myisam_max_sort_file_size=default;
405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
100	200
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
100	200
set @@global.max_user_connections=111,@@local.max_join_size=222;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
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;
@@global.max_user_connections	@@session.max_join_size
222	111
set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
100	200
set @a=1, @b=2;
set @a=@b, @b=@a;
select @a, @b;
@a	@b
2	1
429
set @@global.global.key_buffer_size= 1;
430
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
431
set GLOBAL global.key_buffer_size= 1;
432
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
433
SELECT @@global.global.key_buffer_size;
434
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
435
SELECT @@global.session.key_buffer_size;
436
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
437
SELECT @@global.local.key_buffer_size;
438
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
439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454
set @tstlw = @@log_warnings;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1
set global log_warnings = 0;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	0
set global log_warnings = 42;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	42
set global log_warnings = @tstlw;
show global variables like 'log_warnings';
Variable_name	Value
log_warnings	1