5 drop table if exists t1,t2;
9 # Bug#19263: variables.test doesn't clean up after itself (I/II -- save)
11 set @my_binlog_cache_size =@@global.binlog_cache_size;
12 set @my_connect_timeout =@@global.connect_timeout;
13 set @my_delayed_insert_timeout =@@global.delayed_insert_timeout;
14 set @my_delayed_queue_size =@@global.delayed_queue_size;
15 set @my_flush =@@global.flush;
16 set @my_flush_time =@@global.flush_time;
17 set @my_key_buffer_size =@@global.key_buffer_size;
18 set @my_max_binlog_cache_size =@@global.max_binlog_cache_size;
19 set @my_max_binlog_size =@@global.max_binlog_size;
20 set @my_max_connect_errors =@@global.max_connect_errors;
21 set @my_max_connections =@@global.max_connections;
22 set @my_max_delayed_threads =@@global.max_delayed_threads;
23 set @my_max_heap_table_size =@@global.max_heap_table_size;
24 set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads;
25 set @my_max_join_size =@@global.max_join_size;
26 set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size;
27 set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
28 set @my_net_buffer_length =@@global.net_buffer_length;
29 set @my_net_write_timeout =@@global.net_write_timeout;
30 set @my_net_read_timeout =@@global.net_read_timeout;
31 set @my_query_cache_limit =@@global.query_cache_limit;
32 set @my_query_cache_type =@@global.query_cache_type;
33 set @my_rpl_recovery_rank =@@global.rpl_recovery_rank;
34 set @my_server_id =@@global.server_id;
35 set @my_slow_launch_time =@@global.slow_launch_time;
36 set @my_storage_engine =@@global.storage_engine;
37 set @my_thread_cache_size =@@global.thread_cache_size;
38 set @my_max_allowed_packet =@@global.max_allowed_packet;
39 set @my_join_buffer_size =@@global.join_buffer_size;
40 # case insensitivity tests (new in 5.0)
42 select @test, @`test`, @TEST, @`TEST`, @"teSt";
44 select @test, @`test`, @TEST, @`TEST`, @"teSt";
46 select @test, @`test`, @TEST, @`TEST`, @"teSt";
48 select @test, @`test`, @TEST, @`TEST`, @"teSt";
50 select @test, @`test`, @TEST, @`TEST`, @"teSt";
52 set @select=2,@t5=1.23456;
53 select @`select`,@not_used;
54 set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
55 # Expected result "1e-10", windows returns "1e-010"
56 --replace_result 1e-010 1e-10
57 select @test_int,@test_double,@test_string,@test_string2,@select;
58 set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
59 select @test_int,@test_double,@test_string,@test_string2;
60 set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
61 select @test_int,@test_double,@test_string,@test_string2;
62 set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
63 select @test_int,@test_double,@test_string,@test_string2;
64 select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
65 explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
69 # Test problem with WHERE and variables
72 CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id));
73 INSERT INTO t1 VALUES (1,'Bozo','USA'),(2,'Ronald','USA'),(3,'Kinko','IRE'),(4,'Mr. Floppy','GB');
74 SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1;
75 SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
76 SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
77 ALTER TABLE t1 DROP PRIMARY KEY;
78 select * from t1 where c_id=@min_cid OR c_id=@max_cid;
82 # Test system variables
84 set GLOBAL max_join_size=10;
85 set max_join_size=100;
86 show variables like 'max_join_size';
87 select * from information_schema.session_variables where variable_name like 'max_join_size';
88 --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
89 show global variables like 'max_join_size';
90 --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
91 select * from information_schema.global_variables where variable_name like 'max_join_size';
92 set GLOBAL max_join_size=2000;
93 show global variables like 'max_join_size';
94 select * from information_schema.global_variables where variable_name like 'max_join_size';
95 set max_join_size=DEFAULT;
96 --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
97 show variables like 'max_join_size';
98 --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
99 select * from information_schema.session_variables where variable_name like 'max_join_size';
100 set GLOBAL max_join_size=DEFAULT;
101 --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
102 show global variables like 'max_join_size';
103 --replace_result 18446744073709551615 HA_POS_ERROR 4294967295 HA_POS_ERROR
104 select * from information_schema.global_variables where variable_name like 'max_join_size';
105 set @@max_join_size=1000, @@global.max_join_size=2000;
106 select @@local.max_join_size, @@global.max_join_size;
107 select @@identity, length(@@version)>0;
108 select @@VERSION=version();
109 select last_insert_id(345);
110 explain extended select last_insert_id(345);
111 select @@IDENTITY,last_insert_id(), @@identity;
112 explain extended select @@IDENTITY,last_insert_id(), @@identity;
114 set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
116 set global concurrent_insert=2;
117 show variables like 'concurrent_insert';
118 select * from information_schema.session_variables where variable_name like 'concurrent_insert';
119 set global concurrent_insert=1;
120 show variables like 'concurrent_insert';
121 select * from information_schema.session_variables where variable_name like 'concurrent_insert';
122 set global concurrent_insert=0;
123 show variables like 'concurrent_insert';
124 select * from information_schema.session_variables where variable_name like 'concurrent_insert';
125 set global concurrent_insert=DEFAULT;
126 select @@concurrent_insert;
128 set global timed_mutexes=ON;
129 show variables like 'timed_mutexes';
130 select * from information_schema.session_variables where variable_name like 'timed_mutexes';
131 set global timed_mutexes=0;
132 show variables like 'timed_mutexes';
133 select * from information_schema.session_variables where variable_name like 'timed_mutexes';
135 set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
136 show local variables like 'storage_engine';
137 select * from information_schema.session_variables where variable_name like 'storage_engine';
138 show global variables like 'storage_engine';
139 select * from information_schema.global_variables where variable_name like 'storage_engine';
140 set GLOBAL query_cache_size=100000;
142 set GLOBAL myisam_max_sort_file_size=2000000;
143 show global variables like 'myisam_max_sort_file_size';
144 select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
145 set GLOBAL myisam_max_sort_file_size=default;
146 --replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE
147 show global variables like 'myisam_max_sort_file_size';
148 --replace_result 9223372036853727232 FILE_SIZE 2146435072 FILE_SIZE
149 select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
151 # bug#22891: modified to take read-only SESSION net_buffer_length into account
152 set global net_retry_count=10, session net_retry_count=10;
153 set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
154 show global variables like 'net_%';
155 select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
156 show session variables like 'net_%';
157 select * from information_schema.session_variables where variable_name like 'net_%' order by 1;
158 set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
159 show global variables like 'net_%';
160 select * from information_schema.global_variables where variable_name like 'net_%' order by 1;
161 set global net_buffer_length=1;
162 show global variables like 'net_buffer_length';
163 select * from information_schema.global_variables where variable_name like 'net_buffer_length';
165 set global net_buffer_length=2000000000;
166 show global variables like 'net_buffer_length';
167 select * from information_schema.global_variables where variable_name like 'net_buffer_length';
169 set character set cp1251_koi8;
170 show variables like "character_set_client";
171 select * from information_schema.session_variables where variable_name like 'character_set_client';
172 select @@timestamp>0;
174 set @@rand_seed1=10000000,@@rand_seed2=1000000;
175 select ROUND(RAND(),5);
179 --echo ==+ Testing %alloc% system variables +==
180 --echo ==+ NOTE: These values *must* be a multiple of 1024 +==
181 --echo ==+ Other values will be rounded down to nearest multiple +==
183 --echo ==+ Show initial values +==
184 SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
185 'query_alloc_block_size', 'query_prealloc_size',
186 'transaction_alloc_block_size', 'transaction_prealloc_size');
188 --echo ==+ information_schema data +==
189 SELECT * FROM information_schema.session_variables
190 WHERE variable_name IN ('range_alloc_block_size',
191 'query_alloc_block_size', 'query_prealloc_size',
192 'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
193 --echo Testing values that are multiples of 1024
194 set @@range_alloc_block_size=1024*15+1024;
195 set @@query_alloc_block_size=1024*15+1024*2;
196 set @@query_prealloc_size=1024*18-1024;
197 set @@transaction_alloc_block_size=1024*21-1024*1;
198 set @@transaction_prealloc_size=1024*21-2048;
199 --echo ==+ Check manipulated values ==+
200 select @@query_alloc_block_size;
201 SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
202 'query_alloc_block_size', 'query_prealloc_size',
203 'transaction_alloc_block_size', 'transaction_prealloc_size');
204 --echo ==+ information_schema data +==
205 SELECT * FROM information_schema.session_variables
206 WHERE variable_name IN ('range_alloc_block_size',
207 'query_alloc_block_size', 'query_prealloc_size',
208 'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
209 --echo ==+ Manipulate variable values +==
210 --echo Testing values that are not 1024 multiples
211 set @@range_alloc_block_size=1024*16+1023;
212 set @@query_alloc_block_size=1024*17+2;
213 set @@query_prealloc_size=1024*18-1023;
214 set @@transaction_alloc_block_size=1024*20-1;
215 set @@transaction_prealloc_size=1024*21-1;
216 select @@query_alloc_block_size;
217 --echo ==+ Check manipulated values ==+
218 SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
219 'query_alloc_block_size', 'query_prealloc_size',
220 'transaction_alloc_block_size', 'transaction_prealloc_size');
221 --echo ==+ information_schema data +==
222 SELECT * FROM information_schema.session_variables
223 WHERE variable_name IN ('range_alloc_block_size',
224 'query_alloc_block_size', 'query_prealloc_size',
225 'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
226 --echo ==+ Set values back to the default values +==
227 set @@range_alloc_block_size=default;
228 set @@query_alloc_block_size=default, @@query_prealloc_size=default;
229 set transaction_alloc_block_size=default, @@transaction_prealloc_size=default;
230 --echo ==+ Check the values now that they are reset +==
231 SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
232 'query_alloc_block_size', 'query_prealloc_size',
233 'transaction_alloc_block_size', 'transaction_prealloc_size');
236 # Bug #10904 Illegal mix of collations between
237 # a system variable and a constant
239 SELECT @@version LIKE 'non-existent';
240 SELECT @@version_compile_os LIKE 'non-existent';
242 # The following should give errors
244 --error ER_WRONG_VALUE_FOR_VAR
246 --error ER_WRONG_VALUE_FOR_VAR
247 set big_tables="OFFF";
248 --error ER_UNKNOWN_SYSTEM_VARIABLE
249 set unknown_variable=1;
250 --error ER_WRONG_TYPE_FOR_VAR
251 set max_join_size="hello";
252 --error ER_UNKNOWN_STORAGE_ENGINE
253 set storage_engine=UNKNOWN_TABLE_TYPE;
254 --error ER_WRONG_VALUE_FOR_VAR
255 set storage_engine=MERGE, big_tables=2;
256 show local variables like 'storage_engine';
257 --error ER_GLOBAL_VARIABLE
258 set SESSION query_cache_size=10000;
259 --error ER_NO_DEFAULT
260 set GLOBAL storage_engine=DEFAULT;
261 --error ER_UNKNOWN_CHARACTER_SET
262 set character_set_client=UNKNOWN_CHARACTER_SET;
263 --error ER_UNKNOWN_COLLATION
264 set collation_connection=UNKNOWN_COLLATION;
265 --error ER_WRONG_VALUE_FOR_VAR
266 set character_set_client=NULL;
267 --error ER_WRONG_VALUE_FOR_VAR
268 set collation_connection=NULL;
269 --error ER_LOCAL_VARIABLE
270 set global autocommit=1;
271 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
272 select @@global.timestamp;
273 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
275 --error ER_GLOBAL_VARIABLE
276 set @@concurrent_insert=1;
277 --error ER_LOCAL_VARIABLE
278 set @@global.sql_auto_is_null=1;
279 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
280 select @@global.sql_auto_is_null;
281 --error ER_GLOBAL_VARIABLE
282 set myisam_max_sort_file_size=100;
283 --error ER_WRONG_VALUE_FOR_VAR
284 set @@SQL_WARNINGS=NULL;
286 # Test setting all variables
290 select @@autocommit, @@big_tables;
291 set global binlog_cache_size=100;
292 set bulk_insert_buffer_size=100;
293 set character set cp1251_koi8;
294 set character set default;
295 set @@global.concurrent_insert=1;
296 set global connect_timeout=100;
297 select @@delay_key_write;
298 set global delay_key_write="OFF";
299 select @@delay_key_write;
300 set global delay_key_write=ALL;
301 select @@delay_key_write;
302 set global delay_key_write=1;
303 select @@delay_key_write;
304 set global delayed_insert_limit=100;
305 set global delayed_insert_timeout=100;
306 set global delayed_queue_size=100;
308 set global flush_time=100;
310 set interactive_timeout=100;
311 set join_buffer_size=100;
312 set last_insert_id=1;
313 set global local_infile=1;
314 set long_query_time=0.000001;
315 select @@long_query_time;
316 set long_query_time=100.000001;
317 select @@long_query_time;
318 set low_priority_updates=1;
319 set global max_allowed_packet=100;
320 set global max_binlog_cache_size=100;
321 set global max_binlog_size=100;
322 set global max_connect_errors=100;
323 set global max_connections=100;
324 set global max_delayed_threads=100;
325 set max_heap_table_size=100;
326 set max_join_size=100;
327 set max_sort_length=100;
328 set max_tmp_tables=100;
329 set global max_user_connections=100;
330 select @@max_user_connections;
331 set global max_write_lock_count=100;
332 set myisam_sort_buffer_size=100;
333 set global net_buffer_length=100;
334 set net_read_timeout=100;
335 set net_write_timeout=100;
336 set global query_cache_limit=100;
337 set global query_cache_size=100;
338 set global query_cache_type=demand;
339 set read_buffer_size=100;
340 set read_rnd_buffer_size=100;
341 set global rpl_recovery_rank=100;
342 set global server_id=100;
343 set global slow_launch_time=100;
344 set sort_buffer_size=100;
345 set @@max_sp_recursion_depth=10;
346 select @@max_sp_recursion_depth;
347 set @@max_sp_recursion_depth=0;
348 select @@max_sp_recursion_depth;
349 set sql_auto_is_null=1;
350 select @@sql_auto_is_null;
351 set @@sql_auto_is_null=0;
352 select @@sql_auto_is_null;
353 set sql_big_selects=1;
354 set sql_big_tables=1;
355 set sql_buffer_result=1;
358 set sql_log_update=1;
359 set sql_low_priority_updates=1;
360 set sql_max_join_size=200;
361 select @@sql_max_join_size,@@max_join_size;
362 set sql_quote_show_create=1;
363 set sql_safe_updates=1;
364 set sql_select_limit=1;
365 # reset it, so later tests don't get confused
366 set sql_select_limit=default;
368 set global table_open_cache=100;
369 set storage_engine=myisam;
370 set global thread_cache_size=100;
371 set timestamp=1, timestamp=default;
372 set tmp_table_size=100;
373 set tx_isolation="READ-COMMITTED";
374 set wait_timeout=100;
376 set global log_warnings=1;
379 # Bugs: #20392: INSERT_ID session variable has weird value
381 select @@session.insert_id;
382 set @save_insert_id=@@session.insert_id;
383 set session insert_id=20;
384 select @@session.insert_id;
386 set session last_insert_id=100;
387 select @@session.insert_id;
388 select @@session.last_insert_id;
389 select @@session.insert_id;
391 set @@session.insert_id=@save_insert_id;
392 select @@session.insert_id;
398 create table t1 (a int not null auto_increment, primary key(a));
399 create table t2 (a int not null auto_increment, primary key(a));
400 insert into t1 values(null),(null),(null);
401 insert into t2 values(null),(null),(null);
402 set global key_buffer_size=100000;
403 select @@key_buffer_size;
404 select * from t1 where a=2;
405 select * from t2 where a=3;
407 select max(a) +1, max(a) +2 into @xx,@yy from t1;
414 --error ER_UNKNOWN_SYSTEM_VARIABLE
418 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
419 select @@session.key_buffer_size;
421 --error ER_GLOBAL_VARIABLE
422 set ft_boolean_syntax = @@init_connect;
423 --error ER_WRONG_VALUE_FOR_VAR
424 set global ft_boolean_syntax = @@init_connect;
425 --error ER_GLOBAL_VARIABLE
426 set init_connect = NULL;
427 set global init_connect = NULL;
428 --error ER_GLOBAL_VARIABLE
429 set ft_boolean_syntax = @@init_connect;
430 --error ER_WRONG_VALUE_FOR_VAR
431 set global ft_boolean_syntax = @@init_connect;
433 # Bug#3754 SET GLOBAL myisam_max_sort_file_size doesn't work as
434 # expected: check that there is no overflow when 64-bit unsigned
437 set global myisam_max_sort_file_size=4294967296;
438 --replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
439 show global variables like 'myisam_max_sort_file_size';
440 --replace_result 4294967296 MAX_FILE_SIZE 2146435072 MAX_FILE_SIZE
441 select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
442 set global myisam_max_sort_file_size=default;
447 select @@global.max_user_connections,@@local.max_join_size;
448 set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
449 select @@global.max_user_connections,@@local.max_join_size;
450 set @@global.max_user_connections=111,@@local.max_join_size=222;
451 select @@global.max_user_connections,@@local.max_join_size;
452 set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections;
453 select @@global.max_user_connections,@@local.max_join_size;
454 set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
455 select @@global.max_user_connections,@@local.max_join_size;
461 # Bug#2586:Disallow global/session/local as structured var. instance names
463 --error ER_PARSE_ERROR
464 set @@global.global.key_buffer_size= 1;
465 --error ER_PARSE_ERROR
466 set GLOBAL global.key_buffer_size= 1;
467 --error ER_PARSE_ERROR
468 SELECT @@global.global.key_buffer_size;
469 --error ER_PARSE_ERROR
470 SELECT @@global.session.key_buffer_size;
471 --error ER_PARSE_ERROR
472 SELECT @@global.local.key_buffer_size;
474 # BUG#5135: cannot turn on log_warnings with SET in 4.1 (and 4.0)
475 set @tstlw = @@log_warnings;
476 show global variables like 'log_warnings';
477 select * from information_schema.global_variables where variable_name like 'log_warnings';
478 set global log_warnings = 0;
479 show global variables like 'log_warnings';
480 select * from information_schema.global_variables where variable_name like 'log_warnings';
481 set global log_warnings = 42;
482 show global variables like 'log_warnings';
483 select * from information_schema.global_variables where variable_name like 'log_warnings';
484 set global log_warnings = @tstlw;
485 show global variables like 'log_warnings';
486 select * from information_schema.global_variables where variable_name like 'log_warnings';
489 # BUG#4788 show create table provides incorrect statement
491 # What default width have numeric types?
498 show create table t1;
501 # What types and widths have variables?
502 set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0;
503 create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4;
504 show create table t1;
509 # Bug #6993: myisam_data_pointer_size
510 # Wrong bug report, data pointer size must be restricted to 7,
511 # setting to 8 will not work on all computers, myisamchk and
512 # the server may see a wrong value, such as 0 or negative number
516 SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
517 SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
518 SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
521 # Bug #6958: negative arguments to integer options wrap around
524 SET GLOBAL table_open_cache=-1;
525 SHOW VARIABLES LIKE 'table_open_cache';
526 SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
527 SET GLOBAL table_open_cache=DEFAULT;
530 # Bugs12363: character_set_results is nullable,
531 # but value_ptr returns string "NULL"
533 set character_set_results=NULL;
534 select ifnull(@@character_set_results,"really null");
539 # Bug #9613: @@have_innodb
543 select @@have_innodb;
546 # Tests for lc_time_names
547 # Note, when adding new locales, please fix ID accordingly:
548 # - to test the last ID (currently 108)
549 # - and the next after the last (currently 109)
551 --echo *** Various tests with LC_TIME_NAMES
552 --echo *** LC_TIME_NAMES: testing case insensitivity
553 set @@lc_time_names='ru_ru';
554 select @@lc_time_names;
555 --echo *** LC_TIME_NAMES: testing with a user variable
557 set @@lc_time_names=@lc;
558 select @@lc_time_names;
559 --echo *** LC_TIME_NAMES: testing with string expressions
560 set lc_time_names=concat('de','_','DE');
561 select @@lc_time_names;
562 --error ER_UNKNOWN_ERROR
563 set lc_time_names=concat('de','+','DE');
564 select @@lc_time_names;
565 --echo LC_TIME_NAMES: testing with numeric expressions
566 set @@lc_time_names=1+2;
567 select @@lc_time_names;
568 --error ER_WRONG_TYPE_FOR_VAR
569 set @@lc_time_names=1/0;
570 select @@lc_time_names;
571 set lc_time_names=en_US;
572 --echo LC_TIME_NAMES: testing NULL and a negative number:
573 --error ER_WRONG_VALUE_FOR_VAR
574 set lc_time_names=NULL;
575 --error ER_UNKNOWN_ERROR
576 set lc_time_names=-1;
577 select @@lc_time_names;
578 --echo LC_TIME_NAMES: testing locale with the last ID:
579 set lc_time_names=108;
580 select @@lc_time_names;
581 --echo LC_TIME_NAMES: testing a number beyond the valid ID range:
582 --error ER_UNKNOWN_ERROR
583 set lc_time_names=109;
584 select @@lc_time_names;
585 --echo LC_TIME_NAMES: testing that 0 is en_US:
587 select @@lc_time_names;
590 # Bug #22648 LC_TIME_NAMES: Setting GLOBAL has no effect
592 select @@global.lc_time_names, @@lc_time_names;
593 set @@global.lc_time_names=fr_FR;
594 select @@global.lc_time_names, @@lc_time_names;
595 --echo New connection
596 connect (con1,localhost,root,,);
598 select @@global.lc_time_names, @@lc_time_names;
599 set @@lc_time_names=ru_RU;
600 select @@global.lc_time_names, @@lc_time_names;
603 --echo Returnung to default connection
604 select @@global.lc_time_names, @@lc_time_names;
605 set lc_time_names=default;
606 select @@global.lc_time_names, @@lc_time_names;
607 set @@global.lc_time_names=default;
608 select @@global.lc_time_names, @@lc_time_names;
609 set @@lc_time_names=default;
610 select @@global.lc_time_names, @@lc_time_names;
614 # Bug #13334: query_prealloc_size default less than minimum
616 set @test = @@query_prealloc_size;
617 set @@query_prealloc_size = @test;
618 select @@query_prealloc_size = @test;
621 # Bug#31588 buffer overrun when setting variables
623 # Buffer-size Off By One. Should throw valgrind-warning without fix #31588.
624 --error ER_WRONG_VALUE_FOR_VAR
625 set global sql_mode=repeat('a',80);
627 --echo End of 4.1 tests
630 # Bug#6282 Packet error with SELECT INTO
633 create table t1 (a int);
634 select a into @x from t1;
639 # Bug #10339: read only variables.
642 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
643 set @@warning_count=1;
644 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
645 set @@global.error_count=1;
648 # Bug #10351: Setting ulong variable to > MAX_ULONG fails on 32-bit platform
652 set @@max_heap_table_size= 4294967296;
653 select @@max_heap_table_size > 0;
654 set global max_heap_table_size= 4294967296;
655 select @@max_heap_table_size > 0;
656 set @@max_heap_table_size= 4294967296;
657 select @@max_heap_table_size > 0;
661 # Bug #11775 Variable character_set_system does not exist (sometimes)
663 select @@character_set_system;
664 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
665 set global character_set_system = latin1;
666 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
667 set @@global.version_compile_os='234';
670 # Check character_set_filesystem variable
672 set character_set_filesystem=latin1;
673 select @@character_set_filesystem;
674 set @@global.character_set_filesystem=latin2;
675 set character_set_filesystem=latin1;
676 select @@character_set_filesystem;
677 set @@global.character_set_filesystem=latin2;
678 set character_set_filesystem=default;
679 select @@character_set_filesystem;
680 set @@global.character_set_filesystem=default;
681 select @@global.character_set_filesystem;
684 # Bug #17849: Show sql_big_selects in SHOW VARIABLES
686 set @old_sql_big_selects = @@sql_big_selects;
687 set @@sql_big_selects = 1;
688 show variables like 'sql_big_selects';
689 select * from information_schema.session_variables where variable_name like 'sql_big_selects';
690 set @@sql_big_selects = @old_sql_big_selects;
693 # Bug #16195: SHOW VARIABLES doesn't report correctly sql_warnings and
696 set @@sql_notes = 0, @@sql_warnings = 0;
697 show variables like 'sql_notes';
698 select * from information_schema.session_variables where variable_name like 'sql_notes';
699 show variables like 'sql_warnings';
700 select * from information_schema.session_variables where variable_name like 'sql_warnings';
701 set @@sql_notes = 1, @@sql_warnings = 1;
702 show variables like 'sql_notes';
703 select * from information_schema.session_variables where variable_name like 'sql_notes';
704 show variables like 'sql_warnings';
705 select * from information_schema.session_variables where variable_name like 'sql_warnings';
708 # Bug #12792: @@system_time_zone is not SELECTable.
710 # Don't actually output, since it depends on the system
712 select @@system_time_zone;
715 # Bug #15684: system variables cannot be SELECTed (e.g. @@version_comment)
717 # Don't actually output, since it depends on the system
718 --replace_column 1 # 2 # 3 # 4 #
719 select @@version, @@version_comment, @@version_compile_machine,
720 @@version_compile_os;
723 # Bug #1039: make tmpdir and datadir available as @@variables (also included
726 # Don't actually output, since it depends on the system
727 --replace_column 1 # 2 # 3 #
728 select @@basedir, @@datadir, @@tmpdir;
730 show variables like 'basedir';
732 select * from information_schema.session_variables where variable_name like 'basedir';
734 show variables like 'datadir';
736 select * from information_schema.session_variables where variable_name like 'datadir';
738 show variables like 'tmpdir';
740 select * from information_schema.session_variables where variable_name like 'tmpdir';
743 # Bug #19606: make ssl settings available via SHOW VARIABLES and @@variables
745 # Don't actually output, since it depends on the system
746 --replace_column 1 # 2 # 3 # 4 # 5 #
747 select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key;
749 show variables like 'ssl%';
751 select * from information_schema.session_variables where variable_name like 'ssl%' order by 1;
754 # Bug #19616: make log_queries_not_using_indexes available in SHOW VARIABLES
755 # and as @@log_queries_not_using_indexes
757 select @@log_queries_not_using_indexes;
758 show variables like 'log_queries_not_using_indexes';
759 select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
762 # Bug#20908: Crash if select @@""
764 --error ER_PARSE_ERROR
766 --error ER_PARSE_ERROR
768 --error ER_PARSE_ERROR
772 # Bug#20166 mysql-test-run.pl does not test system privilege tables creation
774 # Don't actually output, since it depends on the system
777 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
778 set @@hostname= "anothername";
780 show variables like 'hostname';
783 --echo # BUG#37408 - Compressed MyISAM files should not require/use mmap()
785 --echo # Test 'myisam_mmap_size' option is not dynamic
786 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
787 SET @@myisam_mmap_size= 500M;
791 --echo # Bug #52315: utc_date() crashes when system time > year 2037
794 --error 0, ER_WRONG_VALUE_FOR_VAR
795 SET TIMESTAMP=2*1024*1024*1024;
796 --echo #Should not crash
800 SET TIMESTAMP=DEFAULT;
803 --echo End of 5.0 tests
806 # Bug#36446: Attempt to set @@join_buffer_size to its minimum value
807 # produces spurious warning
810 # set to 1 so mysqld will correct to minimum (+ warn)
811 set join_buffer_size=1;
813 set @save_join_buffer_size=@@join_buffer_size;
815 set join_buffer_size=@save_join_buffer_size;
817 # This is at the very after the versioned tests, since it involves doing
820 # Bug #19263: variables.test doesn't clean up after itself (II/II --
823 set global binlog_cache_size =@my_binlog_cache_size;
824 set global connect_timeout =@my_connect_timeout;
825 set global delayed_insert_timeout =@my_delayed_insert_timeout;
826 set global delayed_queue_size =@my_delayed_queue_size;
827 set global flush =@my_flush;
828 set global flush_time =@my_flush_time;
829 set global key_buffer_size =@my_key_buffer_size;
830 set global max_binlog_cache_size =@my_max_binlog_cache_size;
831 set global max_binlog_size =@my_max_binlog_size;
832 set global max_connect_errors =@my_max_connect_errors;
833 set global max_connections =@my_max_connections;
834 set global max_delayed_threads =@my_max_delayed_threads;
835 set global max_heap_table_size =@my_max_heap_table_size;
836 set global max_insert_delayed_threads=@my_max_insert_delayed_threads;
837 set global max_join_size =@my_max_join_size;
838 set global max_user_connections =default;
839 set global max_write_lock_count =default;
840 set global myisam_data_pointer_size =@my_myisam_data_pointer_size;
841 set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
842 set global net_buffer_length =@my_net_buffer_length;
843 set global net_write_timeout =@my_net_write_timeout;
844 set global net_read_timeout =@my_net_read_timeout;
845 set global query_cache_limit =@my_query_cache_limit;
846 set global query_cache_type =@my_query_cache_type;
847 set global rpl_recovery_rank =@my_rpl_recovery_rank;
848 set global server_id =@my_server_id;
849 set global slow_launch_time =@my_slow_launch_time;
850 set global storage_engine =@my_storage_engine;
851 set global thread_cache_size =@my_thread_cache_size;
852 set global max_allowed_packet =@my_max_allowed_packet;
853 set global join_buffer_size =@my_join_buffer_size;
856 # Bug#28580 Repeatation of status variables
859 show global variables where Variable_name='table_definition_cache' or
860 Variable_name='table_lock_wait_timeout';
862 ###########################################################################
866 --echo # -- Bug#34820: log_output can be set to illegal value.
869 --error ER_WRONG_VALUE_FOR_VAR
870 SET GLOBAL log_output = '';
872 --error ER_WRONG_VALUE_FOR_VAR
873 SET GLOBAL log_output = 0;
876 --echo # -- End of Bug#34820.
879 # Bug#28234 - global/session scope - documentation vs implementation
883 SHOW VARIABLES like 'ft_max_word_len';
884 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
885 SELECT @@session.ft_max_word_len;
886 SELECT @@global.ft_max_word_len;
887 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
888 SET @@session.ft_max_word_len= 7;
889 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
890 SET @@global.ft_max_word_len= 7;
893 SHOW VARIABLES like 'ft_min_word_len';
894 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
895 SELECT @@session.ft_min_word_len;
896 SELECT @@global.ft_min_word_len;
897 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
898 SET @@session.ft_min_word_len= 7;
899 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
900 SET @@global.ft_min_word_len= 7;
903 SHOW VARIABLES like 'ft_query_expansion_limit';
904 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
905 SELECT @@session.ft_query_expansion_limit;
906 SELECT @@global.ft_query_expansion_limit;
907 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
908 SET @@session.ft_query_expansion_limit= 7;
909 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
910 SET @@global.ft_query_expansion_limit= 7;
913 SHOW VARIABLES like 'ft_stopword_file';
914 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
915 SELECT @@session.ft_stopword_file;
916 SELECT @@global.ft_stopword_file;
917 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
918 SET @@session.ft_stopword_file= 'x';
919 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
920 SET @@global.ft_stopword_file= 'x';
922 # Additional variables fixed.
925 SHOW VARIABLES like 'back_log';
926 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
927 SELECT @@session.back_log;
928 SELECT @@global.back_log;
929 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
930 SET @@session.back_log= 7;
931 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
932 SET @@global.back_log= 7;
936 SHOW VARIABLES like 'large_files_support';
937 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
938 SELECT @@session.large_files_support;
940 SELECT @@global.large_files_support;
941 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
942 SET @@session.large_files_support= true;
943 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
944 SET @@global.large_files_support= true;
948 SHOW VARIABLES like 'character_sets_dir';
949 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
950 SELECT @@session.character_sets_dir;
952 SELECT @@global.character_sets_dir;
953 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
954 SET @@session.character_sets_dir= 'x';
955 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
956 SET @@global.character_sets_dir= 'x';
960 SHOW VARIABLES like 'init_file';
961 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
962 SELECT @@session.init_file;
964 SELECT @@global.init_file;
965 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
966 SET @@session.init_file= 'x';
967 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
968 SET @@global.init_file= 'x';
972 SHOW VARIABLES like 'language';
973 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
974 SELECT @@session.language;
976 SELECT @@global.language;
977 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
978 SET @@session.language= 'x';
979 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
980 SET @@global.language= 'x';
984 SHOW VARIABLES like 'large_page_size';
985 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
986 SELECT @@session.large_page_size;
988 SELECT @@global.large_page_size;
989 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
990 SET @@session.large_page_size= 7;
991 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
992 SET @@global.large_page_size= 7;
996 SHOW VARIABLES like 'large_pages';
997 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
998 SELECT @@session.large_pages;
1000 SELECT @@global.large_pages;
1001 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1002 SET @@session.large_pages= true;
1003 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1004 SET @@global.large_pages= true;
1007 SHOW VARIABLES like 'log_bin';
1008 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1009 SELECT @@session.log_bin;
1010 SELECT @@global.log_bin;
1011 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1012 SET @@session.log_bin= true;
1013 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1014 SET @@global.log_bin= true;
1017 --replace_column 2 #
1018 SHOW VARIABLES like 'log_error';
1019 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1020 SELECT @@session.log_error;
1021 --replace_column 1 #
1022 SELECT @@global.log_error;
1023 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1024 SET @@session.log_error= 'x';
1025 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1026 SET @@global.log_error= 'x';
1029 --replace_column 2 #
1030 SHOW VARIABLES like 'lower_case_file_system';
1031 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1032 SELECT @@session.lower_case_file_system;
1033 --replace_column 1 #
1034 SELECT @@global.lower_case_file_system;
1035 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1036 SET @@session.lower_case_file_system= true;
1037 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1038 SET @@global.lower_case_file_system= true;
1041 --replace_column 2 #
1042 SHOW VARIABLES like 'lower_case_table_names';
1043 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1044 SELECT @@session.lower_case_table_names;
1045 --replace_column 1 #
1046 SELECT @@global.lower_case_table_names;
1047 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1048 SET @@session.lower_case_table_names= 7;
1049 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1050 SET @@global.lower_case_table_names= 7;
1053 SHOW VARIABLES like 'myisam_recover_options';
1054 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1055 SELECT @@session.myisam_recover_options;
1056 SELECT @@global.myisam_recover_options;
1057 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1058 SET @@session.myisam_recover_options= 'x';
1059 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1060 SET @@global.myisam_recover_options= 'x';
1063 --replace_column 2 #
1064 SHOW VARIABLES like 'open_files_limit';
1065 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1066 SELECT @@session.open_files_limit;
1067 --replace_column 1 #
1068 SELECT @@global.open_files_limit;
1069 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1070 SET @@session.open_files_limit= 7;
1071 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1072 SET @@global.open_files_limit= 7;
1075 --replace_column 2 #
1076 SHOW VARIABLES like 'pid_file';
1077 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1078 SELECT @@session.pid_file;
1079 --replace_column 1 #
1080 SELECT @@global.pid_file;
1081 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1082 SET @@session.pid_file= 'x';
1083 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1084 SET @@global.pid_file= 'x';
1087 --replace_column 2 #
1088 SHOW VARIABLES like 'plugin_dir';
1089 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1090 SELECT @@session.plugin_dir;
1091 --replace_column 1 #
1092 SELECT @@global.plugin_dir;
1093 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1094 SET @@session.plugin_dir= 'x';
1095 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1096 SET @@global.plugin_dir= 'x';
1099 --replace_column 2 #
1100 SHOW VARIABLES like 'port';
1101 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1102 SELECT @@session.port;
1103 --replace_column 1 #
1104 SELECT @@global.port;
1105 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1106 SET @@session.port= 7;
1107 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1108 SET @@global.port= 7;
1111 SHOW VARIABLES like 'protocol_version';
1112 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1113 SELECT @@session.protocol_version;
1114 SELECT @@global.protocol_version;
1115 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1116 SET @@session.protocol_version= 7;
1117 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1118 SET @@global.protocol_version= 7;
1121 SHOW VARIABLES like 'skip_external_locking';
1122 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1123 SELECT @@session.skip_external_locking;
1124 SELECT @@global.skip_external_locking;
1125 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1126 SET @@session.skip_external_locking= true;
1127 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1128 SET @@global.skip_external_locking= true;
1131 SHOW VARIABLES like 'skip_networking';
1132 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1133 SELECT @@session.skip_networking;
1134 SELECT @@global.skip_networking;
1135 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1136 SET @@session.skip_networking= true;
1137 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1138 SET @@global.skip_networking= true;
1141 SHOW VARIABLES like 'skip_show_database';
1142 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1143 SELECT @@session.skip_show_database;
1144 SELECT @@global.skip_show_database;
1145 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1146 SET @@session.skip_show_database= true;
1147 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1148 SET @@global.skip_show_database= true;
1151 --replace_column 2 #
1152 SHOW VARIABLES like 'thread_stack';
1153 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1154 SELECT @@session.thread_stack;
1155 --replace_column 1 #
1156 SELECT @@global.thread_stack;
1157 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1158 SET @@session.thread_stack= 7;
1159 --error ER_INCORRECT_GLOBAL_LOCAL_VAR
1160 SET @@global.thread_stack= 7;
1164 # Bug #40657 - assertion with out of range variables and traditional sql_mode
1167 SELECT @@global.expire_logs_days INTO @old_eld;
1169 SET GLOBAL expire_logs_days = -1;
1170 --echo needs to've been adjusted (0)
1171 SELECT @@global.expire_logs_days;
1173 SET GLOBAL expire_logs_days = 11;
1174 SET @old_mode=@@sql_mode;
1175 SET SESSION sql_mode = 'TRADITIONAL';
1176 --error ER_WRONG_VALUE_FOR_VAR
1177 SET GLOBAL expire_logs_days = 100;
1178 --echo needs to be unchanged (11)
1179 SELECT @@global.expire_logs_days;
1180 SET SESSION sql_mode = @old_mode;
1182 SET GLOBAL expire_logs_days = 100;
1183 --echo needs to've been adjusted (99)
1184 SELECT @@global.expire_logs_days;
1186 SET GLOBAL expire_logs_days = 11;
1187 SET GLOBAL expire_logs_days = 99;
1188 --echo needs to pass with no warnings (99)
1189 SELECT @@global.expire_logs_days;
1192 SET GLOBAL expire_logs_days = @old_eld;
1194 # show that warning uses underscore (sysvar-name), not hyphens (option-name)
1195 SET GLOBAL auto_increment_offset=-1;
1196 SET GLOBAL auto_increment_offset=0;
1201 # Bug#41030 Wrong meta data (incorrect fieldlen)
1205 select @@storage_engine;
1209 # Bug#36540: CREATE EVENT and ALTER EVENT statements fail with large server_id
1212 SET @old_server_id = @@GLOBAL.server_id;
1213 SET GLOBAL server_id = (1 << 32) - 1;
1214 SELECT @@GLOBAL.server_id;
1215 SET GLOBAL server_id = (1 << 32);
1216 SELECT @@GLOBAL.server_id;
1217 SET GLOBAL server_id = (1 << 60);
1218 SELECT @@GLOBAL.server_id;
1219 SET GLOBAL server_id = 0;
1220 SELECT @@GLOBAL.server_id;
1221 SET GLOBAL server_id = -1;
1222 SELECT @@GLOBAL.server_id;
1223 SET GLOBAL server_id = @old_server_id;
1226 # Bug #42778: delete order by null global variable causes
1227 # assertion .\filesort.cc, line 797
1230 SELECT @@GLOBAL.INIT_FILE, @@GLOBAL.INIT_FILE IS NULL;
1232 CREATE TABLE t1 (a INT);
1233 INSERT INTO t1 VALUES ();
1234 SET @bug42778= @@sql_safe_updates;
1235 SET @@sql_safe_updates= 0;
1236 DELETE FROM t1 ORDER BY (@@GLOBAL.INIT_FILE) ASC LIMIT 10;
1237 SET @@sql_safe_updates= @bug42778;
1242 --echo # BUG#10206 - InnoDB: Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback
1245 SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size;
1246 --echo # Set the max_binlog_cache_size to size more than 4GB.
1247 SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024;
1248 SELECT @@GLOBAL.max_binlog_cache_size;
1249 SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size;
1252 --echo # Bug #37168 : Missing variable - skip_name_resolve
1255 SELECT @@skip_name_resolve;
1256 SHOW VARIABLES LIKE 'skip_name_resolve';
1259 --echo # Bug #43233 : Some server variables are clipped during "update,"
1260 --echo # not "check" stage
1263 SET @kbs=@@global.key_buffer_size;
1264 SET @kcbs=@@global.key_cache_block_size;
1266 --echo throw errors in STRICT mode
1267 SET SQL_MODE=STRICT_ALL_TABLES;
1269 # sys_var_ulonglong_ptr: sys_max_binlog_cache_size
1270 --error ER_WRONG_VALUE_FOR_VAR
1271 SET @@global.max_binlog_cache_size=-1;
1273 # sys_var_thd_ha_rows: "max_join_size" et al.
1274 --error ER_WRONG_VALUE_FOR_VAR
1275 SET @@global.max_join_size=0;
1277 # sys_var_key_buffer_size: "key_buffer_size"
1278 --error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE
1279 SET @@global.key_buffer_size=0;
1281 # sys_var_key_cache_long: "key_cache_block_size" et al.
1282 --error ER_WRONG_VALUE_FOR_VAR
1283 SET @@global.key_cache_block_size=0;
1285 --echo throw warnings in default mode
1286 SET SQL_MODE=DEFAULT;
1288 SET @@global.max_binlog_cache_size=-1;
1289 SET @@global.max_join_size=0;
1290 # this is an exception. since this is a new error/warning, let's stay
1291 # compatible with the upcoming 5.6.
1292 --error ER_WARN_CANT_DROP_DEFAULT_KEYCACHE
1293 SET @@global.key_buffer_size=0;
1294 SET @@global.key_cache_block_size=0;
1297 # Bug#56976: added new start-up parameter
1299 select @@max_long_data_size;
1302 --echo # Bug#11766424 59527: DECIMAL_BIN_SIZE: ASSERTION `SCALE >= 0 && PRECISION > 0 && SCALE <= PRE
1305 CREATE TABLE t1(f1 DECIMAL(1,1) UNSIGNED);
1306 INSERT INTO t1 VALUES (0.2),(0.1);
1307 SELECT 1 FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE @a=f1);
1310 CREATE TABLE t1 AS SELECT @a:= CAST(1 AS UNSIGNED) AS a;
1311 SHOW CREATE TABLE t1;
1315 SET @@global.max_binlog_cache_size=DEFAULT;
1316 SET @@global.max_join_size=DEFAULT;
1317 SET @@global.key_buffer_size=@kbs;
1318 SET @@global.key_cache_block_size=@kcbs;
1321 --echo End of 5.1 tests