mysql-server/mysql-test/t/persisted_variables_bugs.test

--source include/have_log_bin.inc
--source include/have_binlog_format_row.inc

--echo #
--echo # Bug #27016247: SET PERSIST_ONLY DOES NOT CONSIDER RUNTIME VALIDATION
--echo #

--echo # Should fail: event_scheduler does not allow setting to DISABLED at runtime
--error ER_WRONG_VALUE_FOR_VAR
SET PERSIST event_scheduler=DISABLED;

--echo # Should fail: data type still checked even with persist_only
--error ER_WRONG_TYPE_FOR_VAR
SET PERSIST_ONLY event_scheduler=123.456;

--echo # Should work: check not called for PERSIST_ONLY
SET PERSIST_ONLY event_scheduler=DISABLED;

--echo # cleanup
RESET PERSIST event_scheduler;

--echo #
--echo # Bug #27374791: RESET PERSIST DOES IMPLICIT COMMIT AND DOESN'T NEED
--echo #                RELOAD PRIVILEGES
--echo #

CREATE DATABASE bug27374791;
USE bug27374791;
CREATE TABLE T( i int);
# Get rid of previous tests binlog
--disable_query_log
RESET BINARY LOGS AND GTIDS;
--enable_query_log
--source include/rpl/deprecated/show_binlog_events.inc
BEGIN;
INSERT INTO T values (9);
--source include/rpl/deprecated/show_binlog_events.inc
SET PERSIST max_connections=31;
--source include/rpl/deprecated/show_binlog_events.inc
RESET PERSIST;
# There should be no COMMIT event in binlog
--source include/rpl/deprecated/show_binlog_events.inc

# cleanup
SET GLOBAL max_connections=DEFAULT;
DROP DATABASE bug27374791;

--echo #
--echo # Bug #27322254: WITH PERSIST_ONLY, VARIABLE VALUE EQUALS 18446744073709547520
--echo #                IS STORED AS -4096
--echo #

SELECT @@max_binlog_cache_size;
SET PERSIST max_binlog_cache_size= @@global.max_binlog_cache_size;
SELECT * FROM performance_schema.persisted_variables WHERE
  VARIABLE_NAME= 'max_binlog_cache_size';
SET PERSIST_ONLY max_binlog_cache_size= @@global.max_binlog_cache_size;
SELECT * FROM performance_schema.persisted_variables WHERE
  VARIABLE_NAME= 'max_binlog_cache_size';

# Similar test which fixes this bug without SET PERSIST_ONLY
SET @a=cast(@@max_binlog_cache_size as char);
SELECT @a;

# Cleanup
RESET PERSIST;
SET GLOBAL max_binlog_cache_size= DEFAULT;

--echo #
--echo # Bug #27536421: SET PERSIST CHANGES THE VALUE OF OPTIMIZER_TRACE_OFFSET
--echo #                AFTER RESTART
--echo #

# default value
--replace_column 6 ###
SELECT @@global.optimizer_trace_offset, @@global.activate_all_roles_on_login,
       @@global.auto_increment_increment, @@global.auto_increment_offset,
       @@global.binlog_error_action, @@global.binlog_format,
       @@global.cte_max_recursion_depth, @@global.eq_range_index_dive_limit,
       @@global.innodb_monitor_disable, @@global.histogram_generation_max_mem_size,
       @@global.innodb_max_dirty_pages_pct, @@global.init_connect,
       @@global.max_join_size;

