Debian Codegraph @

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

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