Debian Codegraph @

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

raw
1 # InnoDB transparent tablespace data encryption
2 # This test case will test basic encryption support features.
3
4 --source include/no_valgrind_without_big.inc
5 --source include/have_innodb_max_16k.inc
6 --source include/have_debug.inc
7 --source include/have_punch_hole.inc
8
9 #let $KEYRING_PLUGIN = 'keyring_file.so';
10
11 --disable_query_log
12 call mtr.add_suppression("\\[Warning\\] .* Ignoring tablespace .* because it could not be opened");
13 call mtr.add_suppression("\\[ERROR\\] .* Encryption can't find master key, please check the keyring plugin is loaded.");
14 call mtr.add_suppression("\\[ERROR\\] .* Failed to find tablespace for table `\.\.*`\.`\.\.*` in the cache.");
15 call mtr.add_suppression("\\[ERROR\\] .* Operating system error number 2 in a file operation.");
16 call mtr.add_suppression("\\[ERROR\\] .* The error means the system cannot find the path specified.");
17 call mtr.add_suppression("\\[ERROR\\] .* Could not find a valid tablespace file for");
18 call mtr.add_suppression("\\[ERROR\\] .* If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.");
19 call mtr.add_suppression("\\[ERROR\\] .* Tablespace [0-9]+, name 'test.*t1', unable to open file '.*test.*t1.ibd' - Data structure corruption");
20 call mtr.add_suppression("\\[ERROR\\] .* Encryption information in datafile: .* can't be decrypted, please confirm the keyfile is match and keyring plugin is loaded");
21 call mtr.add_suppression("\\[ERROR\\] .* ibd can't be decrypted, please confirm the keyfile is match and keyring plugin is loaded.");
22 call mtr.add_suppression("\\[ERROR\\] .* Check keyring plugin fail, please check the keyring plugin is loaded.");
23 --enable_query_log
24
25 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
26
27 SET GLOBAL innodb_file_per_table = 0;
28 SELECT @@innodb_file_per_table;
29 # Test create encrypted table when inndb_file_per_table is 0,
30 # Should fail, since it will try to create table in system tablespace and
31 # We cannot create encrypted table in unencrypted system tablespace.
32 --error ER_INVALID_ENCRYPTION_REQUEST
33 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
34 SHOW WARNINGS;
35
36 SET GLOBAL innodb_file_per_table = 1;
37 SELECT @@innodb_file_per_table;
38
39 # Test create encrypted table in system tablespace,
40 # Should fail, 'ENCRYPTION=Y' option is not allowed with system tablespace.
41 --error ER_INVALID_ENCRYPTION_REQUEST
42 CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system;
43 SHOW WARNINGS;
44 CREATE TABLE t1(c int) ENCRYPTION="N" tablespace innodb_system;
45 DROP TABLE t1;
46
47 # Test create temporary encrypted table,
48 # Should fail, 'ENCRYPTION' clause is not allowed with temporary tablespace.
49 --error ER_CANNOT_USE_ENCRYPTION_CLAUSE
50 CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="Y";
51 SHOW WARNINGS;
52 --error ER_CANNOT_USE_ENCRYPTION_CLAUSE
53 CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="N";
54 SHOW WARNINGS;
55
56 # Test create encrypted table with incorrect option.
57 --error ER_INVALID_ENCRYPTION_OPTION
58 CREATE TABLE t1(c int) ENCRYPTION="R" ENGINE = InnoDB;
59
60 # Crash/recovery test.
61 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
62
63 INSERT INTO t1 VALUES(0, "aaaaa");
64 INSERT INTO t1 VALUES(1, "bbbbb");
65 INSERT INTO t1 VALUES(2, "ccccc");
66 INSERT INTO t1 VALUES(3, "ddddd");
67 INSERT INTO t1 VALUES(4, "eeeee");
68 INSERT INTO t1 VALUES(5, "fffff");
69 INSERT INTO t1 VALUES(6, "ggggg");
70 INSERT INTO t1 VALUES(7, "hhhhh");
71 INSERT INTO t1 VALUES(8, "iiiii");
72 INSERT INTO t1 VALUES(9, "jjjjj");
73
74 # Restart to confirm the encryption info can be retrieved properly.
75 --source include/kill_mysqld.inc
76 --let $restart_parameters= restart:
77 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH
78 --replace_regex /\.dll/.so/
79 --source include/start_mysqld.inc
80
81 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
82
83 # Test alter table.
84 # We don't support inplace alter
85 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
86 ALTER TABLE t1 ENCRYPTION="N", algorithm=inplace;
87
88 # Don't support move it to shared tablespace
89 --error ER_INVALID_ENCRYPTION_REQUEST
90 ALTER TABLE t1 TABLESPACE=`innodb_system`;
91
92 # Alter using copy algorithm
93 ALTER TABLE t1 ENCRYPTION="N", algorithm=copy;
94
95 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
96
97 DROP TABLE t1;
98
99 CREATE TABLE t1 (c1 int) ENCRYPTION='N';
100
101 --error ER_INVALID_ENCRYPTION_OPTION
102 ALTER TABLE t1 ENCRYPTION='P',algorithm=copy;
103
104 ALTER TABLE t1 ADD KEY k1 (c1) ,algorithm=inplace;
105
106 # Alter using inpalce algorithm
107 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
108 ALTER TABLE t1 ENCRYPTION='Y',algorithm=inplace;
109
110 drop table t1;
111
112 # Test compression + encryption.
113 --disable_warnings
114 CREATE TABLE t1(c1 INT PRIMARY KEY) COMPRESSION = "ZLIB" ENCRYPTION = "Y" ENGINE = InnoDB;
115 let COMPR_ZIP_WARN= `SHOW WARNINGS`;
116 --enable_warnings
117
118 INSERT INTO t1 VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
119
120 SHOW CREATE TABLE t1;
121
122 FLUSH TABLES t1 WITH READ LOCK;
123 UNLOCK TABLES;
124
125 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
126
127 # Restart to confirm the encryption info can be retrieved properly.
128 --let $restart_parameters= restart:
129 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH
130 --replace_regex /\.dll/.so/
131 --source include/restart_mysqld.inc
132
133 SELECT * FROM t1 ORDER BY c1;
134
135 DROP TABLE t1;
136
137 # Test old compression + encryption.
138 CREATE TABLE t1(c1 int null) ENCRYPTION='Y' ROW_FORMAT=compressed;
139
140 INSERT INTO t1 VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
141
142 SHOW CREATE TABLE t1;
143
144 FLUSH TABLES t1 WITH READ LOCK;
145 UNLOCK TABLES;
146
147 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
148
149 # Restart to confirm the encryption info can be retrieved properly.
150 --let $restart_parameters= restart:
151 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH
152 --replace_regex /\.dll/.so/
153 --source include/restart_mysqld.inc
154
155 SELECT * FROM t1 ORDER BY c1;
156
157 DROP TABLE t1;
158
159 # Test encryption for rtree.
160 --disable_warnings
161 CREATE TABLE t1(c1 INT PRIMARY KEY, g geometry not null, spatial index(g)) ENCRYPTION = "Y" ENGINE = InnoDB;
162 --enable_warnings
163
164 INSERT INTO t1 VALUES(0, POINT(0, 0));
165 INSERT INTO t1 VALUES(1, POINT(1, 1));
166 INSERT INTO t1 VALUES(2, POINT(2, 2));
167 INSERT INTO t1 VALUES(3, POINT(3, 3));
168 INSERT INTO t1 VALUES(4, POINT(4, 4));
169 INSERT INTO t1 VALUES(5, POINT(5, 5));
170 INSERT INTO t1 VALUES(6, POINT(6, 6));
171 INSERT INTO t1 VALUES(7, POINT(7, 7));
172 INSERT INTO t1 VALUES(8, POINT(8, 8));
173 INSERT INTO t1 VALUES(9, POINT(9, 9));
174
175 SHOW CREATE TABLE t1;
176
177 FLUSH TABLES t1 WITH READ LOCK;
178 UNLOCK TABLES;
179
180 SELECT c1, ST_AsText(g) FROM t1 ORDER BY c1 LIMIT 10;
181
182 # Restart to confirm the encryption info can be retrieved properly.
183 --let $restart_parameters= restart: --innodb_strict_mode=OFF --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT
184 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH
185 --replace_regex /\.dll/.so/
186 --source include/restart_mysqld.inc
187
188 SELECT c1, ST_AsText(g) FROM t1 ORDER BY c1 LIMIT 10;
189
190 DROP TABLE t1;
191
192 # Test alter table when file per table is OFF and strict mode = 0.
193 SET GLOBAL innodb_file_per_table=OFF;
194
195 CREATE TABLE t1 (c1 int);
196
197 --error ER_ILLEGAL_HA_CREATE_OPTION
198 ALTER TABLE t1 COMPRESSION='zlib';
199 SHOW WARNINGS;
200
201 --error ER_INVALID_ENCRYPTION_REQUEST
202 ALTER TABLE t1 ENCRYPTION='Y',ALGORITHM=COPY;
203
204 # Cleanup
205 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
206 DROP TABLE t1;
207
208 # Restart with default innodb_strict_mode
209 --let $restart_parameters= restart:
210 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH
211 --replace_regex /\.dll/.so/
212 --source include/restart_mysqld.inc