variables.result 10 KB
Newer Older
1
drop table if exists t1,t2;
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
22
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
23 24
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
unknown's avatar
unknown committed
25
select @t5;
26 27
@t5
1.23456
unknown's avatar
unknown committed
28 29 30
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
31 32
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
unknown's avatar
unknown committed
33
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
unknown's avatar
unknown committed
34 35 36
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
37
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
unknown's avatar
unknown committed
38 39 40
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
41 42
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
unknown's avatar
unknown committed
43 44 45
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
unknown's avatar
unknown committed
46
drop table t1;
unknown's avatar
unknown committed
47 48 49 50 51 52
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
53
max_join_size	HA_POS_ERROR
unknown's avatar
unknown committed
54 55 56 57 58 59 60 61 62 63 64
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
65
max_join_size	HA_POS_ERROR
unknown's avatar
unknown committed
66 67
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
unknown's avatar
unknown committed
68
@@session.max_join_size	@@global.max_join_size
unknown's avatar
unknown committed
69 70 71 72
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
unknown's avatar
unknown committed
73 74 75 76 77 78
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
unknown's avatar
unknown committed
79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121
select @@IDENTITY,last_insert_id(), @@identity;
@@identity	last_insert_id()	@@identity
345	345	345
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
set table_type=MYISAM, table_type="HEAP", global table_type="INNODB";
show local variables like 'table_type';
Variable_name	Value
table_type	HEAP
show global variables like 'table_type';
Variable_name	Value
table_type	INNODB
set GLOBAL query_cache_size=100000;
set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000;
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
myisam_max_sort_file_size	10000
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
myisam_max_sort_file_size	20000
set myisam_max_sort_file_size=default;
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
myisam_max_sort_file_size	20000
122
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
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;
unknown's avatar
unknown committed
159
show variables like "collation_client";
unknown's avatar
unknown committed
160
Variable_name	Value
unknown's avatar
unknown committed
161
collation_client	cp1251_bulgarian_ci
unknown's avatar
unknown committed
162 163 164
select @@timestamp>0;
@@timestamp>0
1
165 166 167 168
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
unknown's avatar
unknown committed
169 170 171 172 173
set big_tables=OFFF;
Variable 'big_tables' can't be set to the value of 'OFFF'
set big_tables="OFFF";
Variable 'big_tables' can't be set to the value of 'OFFF'
set unknown_variable=1;
unknown's avatar
unknown committed
174
Unknown system variable 'unknown_variable'
unknown's avatar
unknown committed
175 176 177 178 179 180 181 182 183 184 185 186 187
set max_join_size="hello";
Wrong argument type to variable 'max_join_size'
set table_type=UNKNOWN_TABLE_TYPE;
Variable 'table_type' can't be set to the value of 'UNKNOWN_TABLE_TYPE'
set table_type=INNODB, big_tables=2;
Variable 'big_tables' can't be set to the value of '2'
show local variables like 'table_type';
Variable_name	Value
table_type	HEAP
set SESSION query_cache_size=10000;
Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
set GLOBAL table_type=DEFAULT;
Variable 'table_type' doesn't have a default value
unknown's avatar
unknown committed
188
set collation_client=UNKNOWN_CHARACTER_SET;
unknown's avatar
unknown committed
189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
Unknown character set: 'UNKNOWN_CHARACTER_SET'
set global autocommit=1;
Variable 'autocommit' is a LOCAL variable and can't be used with SET GLOBAL
select @@global.timestamp;
Variable 'timestamp' is a LOCAL variable and can't be used with SET GLOBAL
set @@version='';
Unknown system variable 'version'
set @@concurrent_insert=1;
Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
set @@global.sql_auto_is_null=1;
Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL
select @@global.sql_auto_is_null;
Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL
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
209 210
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
211 212
set @@global.concurrent_insert=1;
set global connect_timeout=100;
213 214 215 216 217 218 219 220 221 222 223
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
224
set global delay_key_write=1;
225 226 227
select @@delay_key_write;
@@delay_key_write
ON
unknown's avatar
unknown committed
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 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 311 312 313 314 315 316 317 318 319 320 321 322 323
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_max_extra_sort_file_size=100;
select @@myisam_max_extra_sort_file_size;
@@myisam_max_extra_sort_file_size
100
set myisam_max_sort_file_size=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 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;
set table_type=myisam;
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
324
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
325
drop table t1,t2;