################################################################################ # Test script to test UNDO tablespace encryption setting to be displayed in # INFORMATION_SCHEMA.INNODB_TABLESPACES. ################################################################################ --source include/have_debug.inc # Note: # Due to encrypt options used below its not possible to return to clean # state: create and use separate datadir for this test --echo # --echo # Bug #31467626 : SERVER CRASH OBSERVED AT ROTATE_INNODB_MASTER_KEY --echo # let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; let $MYSQL_DATA_DIR = `select @@datadir`; SHOW VARIABLES LIKE "%innodb_undo_log_encrypt%"; --let $INNODB_FILE_PER_TABLE= `SELECT @@innodb_file_per_table` --let $PAGE_SIZE= `select @@innodb_page_size` --let $LOG_SIZE= `select @@innodb_redo_log_capacity` --let $MYSQL_ENC_DATADIR= $MYSQL_TMP_DIR/undo_tablespace_encrypt_dir --mkdir $MYSQL_ENC_DATADIR --let $BOOTSTRAP_LOG= $MYSQL_TMP_DIR/bootstrap_encrypt.log --let $BOOTSTRAP_SQL= $MYSQL_TMP_DIR/boot.sql --write_file $BOOTSTRAP_SQL CREATE DATABASE test; EOF --source include/shutdown_mysqld.inc --source include/wait_until_disconnected.inc --let NEW_CMD = $MYSQLD --no-defaults --innodb_dedicated_server=OFF --initialize-insecure --innodb_redo_log_capacity=$LOG_SIZE --innodb_page_size=$PAGE_SIZE --datadir=$MYSQL_ENC_DATADIR --init-file=$BOOTSTRAP_SQL --secure-file-priv="" >>$BOOTSTRAP_LOG 2>&1 --exec $NEW_CMD --echo ############################################################################### --echo # With keyring plugin and innodb_undo_log_encrypt=ON --echo ############################################################################### --let $restart_parameters="restart: --datadir=$MYSQL_ENC_DATADIR --log_error_verbosity=3 --early-plugin-load="keyring_file=$KEYRING_PLUGIN" $KEYRING_PLUGIN_OPT --keyring_file_data=$MYSQL_TMP_DIR/my_keyfile --innodb_undo_log_encrypt=ON --log-error=$MYSQLTEST_VARDIR/log/mysqld.1.err " --source include/start_mysqld_no_echo.inc SHOW VARIABLES LIKE "%innodb_undo_log_encrypt%"; CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu'; CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu'; SELECT NAME, SPACE_TYPE, ENCRYPTION, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'; SHOW VARIABLES LIKE "%innodb_undo_log_encrypt%"; CREATE DATABASE crypt; DROP PROCEDURE IF EXISTS crypt.rotate_and_truncate; DELIMITER |; CREATE PROCEDURE crypt.rotate_and_truncate(IN start BIGINT) BEGIN SET @idx = start; SET global innodb_undo_log_encrypt=ON; WHILE (@idx > 0) DO ALTER UNDO TABLESPACE undo_003 SET ACTIVE; ALTER UNDO TABLESPACE undo_004 SET ACTIVE; ALTER INSTANCE ROTATE INNODB MASTER KEY; ALTER UNDO TABLESPACE undo_003 SET INACTIVE; ALTER UNDO TABLESPACE undo_004 SET INACTIVE; SET @idx = @idx - 1; END WHILE; END| DELIMITER ;| call crypt.rotate_and_truncate(50); ALTER UNDO TABLESPACE undo_003 SET ACTIVE; ALTER UNDO TABLESPACE undo_004 SET ACTIVE; SELECT NAME, SPACE_TYPE, ENCRYPTION, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'; --echo # --echo # Make sure the rotation code recovers when --echo # one of the tables cannot be rotated. --echo # SET SESSION DEBUG="+d,ib_encryption_rotate_skip"; ALTER INSTANCE ROTATE INNODB MASTER KEY; SET SESSION DEBUG="-d,ib_encryption_rotate_skip"; let SEARCH_PATTERN = ib_encryption_rotate_skip; --source include/search_pattern.inc SELECT NAME, SPACE_TYPE, ENCRYPTION, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'; ALTER INSTANCE ROTATE INNODB MASTER KEY; SELECT NAME, SPACE_TYPE, ENCRYPTION, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'; --echo # --echo # Get the UNDO tablespaces ready to drop --echo # ALTER UNDO TABLESPACE undo_003 SET INACTIVE; let $inactive_undo_space = undo_003; source include/wait_until_undo_space_is_empty.inc; SELECT NAME, SPACE_TYPE, ENCRYPTION, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'; --echo # --echo # Be sure that undo tablespaces can be dropped when a concurrent --echo # ENCRYPTION ROTATE is occurring --echo # --echo # Start and pause a ROTATE INNODB MASTER KEY in a second thread connect (con2,localhost,root); SET DEBUG_SYNC='ib_pause_encryption_rotate SIGNAL rotate_paused WAIT_FOR rotate_go'; # This ROTATE thread will get an exclusive backup lock and wait. --send ALTER INSTANCE ROTATE INNODB MASTER KEY connection default; SET DEBUG_SYNC='now WAIT_FOR rotate_paused'; --echo # In a third thread, try to drop an undo tablespace connect (con3,localhost,root); # This command should wait for a shared backup lock --send DROP UNDO TABLESPACE undo_003 --echo # In a fourth thread, try to set an undo tablespace inactive --echo # which would cause the purge thread to truncate it. connect (con4,localhost,root); # This command should wait for a shared backup lock. --send ALTER UNDO TABLESPACE undo_004 SET INACTIVE; --echo # Switch to the default thread and check that the DDL are waiting connection default; --sleep 2 # This log message should NOT be found because the thread is # waiting on the ROTATE thread which has an exclusive backup lock let SEARCH_PATTERN = Dropped undo tablespace 'undo_003'; --source include/search_pattern.inc # This should show that 'undo_004 is still active. SELECT NAME, SPACE_TYPE, ENCRYPTION, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE='Undo'; --echo # Resume the ROTATE thread. SET DEBUG_SYNC='now SIGNAL rotate_go'; --echo # Reap the other three threads. connection con2; --reap connection con3; --reap connection con4; --reap --echo # Return to the default thread and disconnect the other three. connection default; disconnect con2; disconnect con3; disconnect con4; --echo # --echo # Cleanup --echo # let $inactive_undo_space = undo_004; source include/wait_until_undo_space_is_empty.inc; DROP UNDO TABLESPACE undo_004; DROP PROCEDURE crypt.rotate_and_truncate; DROP DATABASE crypt; --echo # Reset to initial state --let $restart_parameters="restart:" --source include/restart_mysqld.inc # Now these log message should be found. # The 'Encryption' message should occur in the log before the 'Dropped' messages. let SEARCH_PATTERN = The InnoDB Encryption Master Key has been rotated; --source include/search_pattern.inc let SEARCH_PATTERN = Dropped undo tablespace 'undo_003'; --source include/search_pattern.inc let SEARCH_PATTERN = Dropped undo tablespace 'undo_004'; --source include/search_pattern.inc --remove_file $MYSQL_TMP_DIR/my_keyfile --remove_file $BOOTSTRAP_LOG --remove_file $BOOTSTRAP_SQL --force-rmdir $MYSQL_ENC_DATADIR