Debian Codegraph @

mysql-test/suite/component_keyring_file/r/undo_encrypt_basic.result

raw
1 # ----------------------------------------------------------------------
2 # Setup
3 # Creating custom global manifest file for MySQL server
4 # Creating custom global configuration file for keyring component: component_keyring_file
5 # Re-starting mysql server with manifest file
6 # ----------------------------------------------------------------------
7 # WL#9289 InnoDB transparent tablespace data encryption for undo log
8 # create bootstrap file
9 # Stop the MTR default DB server
10 # Test1: bootstrap with 2 undo tablespaces and with keyring.
11 # Run the bootstrap command with keyring
12 # Start the DB server with undo log encryption disabled, and no keyring.
13 # Taking backup of global manifest file for MySQL server
14 # Enable undo log encryption, should report error in server log, since keyring is not loaded.
15 SET GLOBAL innodb_undo_log_encrypt = ON;
16 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
17 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
18 # Start the DB server with undo log encryption disabled and keyring loaded. It should success.
19 # Restore global manifest file for MySQL server from backup
20 # Enable undo log encryption, shouldn't report error in server log.
21 SET GLOBAL innodb_undo_log_encrypt = ON;
22 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION='Y' ENGINE = InnoDB;
23 SHOW CREATE TABLE t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `c1` int DEFAULT NULL,
27 `c2` char(20) DEFAULT NULL
28 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'
29 INSERT INTO t1 VALUES(0, "aaaaa");
30 INSERT INTO t1 VALUES(1, "bbbbb");
31 INSERT INTO t1 VALUES(2, "ccccc");
32 INSERT INTO t1 VALUES(3, "ddddd");
33 INSERT INTO t1 VALUES(4, "eeeee");
34 INSERT INTO t1 VALUES(5, "fffff");
35 INSERT INTO t1 VALUES(6, "ggggg");
36 INSERT INTO t1 VALUES(7, "hhhhh");
37 INSERT INTO t1 VALUES(8, "iiiii");
38 INSERT INTO t1 VALUES(9, "jjjjj");
39 INSERT INTO t1 select * from t1;
40 INSERT INTO t1 select * from t1;
41 INSERT INTO t1 select * from t1;
42 INSERT INTO t1 select * from t1;
43 INSERT INTO t1 select * from t1;
44 INSERT INTO t1 select * from t1;
45 CREATE TABLE t2(c1 INT, c2 char(20)) ENGINE = InnoDB;
46 INSERT INTO t2 select * from t1;
47 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
48 c1 c2
49 0 aaaaa
50 0 aaaaa
51 0 aaaaa
52 0 aaaaa
53 0 aaaaa
54 0 aaaaa
55 0 aaaaa
56 0 aaaaa
57 0 aaaaa
58 0 aaaaa
59 SELECT * FROM t2 ORDER BY c1 LIMIT 10;
60 c1 c2
61 0 aaaaa
62 0 aaaaa
63 0 aaaaa
64 0 aaaaa
65 0 aaaaa
66 0 aaaaa
67 0 aaaaa
68 0 aaaaa
69 0 aaaaa
70 0 aaaaa
71 # Start the DB server with undo log encryption enabled and keyring loaded. It should success.
72 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
73 c1 c2
74 0 aaaaa
75 0 aaaaa
76 0 aaaaa
77 0 aaaaa
78 0 aaaaa
79 0 aaaaa
80 0 aaaaa
81 0 aaaaa
82 0 aaaaa
83 0 aaaaa
84 SELECT * FROM t2 ORDER BY c1 LIMIT 10;
85 c1 c2
86 0 aaaaa
87 0 aaaaa
88 0 aaaaa
89 0 aaaaa
90 0 aaaaa
91 0 aaaaa
92 0 aaaaa
93 0 aaaaa
94 0 aaaaa
95 0 aaaaa
96 DROP TABLE t1,t2;
97 # Search for particular string of encryption metadata, should success since it's encrypted.
98 Pattern "lCC" found
99 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB;
100 START TRANSACTION;
101 INSERT INTO t1 VALUES(0, "aaaaa");
102 INSERT INTO t1 VALUES(1, "bbbbb");
103 SET GLOBAL innodb_undo_log_encrypt = OFF;
104 INSERT INTO t1 VALUES(2, "ccccc");
105 INSERT INTO t1 VALUES(3, "ddddd");
106 SET GLOBAL innodb_undo_log_encrypt = ON;
107 INSERT INTO t1 VALUES(4, "eeeee");
108 INSERT INTO t1 VALUES(5, "fffff");
109 COMMIT;
110 START TRANSACTION;
111 INSERT INTO t1 VALUES(6, "ggggg");
112 SET GLOBAL innodb_undo_log_encrypt = OFF;
113 INSERT INTO t1 VALUES(7, "hhhhh");
114 INSERT INTO t1 VALUES(8, "iiiii");
115 SET GLOBAL innodb_undo_log_encrypt = ON;
116 INSERT INTO t1 VALUES(9, "jjjjj");
117 # Kill and restart to confirm the encryption info can be retrieved properly.
118 # Kill the server
119 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
120 c1 c2
121 0 aaaaa
122 1 bbbbb
123 2 ccccc
124 3 ddddd
125 4 eeeee
126 5 fffff
127 DELETE FROM t1;
128 START TRANSACTION;
129 INSERT INTO t1 VALUES(0, "aaaaa");
130 INSERT INTO t1 VALUES(1, "bbbbb");
131 INSERT INTO t1 VALUES(2, "ccccc");
132 INSERT INTO t1 VALUES(3, "ddddd");
133 INSERT INTO t1 VALUES(4, "eeeee");
134 INSERT INTO t1 VALUES(5, "fffff");
135 INSERT INTO t1 VALUES(6, "ggggg");
136 INSERT INTO t1 VALUES(7, "hhhhh");
137 INSERT INTO t1 VALUES(8, "iiiii");
138 INSERT INTO t1 VALUES(9, "jjjjj");
139 # Start the DB server with undo log encryption disabled and keyring loaded. It should succeed.
140 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
141 c1 c2
142 ALTER INSTANCE ROTATE INNODB MASTER KEY;
143 INSERT INTO t1 VALUES(0, "aaaaa");
144 INSERT INTO t1 VALUES(1, "bbbbb");
145 INSERT INTO t1 VALUES(2, "ccccc");
146 INSERT INTO t1 VALUES(3, "ddddd");
147 INSERT INTO t1 VALUES(4, "eeeee");
148 INSERT INTO t1 VALUES(5, "fffff");
149 INSERT INTO t1 VALUES(6, "ggggg");
150 INSERT INTO t1 VALUES(7, "hhhhh");
151 INSERT INTO t1 VALUES(8, "iiiii");
152 INSERT INTO t1 VALUES(9, "jjjjj");
153 FLUSH ENGINE LOGS;
154 # Start the DB server with undo log encryption and keyring loaded. It should success.
155 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
156 c1 c2
157 0 aaaaa
158 1 bbbbb
159 2 ccccc
160 3 ddddd
161 4 eeeee
162 5 fffff
163 6 ggggg
164 7 hhhhh
165 8 iiiii
166 9 jjjjj
167 # Cleanup
168 DROP TABLE t1;
169 # restart the server with MTR default
170 # Remove residue files
171 # ----------------------------------------------------------------------
172 # Teardown
173 # Removing local keyring file for keyring component: component_keyring_file
174 # Removing global configuration file for keyring component: component_keyring_file
175 # Removing global manifest file for MySQL server
176 # Restarting server without the manifest file
177 # ----------------------------------------------------------------------