# SHOW_SIGNED_LONG
SET PERSIST optimizer_trace_offset = default;
# SHOW_MY_BOOL
SET PERSIST activate_all_roles_on_login= ON;
# SHOW_LONG
SET PERSIST auto_increment_increment= 4, auto_increment_offset= 2;
# SHOW_CHAR
SET PERSIST binlog_error_action= IGNORE_ERROR, binlog_format= ROW;
# SHOW_INT
SET PERSIST cte_max_recursion_depth= 4294967295, eq_range_index_dive_limit= 4294967295;
# SHOW_CHAR_PTR
SET PERSIST innodb_monitor_disable='latch';
# SHOW_DOUBLE
SET PERSIST innodb_max_dirty_pages_pct= 97.3;
# SHOW_LEX_STRING
SET PERSIST init_connect='SET autocommit=0';
# SHOW_HA_ROWS
SET PERSIST max_join_size= 18446744073709551615;

# persisted value
--replace_column 6 ###
SELECT @@global.optimizer_trace_offset, @@global.activate_all_roles_on_login,
       @@global.auto_increment_increment, @@global.auto_increment_offset,
       @@global.binlog_error_action, @@global.binlog_format,
       @@global.cte_max_recursion_depth, @@global.eq_range_index_dive_limit,
       @@global.innodb_monitor_disable,
       @@global.innodb_max_dirty_pages_pct, @@global.init_connect,
       @@global.max_join_size;

SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
--echo # Restart server
--source include/restart_mysqld.inc

# persisted value after restart
--replace_column 6 ###
SELECT @@global.optimizer_trace_offset, @@global.activate_all_roles_on_login,
       @@global.auto_increment_increment, @@global.auto_increment_offset,
       @@global.binlog_error_action, @@global.binlog_format,
       @@global.cte_max_recursion_depth, @@global.eq_range_index_dive_limit,
       @@global.innodb_monitor_disable,
       @@global.innodb_max_dirty_pages_pct, @@global.init_connect,
       @@global.max_join_size;

SELECT * FROM performance_schema.persisted_variables ORDER BY 1;

--echo # Cleanup
RESET PERSIST;
SET GLOBAL optimizer_trace_offset = default, activate_all_roles_on_login = default,
       auto_increment_increment = default, auto_increment_offset = default,
       binlog_error_action = default, binlog_format = default,
       cte_max_recursion_depth = default, eq_range_index_dive_limit = default,
       innodb_monitor_disable = default,
       innodb_max_dirty_pages_pct = default, init_connect = default,
       max_join_size = default;

--echo #
--echo # Bug #27629719: SET PERSIST STORES TRUNCATED TIMESTAMPS
--echo #

--echo # Set one variable in the mysqld-auto.cnf
SET PERSIST max_join_size= 10000000;
--echo # Set another variable in the mysqld-auto.cnf
SET PERSIST init_connect='';

--echo # Restart the server so it sets the variable time from mysqld-auto.cnf
--source include/restart_mysqld.inc

--echo # Check the microseconds of set_time: must be 2 (i.e. different for the two vars)
SELECT COUNT(DISTINCT MICROSECOND(set_time)) FROM performance_schema.variables_info
  WHERE variable_name IN ('max_join_size', 'init_connect');

--echo # Cleanup
SET GLOBAL max_join_size=DEFAULT, init_connect=DEFAULT;
RESET PERSIST;

--echo #
--echo # Bug #27489026: PERSIST_ONLY DOESN'T RESPECT DEFAULT INSTEAD COPIES GLOBAL VALUE
--echo #

SELECT @@global.binlog_cache_size;
SELECT @@global.collation_database;
# test SIGNED long value
SELECT @@global.optimizer_trace_offset;
# test Sys_var_flagset type
SELECT @@global.optimizer_switch;
# test Sys_var_multi_enum type
SELECT @@global.enforce_gtid_consistency;
# test Sys_var_set type
SELECT @@global.sql_mode;
# set value different from default
SET @@global.binlog_cache_size= 4096;

# persist default values
SET @@persist_only.binlog_cache_size= default,
    @@persist_only.collation_database= default,
    @@persist_only.optimizer_trace_offset= default,
    @@persist_only.optimizer_switch= default,
    @@persist_only.enforce_gtid_consistency= default,
    @@persist_only.sql_mode= default;

