Debian Codegraph @

mysql-test/suite/innodb/t/undo_encrypt_basic.test

raw
1 --echo # WL#9289 InnoDB transparent tablespace data encryption for undo log
2 # This test case will test basic undo encryption support features.
3
4 --source include/no_valgrind_without_big.inc
5 --source include/have_innodb_max_16k.inc
6
7 --disable_query_log
8 call mtr.add_suppression("\\[Warning\\] .* Ignoring tablespace .* because it could not be opened");
9 call mtr.add_suppression("\\[ERROR\\] .* Encryption can't find master key, please check the keyring is loaded.");
10 call mtr.add_suppression("\\[ERROR\\] .* Failed to find tablespace for table `\.\.*`\.`\.\.*` in the cache.");
11 call mtr.add_suppression("\\[ERROR\\] .* Operating system error number 2 in a file operation.");
12 call mtr.add_suppression("\\[ERROR\\] .* The error means the system cannot find the path specified.");
13 call mtr.add_suppression("\\[ERROR\\] .* Could not find a valid tablespace file for");
14 call mtr.add_suppression("\\[ERROR\\] .* If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.");
15 call mtr.add_suppression("\\[ERROR\\] .* Can't set undo tablespace 'innodb_undo_001' to be encrypted");
16 --enable_query_log
17
18 let $MYSQLD_BASEDIR= `select @@basedir`;
19 let $START_PAGE_SIZE= `select @@innodb_page_size`;
20 let $LOG_CAPACITY= `select @@innodb_redo_log_capacity`;
21
22 # Create path for ibdata* & undo* files both DBs
23 --mkdir $MYSQL_TMP_DIR/innodb_undo_data_dir
24 --mkdir $MYSQL_TMP_DIR/innodb_data_home_dir
25 --mkdir $MYSQL_TMP_DIR/datadir
26
27 # Set path for --datadir
28 let $MYSQLD_DATADIR = $MYSQL_TMP_DIR/datadir/data;
29
30 # Set path for undo* files.
31 let $MYSQLD_UNDO_DATADIR = $MYSQL_TMP_DIR/innodb_undo_data_dir;
32
33 # Set path for ibdata* files.
34 let $MYSQLD_HOME_DATA_DIR = $MYSQL_TMP_DIR/innodb_data_home_dir;
35
36 let BOOTSTRAP_SQL=$MYSQL_TMP_DIR/boot.sql;
37
38 --echo # create bootstrap file
39 write_file $BOOTSTRAP_SQL;
40 CREATE DATABASE test DEFAULT CHARACTER SET latin1;
41 EOF
42
43 --echo # Stop the MTR default DB server
44 --source include/shutdown_mysqld.inc
45
46 --echo # Test1: bootstrap with 2 undo tablespaces and with keyring.
47 let NEW_CMD = $MYSQLD --no-defaults --innodb_dedicated_server=OFF --initialize-insecure --innodb_redo_log_capacity=$LOG_CAPACITY --innodb_page_size=$START_PAGE_SIZE --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --basedir=$MYSQLD_BASEDIR --datadir=$MYSQLD_DATADIR --init-file=$BOOTSTRAP_SQL --secure-file-priv="" --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --keyring_file_data=$MYSQL_TMP_DIR/my_key_undo1 $KEYRING_PLUGIN_OPT </dev/null>>$MYSQLTEST_VARDIR/tmp/bootstrap2.log 2>&1;
48
49 --echo # Run the bootstrap command with keyring
50 --exec $NEW_CMD
51
52 --echo # Start the DB server with undo log encryption disabled, and no keyring plugin.
53 --replace_result $MYSQLD_HOME_DATA_DIR MYSQLD_HOME_DATA_DIR $MYSQLD_UNDO_DATADIR MYSQLD_UNDO_DATADIR $MYSQLD_DATADIR MYSQLD_DATADIR $START_PAGE_SIZE PAGE_SIZE $LOG_CAPACITY LOG_CAPACITY
54 --let $restart_parameters="restart: --innodb_page_size=$START_PAGE_SIZE --innodb_redo_log_capacity=$LOG_CAPACITY --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --datadir=$MYSQLD_DATADIR"
55 --source include/start_mysqld_no_echo.inc
56
57 --echo # Enable undo log encryption, should report error in server log, since keyring is not loaded.
58 SET GLOBAL innodb_undo_log_encrypt = ON;
59
60 --error ER_CANNOT_FIND_KEY_IN_KEYRING
61 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
62
63 --echo # Start the DB server with undo log encryption disabled and keyring plugin loaded. It should success.
64 --let $restart_parameters="restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" $KEYRING_PLUGIN_OPT --keyring_file_data=$MYSQL_TMP_DIR/my_key_undo1 --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --datadir=$MYSQLD_DATADIR --innodb_page_size=$START_PAGE_SIZE --innodb_redo_log_capacity=$LOG_CAPACITY"
65 --replace_result $MYSQL_TMP_DIR TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $MYSQLD_HOME_DATA_DIR HOME_DIR $MYSQLD_UNDO_DATADIR UNDO_DATADIR $MYSQLD_DATADIR DATADIR $START_PAGE_SIZE PAGE_SIZE $LOG_CAPACITY LOG_CAPACITY
66 --replace_regex /\.dll/.so/
67 --source include/restart_mysqld_no_echo.inc
68
69 --echo # Enable undo log encryption, shouldn't report error in server log.
70 SET GLOBAL innodb_undo_log_encrypt = ON;
71
72 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION='Y' ENGINE = InnoDB;
73
74 SHOW CREATE TABLE t1;
75
76 INSERT INTO t1 VALUES(0, "aaaaa");
77 INSERT INTO t1 VALUES(1, "bbbbb");
78 INSERT INTO t1 VALUES(2, "ccccc");
79 INSERT INTO t1 VALUES(3, "ddddd");
80 INSERT INTO t1 VALUES(4, "eeeee");
81 INSERT INTO t1 VALUES(5, "fffff");
82 INSERT INTO t1 VALUES(6, "ggggg");
83 INSERT INTO t1 VALUES(7, "hhhhh");
84 INSERT INTO t1 VALUES(8, "iiiii");
85 INSERT INTO t1 VALUES(9, "jjjjj");
86 INSERT INTO t1 select * from t1;
87 INSERT INTO t1 select * from t1;
88 INSERT INTO t1 select * from t1;
89 INSERT INTO t1 select * from t1;
90 INSERT INTO t1 select * from t1;
91 INSERT INTO t1 select * from t1;
92
93 CREATE TABLE t2(c1 INT, c2 char(20)) ENGINE = InnoDB;
94 INSERT INTO t2 select * from t1;
95
96 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
97 SELECT * FROM t2 ORDER BY c1 LIMIT 10;
98
99 --echo # Start the DB server with undo log encryption enabled and keyring plugin loaded. It should success.
100 --let $restart_parameters="restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" $KEYRING_PLUGIN_OPT --keyring_file_data=$MYSQL_TMP_DIR/my_key_undo1 --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --datadir=$MYSQLD_DATADIR --innodb_page_size=$START_PAGE_SIZE --innodb_redo_log_capacity=$LOG_CAPACITY --innodb_undo_log_encrypt=OFF"
101 --replace_result $MYSQL_TMP_DIR TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PATH $MYSQLD_HOME_DATA_DIR HOME_DIR $MYSQLD_UNDO_DATADIR UNDO_DIR $MYSQLD_DATADIR DATADIR $START_PAGE_SIZE PAGE_SIZE $LOG_CAPACITY LOG_CAPACITY
102 --replace_regex /\.dll/.so/
103 --source include/restart_mysqld_no_echo.inc
104
105 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
106 SELECT * FROM t2 ORDER BY c1 LIMIT 10;
107
108 DROP TABLE t1,t2;
109
110 --echo # Search for particular string of encryption metadata, should success since it's encrypted.
111 let SEARCH_FILE= $MYSQLD_UNDO_DATADIR/undo_001;
112 let SEARCH_PATTERN= lCC;
113 --source include/search_pattern.inc
114
115 # Crash/recovery test.
116 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB;
117
118 START TRANSACTION;
119 INSERT INTO t1 VALUES(0, "aaaaa");
120 INSERT INTO t1 VALUES(1, "bbbbb");
121 SET GLOBAL innodb_undo_log_encrypt = OFF;
122 INSERT INTO t1 VALUES(2, "ccccc");
123 INSERT INTO t1 VALUES(3, "ddddd");
124 SET GLOBAL innodb_undo_log_encrypt = ON;
125 INSERT INTO t1 VALUES(4, "eeeee");
126 INSERT INTO t1 VALUES(5, "fffff");
127 COMMIT;
128 START TRANSACTION;
129 INSERT INTO t1 VALUES(6, "ggggg");
130 SET GLOBAL innodb_undo_log_encrypt = OFF;
131 INSERT INTO t1 VALUES(7, "hhhhh");
132 INSERT INTO t1 VALUES(8, "iiiii");
133 SET GLOBAL innodb_undo_log_encrypt = ON;
134 INSERT INTO t1 VALUES(9, "jjjjj");
135
136 --echo # Kill and restart to confirm the encryption info can be retrieved properly.
137 --source include/kill_mysqld.inc
138 --exec echo "restart:--early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring_log1 $KEYRING_PLUGIN_OPT --keyring_file_data=$MYSQL_TMP_DIR/my_key_undo1 --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --datadir=$MYSQLD_DATADIR --innodb_page_size=$START_PAGE_SIZE --innodb_redo_log_capacity=$LOG_CAPACITY --innodb_undo_log_encrypt=ON" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
139
140 --enable_reconnect
141 --source include/wait_until_connected_again.inc
142 --disable_reconnect
143
144 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
145 DELETE FROM t1;
146
147 START TRANSACTION;
148 INSERT INTO t1 VALUES(0, "aaaaa");
149 INSERT INTO t1 VALUES(1, "bbbbb");
150 INSERT INTO t1 VALUES(2, "ccccc");
151 INSERT INTO t1 VALUES(3, "ddddd");
152 INSERT INTO t1 VALUES(4, "eeeee");
153 INSERT INTO t1 VALUES(5, "fffff");
154 INSERT INTO t1 VALUES(6, "ggggg");
155 INSERT INTO t1 VALUES(7, "hhhhh");
156 INSERT INTO t1 VALUES(8, "iiiii");
157 INSERT INTO t1 VALUES(9, "jjjjj");
158
159 --echo # Start the DB server with undo log encryption disabled and keyring plugin loaded. It should succeed.
160 --let $restart_parameters="restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" $KEYRING_PLUGIN_OPT --keyring_file_data=$MYSQL_TMP_DIR/my_key_undo1 --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --datadir=$MYSQLD_DATADIR --innodb_page_size=$START_PAGE_SIZE --innodb_redo_log_capacity=$LOG_CAPACITY"
161 --replace_result $MYSQL_TMP_DIR TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PATH $MYSQLD_HOME_DATA_DIR HOME_DIR $MYSQLD_UNDO_DATADIR UNDO_DIR $MYSQLD_DATADIR DATADIR $START_PAGE_SIZE PAGE_SIZE $LOG_CAPACITY LOG_CAPACITY
162 --replace_regex /\.dll/.so/
163 --source include/restart_mysqld_no_echo.inc
164
165 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
166
167 # Test key rotation.
168 ALTER INSTANCE ROTATE INNODB MASTER KEY;
169
170 INSERT INTO t1 VALUES(0, "aaaaa");
171 INSERT INTO t1 VALUES(1, "bbbbb");
172 INSERT INTO t1 VALUES(2, "ccccc");
173 INSERT INTO t1 VALUES(3, "ddddd");
174 INSERT INTO t1 VALUES(4, "eeeee");
175 INSERT INTO t1 VALUES(5, "fffff");
176 INSERT INTO t1 VALUES(6, "ggggg");
177 INSERT INTO t1 VALUES(7, "hhhhh");
178 INSERT INTO t1 VALUES(8, "iiiii");
179 INSERT INTO t1 VALUES(9, "jjjjj");
180
181 FLUSH ENGINE LOGS;
182
183 --echo # Start the DB server with undo log encryption and keyring plugin loaded. It should success.
184 --let $restart_parameters="restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" $KEYRING_PLUGIN_OPT --keyring_file_data=$MYSQL_TMP_DIR/my_key_undo1 --innodb_data_home_dir=$MYSQLD_HOME_DATA_DIR --innodb_undo_directory=$MYSQLD_UNDO_DATADIR --datadir=$MYSQLD_DATADIR --innodb_page_size=$START_PAGE_SIZE --innodb_redo_log_capacity=$LOG_CAPACITY --innodb_undo_log_encrypt=ON"
185 --replace_result $MYSQL_TMP_DIR TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PATH $MYSQLD_HOME_DATA_DIR HOME_DIR $MYSQLD_UNDO_DATADIR UNDO_DIR $MYSQLD_DATADIR DATADIR $START_PAGE_SIZE PAGE_SIZE $LOG_CAPACITY LOG_CAPACITY
186 --replace_regex /\.dll/.so/
187 --source include/restart_mysqld_no_echo.inc
188
189 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
190
191 --echo # Cleanup
192 --remove_file $MYSQL_TMP_DIR/my_key_undo1
193 DROP TABLE t1;
194
195 --echo # restart the server with MTR default
196 --let $restart_parameters=
197 --source include/restart_mysqld.inc
198
199 --remove_file $BOOTSTRAP_SQL
200
201 --echo # Remove residue files
202 --force-rmdir $MYSQL_TMP_DIR/datadir
203 --force-rmdir $MYSQL_TMP_DIR/innodb_data_home_dir
204 --force-rmdir $MYSQL_TMP_DIR/innodb_undo_data_dir
205 --remove_file $MYSQLTEST_VARDIR/tmp/bootstrap2.log