You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
2038 lines
73 KiB
2038 lines
73 KiB
drop table if exists t1,t2;
|
|
SET @default_table_open_cache = @@table_open_cache;
|
|
set @my_log_error_verbosity =@@global.log_error_verbosity;
|
|
set @my_connect_timeout =@@global.connect_timeout;
|
|
set @my_delayed_insert_timeout =@@global.delayed_insert_timeout;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_insert_timeout' is deprecated and will be removed in a future release.
|
|
set @my_delayed_queue_size =@@global.delayed_queue_size;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_queue_size' is deprecated and will be removed in a future release.
|
|
set @my_flush =@@global.flush;
|
|
set @my_flush_time =@@global.flush_time;
|
|
set @my_key_buffer_size =@@global.key_buffer_size;
|
|
set @my_max_binlog_cache_size =@@global.max_binlog_cache_size;
|
|
set @my_binlog_cache_size =@@global.binlog_cache_size;
|
|
set @my_max_binlog_size =@@global.max_binlog_size;
|
|
set @my_max_connect_errors =@@global.max_connect_errors;
|
|
set @my_max_connections =@@global.max_connections;
|
|
set @my_max_delayed_threads =@@global.max_delayed_threads;
|
|
Warnings:
|
|
Warning 1287 '@@max_delayed_threads' is deprecated and will be removed in a future release.
|
|
set @my_max_heap_table_size =@@global.max_heap_table_size;
|
|
set @my_max_insert_delayed_threads=@@global.max_insert_delayed_threads;
|
|
Warnings:
|
|
Warning 1287 '@@max_insert_delayed_threads' is deprecated and will be removed in a future release.
|
|
set @my_max_join_size =@@global.max_join_size;
|
|
set @my_myisam_data_pointer_size =@@global.myisam_data_pointer_size;
|
|
set @my_myisam_max_sort_file_size =@@global.myisam_max_sort_file_size;
|
|
set @my_net_buffer_length =@@global.net_buffer_length;
|
|
set @my_net_write_timeout =@@global.net_write_timeout;
|
|
set @my_net_read_timeout =@@global.net_read_timeout;
|
|
set @my_server_id =@@global.server_id;
|
|
set @my_slow_launch_time =@@global.slow_launch_time;
|
|
set @my_storage_engine =@@global.default_storage_engine;
|
|
set @my_max_allowed_packet =@@global.max_allowed_packet;
|
|
set @my_join_buffer_size =@@global.join_buffer_size;
|
|
set @`test`=1;
|
|
select @test, @`test`, @TEST, @`TEST`, @"teSt";
|
|
@test @`test` @TEST @`TEST` @"teSt"
|
|
1 1 1 1 1
|
|
set @TEST=2;
|
|
select @test, @`test`, @TEST, @`TEST`, @"teSt";
|
|
@test @`test` @TEST @`TEST` @"teSt"
|
|
2 2 2 2 2
|
|
set @"tEST"=3;
|
|
select @test, @`test`, @TEST, @`TEST`, @"teSt";
|
|
@test @`test` @TEST @`TEST` @"teSt"
|
|
3 3 3 3 3
|
|
set @`TeST`=4;
|
|
select @test, @`test`, @TEST, @`TEST`, @"teSt";
|
|
@test @`test` @TEST @`TEST` @"teSt"
|
|
4 4 4 4 4
|
|
select @`teST`:=5;
|
|
@`teST`:=5
|
|
5
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
select @test, @`test`, @TEST, @`TEST`, @"teSt";
|
|
@test @`test` @TEST @`TEST` @"teSt"
|
|
5 5 5 5 5
|
|
set @select=2,@t5=1.23456;
|
|
select @`select`,@not_used;
|
|
@`select` @not_used
|
|
2 NULL
|
|
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;
|
|
@test_int @test_double @test_string @test_string2 @select
|
|
10 0.0000000001 abcdeghi abcdefghij NULL
|
|
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
|
|
select @test_int,@test_double,@test_string,@test_string2;
|
|
@test_int @test_double @test_string @test_string2
|
|
hello hello hello hello
|
|
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
|
|
select @test_int,@test_double,@test_string,@test_string2;
|
|
@test_int @test_double @test_string @test_string2
|
|
hellohello hellohello hellohello hellohello
|
|
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
|
|
select @test_int,@test_double,@test_string,@test_string2;
|
|
@test_int @test_double @test_string @test_string2
|
|
NULL NULL NULL NULL
|
|
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
|
|
@t1:=(@t2:=1)+@t3:=4 @t1 @t2 @t3
|
|
5 5 1 4
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
explain select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
Note 1003 /* select#1 */ select (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@`t1`) AS `@t1`,(@`t2`) AS `@t2`,(@`t3`) AS `@t3`
|
|
select @t5;
|
|
@t5
|
|
1.23456
|
|
CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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;
|
|
@min_cid:=min(c_id) @max_cid:=max(c_id)
|
|
1 4
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
|
|
c_id c_name c_country
|
|
1 Bozo USA
|
|
4 Mr. Floppy GB
|
|
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
|
|
c_id c_name c_country
|
|
1 Bozo USA
|
|
4 Mr. Floppy GB
|
|
ALTER TABLE t1 DROP PRIMARY KEY;
|
|
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
|
|
c_id c_name c_country
|
|
1 Bozo USA
|
|
4 Mr. Floppy GB
|
|
drop table t1;
|
|
set GLOBAL max_join_size=10;
|
|
set max_join_size=100;
|
|
set max_join_size=1000;
|
|
show variables like 'max_join_size';
|
|
Variable_name Value
|
|
max_join_size 1000
|
|
set max_join_size=100;
|
|
SET SQL_BIG_SELECTS=1;
|
|
select * from performance_schema.session_variables where variable_name like 'max_join_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
max_join_size 100
|
|
show global variables like 'max_join_size';
|
|
Variable_name Value
|
|
max_join_size 10
|
|
select * from performance_schema.global_variables where variable_name like 'max_join_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
max_join_size 10
|
|
set GLOBAL max_join_size=2000;
|
|
show global variables like 'max_join_size';
|
|
Variable_name Value
|
|
max_join_size 2000
|
|
select * from performance_schema.global_variables where variable_name like 'max_join_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
max_join_size 2000
|
|
set max_join_size=DEFAULT;
|
|
show variables like 'max_join_size';
|
|
Variable_name Value
|
|
max_join_size 2000
|
|
select * from performance_schema.session_variables where variable_name like 'max_join_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
max_join_size 2000
|
|
set GLOBAL max_join_size=DEFAULT;
|
|
show global variables like 'max_join_size';
|
|
Variable_name Value
|
|
max_join_size HA_POS_ERROR
|
|
select * from performance_schema.global_variables where variable_name like 'max_join_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
max_join_size HA_POS_ERROR
|
|
set @@max_join_size=1000, @@global.max_join_size=2000;
|
|
select @@local.max_join_size, @@global.max_join_size;
|
|
@@local.max_join_size @@global.max_join_size
|
|
1000 2000
|
|
select @@identity, length(@@version)>0;
|
|
@@identity length(@@version)>0
|
|
0 1
|
|
select @@VERSION=version();
|
|
@@VERSION=version()
|
|
1
|
|
select last_insert_id(345);
|
|
last_insert_id(345)
|
|
345
|
|
explain select last_insert_id(345);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select last_insert_id(345) AS `last_insert_id(345)`
|
|
select @@IDENTITY,last_insert_id(), @@identity;
|
|
@@IDENTITY last_insert_id() @@identity
|
|
345 345 345
|
|
explain select @@IDENTITY,last_insert_id(), @@identity;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select @@IDENTITY AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,@@identity AS `@@identity`
|
|
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
|
|
set global concurrent_insert=2;
|
|
show variables like 'concurrent_insert';
|
|
Variable_name Value
|
|
concurrent_insert ALWAYS
|
|
select * from performance_schema.session_variables where variable_name like 'concurrent_insert';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert ALWAYS
|
|
set global concurrent_insert=1;
|
|
show variables like 'concurrent_insert';
|
|
Variable_name Value
|
|
concurrent_insert AUTO
|
|
select * from performance_schema.session_variables where variable_name like 'concurrent_insert';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert AUTO
|
|
set global concurrent_insert=0;
|
|
show variables like 'concurrent_insert';
|
|
Variable_name Value
|
|
concurrent_insert NEVER
|
|
select * from performance_schema.session_variables where variable_name like 'concurrent_insert';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert NEVER
|
|
set global concurrent_insert=DEFAULT;
|
|
select @@concurrent_insert;
|
|
@@concurrent_insert
|
|
AUTO
|
|
set default_storage_engine=MYISAM, default_storage_engine="HEAP", global default_storage_engine="MERGE";
|
|
show local variables like 'default_storage_engine';
|
|
Variable_name Value
|
|
default_storage_engine MEMORY
|
|
select * from performance_schema.session_variables where variable_name like 'default_storage_engine';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
default_storage_engine MEMORY
|
|
show global variables like 'default_storage_engine';
|
|
Variable_name Value
|
|
default_storage_engine MRG_MYISAM
|
|
select * from performance_schema.global_variables where variable_name like 'default_storage_engine';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
default_storage_engine MRG_MYISAM
|
|
set GLOBAL myisam_max_sort_file_size=2000000;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect myisam_max_sort_file_size value: '2000000'
|
|
show global variables like 'myisam_max_sort_file_size';
|
|
Variable_name Value
|
|
myisam_max_sort_file_size 1048576
|
|
select * from performance_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
myisam_max_sort_file_size 1048576
|
|
set GLOBAL myisam_max_sort_file_size=default;
|
|
show global variables like 'myisam_max_sort_file_size';
|
|
Variable_name Value
|
|
myisam_max_sort_file_size FILE_SIZE
|
|
select * from performance_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
myisam_max_sort_file_size FILE_SIZE
|
|
set global net_retry_count=10, session net_retry_count=10;
|
|
set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
|
|
show global variables like 'net_%';
|
|
Variable_name Value
|
|
net_buffer_length 1024
|
|
net_read_timeout 300
|
|
net_retry_count 10
|
|
net_write_timeout 200
|
|
select * from performance_schema.global_variables where variable_name like 'net_%' order by 1;
|
|
VARIABLE_NAME VARIABLE_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 16384
|
|
net_read_timeout 30
|
|
net_retry_count 10
|
|
net_write_timeout 60
|
|
select * from performance_schema.session_variables where variable_name like 'net_%' order by 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
net_buffer_length 16384
|
|
net_read_timeout 30
|
|
net_retry_count 10
|
|
net_write_timeout 60
|
|
set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect net_buffer_length value: '8000'
|
|
show global variables like 'net_%';
|
|
Variable_name Value
|
|
net_buffer_length 7168
|
|
net_read_timeout 900
|
|
net_retry_count 10
|
|
net_write_timeout 1000
|
|
select * from performance_schema.global_variables where variable_name like 'net_%' order by 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
net_buffer_length 7168
|
|
net_read_timeout 900
|
|
net_retry_count 10
|
|
net_write_timeout 1000
|
|
set global net_buffer_length=1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect net_buffer_length value: '1'
|
|
show global variables like 'net_buffer_length';
|
|
Variable_name Value
|
|
net_buffer_length 1024
|
|
select * from performance_schema.global_variables where variable_name like 'net_buffer_length';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
net_buffer_length 1024
|
|
set global net_buffer_length=2000000000;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect net_buffer_length value: '2000000000'
|
|
show global variables like 'net_buffer_length';
|
|
Variable_name Value
|
|
net_buffer_length 1048576
|
|
select * from performance_schema.global_variables where variable_name like 'net_buffer_length';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
net_buffer_length 1048576
|
|
set character set cp1251_koi8;
|
|
show variables like "character_set_client";
|
|
Variable_name Value
|
|
character_set_client cp1251
|
|
select * from performance_schema.session_variables where variable_name like 'character_set_client';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
character_set_client cp1251
|
|
select @@timestamp>0;
|
|
@@timestamp>0
|
|
1
|
|
set @@rand_seed1=10000000,@@rand_seed2=1000000;
|
|
select ROUND(RAND(),5);
|
|
ROUND(RAND(),5)
|
|
0.02887
|
|
|
|
==+ Testing %alloc% system variables +==
|
|
==+ NOTE: These values *must* be a multiple of 1024 +==
|
|
==+ Other values will be rounded down to nearest multiple +==
|
|
|
|
==+ Show initial values +==
|
|
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size');
|
|
Variable_name Value
|
|
query_alloc_block_size 8192
|
|
query_prealloc_size 8192
|
|
range_alloc_block_size 4096
|
|
transaction_alloc_block_size 8192
|
|
transaction_prealloc_size 4096
|
|
==+ performance_schema data +==
|
|
SELECT * FROM performance_schema.session_variables
|
|
WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
query_alloc_block_size 8192
|
|
query_prealloc_size 8192
|
|
range_alloc_block_size 4096
|
|
transaction_alloc_block_size 8192
|
|
transaction_prealloc_size 4096
|
|
Testing values that are multiples of 1024
|
|
set @@range_alloc_block_size=1024*15+1024;
|
|
set @@query_alloc_block_size=1024*15+1024*2;
|
|
set @@query_prealloc_size=1024*18-1024;
|
|
set @@transaction_alloc_block_size=1024*21-1024*1;
|
|
set @@transaction_prealloc_size=1024*21-2048;
|
|
==+ Check manipulated values ==+
|
|
select @@query_alloc_block_size;
|
|
@@query_alloc_block_size
|
|
17408
|
|
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size');
|
|
Variable_name Value
|
|
query_alloc_block_size 17408
|
|
query_prealloc_size 17408
|
|
range_alloc_block_size 16384
|
|
transaction_alloc_block_size 20480
|
|
transaction_prealloc_size 19456
|
|
==+ performance_schema data +==
|
|
SELECT * FROM performance_schema.session_variables
|
|
WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
query_alloc_block_size 17408
|
|
query_prealloc_size 17408
|
|
range_alloc_block_size 16384
|
|
transaction_alloc_block_size 20480
|
|
transaction_prealloc_size 19456
|
|
==+ Manipulate variable values +==
|
|
Testing values that are not 1024 multiples
|
|
set @@range_alloc_block_size=1024*16+1023;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect range_alloc_block_size value: '17407'
|
|
set @@query_alloc_block_size=1024*17+2;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect query_alloc_block_size value: '17410'
|
|
set @@query_prealloc_size=1024*18-1023;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect query_prealloc_size value: '17409'
|
|
set @@transaction_alloc_block_size=1024*20-1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect transaction_alloc_block_size value: '20479'
|
|
set @@transaction_prealloc_size=1024*21-1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect transaction_prealloc_size value: '21503'
|
|
==+ Check manipulated values ==+
|
|
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size');
|
|
Variable_name Value
|
|
query_alloc_block_size 17408
|
|
query_prealloc_size 17408
|
|
range_alloc_block_size 16384
|
|
transaction_alloc_block_size 19456
|
|
transaction_prealloc_size 20480
|
|
==+ performance_schema data +==
|
|
SELECT * FROM performance_schema.session_variables
|
|
WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size') ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
query_alloc_block_size 17408
|
|
query_prealloc_size 17408
|
|
range_alloc_block_size 16384
|
|
transaction_alloc_block_size 19456
|
|
transaction_prealloc_size 20480
|
|
==+ Set values back to the default values +==
|
|
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;
|
|
==+ Check the values now that they are reset +==
|
|
SHOW VARIABLES WHERE variable_name IN ('range_alloc_block_size',
|
|
'query_alloc_block_size', 'query_prealloc_size',
|
|
'transaction_alloc_block_size', 'transaction_prealloc_size');
|
|
Variable_name Value
|
|
query_alloc_block_size 8192
|
|
query_prealloc_size 8192
|
|
range_alloc_block_size 4096
|
|
transaction_alloc_block_size 8192
|
|
transaction_prealloc_size 4096
|
|
SELECT @@version LIKE 'non-existent';
|
|
@@version LIKE 'non-existent'
|
|
0
|
|
SELECT @@version_compile_os LIKE 'non-existent';
|
|
@@version_compile_os LIKE 'non-existent'
|
|
0
|
|
set big_tables=OFFF;
|
|
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
|
|
set big_tables="OFFF";
|
|
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
|
|
set unknown_variable=1;
|
|
ERROR HY000: Unknown system variable 'unknown_variable'
|
|
set max_join_size="hello";
|
|
ERROR 42000: Incorrect argument type to variable 'max_join_size'
|
|
set default_storage_engine=UNKNOWN_TABLE_TYPE;
|
|
ERROR 42000: Unknown storage engine 'UNKNOWN_TABLE_TYPE'
|
|
set default_storage_engine=MERGE, big_tables=2;
|
|
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
|
|
show local variables like 'default_storage_engine';
|
|
Variable_name Value
|
|
default_storage_engine MEMORY
|
|
set character_set_client=UNKNOWN_CHARACTER_SET;
|
|
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
|
|
set collation_connection=UNKNOWN_COLLATION;
|
|
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
|
|
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'
|
|
select @@global.timestamp;
|
|
ERROR HY000: Variable 'timestamp' is a SESSION variable
|
|
set @@version='';
|
|
ERROR HY000: Variable 'version' is a read only variable
|
|
set @@concurrent_insert=1;
|
|
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
|
|
set myisam_max_sort_file_size=100;
|
|
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
|
|
set @@SQL_WARNINGS=NULL;
|
|
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
|
|
set autocommit=1;
|
|
set big_tables=1;
|
|
select @@autocommit, @@big_tables;
|
|
@@autocommit @@big_tables
|
|
1 1
|
|
set global binlog_cache_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect binlog_cache_size value: '100'
|
|
set bulk_insert_buffer_size=100;
|
|
set character set cp1251_koi8;
|
|
set character set default;
|
|
set @@global.concurrent_insert=1;
|
|
set global connect_timeout=100;
|
|
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
|
|
set global delay_key_write=1;
|
|
select @@delay_key_write;
|
|
@@delay_key_write
|
|
ON
|
|
set global delayed_insert_limit=100;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_insert_limit' is deprecated and will be removed in a future release.
|
|
set global delayed_insert_timeout=100;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_insert_timeout' is deprecated and will be removed in a future release.
|
|
set global delayed_queue_size=100;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_queue_size' is deprecated and will be removed in a future release.
|
|
set global flush=1;
|
|
set global flush_time=100;
|
|
set insert_id=1;
|
|
set interactive_timeout=100;
|
|
set join_buffer_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect join_buffer_size value: '100'
|
|
set last_insert_id=1;
|
|
set global local_infile=0;
|
|
set long_query_time=0.000001;
|
|
select @@long_query_time;
|
|
@@long_query_time
|
|
0.000001
|
|
set long_query_time=100.000001;
|
|
select @@long_query_time;
|
|
@@long_query_time
|
|
100.000001
|
|
set low_priority_updates=1;
|
|
set global max_allowed_packet=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_allowed_packet value: '100'
|
|
Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
|
|
set global max_binlog_cache_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_binlog_cache_size value: '100'
|
|
set global max_binlog_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_binlog_size value: '100'
|
|
set global max_connect_errors=100;
|
|
set global max_connections=100;
|
|
set global max_delayed_threads=100;
|
|
Warnings:
|
|
Warning 1287 '@@max_delayed_threads' is deprecated and will be removed in a future release.
|
|
set max_heap_table_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_heap_table_size value: '100'
|
|
set max_join_size=100;
|
|
set max_sort_length=100;
|
|
set global max_user_connections=100;
|
|
select @@max_user_connections;
|
|
@@max_user_connections
|
|
100
|
|
set global max_write_lock_count=100;
|
|
set myisam_sort_buffer_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '100'
|
|
set global net_buffer_length=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect net_buffer_length value: '100'
|
|
set net_read_timeout=100;
|
|
set net_write_timeout=100;
|
|
set read_buffer_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect read_buffer_size value: '100'
|
|
set read_rnd_buffer_size=100;
|
|
set global server_id=100;
|
|
set global slow_launch_time=100;
|
|
set sort_buffer_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect sort_buffer_size value: '100'
|
|
set @@max_sp_recursion_depth=10;
|
|
select @@max_sp_recursion_depth;
|
|
@@max_sp_recursion_depth
|
|
10
|
|
set @@max_sp_recursion_depth=0;
|
|
select @@max_sp_recursion_depth;
|
|
@@max_sp_recursion_depth
|
|
0
|
|
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_buffer_result=1;
|
|
set sql_log_bin=1;
|
|
set sql_log_off=1;
|
|
set sql_quote_show_create=1;
|
|
set sql_safe_updates=1;
|
|
set sql_select_limit=1;
|
|
set sql_select_limit=default;
|
|
set sql_warnings=1;
|
|
set global table_open_cache=100;
|
|
set default_storage_engine=myisam;
|
|
set timestamp=1, timestamp=default;
|
|
set tmp_table_size=100;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect tmp_table_size value: '100'
|
|
set transaction_isolation="READ-COMMITTED";
|
|
set wait_timeout=100;
|
|
set global log_error_verbosity=2;
|
|
set sort_buffer_size=default;
|
|
set global max_allowed_packet =@my_max_allowed_packet;
|
|
select @@session.insert_id;
|
|
@@session.insert_id
|
|
1
|
|
set @save_insert_id=@@session.insert_id;
|
|
set session insert_id=20;
|
|
select @@session.insert_id;
|
|
@@session.insert_id
|
|
20
|
|
set session last_insert_id=100;
|
|
select @@session.insert_id;
|
|
@@session.insert_id
|
|
20
|
|
select @@session.last_insert_id;
|
|
@@session.last_insert_id
|
|
100
|
|
select @@session.insert_id;
|
|
@@session.insert_id
|
|
20
|
|
set @@session.insert_id=@save_insert_id;
|
|
select @@session.insert_id;
|
|
@@session.insert_id
|
|
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;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect key_buffer_size value: '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
|
|
select max(a) +1, max(a) +2 into @xx,@yy from t1;
|
|
drop table t1,t2;
|
|
select @@xxxxxxxxxx;
|
|
ERROR HY000: Unknown system variable 'xxxxxxxxxx'
|
|
select 1;
|
|
1
|
|
1
|
|
select @@session.key_buffer_size;
|
|
ERROR HY000: Variable 'key_buffer_size' is a GLOBAL variable
|
|
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 ''
|
|
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 ''
|
|
set global myisam_max_sort_file_size=4294967296;
|
|
show global variables like 'myisam_max_sort_file_size';
|
|
Variable_name Value
|
|
myisam_max_sort_file_size MAX_FILE_SIZE
|
|
select * from performance_schema.global_variables where variable_name like 'myisam_max_sort_file_size';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
myisam_max_sort_file_size MAX_FILE_SIZE
|
|
set global myisam_max_sort_file_size=default;
|
|
select @@global.max_user_connections,@@local.max_join_size;
|
|
@@global.max_user_connections @@local.max_join_size
|
|
100 100
|
|
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
|
|
select @@global.max_user_connections,@@local.max_join_size;
|
|
@@global.max_user_connections @@local.max_join_size
|
|
100 100
|
|
set @@global.max_user_connections=111,@@local.max_join_size=222;
|
|
select @@global.max_user_connections,@@local.max_join_size;
|
|
@@global.max_user_connections @@local.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 @@local.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 @@local.max_join_size
|
|
100 100
|
|
set @a=1, @b=2;
|
|
set @a=@b, @b=@a;
|
|
select @a, @b;
|
|
@a @b
|
|
2 1
|
|
set @@global.global.key_buffer_size= 1;
|
|
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 'global.key_buffer_size= 1' at line 1
|
|
set GLOBAL global.key_buffer_size= 1;
|
|
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 'global.key_buffer_size= 1' at line 1
|
|
SELECT @@global.global.key_buffer_size;
|
|
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 'global.key_buffer_size' at line 1
|
|
SELECT @@global.session.key_buffer_size;
|
|
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 'session.key_buffer_size' at line 1
|
|
SELECT @@global.local.key_buffer_size;
|
|
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 'local.key_buffer_size' at line 1
|
|
create table t1 (
|
|
c1 tinyint,
|
|
c2 smallint,
|
|
c3 mediumint,
|
|
c4 int,
|
|
c5 bigint);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` tinyint(4) DEFAULT NULL,
|
|
`c2` smallint(6) DEFAULT NULL,
|
|
`c3` mediumint(9) DEFAULT NULL,
|
|
`c4` int(11) DEFAULT NULL,
|
|
`c5` bigint(20) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
set @arg00= 8, @arg01= 8.8, @arg02= 'a string', @arg03= 0.2e0;
|
|
create table t1 as select @arg00 as c1, @arg01 as c2, @arg02 as c3, @arg03 as c4;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` bigint(20) DEFAULT NULL,
|
|
`c2` decimal(65,30) DEFAULT NULL,
|
|
`c3` longtext,
|
|
`c4` double DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
SET GLOBAL MYISAM_DATA_POINTER_SIZE= 7;
|
|
SHOW VARIABLES LIKE 'MYISAM_DATA_POINTER_SIZE';
|
|
Variable_name Value
|
|
myisam_data_pointer_size 7
|
|
SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'MYISAM_DATA_POINTER_SIZE';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
myisam_data_pointer_size 7
|
|
SET GLOBAL table_open_cache=-1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect table_open_cache value: '-1'
|
|
SHOW VARIABLES LIKE 'table_open_cache';
|
|
Variable_name Value
|
|
table_open_cache 1
|
|
SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'table_open_cache';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
table_open_cache 1
|
|
SET GLOBAL table_open_cache= @default_table_open_cache;
|
|
set character_set_results=NULL;
|
|
select ifnull(@@character_set_results,"really null");
|
|
ifnull(@@character_set_results,"really null")
|
|
really null
|
|
set names latin1;
|
|
*** Various tests with LC_TIME_NAMES
|
|
*** LC_TIME_NAMES: testing case insensitivity
|
|
set @@lc_time_names='ru_ru';
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
ru_RU
|
|
*** LC_TIME_NAMES: testing with a user variable
|
|
set @lc='JA_JP';
|
|
set @@lc_time_names=@lc;
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
ja_JP
|
|
*** LC_TIME_NAMES: testing with string expressions
|
|
set lc_time_names=concat('de','_','DE');
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
de_DE
|
|
set lc_time_names=concat('de','+','DE');
|
|
ERROR HY000: Unknown locale: 'de+DE'
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
de_DE
|
|
LC_TIME_NAMES: testing with numeric expressions
|
|
set @@lc_time_names=1+2;
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
sv_SE
|
|
set @@lc_time_names=1/0;
|
|
ERROR 42000: Incorrect argument type to variable 'lc_time_names'
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
sv_SE
|
|
set lc_time_names=en_US;
|
|
LC_TIME_NAMES: testing NULL and a negative number:
|
|
set lc_time_names=NULL;
|
|
ERROR 42000: Variable 'lc_time_names' can't be set to the value of 'NULL'
|
|
set lc_time_names=-1;
|
|
ERROR HY000: Unknown locale: '-1'
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
en_US
|
|
LC_TIME_NAMES: testing locale with the last ID:
|
|
set lc_time_names=110;
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
rm_CH
|
|
LC_TIME_NAMES: testing a number beyond the valid ID range:
|
|
set lc_time_names=111;
|
|
ERROR HY000: Unknown locale: '111'
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
rm_CH
|
|
LC_TIME_NAMES: testing that 0 is en_US:
|
|
set lc_time_names=0;
|
|
select @@lc_time_names;
|
|
@@lc_time_names
|
|
en_US
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
en_US en_US
|
|
set @@global.lc_time_names=fr_FR;
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
fr_FR en_US
|
|
New connection
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
fr_FR fr_FR
|
|
set @@lc_time_names=ru_RU;
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
fr_FR ru_RU
|
|
Returnung to default connection
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
fr_FR en_US
|
|
set lc_time_names=default;
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
fr_FR fr_FR
|
|
set @@global.lc_time_names=default;
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
en_US fr_FR
|
|
set @@lc_time_names=default;
|
|
select @@global.lc_time_names, @@lc_time_names;
|
|
@@global.lc_time_names @@lc_time_names
|
|
en_US en_US
|
|
set @test = @@query_prealloc_size;
|
|
set @@query_prealloc_size = @test;
|
|
select @@query_prealloc_size = @test;
|
|
@@query_prealloc_size = @test
|
|
1
|
|
set global sql_mode=repeat('a',80);
|
|
ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
|
|
End of 4.1 tests
|
|
create table t1 (a int);
|
|
select a into @x from t1;
|
|
Warnings:
|
|
Warning 1329 No data - zero rows fetched, selected, or processed
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1329 No data - zero rows fetched, selected, or processed
|
|
drop table t1;
|
|
set @@warning_count=1;
|
|
ERROR HY000: Variable 'warning_count' is a read only variable
|
|
set @@global.error_count=1;
|
|
ERROR HY000: Variable 'error_count' is a read only variable
|
|
set @@max_heap_table_size= 4294967296;
|
|
select @@max_heap_table_size > 0;
|
|
@@max_heap_table_size > 0
|
|
1
|
|
set global max_heap_table_size= 4294967296;
|
|
select @@max_heap_table_size > 0;
|
|
@@max_heap_table_size > 0
|
|
1
|
|
set @@max_heap_table_size= 4294967296;
|
|
select @@max_heap_table_size > 0;
|
|
@@max_heap_table_size > 0
|
|
1
|
|
select @@character_set_system;
|
|
@@character_set_system
|
|
utf8
|
|
set global character_set_system = latin1;
|
|
ERROR HY000: Variable 'character_set_system' is a read only variable
|
|
set @@global.version_compile_os='234';
|
|
ERROR HY000: Variable 'version_compile_os' is a read only variable
|
|
set character_set_filesystem=latin1;
|
|
select @@character_set_filesystem;
|
|
@@character_set_filesystem
|
|
latin1
|
|
set @@global.character_set_filesystem=latin2;
|
|
set character_set_filesystem=latin1;
|
|
select @@character_set_filesystem;
|
|
@@character_set_filesystem
|
|
latin1
|
|
set @@global.character_set_filesystem=latin2;
|
|
set character_set_filesystem=default;
|
|
select @@character_set_filesystem;
|
|
@@character_set_filesystem
|
|
latin2
|
|
set @@global.character_set_filesystem=default;
|
|
select @@global.character_set_filesystem;
|
|
@@global.character_set_filesystem
|
|
binary
|
|
set @old_sql_big_selects = @@sql_big_selects;
|
|
set @@sql_big_selects = 1;
|
|
show variables like 'sql_big_selects';
|
|
Variable_name Value
|
|
sql_big_selects ON
|
|
select * from performance_schema.session_variables where variable_name like 'sql_big_selects';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
sql_big_selects ON
|
|
set @@sql_big_selects = @old_sql_big_selects;
|
|
set @@sql_notes = 0, @@sql_warnings = 0;
|
|
show variables like 'sql_notes';
|
|
Variable_name Value
|
|
sql_notes OFF
|
|
select * from performance_schema.session_variables where variable_name like 'sql_notes';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
sql_notes OFF
|
|
show variables like 'sql_warnings';
|
|
Variable_name Value
|
|
sql_warnings OFF
|
|
select * from performance_schema.session_variables where variable_name like 'sql_warnings';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
sql_warnings OFF
|
|
set @@sql_notes = 1, @@sql_warnings = 1;
|
|
show variables like 'sql_notes';
|
|
Variable_name Value
|
|
sql_notes ON
|
|
select * from performance_schema.session_variables where variable_name like 'sql_notes';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
sql_notes ON
|
|
show variables like 'sql_warnings';
|
|
Variable_name Value
|
|
sql_warnings ON
|
|
select * from performance_schema.session_variables where variable_name like 'sql_warnings';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
sql_warnings ON
|
|
select @@system_time_zone;
|
|
@@system_time_zone
|
|
#
|
|
select @@version, @@version_comment, @@version_compile_machine,
|
|
@@version_compile_os;
|
|
@@version @@version_comment @@version_compile_machine @@version_compile_os
|
|
# # # #
|
|
SET SQL_BIG_SELECTS=1;
|
|
select @@basedir, @@datadir, @@tmpdir;
|
|
@@basedir @@datadir @@tmpdir
|
|
# # #
|
|
show variables like 'basedir';
|
|
Variable_name Value
|
|
basedir #
|
|
select * from performance_schema.session_variables where variable_name like 'basedir';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
basedir #
|
|
show variables like 'datadir';
|
|
Variable_name Value
|
|
datadir #
|
|
select * from performance_schema.session_variables where variable_name like 'datadir';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
datadir #
|
|
show variables like 'tmpdir';
|
|
Variable_name Value
|
|
tmpdir #
|
|
select * from performance_schema.session_variables where variable_name like 'tmpdir';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
tmpdir #
|
|
select @@ssl_ca, @@ssl_capath, @@ssl_cert, @@ssl_cipher, @@ssl_key;
|
|
@@ssl_ca @@ssl_capath @@ssl_cert @@ssl_cipher @@ssl_key
|
|
# # # # #
|
|
show variables like 'ssl%';
|
|
Variable_name Value
|
|
ssl_ca #
|
|
ssl_capath #
|
|
ssl_cert #
|
|
ssl_cipher #
|
|
ssl_crl #
|
|
ssl_crlpath #
|
|
ssl_fips_mode #
|
|
ssl_key #
|
|
select * from performance_schema.session_variables where variable_name like 'ssl%' order by 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
ssl_ca #
|
|
ssl_capath #
|
|
ssl_cert #
|
|
ssl_cipher #
|
|
ssl_crl #
|
|
ssl_crlpath #
|
|
ssl_fips_mode #
|
|
ssl_key #
|
|
SET SQL_BIG_SELECTS=0;
|
|
select @@log_queries_not_using_indexes;
|
|
@@log_queries_not_using_indexes
|
|
0
|
|
show variables like 'log_queries_not_using_indexes';
|
|
Variable_name Value
|
|
log_queries_not_using_indexes OFF
|
|
select * from performance_schema.session_variables where variable_name like 'log_queries_not_using_indexes';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
log_queries_not_using_indexes OFF
|
|
select @@"";
|
|
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
|
|
select @@&;
|
|
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
|
|
select @@@;
|
|
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
|
|
select @@hostname;
|
|
@@hostname
|
|
#
|
|
set @@hostname= "anothername";
|
|
ERROR HY000: Variable 'hostname' is a read only variable
|
|
show variables like 'hostname';
|
|
Variable_name Value
|
|
hostname #
|
|
#
|
|
# BUG#37408 - Compressed MyISAM files should not require/use mmap()
|
|
#
|
|
# Test 'myisam_mmap_size' option is not dynamic
|
|
SET @@myisam_mmap_size= 500M;
|
|
ERROR HY000: Variable 'myisam_mmap_size' is a read only variable
|
|
#
|
|
# Bug #52315: utc_date() crashes when system time > year 2037
|
|
#
|
|
SET TIMESTAMP=2*1024*1024*1024;
|
|
#Should not crash
|
|
SELECT UTC_DATE();
|
|
SET TIMESTAMP=DEFAULT;
|
|
End of 5.0 tests
|
|
set join_buffer_size=1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect join_buffer_size value: '1'
|
|
set @save_join_buffer_size=@@join_buffer_size;
|
|
set join_buffer_size=@save_join_buffer_size;
|
|
set global connect_timeout =@my_connect_timeout;
|
|
set global delayed_insert_timeout =@my_delayed_insert_timeout;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_insert_timeout' is deprecated and will be removed in a future release.
|
|
set global delayed_queue_size =@my_delayed_queue_size;
|
|
Warnings:
|
|
Warning 1287 '@@delayed_queue_size' is deprecated and will be removed in a future release.
|
|
set global flush =@my_flush;
|
|
set global flush_time =@my_flush_time;
|
|
set global key_buffer_size =@my_key_buffer_size;
|
|
set global max_binlog_cache_size =@my_max_binlog_cache_size;
|
|
set global binlog_cache_size =@my_binlog_cache_size;
|
|
set global max_binlog_size =@my_max_binlog_size;
|
|
set global max_connect_errors =@my_max_connect_errors;
|
|
set global max_connections =@my_max_connections;
|
|
set global max_delayed_threads =@my_max_delayed_threads;
|
|
Warnings:
|
|
Warning 1287 '@@max_delayed_threads' is deprecated and will be removed in a future release.
|
|
set global max_heap_table_size =@my_max_heap_table_size;
|
|
set global max_insert_delayed_threads=@my_max_insert_delayed_threads;
|
|
Warnings:
|
|
Warning 1287 '@@max_insert_delayed_threads' is deprecated and will be removed in a future release.
|
|
set global max_join_size =@my_max_join_size;
|
|
set global max_user_connections =default;
|
|
set global max_write_lock_count =default;
|
|
set global myisam_data_pointer_size =@my_myisam_data_pointer_size;
|
|
set global myisam_max_sort_file_size =@my_myisam_max_sort_file_size;
|
|
set global net_buffer_length =@my_net_buffer_length;
|
|
set global net_write_timeout =@my_net_write_timeout;
|
|
set global net_read_timeout =@my_net_read_timeout;
|
|
set global server_id =@my_server_id;
|
|
set global slow_launch_time =@my_slow_launch_time;
|
|
set global default_storage_engine =@my_storage_engine;
|
|
set global max_allowed_packet =@my_max_allowed_packet;
|
|
set global join_buffer_size =@my_join_buffer_size;
|
|
set global log_error_verbosity =@my_log_error_verbosity;
|
|
show global variables where Variable_name='table_definition_cache';
|
|
Variable_name Value
|
|
table_definition_cache #
|
|
|
|
# --
|
|
# -- Bug#34820: log_output can be set to illegal value.
|
|
# --
|
|
SET GLOBAL log_output = '';
|
|
ERROR 42000: Variable 'log_output' can't be set to the value of ''
|
|
SET GLOBAL log_output = 0;
|
|
ERROR 42000: Variable 'log_output' can't be set to the value of '0'
|
|
|
|
# -- End of Bug#34820.
|
|
|
|
#
|
|
SHOW VARIABLES like 'ft_max_word_len';
|
|
Variable_name Value
|
|
ft_max_word_len 84
|
|
SELECT @@session.ft_max_word_len;
|
|
ERROR HY000: Variable 'ft_max_word_len' is a GLOBAL variable
|
|
SELECT @@global.ft_max_word_len;
|
|
@@global.ft_max_word_len
|
|
84
|
|
SET @@session.ft_max_word_len= 7;
|
|
ERROR HY000: Variable 'ft_max_word_len' is a read only variable
|
|
SET @@global.ft_max_word_len= 7;
|
|
ERROR HY000: Variable 'ft_max_word_len' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'ft_min_word_len';
|
|
Variable_name Value
|
|
ft_min_word_len 4
|
|
SELECT @@session.ft_min_word_len;
|
|
ERROR HY000: Variable 'ft_min_word_len' is a GLOBAL variable
|
|
SELECT @@global.ft_min_word_len;
|
|
@@global.ft_min_word_len
|
|
4
|
|
SET @@session.ft_min_word_len= 7;
|
|
ERROR HY000: Variable 'ft_min_word_len' is a read only variable
|
|
SET @@global.ft_min_word_len= 7;
|
|
ERROR HY000: Variable 'ft_min_word_len' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'ft_query_expansion_limit';
|
|
Variable_name Value
|
|
ft_query_expansion_limit 20
|
|
SELECT @@session.ft_query_expansion_limit;
|
|
ERROR HY000: Variable 'ft_query_expansion_limit' is a GLOBAL variable
|
|
SELECT @@global.ft_query_expansion_limit;
|
|
@@global.ft_query_expansion_limit
|
|
20
|
|
SET @@session.ft_query_expansion_limit= 7;
|
|
ERROR HY000: Variable 'ft_query_expansion_limit' is a read only variable
|
|
SET @@global.ft_query_expansion_limit= 7;
|
|
ERROR HY000: Variable 'ft_query_expansion_limit' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'ft_stopword_file';
|
|
Variable_name Value
|
|
ft_stopword_file (built-in)
|
|
SELECT @@session.ft_stopword_file;
|
|
ERROR HY000: Variable 'ft_stopword_file' is a GLOBAL variable
|
|
SELECT @@global.ft_stopword_file;
|
|
@@global.ft_stopword_file
|
|
(built-in)
|
|
SET @@session.ft_stopword_file= 'x';
|
|
ERROR HY000: Variable 'ft_stopword_file' is a read only variable
|
|
SET @@global.ft_stopword_file= 'x';
|
|
ERROR HY000: Variable 'ft_stopword_file' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'back_log';
|
|
Variable_name Value
|
|
back_log 151
|
|
SELECT @@session.back_log;
|
|
ERROR HY000: Variable 'back_log' is a GLOBAL variable
|
|
SELECT @@global.back_log;
|
|
@@global.back_log
|
|
151
|
|
SET @@session.back_log= 7;
|
|
ERROR HY000: Variable 'back_log' is a read only variable
|
|
SET @@global.back_log= 7;
|
|
ERROR HY000: Variable 'back_log' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'large_files_support';
|
|
Variable_name Value
|
|
large_files_support #
|
|
SELECT @@session.large_files_support;
|
|
ERROR HY000: Variable 'large_files_support' is a GLOBAL variable
|
|
SELECT @@global.large_files_support;
|
|
@@global.large_files_support
|
|
#
|
|
SET @@session.large_files_support= true;
|
|
ERROR HY000: Variable 'large_files_support' is a read only variable
|
|
SET @@global.large_files_support= true;
|
|
ERROR HY000: Variable 'large_files_support' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'character_sets_dir';
|
|
Variable_name Value
|
|
character_sets_dir #
|
|
SELECT @@session.character_sets_dir;
|
|
ERROR HY000: Variable 'character_sets_dir' is a GLOBAL variable
|
|
SELECT @@global.character_sets_dir;
|
|
@@global.character_sets_dir
|
|
#
|
|
SET @@session.character_sets_dir= 'x';
|
|
ERROR HY000: Variable 'character_sets_dir' is a read only variable
|
|
SET @@global.character_sets_dir= 'x';
|
|
ERROR HY000: Variable 'character_sets_dir' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'init_file';
|
|
Variable_name Value
|
|
init_file #
|
|
SELECT @@session.init_file;
|
|
ERROR HY000: Variable 'init_file' is a GLOBAL variable
|
|
SELECT @@global.init_file;
|
|
@@global.init_file
|
|
#
|
|
SET @@session.init_file= 'x';
|
|
ERROR HY000: Variable 'init_file' is a read only variable
|
|
SET @@global.init_file= 'x';
|
|
ERROR HY000: Variable 'init_file' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'lc_messages_dir';
|
|
Variable_name Value
|
|
lc_messages_dir #
|
|
SELECT @@session.lc_messages_dir;
|
|
ERROR HY000: Variable 'lc_messages_dir' is a GLOBAL variable
|
|
SELECT @@global.lc_messages_dir;
|
|
@@global.lc_messages_dir
|
|
#
|
|
SET @@session.lc_messages_dir= 'x';
|
|
ERROR HY000: Variable 'lc_messages_dir' is a read only variable
|
|
SET @@global.lc_messages_dir= 'x';
|
|
ERROR HY000: Variable 'lc_messages_dir' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'large_page_size';
|
|
Variable_name Value
|
|
large_page_size #
|
|
SELECT @@session.large_page_size;
|
|
ERROR HY000: Variable 'large_page_size' is a GLOBAL variable
|
|
SELECT @@global.large_page_size;
|
|
@@global.large_page_size
|
|
#
|
|
SET @@session.large_page_size= 7;
|
|
ERROR HY000: Variable 'large_page_size' is a read only variable
|
|
SET @@global.large_page_size= 7;
|
|
ERROR HY000: Variable 'large_page_size' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'large_pages';
|
|
Variable_name Value
|
|
large_pages #
|
|
SELECT @@session.large_pages;
|
|
ERROR HY000: Variable 'large_pages' is a GLOBAL variable
|
|
SELECT @@global.large_pages;
|
|
@@global.large_pages
|
|
#
|
|
SET @@session.large_pages= true;
|
|
ERROR HY000: Variable 'large_pages' is a read only variable
|
|
SET @@global.large_pages= true;
|
|
ERROR HY000: Variable 'large_pages' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'log_bin';
|
|
Variable_name Value
|
|
log_bin #
|
|
SELECT @@session.log_bin;
|
|
ERROR HY000: Variable 'log_bin' is a GLOBAL variable
|
|
SELECT @@global.log_bin;
|
|
@@global.log_bin
|
|
#
|
|
SET @@session.log_bin= true;
|
|
ERROR HY000: Variable 'log_bin' is a read only variable
|
|
SET @@global.log_bin= true;
|
|
ERROR HY000: Variable 'log_bin' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'log_error';
|
|
Variable_name Value
|
|
log_error #
|
|
SELECT @@session.log_error;
|
|
ERROR HY000: Variable 'log_error' is a GLOBAL variable
|
|
SELECT @@global.log_error;
|
|
@@global.log_error
|
|
#
|
|
SET @@session.log_error= 'x';
|
|
ERROR HY000: Variable 'log_error' is a read only variable
|
|
SET @@global.log_error= 'x';
|
|
ERROR HY000: Variable 'log_error' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'lower_case_file_system';
|
|
Variable_name Value
|
|
lower_case_file_system #
|
|
SELECT @@session.lower_case_file_system;
|
|
ERROR HY000: Variable 'lower_case_file_system' is a GLOBAL variable
|
|
SELECT @@global.lower_case_file_system;
|
|
@@global.lower_case_file_system
|
|
#
|
|
SET @@session.lower_case_file_system= true;
|
|
ERROR HY000: Variable 'lower_case_file_system' is a read only variable
|
|
SET @@global.lower_case_file_system= true;
|
|
ERROR HY000: Variable 'lower_case_file_system' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'lower_case_table_names';
|
|
Variable_name Value
|
|
lower_case_table_names #
|
|
SELECT @@session.lower_case_table_names;
|
|
ERROR HY000: Variable 'lower_case_table_names' is a GLOBAL variable
|
|
SELECT @@global.lower_case_table_names;
|
|
@@global.lower_case_table_names
|
|
#
|
|
SET @@session.lower_case_table_names= 7;
|
|
ERROR HY000: Variable 'lower_case_table_names' is a read only variable
|
|
SET @@global.lower_case_table_names= 7;
|
|
ERROR HY000: Variable 'lower_case_table_names' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'myisam_recover_options';
|
|
Variable_name Value
|
|
myisam_recover_options OFF
|
|
SELECT @@session.myisam_recover_options;
|
|
ERROR HY000: Variable 'myisam_recover_options' is a GLOBAL variable
|
|
SELECT @@global.myisam_recover_options;
|
|
@@global.myisam_recover_options
|
|
OFF
|
|
SET @@session.myisam_recover_options= 'x';
|
|
ERROR HY000: Variable 'myisam_recover_options' is a read only variable
|
|
SET @@global.myisam_recover_options= 'x';
|
|
ERROR HY000: Variable 'myisam_recover_options' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'open_files_limit';
|
|
Variable_name Value
|
|
open_files_limit #
|
|
SELECT @@session.open_files_limit;
|
|
ERROR HY000: Variable 'open_files_limit' is a GLOBAL variable
|
|
SELECT @@global.open_files_limit;
|
|
@@global.open_files_limit
|
|
#
|
|
SET @@session.open_files_limit= 7;
|
|
ERROR HY000: Variable 'open_files_limit' is a read only variable
|
|
SET @@global.open_files_limit= 7;
|
|
ERROR HY000: Variable 'open_files_limit' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'pid_file';
|
|
Variable_name Value
|
|
pid_file #
|
|
SELECT @@session.pid_file;
|
|
ERROR HY000: Variable 'pid_file' is a GLOBAL variable
|
|
SELECT @@global.pid_file;
|
|
@@global.pid_file
|
|
#
|
|
SET @@session.pid_file= 'x';
|
|
ERROR HY000: Variable 'pid_file' is a read only variable
|
|
SET @@global.pid_file= 'x';
|
|
ERROR HY000: Variable 'pid_file' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'plugin_dir';
|
|
Variable_name Value
|
|
plugin_dir #
|
|
SELECT @@session.plugin_dir;
|
|
ERROR HY000: Variable 'plugin_dir' is a GLOBAL variable
|
|
SELECT @@global.plugin_dir;
|
|
@@global.plugin_dir
|
|
#
|
|
SET @@session.plugin_dir= 'x';
|
|
ERROR HY000: Variable 'plugin_dir' is a read only variable
|
|
SET @@global.plugin_dir= 'x';
|
|
ERROR HY000: Variable 'plugin_dir' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'port';
|
|
Variable_name Value
|
|
port #
|
|
SELECT @@session.port;
|
|
ERROR HY000: Variable 'port' is a GLOBAL variable
|
|
SELECT @@global.port;
|
|
@@global.port
|
|
#
|
|
SET @@session.port= 7;
|
|
ERROR HY000: Variable 'port' is a read only variable
|
|
SET @@global.port= 7;
|
|
ERROR HY000: Variable 'port' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'protocol_version';
|
|
Variable_name Value
|
|
protocol_version 10
|
|
SELECT @@session.protocol_version;
|
|
ERROR HY000: Variable 'protocol_version' is a GLOBAL variable
|
|
SELECT @@global.protocol_version;
|
|
@@global.protocol_version
|
|
10
|
|
SET @@session.protocol_version= 7;
|
|
ERROR HY000: Variable 'protocol_version' is a read only variable
|
|
SET @@global.protocol_version= 7;
|
|
ERROR HY000: Variable 'protocol_version' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'skip_external_locking';
|
|
Variable_name Value
|
|
skip_external_locking ON
|
|
SELECT @@session.skip_external_locking;
|
|
ERROR HY000: Variable 'skip_external_locking' is a GLOBAL variable
|
|
SELECT @@global.skip_external_locking;
|
|
@@global.skip_external_locking
|
|
1
|
|
SET @@session.skip_external_locking= true;
|
|
ERROR HY000: Variable 'skip_external_locking' is a read only variable
|
|
SET @@global.skip_external_locking= true;
|
|
ERROR HY000: Variable 'skip_external_locking' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'skip_networking';
|
|
Variable_name Value
|
|
skip_networking OFF
|
|
SELECT @@session.skip_networking;
|
|
ERROR HY000: Variable 'skip_networking' is a GLOBAL variable
|
|
SELECT @@global.skip_networking;
|
|
@@global.skip_networking
|
|
0
|
|
SET @@session.skip_networking= true;
|
|
ERROR HY000: Variable 'skip_networking' is a read only variable
|
|
SET @@global.skip_networking= true;
|
|
ERROR HY000: Variable 'skip_networking' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'skip_show_database';
|
|
Variable_name Value
|
|
skip_show_database OFF
|
|
SELECT @@session.skip_show_database;
|
|
ERROR HY000: Variable 'skip_show_database' is a GLOBAL variable
|
|
SELECT @@global.skip_show_database;
|
|
@@global.skip_show_database
|
|
0
|
|
SET @@session.skip_show_database= true;
|
|
ERROR HY000: Variable 'skip_show_database' is a read only variable
|
|
SET @@global.skip_show_database= true;
|
|
ERROR HY000: Variable 'skip_show_database' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'thread_stack';
|
|
Variable_name Value
|
|
thread_stack #
|
|
SELECT @@session.thread_stack;
|
|
ERROR HY000: Variable 'thread_stack' is a GLOBAL variable
|
|
SELECT @@global.thread_stack;
|
|
@@global.thread_stack
|
|
#
|
|
SET @@session.thread_stack= 7;
|
|
ERROR HY000: Variable 'thread_stack' is a read only variable
|
|
SET @@global.thread_stack= 7;
|
|
ERROR HY000: Variable 'thread_stack' is a read only variable
|
|
SELECT @@global.binlog_expire_logs_seconds INTO @old_seconds;
|
|
SET @@global.binlog_expire_logs_seconds = 0;
|
|
SELECT @@global.expire_logs_days INTO @old_eld;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET GLOBAL expire_logs_days = -1;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
Warning 1292 Truncated incorrect expire_logs_days value: '-1'
|
|
needs to've been adjusted (0)
|
|
SELECT @@global.expire_logs_days;
|
|
@@global.expire_logs_days
|
|
0
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET GLOBAL expire_logs_days = 11;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET @old_mode=@@sql_mode;
|
|
SET SESSION sql_mode = 'TRADITIONAL';
|
|
SET GLOBAL expire_logs_days = 100;
|
|
ERROR 42000: Variable 'expire_logs_days' can't be set to the value of '100'
|
|
needs to be unchanged (11)
|
|
SELECT @@global.expire_logs_days;
|
|
@@global.expire_logs_days
|
|
11
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET SESSION sql_mode = @old_mode;
|
|
SET GLOBAL expire_logs_days = 100;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
Warning 1292 Truncated incorrect expire_logs_days value: '100'
|
|
needs to've been adjusted (99)
|
|
SELECT @@global.expire_logs_days;
|
|
@@global.expire_logs_days
|
|
99
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET GLOBAL expire_logs_days = 11;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET GLOBAL expire_logs_days = 99;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
needs to pass with no warnings (99)
|
|
SELECT @@global.expire_logs_days;
|
|
@@global.expire_logs_days
|
|
99
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET GLOBAL expire_logs_days = @old_eld;
|
|
Warnings:
|
|
Warning 1287 '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
|
|
SET GLOBAL binlog_expire_logs_seconds= @old_seconds;
|
|
SET GLOBAL auto_increment_offset=-1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect auto_increment_offset value: '-1'
|
|
SET GLOBAL auto_increment_offset=0;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect auto_increment_offset value: '0'
|
|
select @@default_storage_engine;
|
|
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
|
|
def @@default_storage_engine 253 6 6 Y 0 31 8
|
|
@@default_storage_engine
|
|
MyISAM
|
|
SET @old_server_id = @@GLOBAL.server_id;
|
|
SET GLOBAL server_id = (1 << 32) - 1;
|
|
SELECT @@GLOBAL.server_id;
|
|
@@GLOBAL.server_id
|
|
4294967295
|
|
SET GLOBAL server_id = (1 << 32);
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect server_id value: '4294967296'
|
|
SELECT @@GLOBAL.server_id;
|
|
@@GLOBAL.server_id
|
|
4294967295
|
|
SET GLOBAL server_id = (1 << 60);
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect server_id value: '1152921504606846976'
|
|
SELECT @@GLOBAL.server_id;
|
|
@@GLOBAL.server_id
|
|
4294967295
|
|
SET GLOBAL server_id = 0;
|
|
SELECT @@GLOBAL.server_id;
|
|
@@GLOBAL.server_id
|
|
0
|
|
SET GLOBAL server_id = -1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect server_id value: '-1'
|
|
SELECT @@GLOBAL.server_id;
|
|
@@GLOBAL.server_id
|
|
0
|
|
SET GLOBAL server_id = @old_server_id;
|
|
SELECT @@GLOBAL.INIT_FILE, @@GLOBAL.INIT_FILE IS NULL;
|
|
@@GLOBAL.INIT_FILE @@GLOBAL.INIT_FILE IS NULL
|
|
NULL 1
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES ();
|
|
SET @bug42778= @@sql_safe_updates;
|
|
SET @@sql_safe_updates= 0;
|
|
DELETE FROM t1 ORDER BY (@@GLOBAL.INIT_FILE) ASC LIMIT 10;
|
|
SET @@sql_safe_updates= @bug42778;
|
|
DROP TABLE t1;
|
|
#
|
|
# BUG#10206 - Transaction requiring Max_BinLog_Cache_size > 4GB always rollsback
|
|
#
|
|
SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size;
|
|
# Set the max_binlog_cache_size to size more than 4GB.
|
|
SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024;
|
|
SELECT @@GLOBAL.max_binlog_cache_size;
|
|
@@GLOBAL.max_binlog_cache_size
|
|
5368709120
|
|
SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size;
|
|
#
|
|
# Bug #37168 : Missing variable - skip_name_resolve
|
|
#
|
|
SELECT @@skip_name_resolve;
|
|
@@skip_name_resolve
|
|
0
|
|
SHOW VARIABLES LIKE 'skip_name_resolve';
|
|
Variable_name Value
|
|
skip_name_resolve OFF
|
|
#
|
|
# Bug #43233 : Some server variables are clipped during "update,"
|
|
# not "check" stage
|
|
#
|
|
SET @kbs=@@global.key_buffer_size;
|
|
SET @kcbs=@@global.key_cache_block_size;
|
|
throw errors in STRICT mode
|
|
SET SQL_MODE=STRICT_ALL_TABLES;
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
SET @@global.max_binlog_cache_size=-1;
|
|
ERROR 42000: Variable 'max_binlog_cache_size' can't be set to the value of '-1'
|
|
SET @@global.max_join_size=0;
|
|
ERROR 42000: Variable 'max_join_size' can't be set to the value of '0'
|
|
SET @@global.key_buffer_size=0;
|
|
ERROR HY000: Cannot drop default keycache
|
|
SET @@global.key_cache_block_size=0;
|
|
ERROR 42000: Variable 'key_cache_block_size' can't be set to the value of '0'
|
|
throw warnings in default mode
|
|
SET SQL_MODE=DEFAULT;
|
|
SET @@global.max_binlog_cache_size=-1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_binlog_cache_size value: '-1'
|
|
Warning 1738 Option binlog_cache_size (32768) is greater than max_binlog_cache_size (4096); setting binlog_cache_size equal to max_binlog_cache_size.
|
|
SET @@global.max_join_size=0;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_join_size value: '0'
|
|
SET @@global.key_buffer_size=0;
|
|
ERROR HY000: Cannot drop default keycache
|
|
SET @@global.key_cache_block_size=0;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect key_cache_block_size value: '0'
|
|
#
|
|
# Bug#11766424 59527: DECIMAL_BIN_SIZE: ASSERTION `SCALE >= 0 && PRECISION > 0 && SCALE <= PRE
|
|
#
|
|
CREATE TABLE t1(f1 DECIMAL(1,1) UNSIGNED);
|
|
Warnings:
|
|
Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
|
|
INSERT INTO t1 VALUES (0.2),(0.1);
|
|
SELECT 1 FROM t1 GROUP BY @a:= (SELECT ROUND(f1) FROM t1 WHERE @a=f1);
|
|
1
|
|
1
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 AS SELECT @a:= CAST(1 AS UNSIGNED) AS a;
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(1) unsigned NOT NULL DEFAULT '0'
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
SET @@global.max_binlog_cache_size=DEFAULT;
|
|
SET @@global.binlog_cache_size=DEFAULT;
|
|
SET @@global.max_join_size=DEFAULT;
|
|
SET @@global.key_buffer_size=@kbs;
|
|
SET @@global.key_cache_block_size=@kcbs;
|
|
End of 5.1 tests
|
|
|
|
#
|
|
# Bug#34828: OF is taken as OFF and a value of 0 is set for variable SQL_notes.
|
|
#
|
|
|
|
# Checking sql_notes...
|
|
SET @sql_notes_saved = @@sql_notes;
|
|
|
|
SET @@sql_notes = ON;
|
|
SELECT @@sql_notes;
|
|
@@sql_notes
|
|
1
|
|
|
|
SET @@sql_notes = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@sql_notes;
|
|
@@sql_notes
|
|
1
|
|
|
|
SET @@sql_notes = OFF;
|
|
SELECT @@sql_notes;
|
|
@@sql_notes
|
|
0
|
|
|
|
SET @@sql_notes = @sql_notes_saved;
|
|
|
|
# Checking delay_key_write...
|
|
SET @delay_key_write_saved = @@delay_key_write;
|
|
|
|
SET GLOBAL delay_key_write = ON;
|
|
SELECT @@delay_key_write;
|
|
@@delay_key_write
|
|
ON
|
|
|
|
SET GLOBAL delay_key_write = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@delay_key_write;
|
|
@@delay_key_write
|
|
ON
|
|
|
|
SET GLOBAL delay_key_write = AL;
|
|
ERROR 42000: Variable 'delay_key_write' can't be set to the value of 'AL'
|
|
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
|
|
|
|
SET GLOBAL delay_key_write = @delay_key_write_saved;
|
|
|
|
# Checking sql_safe_updates...
|
|
SET @sql_safe_updates_saved = @@sql_safe_updates;
|
|
|
|
SET @@sql_safe_updates = ON;
|
|
SELECT @@sql_safe_updates;
|
|
@@sql_safe_updates
|
|
1
|
|
|
|
SET @@sql_safe_updates = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@sql_safe_updates;
|
|
@@sql_safe_updates
|
|
1
|
|
|
|
SET @@sql_safe_updates = OFF;
|
|
SELECT @@sql_safe_updates;
|
|
@@sql_safe_updates
|
|
0
|
|
|
|
SET @@sql_safe_updates = @sql_safe_updates_saved;
|
|
|
|
# Checking foreign_key_checks...
|
|
SET @foreign_key_checks_saved = @@foreign_key_checks;
|
|
|
|
SET @@foreign_key_checks = ON;
|
|
SELECT @@foreign_key_checks;
|
|
@@foreign_key_checks
|
|
1
|
|
|
|
SET @@foreign_key_checks = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@foreign_key_checks;
|
|
@@foreign_key_checks
|
|
1
|
|
|
|
SET @@foreign_key_checks = OFF;
|
|
SELECT @@foreign_key_checks;
|
|
@@foreign_key_checks
|
|
0
|
|
|
|
SET @@foreign_key_checks = @foreign_key_checks_saved;
|
|
|
|
# Checking unique_checks...
|
|
SET @unique_checks_saved = @@unique_checks;
|
|
|
|
SET @@unique_checks = ON;
|
|
SELECT @@unique_checks;
|
|
@@unique_checks
|
|
1
|
|
|
|
SET @@unique_checks = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@unique_checks;
|
|
@@unique_checks
|
|
1
|
|
|
|
SET @@unique_checks = OFF;
|
|
SELECT @@unique_checks;
|
|
@@unique_checks
|
|
0
|
|
|
|
SET @@unique_checks = @unique_checks_saved;
|
|
|
|
# Checking sql_buffer_result...
|
|
SET @sql_buffer_result_saved = @@sql_buffer_result;
|
|
|
|
SET @@sql_buffer_result = ON;
|
|
SELECT @@sql_buffer_result;
|
|
@@sql_buffer_result
|
|
1
|
|
|
|
SET @@sql_buffer_result = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@sql_buffer_result;
|
|
@@sql_buffer_result
|
|
1
|
|
|
|
SET @@sql_buffer_result = OFF;
|
|
SELECT @@sql_buffer_result;
|
|
@@sql_buffer_result
|
|
0
|
|
|
|
SET @@sql_buffer_result = @sql_buffer_result_saved;
|
|
|
|
# Checking sql_quote_show_create...
|
|
SET @sql_quote_show_create_saved = @@sql_quote_show_create;
|
|
|
|
SET @@sql_quote_show_create = ON;
|
|
SELECT @@sql_quote_show_create;
|
|
@@sql_quote_show_create
|
|
1
|
|
|
|
SET @@sql_quote_show_create = OF;
|
|
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 'OF' at line 1
|
|
SELECT @@sql_quote_show_create;
|
|
@@sql_quote_show_create
|
|
1
|
|
|
|
SET @@sql_quote_show_create = OFF;
|
|
SELECT @@sql_quote_show_create;
|
|
@@sql_quote_show_create
|
|
0
|
|
|
|
SET @@sql_quote_show_create = @sql_quote_show_create_saved;
|
|
|
|
# End of Bug#34828.
|
|
|
|
# Make sure we can manipulate with autocommit in the
|
|
# along with other variables.
|
|
drop table if exists t1;
|
|
drop function if exists t1_max;
|
|
drop function if exists t1_min;
|
|
create table t1 (a int) engine=innodb;
|
|
insert into t1(a) values (0), (1);
|
|
create function t1_max() returns int return (select max(a) from t1);
|
|
create function t1_min() returns int return (select min(a) from t1);
|
|
select t1_min();
|
|
t1_min()
|
|
0
|
|
select t1_max();
|
|
t1_max()
|
|
1
|
|
set @@session.autocommit=t1_min(), @@session.autocommit=t1_max(),
|
|
@@session.autocommit=t1_min(), @@session.autocommit=t1_max(),
|
|
@@session.autocommit=t1_min(), @@session.autocommit=t1_max();
|
|
# Cleanup.
|
|
drop table t1;
|
|
drop function t1_min;
|
|
drop function t1_max;
|
|
#
|
|
# Bug #59884: setting charset to 2048 crashes
|
|
#
|
|
set session character_set_results = 2048;
|
|
ERROR 42000: Unknown character set: '2048'
|
|
set session character_set_client=2048;
|
|
ERROR 42000: Unknown character set: '2048'
|
|
set session character_set_connection=2048;
|
|
ERROR 42000: Unknown character set: '2048'
|
|
set session character_set_server=2048;
|
|
ERROR 42000: Unknown character set: '2048'
|
|
set session collation_server=2048;
|
|
ERROR HY000: Unknown collation: '2048'
|
|
set session character_set_filesystem=2048;
|
|
ERROR 42000: Unknown character set: '2048'
|
|
set session character_set_database=2048;
|
|
ERROR 42000: Unknown character set: '2048'
|
|
set session collation_connection=2048;
|
|
ERROR HY000: Unknown collation: '2048'
|
|
set session collation_database=2048;
|
|
ERROR HY000: Unknown collation: '2048'
|
|
End of 5.5 tests
|
|
#
|
|
# Bug#57035 'ulonglong sql_mode' stored into ulong, is risky and causes
|
|
# compiler warning
|
|
#
|
|
SET @@sql_mode= 8589934592;
|
|
ERROR 42000: Variable 'sql_mode' can't be set to the value of '8589934592'
|
|
#
|
|
# Bug #59860: log-slow-admin-statements and log-slow-slave-statements not displayed
|
|
#
|
|
show variables like 'log_slow%';
|
|
Variable_name Value
|
|
log_slow_admin_statements OFF
|
|
log_slow_extra OFF
|
|
log_slow_slave_statements OFF
|
|
set global log_slow_admin_statements = on;
|
|
show global variables like 'log_slow_admin_statements';
|
|
Variable_name Value
|
|
log_slow_admin_statements ON
|
|
select * from performance_schema.global_variables where variable_name like 'log_slow_admin_statements';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
log_slow_admin_statements ON
|
|
set global log_slow_slave_statements = on;
|
|
show global variables like 'log_slow_slave_statements';
|
|
Variable_name Value
|
|
log_slow_slave_statements ON
|
|
select * from performance_schema.global_variables where variable_name like 'log_slow_slave_statements';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
log_slow_slave_statements ON
|
|
set global log_slow_admin_statements = default;
|
|
set global log_slow_slave_statements = default;
|
|
#
|
|
# WL#7764 - Make STRICT mode for transactional SEs default in 5.7
|
|
#
|
|
SHOW VARIABLES LIKE 'sql_mode';
|
|
Variable_name Value
|
|
sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
|
|
---- Init ----
|
|
set @my_slave_net_timeout =@@global.slave_net_timeout;
|
|
---- Test ----
|
|
set global slave_net_timeout=100;
|
|
set global sql_slave_skip_counter=100;
|
|
show variables like 'slave_compressed_protocol';
|
|
Variable_name Value
|
|
slave_compressed_protocol OFF
|
|
show variables like 'slave_load_tmpdir';
|
|
Variable_name Value
|
|
slave_load_tmpdir SLAVE_LOAD_TMPDIR
|
|
show variables like 'slave_skip_errors';
|
|
Variable_name Value
|
|
slave_skip_errors OFF
|
|
---- Clean Up ----
|
|
set global slave_net_timeout=default;
|
|
set global sql_slave_skip_counter= 0;
|
|
set @@global.slave_net_timeout= @my_slave_net_timeout;
|
|
|
|
#
|
|
SHOW VARIABLES like 'log_slave_updates';
|
|
Variable_name Value
|
|
log_slave_updates ON
|
|
SELECT @@session.log_slave_updates;
|
|
ERROR HY000: Variable 'log_slave_updates' is a GLOBAL variable
|
|
SELECT @@global.log_slave_updates;
|
|
@@global.log_slave_updates
|
|
1
|
|
SET @@session.log_slave_updates= true;
|
|
ERROR HY000: Variable 'log_slave_updates' is a read only variable
|
|
SET @@global.log_slave_updates= true;
|
|
ERROR HY000: Variable 'log_slave_updates' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'relay_log';
|
|
Variable_name Value
|
|
relay_log hostname-relay-bin
|
|
SELECT @@session.relay_log;
|
|
ERROR HY000: Variable 'relay_log' is a GLOBAL variable
|
|
SELECT @@global.relay_log;
|
|
@@global.relay_log
|
|
hostname-relay-bin
|
|
SET @@session.relay_log= 'x';
|
|
ERROR HY000: Variable 'relay_log' is a read only variable
|
|
SET @@global.relay_log= 'x';
|
|
ERROR HY000: Variable 'relay_log' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'relay_log_basename';
|
|
Variable_name Value
|
|
relay_log_basename MYSQLTEST_VARDIR/mysqld.1/data/hostname-relay-bin
|
|
SELECT @@session.relay_log_basename;
|
|
ERROR HY000: Variable 'relay_log_basename' is a GLOBAL variable
|
|
SELECT @@global.relay_log_basename;
|
|
@@global.relay_log_basename
|
|
MYSQLTEST_VARDIR/mysqld.1/data/hostname-relay-bin
|
|
SET @@session.relay_log_basename= 'x';
|
|
ERROR HY000: Variable 'relay_log_basename' is a read only variable
|
|
SET @@global.relay_log_basename= 'x';
|
|
ERROR HY000: Variable 'relay_log_basename' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'log_bin_basename';
|
|
Variable_name Value
|
|
log_bin_basename #
|
|
SELECT @@session.log_bin_basename;
|
|
ERROR HY000: Variable 'log_bin_basename' is a GLOBAL variable
|
|
SELECT @@global.log_bin_basename;
|
|
@@global.log_bin_basename
|
|
#
|
|
SET @@session.log_bin_basename= 'x';
|
|
ERROR HY000: Variable 'log_bin_basename' is a read only variable
|
|
SET @@global.log_bin_basename= 'x';
|
|
ERROR HY000: Variable 'log_bin_basename' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'relay_log_index';
|
|
Variable_name Value
|
|
relay_log_index MYSQLTEST_VARDIR/mysqld.1/data/hostname-relay-bin.index
|
|
SELECT @@session.relay_log_index;
|
|
ERROR HY000: Variable 'relay_log_index' is a GLOBAL variable
|
|
SELECT @@global.relay_log_index;
|
|
@@global.relay_log_index
|
|
MYSQLTEST_VARDIR/mysqld.1/data/hostname-relay-bin.index
|
|
SET @@session.relay_log_index= 'x';
|
|
ERROR HY000: Variable 'relay_log_index' is a read only variable
|
|
SET @@global.relay_log_index= 'x';
|
|
ERROR HY000: Variable 'relay_log_index' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'log_bin_index';
|
|
Variable_name Value
|
|
log_bin_index #
|
|
SELECT @@session.log_bin_index;
|
|
ERROR HY000: Variable 'log_bin_index' is a GLOBAL variable
|
|
SELECT @@global.log_bin_index;
|
|
@@global.log_bin_index
|
|
#
|
|
SET @@session.log_bin_index= 'x';
|
|
ERROR HY000: Variable 'log_bin_index' is a read only variable
|
|
SET @@global.log_bin_index= 'x';
|
|
ERROR HY000: Variable 'log_bin_index' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'relay_log_info_file';
|
|
Variable_name Value
|
|
relay_log_info_file relay-log.info
|
|
SELECT @@session.relay_log_info_file;
|
|
ERROR HY000: Variable 'relay_log_info_file' is a GLOBAL variable
|
|
SELECT @@global.relay_log_info_file;
|
|
@@global.relay_log_info_file
|
|
relay-log.info
|
|
Warnings:
|
|
Warning 1287 '@@relay_log_info_file' is deprecated and will be removed in a future release.
|
|
SET @@session.relay_log_info_file= 'x';
|
|
ERROR HY000: Variable 'relay_log_info_file' is a read only variable
|
|
SET @@global.relay_log_info_file= 'x';
|
|
ERROR HY000: Variable 'relay_log_info_file' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'relay_log_space_limit';
|
|
Variable_name Value
|
|
relay_log_space_limit 0
|
|
SELECT @@session.relay_log_space_limit;
|
|
ERROR HY000: Variable 'relay_log_space_limit' is a GLOBAL variable
|
|
SELECT @@global.relay_log_space_limit;
|
|
@@global.relay_log_space_limit
|
|
0
|
|
SET @@session.relay_log_space_limit= 7;
|
|
ERROR HY000: Variable 'relay_log_space_limit' is a read only variable
|
|
SET @@global.relay_log_space_limit= 7;
|
|
ERROR HY000: Variable 'relay_log_space_limit' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'slave_load_tmpdir';
|
|
Variable_name Value
|
|
slave_load_tmpdir #
|
|
SELECT @@session.slave_load_tmpdir;
|
|
ERROR HY000: Variable 'slave_load_tmpdir' is a GLOBAL variable
|
|
SELECT @@global.slave_load_tmpdir;
|
|
@@global.slave_load_tmpdir
|
|
#
|
|
SET @@session.slave_load_tmpdir= 'x';
|
|
ERROR HY000: Variable 'slave_load_tmpdir' is a read only variable
|
|
SET @@global.slave_load_tmpdir= 'x';
|
|
ERROR HY000: Variable 'slave_load_tmpdir' is a read only variable
|
|
#
|
|
SHOW VARIABLES like 'slave_skip_errors';
|
|
Variable_name Value
|
|
slave_skip_errors OFF
|
|
SELECT @@session.slave_skip_errors;
|
|
ERROR HY000: Variable 'slave_skip_errors' is a GLOBAL variable
|
|
SELECT @@global.slave_skip_errors;
|
|
@@global.slave_skip_errors
|
|
OFF
|
|
SET @@session.slave_skip_errors= 7;
|
|
ERROR HY000: Variable 'slave_skip_errors' is a read only variable
|
|
SET @@global.slave_skip_errors= 7;
|
|
ERROR HY000: Variable 'slave_skip_errors' is a read only variable
|
|
#
|
|
# Bug #11766769 : 59959: SMALL VALUES OF --MAX-ALLOWED-PACKET
|
|
# ARE NOT BEING HONORED
|
|
#
|
|
CREATE TABLE t1 (a MEDIUMTEXT);
|
|
SET GLOBAL max_allowed_packet=2048;
|
|
Warnings:
|
|
Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
|
|
SET GLOBAL net_buffer_length=4096;
|
|
Warnings:
|
|
Warning 1708 The value of 'max_allowed_packet' should be no less than the value of 'net_buffer_length'
|
|
SHOW SESSION VARIABLES LIKE 'max_allowed_packet';
|
|
Variable_name Value
|
|
max_allowed_packet 2048
|
|
SHOW SESSION VARIABLES LIKE 'net_buffer_length';
|
|
Variable_name Value
|
|
net_buffer_length 4096
|
|
Got one of the listed errors
|
|
SELECT LENGTH(a) FROM t1;
|
|
LENGTH(a)
|
|
SET GLOBAL max_allowed_packet=default;
|
|
SET GLOBAL net_buffer_length=default;
|
|
DROP TABLE t1;
|
|
End of 5.1 tests
|
|
SHOW VARIABLES like 'slave_skip_errors';
|
|
Variable_name Value
|
|
slave_skip_errors OFF
|
|
SET @sql_big_selects_save=@@sql_big_selects;
|
|
SET @@sql_big_selects=1;
|
|
CREATE TABLE all_vars (id INT PRIMARY KEY AUTO_INCREMENT, var_name VARCHAR(64), var_value VARCHAR(1024));
|
|
INSERT INTO all_vars (var_name, var_value)
|
|
SELECT a.variable_name,a.variable_value
|
|
FROM performance_schema.session_variables a
|
|
LEFT JOIN performance_schema.global_variables b
|
|
ON a.variable_name=b.variable_name
|
|
WHERE b.variable_name IS NULL
|
|
AND a.variable_name NOT IN ('debug_sync');
|
|
SET SESSION error_count = @@session.error_count;
|
|
SET SESSION external_user = @@session.external_user;
|
|
SET SESSION gtid_next = @@session.gtid_next;
|
|
SET SESSION identity = @@session.identity;
|
|
SET SESSION immediate_server_version = @@session.immediate_server_version;
|
|
SET SESSION insert_id = @@session.insert_id;
|
|
SET SESSION last_insert_id = @@session.last_insert_id;
|
|
SET SESSION original_commit_timestamp = @@session.original_commit_timestamp;
|
|
SET SESSION original_server_version = @@session.original_server_version;
|
|
SET SESSION proxy_user = @@session.proxy_user;
|
|
SET SESSION pseudo_slave_mode = @@session.pseudo_slave_mode;
|
|
Warnings:
|
|
Warning 1231 'pseudo_slave_mode' change was ineffective.
|
|
SET SESSION pseudo_thread_id = @@session.pseudo_thread_id;
|
|
SET SESSION rand_seed1 = @@session.rand_seed1;
|
|
SET SESSION rand_seed2 = @@session.rand_seed2;
|
|
SET SESSION resultset_metadata = @@session.resultset_metadata;
|
|
SET SESSION show_create_table_skip_secondary_engine = @@session.show_create_table_skip_secondary_engine;
|
|
SET SESSION sql_log_bin = @@session.sql_log_bin;
|
|
SET SESSION timestamp = @@session.timestamp;
|
|
SET SESSION transaction_allow_batching = @@session.transaction_allow_batching;
|
|
SET SESSION use_secondary_engine = @@session.use_secondary_engine;
|
|
SET SESSION warning_count = @@session.warning_count;
|
|
SET @@sql_big_selects=@sql_big_selects_save;
|
|
DROP TABLE all_vars;
|
|
|