SELECT * FROM performance_schema.persisted_variables ORDER BY 1;

--echo # Restart server
--source include/restart_mysqld.inc

# must have default values.
SELECT @@global.binlog_cache_size;
SELECT @@global.collation_database;
SELECT @@global.optimizer_trace_offset;
SELECT @@global.optimizer_switch;
SELECT @@global.enforce_gtid_consistency;
SELECT @@global.sql_mode;

SELECT * FROM performance_schema.persisted_variables ORDER BY 1;

--echo # Cleanup
RESET PERSIST;

--echo #
--echo # Bug #27523095: SET PERSIST MANDATORY_ROLES FAILS TO START THE SERVER
--echo #

SET PERSIST mandatory_roles= default;
--echo # Restart server and check if server starts
--source include/restart_mysqld.inc

SELECT * FROM performance_schema.persisted_variables ORDER BY 1;
# Cleanup
RESET PERSIST;

--echo #
--echo # Bug #27903874: [MYSQL 8.0 GA DEBUG BUILD] ASSERTION `!THD ||
--echo #                THD->LOCKED_TABLES_MODE || !THD->M
--echo #

CREATE DATABASE bug27903874;
USE bug27903874;
SET @@autocommit=FALSE;
CREATE TABLE t(a CHAR (1))ENGINE=InnoDB;
--error ER_VAR_DOES_NOT_EXIST
RESET PERSIST size;
--error ER_BAD_FIELD_ERROR
SELECT JSON_OBJECTAGG(id,x) FROM t;
RESET REPLICA;
# cleanup
DROP DATABASE bug27903874;

--echo #
--echo # Bug #27512616: SET PERSIST COMMAND FAILS TO MAP DEFAULT(NULL) VALUES IN
--echo #                MYSQLD-AUTO.CNF
--echo #

SELECT @@global.innodb_tmpdir;
SET PERSIST innodb_tmpdir = default;
SELECT @@global.innodb_ft_user_stopword_table;
SET PERSIST innodb_ft_user_stopword_table = NULL;
--echo # Restart server and check if server starts
--source include/restart_mysqld.inc
SELECT variable_name FROM performance_schema.variables_info WHERE variable_source='PERSISTED';
SELECT @@global.innodb_tmpdir, @@global.innodb_ft_user_stopword_table;
# Cleanup
RESET PERSIST;

--echo #
--echo # Bug #28564239: RESET PERSIST REQUIRES NO PRIVILEGES
--echo #

CREATE USER u1;
# persist some static and dynamic variables
SET PERSIST_ONLY ft_query_expansion_limit=80, innodb_api_enable_mdl=1;
SET PERSIST sort_buffer_size=156000,max_connections= 52;
SET PERSIST max_heap_table_size=887808, replica_net_timeout=160;

--connect(con1, localhost, u1)
SHOW GRANTS;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
RESET PERSIST max_heap_table_size;
--error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
RESET PERSIST ft_query_expansion_limit;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
RESET PERSIST;

connection default;
GRANT SUPER ON *.* TO u1;
disconnect con1;

# grant SUPER only
--connect(con1, localhost, u1)
SHOW GRANTS;
RESET PERSIST max_heap_table_size;
--error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
RESET PERSIST ft_query_expansion_limit;
--error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
RESET PERSIST;

connection default;
REVOKE SUPER ON *.* FROM u1;
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1;
disconnect con1;

# grant SYSTEM_VARIABLES_ADMIN only
--connect(con1, localhost, u1)
SHOW GRANTS;
RESET PERSIST max_connections;
--error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
RESET PERSIST ft_query_expansion_limit;
--error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
RESET PERSIST;

connection default;
REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM u1;
GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO u1;
disconnect con1;

# grant PERSIST_RO_VARIABLES_ADMIN only
--connect(con1, localhost, u1)
SHOW GRANTS;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
RESET PERSIST replica_net_timeout;
--error ER_PERSIST_ONLY_ACCESS_DENIED_ERROR
RESET PERSIST ft_query_expansion_limit;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
RESET PERSIST;

