1
1
drop table if exists t1,t2;
2
2
set @my_binlog_cache_size =@@global.binlog_cache_size;
3
3
set @my_connect_timeout =@@global.connect_timeout;
4
set @my_delayed_insert_timeout =@@global.delayed_insert_timeout;
5
set @my_delayed_queue_size =@@global.delayed_queue_size;
6
4
set @my_flush =@@global.flush;
7
set @my_flush_time =@@global.flush_time;
8
5
set @my_key_buffer_size =@@global.key_buffer_size;
9
6
set @my_max_binlog_cache_size =@@global.max_binlog_cache_size;
10
7
set @my_max_binlog_size =@@global.max_binlog_size;
11
8
set @my_max_connect_errors =@@global.max_connect_errors;
12
9
set @my_max_connections =@@global.max_connections;
13
set @my_max_delayed_threads =@@global.max_delayed_threads;
14
10
set @my_max_heap_table_size =@@global.max_heap_table_size;
15
set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads;
16
11
set @my_max_join_size =@@global.max_join_size;
17
set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size;
18
12
set @my_net_buffer_length =@@global.net_buffer_length;
19
13
set @my_net_write_timeout =@@global.net_write_timeout;
20
14
set @my_net_read_timeout =@@global.net_read_timeout;
21
set @my_query_cache_limit =@@global.query_cache_limit;
22
set @my_query_cache_type =@@global.query_cache_type;
23
set @my_rpl_recovery_rank =@@global.rpl_recovery_rank;
24
15
set @my_server_id =@@global.server_id;
25
16
set @my_slow_launch_time =@@global.slow_launch_time;
26
17
set @my_storage_engine =@@global.storage_engine;
27
set @my_thread_cache_size =@@global.thread_cache_size;
29
19
select @test, @`test`, @TEST, @`TEST`, @"teSt";
30
20
@test @`test` @TEST @`TEST` @"teSt"
158
148
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
160
150
Note 1003 select 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
161
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
162
set global concurrent_insert=2;
163
show variables like 'concurrent_insert';
166
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
167
VARIABLE_NAME VARIABLE_VALUE
169
set global concurrent_insert=1;
170
show variables like 'concurrent_insert';
173
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
174
VARIABLE_NAME VARIABLE_VALUE
176
set global concurrent_insert=0;
177
show variables like 'concurrent_insert';
180
select * from information_schema.session_variables where variable_name like 'concurrent_insert';
181
VARIABLE_NAME VARIABLE_VALUE
183
set global concurrent_insert=DEFAULT;
184
select @@concurrent_insert;
187
151
set global timed_mutexes=ON;
188
152
show variables like 'timed_mutexes';
189
153
Variable_name Value
296
259
select * from information_schema.session_variables where variable_name like 'net_buffer_length';
297
260
VARIABLE_NAME VARIABLE_VALUE
298
261
NET_BUFFER_LENGTH 1048576
299
set character set cp1251_koi8;
300
show variables like "character_set_client";
302
character_set_client cp1251
303
select * from information_schema.session_variables where variable_name like 'character_set_client';
304
VARIABLE_NAME VARIABLE_VALUE
305
CHARACTER_SET_CLIENT cp1251
306
select @@timestamp>0;
309
set @@rand_seed1=10000000,@@rand_seed2=1000000;
310
select ROUND(RAND(),5);
313
262
show variables like '%alloc%';
314
263
Variable_name Value
315
264
query_alloc_block_size 8192
316
265
query_prealloc_size 8192
317
range_alloc_block_size 4096
266
range_alloc_block_size
318
267
transaction_alloc_block_size 8192
319
268
transaction_prealloc_size 4096
320
269
select * from information_schema.session_variables where variable_name like '%alloc%' order by 1;
321
270
VARIABLE_NAME VARIABLE_VALUE
322
271
QUERY_ALLOC_BLOCK_SIZE 8192
323
272
QUERY_PREALLOC_SIZE 8192
324
RANGE_ALLOC_BLOCK_SIZE 4096
273
RANGE_ALLOC_BLOCK_SIZE
325
274
TRANSACTION_ALLOC_BLOCK_SIZE 8192
326
275
TRANSACTION_PREALLOC_SIZE 4096
327
276
set @@range_alloc_block_size=1024*16;
369
318
SELECT @@version_compile_os LIKE 'non-existent';
370
319
@@version_compile_os LIKE 'non-existent'
373
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
374
set big_tables="OFFF";
375
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
376
321
set unknown_variable=1;
377
322
ERROR HY000: Unknown system variable 'unknown_variable'
378
323
set max_join_size="hello";
379
324
ERROR 42000: Incorrect argument type to variable 'max_join_size'
380
325
set storage_engine=UNKNOWN_TABLE_TYPE;
381
326
ERROR 42000: Unknown table engine 'UNKNOWN_TABLE_TYPE'
382
set SESSION query_cache_size=10000;
383
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
384
327
set GLOBAL storage_engine=DEFAULT;
385
328
ERROR 42000: Variable 'storage_engine' doesn't have a default value
386
set character_set_client=UNKNOWN_CHARACTER_SET;
387
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
388
329
set collation_connection=UNKNOWN_COLLATION;
389
330
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
390
set character_set_client=NULL;
391
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'NULL'
392
331
set collation_connection=NULL;
393
332
ERROR 42000: Variable 'collation_connection' can't be set to the value of 'NULL'
394
333
set global autocommit=1;
397
336
ERROR HY000: Variable 'timestamp' is a SESSION variable
398
337
set @@version='';
399
338
ERROR HY000: Variable 'version' is a read only variable
400
set @@concurrent_insert=1;
401
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
402
set @@global.sql_auto_is_null=1;
403
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable and can't be used with SET GLOBAL
404
select @@global.sql_auto_is_null;
405
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
406
339
set myisam_max_sort_file_size=100;
407
340
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
408
341
set @@SQL_WARNINGS=NULL;
409
342
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
410
343
set autocommit=1;
412
select @@autocommit, @@big_tables;
413
@@autocommit @@big_tables
415
347
set global binlog_cache_size=100;
417
349
Warning 1292 Truncated incorrect binlog_cache_size value: '100'
418
350
set bulk_insert_buffer_size=100;
419
set character set cp1251_koi8;
420
set character set default;
421
set @@global.concurrent_insert=1;
422
351
set global connect_timeout=100;
423
352
select @@delay_key_write;
424
353
@@delay_key_write
485
392
Warning 1292 Truncated incorrect net_buffer_length value: '100'
486
393
set net_read_timeout=100;
487
394
set net_write_timeout=100;
488
set global query_cache_limit=100;
489
set global query_cache_size=100;
490
set global query_cache_type=demand;
491
395
set read_buffer_size=100;
493
397
Warning 1292 Truncated incorrect read_buffer_size value: '100'
494
398
set read_rnd_buffer_size=100;
495
set global rpl_recovery_rank=100;
496
399
set global server_id=100;
497
400
set global slow_launch_time=100;
498
401
set sort_buffer_size=100;
500
403
Warning 1292 Truncated incorrect sort_buffer_size value: '100'
501
set @@max_sp_recursion_depth=10;
502
select @@max_sp_recursion_depth;
503
@@max_sp_recursion_depth
505
set @@max_sp_recursion_depth=0;
506
select @@max_sp_recursion_depth;
507
@@max_sp_recursion_depth
509
set sql_auto_is_null=1;
510
select @@sql_auto_is_null;
513
set @@sql_auto_is_null=0;
514
select @@sql_auto_is_null;
517
404
set sql_big_selects=1;
518
set sql_big_tables=1;
519
405
set sql_buffer_result=1;
520
406
set sql_log_bin=1;
522
set sql_log_update=1;
524
Note 1315 The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored
525
set sql_low_priority_updates=1;
526
set sql_max_join_size=200;
527
select @@sql_max_join_size,@@max_join_size;
528
@@sql_max_join_size @@max_join_size
530
407
set sql_quote_show_create=1;
531
408
set sql_safe_updates=1;
532
409
set sql_select_limit=1;
603
477
VARIABLE_NAME VARIABLE_VALUE
604
478
MYISAM_MAX_SORT_FILE_SIZE MAX_FILE_SIZE
605
479
set global myisam_max_sort_file_size=default;
606
select @@global.max_user_connections,@@local.max_join_size;
607
@@global.max_user_connections @@local.max_join_size
609
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
610
select @@global.max_user_connections,@@local.max_join_size;
611
@@global.max_user_connections @@local.max_join_size
613
set @@global.max_user_connections=111,@@local.max_join_size=222;
614
select @@global.max_user_connections,@@local.max_join_size;
615
@@global.max_user_connections @@local.max_join_size
617
set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections;
618
select @@global.max_user_connections,@@local.max_join_size;
619
@@global.max_user_connections @@local.max_join_size
621
set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
622
select @@global.max_user_connections,@@local.max_join_size;
623
@@global.max_user_connections @@local.max_join_size
480
select @@global.myisam_max_sort_file_size,@@local.max_join_size;
481
@@global.myisam_max_sort_file_size @@local.max_join_size
483
set @svc=@@global.myisam_max_sort_file_size, @svj=@@local.max_join_size;
484
select @@global.myisam_max_sort_file_size,@@local.max_join_size;
485
@@global.myisam_max_sort_file_size @@local.max_join_size
487
set @@global.myisam_max_sort_file_size=111,@@local.max_join_size=222;
488
select @@global.myisam_max_sort_file_size,@@local.max_join_size;
489
@@global.myisam_max_sort_file_size @@local.max_join_size
491
set @@global.myisam_max_sort_file_size=@@local.max_join_size,@@local.max_join_size=@@global.myisam_max_sort_file_size;
493
Warning 1292 Truncated incorrect max_join_size value: '0'
494
select @@global.myisam_max_sort_file_size,@@local.max_join_size;
495
@@global.myisam_max_sort_file_size @@local.max_join_size
497
set @@global.myisam_max_sort_file_size=@svc, @@local.max_join_size=@svj;
498
select @@global.myisam_max_sort_file_size,@@local.max_join_size;
499
@@global.myisam_max_sort_file_size @@local.max_join_size
626
502
set @a=@b, @b=@a;
630
506
set @@global.global.key_buffer_size= 1;
631
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
507
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'key_buffer_size= 1' at line 1
632
508
set GLOBAL global.key_buffer_size= 1;
633
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
509
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'key_buffer_size= 1' at line 1
634
510
SELECT @@global.global.key_buffer_size;
635
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
511
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'key_buffer_size' at line 1
636
512
SELECT @@global.session.key_buffer_size;
637
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
513
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'key_buffer_size' at line 1
638
514
SELECT @@global.local.key_buffer_size;
639
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
640
set @tstlw = @@log_warnings;
641
show global variables like 'log_warnings';
644
select * from information_schema.global_variables where variable_name like 'log_warnings';
645
VARIABLE_NAME VARIABLE_VALUE
647
set global log_warnings = 0;
648
show global variables like 'log_warnings';
651
select * from information_schema.global_variables where variable_name like 'log_warnings';
652
VARIABLE_NAME VARIABLE_VALUE
654
set global log_warnings = 42;
655
show global variables like 'log_warnings';
658
select * from information_schema.global_variables where variable_name like 'log_warnings';
659
VARIABLE_NAME VARIABLE_VALUE
661
set global log_warnings = @tstlw;
662
show global variables like 'log_warnings';
665
select * from information_schema.global_variables where variable_name like 'log_warnings';
666
VARIABLE_NAME VARIABLE_VALUE
515
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'key_buffer_size' at line 1
668
516
create table t1 (
674
522
show create table t1;
675
523
Table Create Table
676
524
t1 CREATE TABLE `t1` (
677
`c1` tinyint(4) DEFAULT NULL,
678
`c2` smallint(6) DEFAULT NULL,
679
`c3` mediumint(9) DEFAULT NULL,
680
`c4` int(11) DEFAULT NULL,
681
`c5` bigint(20) DEFAULT NULL
682
) ENGINE=MyISAM DEFAULT CHARSET=latin1
684
532
set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0;
685
533
create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4;
686
534
show create table t1;
687
535
Table Create Table
688
536
t1 CREATE TABLE `t1` (
689
`c1` bigint(20) DEFAULT NULL,
690
`c2` decimal(65,30) DEFAULT NULL,
692
`c4` double DEFAULT NULL
693
) ENGINE=MyISAM DEFAULT CHARSET=latin1
695
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
696
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
698
myisam_data_pointer_size 7
699
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
700
VARIABLE_NAME VARIABLE_VALUE
701
MYISAM_DATA_POINTER_SIZE 7
702
543
SET GLOBAL table_open_cache=-1;
704
545
Warning 1292 Truncated incorrect table_open_cache value: '0'
705
546
SHOW VARIABLES LIKE 'table_open_cache';
706
547
Variable_name Value
707
548
table_open_cache 1
708
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'table_open_cache';
709
VARIABLE_NAME VARIABLE_VALUE
711
549
SET GLOBAL table_open_cache=DEFAULT;
712
set character_set_results=NULL;
713
select ifnull(@@character_set_results,"really null");
714
ifnull(@@character_set_results,"really null")
717
select @@have_innodb;
720
550
*** Various tests with LC_TIME_NAMES
721
551
*** LC_TIME_NAMES: testing case insensitivity
722
552
set @@lc_time_names='ru_ru';
838
666
@@max_heap_table_size > 0
840
668
select @@character_set_system;
841
@@character_set_system
843
set global character_set_system = latin1;
844
ERROR HY000: Variable 'character_set_system' is a read only variable
669
ERROR HY000: Unknown system variable 'character_set_system'
670
set global character_set_system = utf8;
671
ERROR HY000: Unknown system variable 'character_set_system'
845
672
set @@global.version_compile_os='234';
846
673
ERROR HY000: Variable 'version_compile_os' is a read only variable
847
set character_set_filesystem=latin1;
848
select @@character_set_filesystem;
849
@@character_set_filesystem
851
set @@global.character_set_filesystem=latin2;
852
set character_set_filesystem=latin1;
853
select @@character_set_filesystem;
854
@@character_set_filesystem
856
set @@global.character_set_filesystem=latin2;
857
set character_set_filesystem=default;
858
select @@character_set_filesystem;
859
@@character_set_filesystem
861
set @@global.character_set_filesystem=default;
862
select @@global.character_set_filesystem;
863
@@global.character_set_filesystem
674
set @@global.character_set_filesystem=utf8;
675
ERROR HY000: Unknown system variable 'character_set_filesystem'
676
set character_set_filesystem=utf8;
677
ERROR HY000: Unknown system variable 'character_set_filesystem'
865
678
set @old_sql_big_selects = @@sql_big_selects;
866
679
set @@sql_big_selects = 1;
867
680
show variables like 'sql_big_selects';
868
681
Variable_name Value
869
682
sql_big_selects ON
870
select * from information_schema.session_variables where variable_name like 'sql_big_selects';
871
VARIABLE_NAME VARIABLE_VALUE
873
683
set @@sql_big_selects = @old_sql_big_selects;
874
684
set @@sql_notes = 0, @@sql_warnings = 0;
875
685
show variables like 'sql_notes';
876
686
Variable_name Value
878
select * from information_schema.session_variables where variable_name like 'sql_notes';
879
VARIABLE_NAME VARIABLE_VALUE
881
688
show variables like 'sql_warnings';
882
689
Variable_name Value
884
select * from information_schema.session_variables where variable_name like 'sql_warnings';
885
VARIABLE_NAME VARIABLE_VALUE
887
691
set @@sql_notes = 1, @@sql_warnings = 1;
888
692
show variables like 'sql_notes';
889
693
Variable_name Value
891
select * from information_schema.session_variables where variable_name like 'sql_notes';
892
VARIABLE_NAME VARIABLE_VALUE
894
695
show variables like 'sql_warnings';
895
696
Variable_name Value
897
select * from information_schema.session_variables where variable_name like 'sql_warnings';
898
VARIABLE_NAME VARIABLE_VALUE
900
698
select @@system_time_zone;
901
699
@@system_time_zone
910
708
show variables like 'basedir';
911
709
Variable_name Value
913
select * from information_schema.session_variables where variable_name like 'basedir';
914
VARIABLE_NAME VARIABLE_VALUE
916
711
show variables like 'datadir';
917
712
Variable_name Value
919
select * from information_schema.session_variables where variable_name like 'datadir';
920
VARIABLE_NAME VARIABLE_VALUE
922
714
show variables like 'tmpdir';
923
715
Variable_name Value
925
select * from information_schema.session_variables where variable_name like 'tmpdir';
926
VARIABLE_NAME VARIABLE_VALUE
928
select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key;
929
@@ssl_ca @@ssl_capath @@ssl_cert @@ssl_cipher @@ssl_key
931
show variables like 'ssl%';
938
select * from information_schema.session_variables where variable_name like 'ssl%' order by 1;
939
VARIABLE_NAME VARIABLE_VALUE
945
select @@log_queries_not_using_indexes;
946
@@log_queries_not_using_indexes
948
show variables like 'log_queries_not_using_indexes';
950
log_queries_not_using_indexes OFF
951
select * from information_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
952
VARIABLE_NAME VARIABLE_VALUE
953
LOG_QUERIES_NOT_USING_INDEXES OFF
955
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
718
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '""' at line 1
957
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
720
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '&' at line 1
959
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
722
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '@' at line 1
960
723
select @@hostname;
969
732
set global binlog_cache_size =@my_binlog_cache_size;
970
733
set global connect_timeout =@my_connect_timeout;
971
set global delayed_insert_timeout =@my_delayed_insert_timeout;
972
set global delayed_queue_size =@my_delayed_queue_size;
973
734
set global flush =@my_flush;
974
735
set global flush_time =@my_flush_time;
736
ERROR HY000: Unknown system variable 'flush_time'
975
737
set global key_buffer_size =@my_key_buffer_size;
976
738
set global max_binlog_cache_size =default;
977
739
set global max_binlog_size =@my_max_binlog_size;
978
740
set global max_connect_errors =@my_max_connect_errors;
979
741
set global max_connections =@my_max_connections;
980
set global max_delayed_threads =@my_max_delayed_threads;
981
742
set global max_heap_table_size =@my_max_heap_table_size;
982
set global max_insert_delayed_threads=@my_max_insert_delayed_threads;
983
743
set global max_join_size =@my_max_join_size;
984
set global max_user_connections =default;
985
744
set global max_write_lock_count =default;
986
745
set global myisam_data_pointer_size =@my_myisam_data_pointer_size;
746
ERROR HY000: Unknown system variable 'myisam_data_pointer_size'
987
747
set global net_buffer_length =@my_net_buffer_length;
988
748
set global net_write_timeout =@my_net_write_timeout;
989
749
set global net_read_timeout =@my_net_read_timeout;
990
set global query_cache_limit =@my_query_cache_limit;
991
set global query_cache_type =@my_query_cache_type;
992
750
set global rpl_recovery_rank =@my_rpl_recovery_rank;
751
ERROR HY000: Unknown system variable 'rpl_recovery_rank'
993
752
set global server_id =@my_server_id;
994
753
set global slow_launch_time =@my_slow_launch_time;
995
754
set global storage_engine =@my_storage_engine;
996
755
set global thread_cache_size =@my_thread_cache_size;
997
show global variables where Variable_name='table_definition_cache' or
998
Variable_name='table_lock_wait_timeout';
1000
table_definition_cache #
1001
table_lock_wait_timeout #
1004
# -- Bug#34820: log_output can be set to illegal value.
1006
SET GLOBAL log_output = '';
1007
ERROR 42000: Variable 'log_output' can't be set to the value of ''
1008
SET GLOBAL log_output = 0;
1009
ERROR 42000: Variable 'log_output' can't be set to the value of '0'
1011
# -- End of Bug#34820.
756
ERROR HY000: Unknown system variable 'thread_cache_size'