variables.test 7.65 KB
Newer Older
1 2 3
#
# test variables
#
4 5 6 7
--disable_warnings
drop table if exists t1,t2;
--enable_warnings

8 9 10 11 12 13 14 15 16 17 18 19 20 21
# case insensitivity tests (new in 5.0)
set @`test`=1;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
set @TEST=2;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
set @"tEST"=3;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
set @`TeST`=4;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
select @`teST`:=5;
select @test, @`test`, @TEST, @`TEST`, @"teSt";

set @select=2,@t5=1.23456;
select @`select`,@not_used;
22 23 24 25 26 27 28 29 30
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;
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
31 32
select @t5;

unknown's avatar
unknown committed
33 34 35 36 37 38 39 40 41 42 43 44
#
# Test problem with WHERE and variables
#

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;
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
drop table t1;
45 46 47 48 49

#
# Test system variables
#

unknown's avatar
unknown committed
50 51
set max_join_size=100;
show variables like 'max_join_size';
52
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
53 54 55 56
show global variables like 'max_join_size';
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
set max_join_size=DEFAULT;
57
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
58 59
show variables like 'max_join_size';
set GLOBAL max_join_size=DEFAULT;
60
--replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
unknown's avatar
unknown committed
61 62 63 64
show global variables like 'max_join_size';
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
select @@identity,  length(@@version)>0;
65 66
select @@VERSION=version();
select last_insert_id(345);
unknown's avatar
unknown committed
67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
select @@IDENTITY,last_insert_id(), @@identity;

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';
set global concurrent_insert=1;
show variables like 'concurrent_insert';
set global concurrent_insert=0;
show variables like 'concurrent_insert';
set global concurrent_insert=OFF;
show variables like 'concurrent_insert';
set global concurrent_insert=DEFAULT;
show variables like 'concurrent_insert';

set table_type=MYISAM, table_type="HEAP", global table_type="INNODB";
show local variables like 'table_type';
show global variables like 'table_type';
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';
show global variables like 'myisam_max_sort_file_size';
set myisam_max_sort_file_size=default;
show variables like 'myisam_max_sort_file_size';

93
set global net_retry_count=10, session net_retry_count=10;
unknown's avatar
unknown committed
94 95 96 97 98 99 100 101 102 103 104 105 106
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_%';
show session variables like 'net_%';
set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
show global variables like 'net_%';
show session variables like 'net_%';
set net_buffer_length=1;
show variables like 'net_buffer_length';
set net_buffer_length=2000000000;
show variables like 'net_buffer_length';

set character set cp1251_koi8;
unknown's avatar
unknown committed
107
show variables like "collation_client";
unknown's avatar
unknown committed
108 109
select @@timestamp>0;

110 111 112 113
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);


unknown's avatar
unknown committed
114 115 116 117 118 119
# The following should give errors

--error 1231
set big_tables=OFFF;
--error 1231
set big_tables="OFFF";
120
--error 1193
unknown's avatar
unknown committed
121 122 123 124 125 126 127 128 129 130 131 132 133
set unknown_variable=1;
--error 1232
set max_join_size="hello";
--error 1231
set table_type=UNKNOWN_TABLE_TYPE;
--error 1231
set table_type=INNODB, big_tables=2;
show local variables like 'table_type';
--error 1229
set SESSION query_cache_size=10000;
--error 1230
set GLOBAL table_type=DEFAULT;
--error 1115
unknown's avatar
unknown committed
134
set collation_client=UNKNOWN_CHARACTER_SET;
unknown's avatar
unknown committed
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154
--error 1228
set global autocommit=1;
--error 1228
select @@global.timestamp;
--error 1193
set @@version='';
--error 1229
set @@concurrent_insert=1;
--error 1228
set @@global.sql_auto_is_null=1;
--error 1228
select @@global.sql_auto_is_null;

# Test setting all variables

set autocommit=1;
set big_tables=1;
select @@autocommit, @@big_tables;
set global binlog_cache_size=100;
set bulk_insert_buffer_size=100;
unknown's avatar
unknown committed
155 156
set character set cp1251_koi8;
set character set default;
unknown's avatar
unknown committed
157 158
set @@global.concurrent_insert=1;
set global connect_timeout=100;
159 160 161 162 163
select @@delay_key_write;
set global delay_key_write="OFF";
select @@delay_key_write;
set global delay_key_write=ALL;
select @@delay_key_write;
unknown's avatar
unknown committed
164
set global delay_key_write=1;
165
select @@delay_key_write;
unknown's avatar
unknown committed
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 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 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247
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;
set global max_write_lock_count=100;
set myisam_max_extra_sort_file_size=100;
select @@myisam_max_extra_sort_file_size;
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;
set @@sql_auto_is_null=0;
select @@sql_auto_is_null;
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;
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;

#
# key buffer
#

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;
select * from t1 where a=2;
select * from t2 where a=3;
check table t1,t2;
248
select max(a) +1, max(a) +2 into @xx,@yy from t1;
unknown's avatar
unknown committed
249 250
drop table t1,t2;