connection default;
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1;
disconnect con1;

# grant SYSTEM_VARIABLES_ADMIN and PERSIST_RO_VARIABLES_ADMIN
--connect(con1, localhost, u1)
SHOW GRANTS;
RESET PERSIST replica_net_timeout;
RESET PERSIST ft_query_expansion_limit;
RESET PERSIST;

connection default;
# ensure no persisted variables exists
SELECT * FROM performance_schema.persisted_variables;
# set variables to default
SET GLOBAL sort_buffer_size = default, max_connections = default,
  replica_net_timeout = default, max_heap_table_size = default;
DROP USER u1;
disconnect con1;

--echo #
--echo # Bug #28466045: SET PERSIST NOT SETTING CORRECT VALUES FOR VARIABLES
--echo #                LIKE INNODB_STRICT_MODE
--echo #

SELECT @@global.innodb_strict_mode, @@global.innodb_lock_wait_timeout;
SELECT @@global.myisam_sort_buffer_size;
# enum variable type
SELECT @@global.myisam_stats_method;
SET PERSIST innodb_strict_mode=0;
SET GLOBAL innodb_lock_wait_timeout = 150;
SELECT @@global.innodb_lock_wait_timeout;
SET PERSIST innodb_lock_wait_timeout = 110;
SELECT @@global.innodb_lock_wait_timeout;
SET GLOBAL myisam_sort_buffer_size=16777216;
SET PERSIST myisam_sort_buffer_size=default;
SELECT @@global.innodb_lock_wait_timeout;
SET PERSIST myisam_stats_method=nulls_equal;
--echo # Restart server
--source include/restart_mysqld.inc
SELECT @@global.innodb_strict_mode;
SELECT @@global.innodb_lock_wait_timeout;
SELECT @@global.myisam_sort_buffer_size;
SELECT @@global.myisam_stats_method;
SELECT variable_name FROM performance_schema.variables_info WHERE variable_source='PERSISTED';
SET GLOBAL innodb_strict_mode=default, innodb_lock_wait_timeout=default,
  myisam_stats_method=default;
RESET PERSIST;

--echo #
--echo # Bug #30030648: NO DEPRECATION WARNING ON SERVER RESTART FOR SET PERSISTED OPTIONS
--echo #

--echo # should report warning
SET GLOBAL delayed_insert_limit=100;
--echo # should report warning
SET PERSIST_ONLY delayed_insert_limit=100;
--echo # should report warning
SET PERSIST delayed_insert_limit=100;

--let $ofile= $MYSQLTEST_VARDIR/tmp/server_log.err

--echo # Restart server
--let $restart_parameters=restart:--log_error=$MYSQLTEST_VARDIR/tmp/server_log.err
--replace_result $MYSQLTEST_VARDIR tmp_dir
--source include/restart_mysqld_no_echo.inc

--let $assert_text= Assert that there is a warning at bootstrap when setting --delayed_insert_limit
--let $assert_file= $MYSQLTEST_VARDIR/tmp/server_log.err
--let $assert_select= '@@delayed_insert_limit' is deprecated and will be removed in a future release.
--let $assert_count= 1
--source include/assert_grep.inc

--remove_file $MYSQLTEST_VARDIR/tmp/server_log.err
RESET PERSIST;

--echo #
--echo # Bug #28749668: SKIP_NAME_RESOLVE CAN BE PERSIT_ONLY-ED TO ON, BUT NOT OFF
--echo #

SELECT @@global.skip_name_resolve;
CREATE USER 'bug28749668'@'%';
GRANT ALL ON *.* TO 'bug28749668'@'%';
--connect(con1, localhost, bug28749668)
SET PERSIST_ONLY skip_name_resolve=0;

