449 lines
14 KiB
Plaintext
449 lines
14 KiB
Plaintext
#
|
|
# WL#8688: Support ability to persist SET GLOBAL settings
|
|
#
|
|
# Syntax check for PERSIST option
|
|
SET PERSIST autocommit=0;
|
|
SET @@persist.max_execution_time=60000;
|
|
SET PERSIST max_user_connections=10, PERSIST max_allowed_packet=8388608;
|
|
SET @@persist.max_user_connections=10, PERSIST max_allowed_packet=8388608;
|
|
SET @@persist.max_user_connections=10, @@persist.max_allowed_packet=8388608;
|
|
SET PERSIST max_user_connections=10, @@persist.max_allowed_packet=8388608;
|
|
# Syntax check for PERSIST/GLOBAL combination
|
|
SET PERSIST autocommit=0, GLOBAL max_user_connections=10;
|
|
SET @@persist.autocommit=0, @@global.max_user_connections=10;
|
|
SET GLOBAL autocommit=0, PERSIST max_user_connections=10;
|
|
SET @@global.autocommit=0, @@persist.max_user_connections=10;
|
|
# Syntax check for PERSIST/SESSION combination
|
|
SET PERSIST autocommit=0, SESSION auto_increment_offset=10;
|
|
SET @@persist.autocommit=0, @@session.auto_increment_offset=10;
|
|
SET SESSION auto_increment_offset=20, PERSIST max_user_connections=10;
|
|
SET @@session.auto_increment_offset=20, @@persist.max_user_connections=10;
|
|
SET PERSIST autocommit=0, auto_increment_offset=10;
|
|
SET autocommit=0, PERSIST auto_increment_offset=10;
|
|
# Syntax check for PERSIST/SESSION/GLOBAL combination
|
|
SET PERSIST autocommit=0, SESSION auto_increment_offset=10, GLOBAL max_error_count= 128;
|
|
SET SESSION autocommit=0, GLOBAL auto_increment_offset=10, PERSIST max_allowed_packet=8388608;
|
|
SET GLOBAL autocommit=0, PERSIST auto_increment_offset=10, SESSION max_error_count= 128;
|
|
SET @@persist.autocommit=0, @@session.auto_increment_offset=10, @@global.max_allowed_packet=8388608;
|
|
SET @@session.autocommit=0, @@global.auto_increment_offset=10, @@persist.max_allowed_packet=8388608;
|
|
SET @@global.autocommit=0, @@persist.auto_increment_offset=10, @@session.max_error_count= 128;
|
|
# Restart server
|
|
# default values
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
151
|
|
SET PERSIST max_connections=33;
|
|
# Restart server and check for values
|
|
# after restart
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
33
|
|
# default values
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
262144
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
16777216
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
60
|
|
# persist few more variables
|
|
SET PERSIST sort_buffer_size=256000;
|
|
SET PERSIST max_heap_table_size=999424, slave_net_timeout=124;
|
|
SET PERSIST long_query_time= 8.3452;
|
|
SET PERSIST sql_require_primary_key= true;
|
|
SET PERSIST default_table_encryption= true;
|
|
SET PERSIST table_encryption_privilege_check= true;
|
|
# Restart server
|
|
# after restart
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
33
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
256000
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
999424
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
124
|
|
SELECT @@global.long_query_time;
|
|
@@global.long_query_time
|
|
8.345200
|
|
SELECT @@global.sql_require_primary_key;
|
|
@@global.sql_require_primary_key
|
|
1
|
|
SELECT @@global.default_table_encryption;
|
|
@@global.default_table_encryption
|
|
1
|
|
SELECT @@global.table_encryption_privilege_check;
|
|
@@global.table_encryption_privilege_check
|
|
1
|
|
# modify existing persisted variables
|
|
SET PERSIST sort_buffer_size=156000,max_connections= 52;
|
|
SET PERSIST max_heap_table_size=887808, slave_net_timeout=160;
|
|
SET PERSIST long_query_time= 7.8102;
|
|
SET PERSIST sql_require_primary_key= false;
|
|
SET PERSIST default_table_encryption= false;
|
|
SET PERSIST table_encryption_privilege_check= false;
|
|
# Restart server
|
|
# after restart
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
52
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
156000
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
887808
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
160
|
|
SELECT @@global.long_query_time;
|
|
@@global.long_query_time
|
|
7.810200
|
|
SELECT @@global.sql_require_primary_key;
|
|
@@global.sql_require_primary_key
|
|
0
|
|
SELECT @@global.default_table_encryption;
|
|
@@global.default_table_encryption
|
|
0
|
|
SELECT @@global.table_encryption_privilege_check;
|
|
@@global.table_encryption_privilege_check
|
|
0
|
|
SELECT @@global.max_user_connections;
|
|
@@global.max_user_connections
|
|
0
|
|
SELECT @@global.max_execution_time;
|
|
@@global.max_execution_time
|
|
0
|
|
# modify existing persisted variables and add new
|
|
SET PERSIST sort_buffer_size=256000;
|
|
SET PERSIST max_heap_table_size=999424, slave_net_timeout=124;
|
|
SET PERSIST long_query_time= 2.999999;
|
|
SET PERSIST sql_require_primary_key= true;
|
|
SET PERSIST default_table_encryption= true;
|
|
SET PERSIST table_encryption_privilege_check= true;
|
|
SET @@persist.max_execution_time=44000, @@persist.max_user_connections=30;
|
|
# Restart server
|
|
# after restart
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
256000
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
999424
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
124
|
|
SELECT @@global.max_user_connections;
|
|
@@global.max_user_connections
|
|
30
|
|
SELECT @@global.max_execution_time;
|
|
@@global.max_execution_time
|
|
44000
|
|
SELECT @@global.long_query_time;
|
|
@@global.long_query_time
|
|
2.999999
|
|
SELECT @@global.sql_require_primary_key;
|
|
@@global.sql_require_primary_key
|
|
1
|
|
SELECT @@global.default_table_encryption;
|
|
@@global.default_table_encryption
|
|
1
|
|
SELECT @@global.table_encryption_privilege_check;
|
|
@@global.table_encryption_privilege_check
|
|
1
|
|
# reset persisted variables
|
|
RESET PERSIST sort_buffer_size;
|
|
RESET PERSIST max_heap_table_size;
|
|
RESET PERSIST max_execution_time;
|
|
RESET PERSIST max_user_connections;
|
|
RESET PERSIST sql_require_primary_key;
|
|
RESET PERSIST default_table_encryption;
|
|
RESET PERSIST table_encryption_privilege_check;
|
|
# Restart server
|
|
# after restart
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
262144
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
16777216
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
124
|
|
SELECT @@global.max_user_connections;
|
|
@@global.max_user_connections
|
|
0
|
|
SELECT @@global.max_execution_time;
|
|
@@global.max_execution_time
|
|
0
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
52
|
|
SELECT @@global.server_id;
|
|
@@global.server_id
|
|
1
|
|
SELECT @@global.general_log;
|
|
@@global.general_log
|
|
1
|
|
SELECT @@global.concurrent_insert;
|
|
@@global.concurrent_insert
|
|
AUTO
|
|
SELECT @@global.sql_require_primary_key;
|
|
@@global.sql_require_primary_key
|
|
0
|
|
SELECT @@global.default_table_encryption;
|
|
@@global.default_table_encryption
|
|
0
|
|
SELECT @@global.table_encryption_privilege_check;
|
|
@@global.table_encryption_privilege_check
|
|
0
|
|
# reset persisted variables and add new
|
|
RESET PERSIST slave_net_timeout;
|
|
SET PERSIST server_id=47, @@persist.general_log=0;
|
|
SET PERSIST concurrent_insert=NEVER;
|
|
RESET PERSIST max_connections;
|
|
# Restart server
|
|
# after restart
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
262144
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
16777216
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
60
|
|
SELECT @@global.max_user_connections;
|
|
@@global.max_user_connections
|
|
0
|
|
SELECT @@global.max_execution_time;
|
|
@@global.max_execution_time
|
|
0
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
151
|
|
SELECT @@global.server_id;
|
|
@@global.server_id
|
|
47
|
|
SELECT @@global.general_log;
|
|
@@global.general_log
|
|
0
|
|
SELECT @@global.concurrent_insert;
|
|
@@global.concurrent_insert
|
|
NEVER
|
|
# check precedence with command line option
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
151
|
|
SELECT @@global.server_id;
|
|
@@global.server_id
|
|
47
|
|
SET PERSIST max_connections=88;
|
|
SET PERSIST server_id=9;
|
|
SET PERSIST session_track_system_variables='autocommit';
|
|
# Restart server with command line option
|
|
# after restart must be 88,9, autocommit
|
|
SELECT @@global.max_connections;
|
|
@@global.max_connections
|
|
88
|
|
SELECT @@global.server_id;
|
|
@@global.server_id
|
|
9
|
|
SELECT @@global.session_track_system_variables;
|
|
@@global.session_track_system_variables
|
|
autocommit
|
|
|
|
SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert NEVER
|
|
general_log OFF
|
|
long_query_time 2.999999
|
|
max_connections 88
|
|
server_id 9
|
|
session_track_system_variables autocommit
|
|
RESET PERSIST server_id;
|
|
RESET PERSIST general_log;
|
|
SET PERSIST max_connections=77;
|
|
SET PERSIST session_track_system_variables='max_connections';
|
|
SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert NEVER
|
|
long_query_time 2.999999
|
|
max_connections 77
|
|
session_track_system_variables max_connections
|
|
|
|
SET PERSIST max_connections=99;
|
|
RESET PERSIST session_track_system_variables;
|
|
SET PERSIST concurrent_insert=ALWAYS;
|
|
SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert ALWAYS
|
|
long_query_time 2.999999
|
|
max_connections 99
|
|
|
|
RESET PERSIST max_connections;
|
|
SET PERSIST concurrent_insert=AUTO;
|
|
SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
concurrent_insert AUTO
|
|
long_query_time 2.999999
|
|
|
|
# negative tests persist session only variables
|
|
SET @@persist.sql_log_bin=0;
|
|
ERROR HY000: Variable 'sql_log_bin' is a SESSION variable and can't be used with SET GLOBAL
|
|
# Test persisted-globals-load
|
|
SELECT @@global.persisted_globals_load;
|
|
@@global.persisted_globals_load
|
|
1
|
|
# New read-only variable persisted_globals_load.
|
|
SET PERSIST persisted_globals_load=0;
|
|
ERROR HY000: Variable 'persisted_globals_load' is a read only variable
|
|
SET GLOBAL persisted_globals_load=ON;
|
|
ERROR HY000: Variable 'persisted_globals_load' is a read only variable
|
|
SET SESSION persisted_globals_load=1;
|
|
ERROR HY000: Variable 'persisted_globals_load' is a read only variable
|
|
SET GLOBAL persisted_globals_load= 'abc';
|
|
ERROR HY000: Variable 'persisted_globals_load' is a read only variable
|
|
SET GLOBAL persisted_globals_load= -1;
|
|
ERROR HY000: Variable 'persisted_globals_load' is a read only variable
|
|
# Test persisted-globals-load functionality
|
|
SET PERSIST sort_buffer_size=256000;
|
|
SET PERSIST max_heap_table_size=999424, slave_net_timeout=124;
|
|
# Restart server
|
|
# values must be read from persited config file
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
256000
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
999424
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
124
|
|
SELECT @@global.foreign_key_checks;
|
|
@@global.foreign_key_checks
|
|
1
|
|
SET PERSIST foreign_key_checks=0;
|
|
SET PERSIST flush_time=2;
|
|
# Restart server with persisted-globals-load=0
|
|
# should have default values
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
262144
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
16777216
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
60
|
|
# Expected value 1
|
|
SELECT @@global.foreign_key_checks;
|
|
@@global.foreign_key_checks
|
|
1
|
|
# Expected value 0
|
|
SELECT @@global.flush_time;
|
|
@@global.flush_time
|
|
0
|
|
# reset some persisted values
|
|
RESET PERSIST sort_buffer_size;
|
|
RESET PERSIST slave_net_timeout;
|
|
# Restart server with persisted-globals-load=1
|
|
# should have default values
|
|
SELECT @@global.sort_buffer_size;
|
|
@@global.sort_buffer_size
|
|
262144
|
|
SELECT @@global.slave_net_timeout;
|
|
@@global.slave_net_timeout
|
|
60
|
|
SELECT @@global.max_heap_table_size;
|
|
@@global.max_heap_table_size
|
|
999424
|
|
# Expected value 0
|
|
SELECT @@global.foreign_key_checks;
|
|
@@global.foreign_key_checks
|
|
0
|
|
# Expected value 2
|
|
SELECT @@global.flush_time;
|
|
@@global.flush_time
|
|
2
|
|
call mtr.add_suppression("\\[Warning\\] \\[[^]]*\\] \\[[^]]*\\] option 'persisted_globals_load': boolean value 'NULL' was not recognized. Set to OFF.");
|
|
call mtr.add_suppression("\\[Warning\\] option 'persisted_globals_load': boolean value 'NULL' was not recognized. Set to OFF.");
|
|
# Restart server with persisted-globals-load=NULL
|
|
# Should get warning in error log.
|
|
Pattern "persisted_globals_load" found
|
|
SELECT @@global.persisted_globals_load;
|
|
@@global.persisted_globals_load
|
|
0
|
|
# Restart server with persisted-globals-load=1
|
|
#
|
|
# Bug24569624: "SET PERSIST GENERAL_LOG=1" DOES NOT RESUME ON RESTART.
|
|
#
|
|
SET PERSIST general_log=ON;
|
|
SET PERSIST autocommit= 0, innodb_deadlock_detect= OFF;
|
|
SET PERSIST enforce_gtid_consistency=ON;
|
|
SELECT @@global.general_log, @@global.autocommit,
|
|
@@global.innodb_deadlock_detect, @@global.enforce_gtid_consistency;
|
|
@@global.general_log @@global.autocommit @@global.innodb_deadlock_detect @@global.enforce_gtid_consistency
|
|
1 0 0 ON
|
|
# Restart server
|
|
SELECT @@global.general_log, @@global.autocommit,
|
|
@@global.innodb_deadlock_detect, @@global.enforce_gtid_consistency;
|
|
@@global.general_log @@global.autocommit @@global.innodb_deadlock_detect @@global.enforce_gtid_consistency
|
|
1 0 0 ON
|
|
#
|
|
# Bug#24613005: SET PERSIST STORES PREVIOUS VALUE OF
|
|
# INNODB_BUFFER_POOL_SIZE TO MYSQLD-AUTO.CNF
|
|
#
|
|
SELECT @@global.innodb_buffer_pool_size;
|
|
@@global.innodb_buffer_pool_size
|
|
25165824
|
|
SET PERSIST innodb_buffer_pool_size=10*1024*1024;
|
|
Warnings:
|
|
Warning 1210 InnoDB: Cannot resize buffer pool to lesser than chunk size of 25165824 bytes.
|
|
SELECT @@global.innodb_buffer_pool_size;
|
|
@@global.innodb_buffer_pool_size
|
|
25165824
|
|
# Restart server
|
|
# restart
|
|
SELECT @@global.innodb_buffer_pool_size;
|
|
@@global.innodb_buffer_pool_size
|
|
25165824
|
|
SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
autocommit OFF
|
|
enforce_gtid_consistency ON
|
|
general_log ON
|
|
innodb_buffer_pool_size 25165824
|
|
innodb_deadlock_detect OFF
|
|
|
|
#
|
|
# Bug#26085712: SERVER FAILS TO START AFTER SET PERSIST GTID_MODE
|
|
# = ON_PERMISSIVE OR ON
|
|
#
|
|
RESET PERSIST;
|
|
SELECT @@gtid_mode;
|
|
@@gtid_mode
|
|
OFF
|
|
SET PERSIST gtid_mode=OFF_PERMISSIVE;
|
|
SET PERSIST gtid_mode=ON_PERMISSIVE;
|
|
SELECT @@gtid_mode;
|
|
@@gtid_mode
|
|
ON_PERMISSIVE
|
|
# Restart server and check for gtid_mode value
|
|
# restart
|
|
SELECT @@gtid_mode;
|
|
@@gtid_mode
|
|
ON_PERMISSIVE
|
|
RESET PERSIST;
|
|
# Restart server with all defaults
|
|
# restart
|
|
SELECT 'END OF TEST';
|
|
END OF TEST
|
|
END OF TEST
|