disconnect con1;
connection default;
--echo # Restart server and check if server starts
--let $restart_parameters=restart:
--source include/restart_mysqld.inc

--connect(con1, localhost, bug28749668)
SELECT @@global.skip_name_resolve;
SELECT * FROM performance_schema.persisted_variables;
connection default;
RESET PERSIST;
#cleanup
DROP USER 'bug28749668'@'%';
disconnect con1;

--echo #
--echo # Bug #30169731: MYSQL SERVER DOESN'T START AND NO ERROR IN THE LOG
--echo #

# Set variables to be used in parameters of mysqld.
let $MYSQLD_DATADIR= `SELECT @@datadir`;
let $MYSQL_BASEDIR= `SELECT @@basedir`;
let $MYSQL_SOCKET= `SELECT @@socket`;
let $MYSQL_PIDFILE= `SELECT @@pid_file`;
let $MYSQL_PORT= `SELECT @@port`;
let $MYSQL_MESSAGESDIR= `SELECT @@lc_messages_dir`;
let MYSQLD_LOG=$MYSQL_TMP_DIR/server.log;

--source include/shutdown_mysqld.inc

--remove_file $MYSQLD_DATADIR/mysqld-auto.cnf
--echo # config file is in wrong json format
--write_file $MYSQLD_DATADIR/mysqld-auto.cnf
{ Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "23" , "Metadata" : { "Timestamp" : 1573471430265110 , "User" : "root" , "Host" : "localhost" } } } }
EOF

--echo # server should fail to start
--error 0,1
--exec $MYSQLD_CMD --basedir=$MYSQL_BASEDIR --datadir=$MYSQLD_DATADIR --socket=$MYSQL_SOCKET --pid-file=$MYSQL_PIDFILE --port=$MYSQL_PORT --lc-messages-dir=$MYSQL_MESSAGESDIR --daemonize --secure-file-priv="" > $MYSQLD_LOG 2>&1

--remove_file $MYSQLD_DATADIR/mysqld-auto.cnf

--echo # start server with all defaults
--source include/start_mysqld.inc

--let $assert_text=Server should fail to start as mysqld-auto.cnf file is with wrong JSON format.
--let $assert_file=$MYSQLD_LOG
--let $assert_select=Persisted config file is corrupt. Please ensure mysqld-auto.cnf file is valid JSON.
--let $assert_count=1
--source include/assert_grep.inc

--remove_file $MYSQLD_LOG


--echo #
--echo # Bug #32761053: SET PERSIST_ONLY ... = DEFAULT IS BROKEN FOR ENUM VARIABLES
--echo #

SET PERSIST_ONLY replica_exec_mode = DEFAULT;
SELECT * FROM performance_schema.persisted_variables
  WHERE variable_name='replica_exec_mode';

RESET PERSIST;


--echo #
--echo # Bug #32640588: SOURCE FOR SKIP_SLAVE_START DISPLAYED AS COMPILED
--echo #   WHEN IT IS PERSISTED
--echo #

--echo # Should both be "COMPILED"
SELECT VARIABLE_NAME, VARIABLE_SOURCE FROM performance_schema.variables_info
  WHERE variable_name LIKE 'skip_%_start' ORDER BY 1;
SET PERSIST_ONLY skip_replica_start = ON;
--echo # Should still be "COMPILED" pre-restart
SELECT VARIABLE_NAME, VARIABLE_SOURCE FROM performance_schema.variables_info
  WHERE variable_name LIKE 'skip_%_start' ORDER BY 1;
--source include/restart_mysqld.inc
--echo # Success criteria: Should be "PERSISTED" post-restart
SELECT VARIABLE_NAME, VARIABLE_SOURCE FROM performance_schema.variables_info
  WHERE variable_name LIKE 'skip_%_start' ORDER BY 1;
RESET PERSIST;
--source include/restart_mysqld.inc

--echo #
--echo # Bug #33388488: NO DEPRECATION WARNING ON SERVER RESTART IF TWO OR MORE
--echo # PERSISTED OPTIONS
--echo #

SET PERSIST temptable_use_mmap=FALSE;
SET PERSIST delayed_insert_limit=100;

--let LOG_ERR=$MYSQLTEST_VARDIR/tmp/myerror.err
--let $restart_parameters=restart:--log_error=$LOG_ERR --no-console
--replace_result $LOG_ERR LOG_ERR
--source include/restart_mysqld.inc

--let $assert_text= Assert that there is a warning at bootstrap when setting temptable_use_mmap
--let $assert_file= $LOG_ERR
--let $assert_select= 'temptable_use_mmap' is deprecated and will be removed in a future release.
--let $assert_count= 1
--source include/assert_grep.inc

--let $assert_text= Assert that there is a warning at bootstrap when setting delayed_insert_limit
--let $assert_file= $LOG_ERR
--let $assert_select= '@@delayed_insert_limit' is deprecated and will be removed in a future release.
--let $assert_count= 1
--source include/assert_grep.inc

RESET PERSIST;
--remove_file $LOG_ERR

SET PERSIST_ONLY temptable_use_mmap=FALSE;
SET PERSIST_ONLY delayed_insert_limit=100;

--let $restart_parameters=restart:--log_error=$LOG_ERR --no-console
--replace_result $LOG_ERR LOG_ERR
--source include/restart_mysqld.inc

--let $assert_text= Assert that there is a warning at bootstrap when setting temptable_use_mmap
--let $assert_file= $LOG_ERR
--let $assert_select= 'temptable_use_mmap' is deprecated and will be removed in a future release.
--let $assert_count= 1
--source include/assert_grep.inc

--let $assert_text= Assert that there is a warning at bootstrap when setting delayed_insert_limit
--let $assert_file= $LOG_ERR
--let $assert_select= '@@delayed_insert_limit' is deprecated and will be removed in a future release.
--let $assert_count= 1
--source include/assert_grep.inc

RESET PERSIST;
--remove_file $LOG_ERR

# set to default
--let $restart_parameters=restart:
--source include/restart_mysqld.inc


--echo #
--echo # Bug #34751419: component system variable behaves differently from command-line and set persist
--echo #

call mtr.add_suppression("Duplicate variable name 'test_component.int_sys_var'");
INSTALL COMPONENT "file://component_test_sys_var_service";
SET PERSIST test_component.bool_sys_var = OFF;
SET PERSIST test_component.bool_ro_sys_var = OFF;
SET PERSIST replica_preserve_commit_order = OFF;
--source include/restart_mysqld.inc
UNINSTALL COMPONENT "file://component_test_sys_var_service";
INSTALL COMPONENT "file://component_test_sys_var_service";
--echo # Test: Should be set to OFF
SHOW VARIABLES LIKE 'test_component.bool_sys_var';
--echo # Test read only: Should be set to OFF
SHOW VARIABLES LIKE 'test_component.bool_ro_sys_var';

# Cleanup
RESET PERSIST test_component.bool_sys_var;
RESET PERSIST test_component.bool_ro_sys_var;
RESET PERSIST replica_preserve_commit_order;
SET GLOBAL replica_preserve_commit_order = DEFAULT;
UNINSTALL COMPONENT "file://component_test_sys_var_service";


--echo #
--echo # Bug #34897517: Can't set report_host to NULL
--echo #

--error ER_NULL_CANT_BE_PERSISTED_FOR_READONLY
SET PERSIST_ONLY report_host = NULL;

--error ER_NULL_CANT_BE_PERSISTED_FOR_READONLY
SET PERSIST_ONLY report_host = DEFAULT;

SET PERSIST ssl_crl = NULL;
RESET PERSIST ssl_crl;
SET GLOBAL ssl_crl = DEFAULT;


--echo # End of the 8.0 tests