Debian Codegraph @

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

raw
1 DROP TABLE IF EXISTS t1;
2 SET GLOBAL innodb_file_per_table = 1;
3 SELECT @@innodb_file_per_table;
4 @@innodb_file_per_table
5 1
6 # Create a table with encryption
7 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
8 SHOW CREATE TABLE t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `c1` int DEFAULT NULL,
12 `c2` char(20) DEFAULT NULL
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
14 INSERT INTO t1 VALUES(0, "aaaaa");
15 INSERT INTO t1 VALUES(1, "bbbbb");
16 INSERT INTO t1 VALUES(2, "ccccc");
17 INSERT INTO t1 VALUES(3, "ddddd");
18 INSERT INTO t1 VALUES(4, "eeeee");
19 INSERT INTO t1 VALUES(5, "fffff");
20 INSERT INTO t1 VALUES(6, "ggggg");
21 INSERT INTO t1 VALUES(7, "hhhhh");
22 INSERT INTO t1 VALUES(8, "iiiii");
23 INSERT INTO t1 VALUES(9, "jjjjj");
24 INSERT INTO t1 select * from t1;
25 INSERT INTO t1 select * from t1;
26 INSERT INTO t1 select * from t1;
27 INSERT INTO t1 select * from t1;
28 INSERT INTO t1 select * from t1;
29 INSERT INTO t1 select * from t1;
30 SELECT * FROM t1 LIMIT 10;
31 c1 c2
32 0 aaaaa
33 1 bbbbb
34 2 ccccc
35 3 ddddd
36 4 eeeee
37 5 fffff
38 6 ggggg
39 7 hhhhh
40 8 iiiii
41 9 jjjjj
42 # Test export/import encrypted tablespace.
43 FLUSH TABLES test.t1 FOR EXPORT;
44 UNLOCK TABLES;
45 ALTER TABLE test.t1 DISCARD TABLESPACE;
46 ALTER TABLE test.t1 IMPORT TABLESPACE;
47 CHECK TABLE test.t1;
48 Table Op Msg_type Msg_text
49 test.t1 check status OK
50 SHOW CREATE TABLE test.t1;
51 Table Create Table
52 t1 CREATE TABLE `t1` (
53 `c1` int DEFAULT NULL,
54 `c2` char(20) DEFAULT NULL
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
56 SELECT * FROM test.t1 LIMIT 10;
57 c1 c2
58 0 aaaaa
59 1 bbbbb
60 2 ccccc
61 3 ddddd
62 4 eeeee
63 5 fffff
64 6 ggggg
65 7 hhhhh
66 8 iiiii
67 9 jjjjj
68 # restart:
69 SELECT * FROM test.t1 LIMIT 10;
70 c1 c2
71 0 aaaaa
72 1 bbbbb
73 2 ccccc
74 3 ddddd
75 4 eeeee
76 5 fffff
77 6 ggggg
78 7 hhhhh
79 8 iiiii
80 9 jjjjj
81 ALTER TABLE test.t1 DISCARD TABLESPACE;
82 # Try import in another session.
83 ALTER TABLE test.t1 IMPORT TABLESPACE;
84 SELECT * FROM test.t1 LIMIT 10;
85 c1 c2
86 0 aaaaa
87 1 bbbbb
88 2 ccccc
89 3 ddddd
90 4 eeeee
91 5 fffff
92 6 ggggg
93 7 hhhhh
94 8 iiiii
95 9 jjjjj
96 ALTER TABLE test.t1 DISCARD TABLESPACE;
97 # Import without .cfg file is posible
98 # copying .cfp and .ibd file
99 ALTER TABLE test.t1 IMPORT TABLESPACE;
100 Warnings:
101 Warning 1810 InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t1.cfg', will attempt to import without schema verification
102 SELECT * FROM test.t1 LIMIT 10;
103 c1 c2
104 0 aaaaa
105 1 bbbbb
106 2 ccccc
107 3 ddddd
108 4 eeeee
109 5 fffff
110 6 ggggg
111 7 hhhhh
112 8 iiiii
113 9 jjjjj
114 ALTER INSTANCE ROTATE INNODB MASTER KEY;
115 SELECT * FROM test.t1 LIMIT 10;
116 c1 c2
117 0 aaaaa
118 1 bbbbb
119 2 ccccc
120 3 ddddd
121 4 eeeee
122 5 fffff
123 6 ggggg
124 7 hhhhh
125 8 iiiii
126 9 jjjjj
127 ALTER TABLE test.t1 DISCARD TABLESPACE;
128 # Import without .cfp file
129 # copying .cfg and .ibd file
130 ALTER TABLE test.t1 IMPORT TABLESPACE;
131 ERROR HY000: Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.)
132 ALTER INSTANCE ROTATE INNODB MASTER KEY;
133 # Import without .idb file
134 # copying .cfp and .cfg file
135 ALTER TABLE test.t1 IMPORT TABLESPACE;
136 ERROR HY000: Internal error: Cannot reset LSNs in table `test`.`t1` : Tablespace not found
137 ALTER INSTANCE ROTATE INNODB MASTER KEY;
138 # Schema mismatch dest table without encryption - fix result
139 DROP TABLE t1;
140 CREATE TABLE test.t1(c1 INT, c2 char(20)) ENGINE = InnoDB;
141 ALTER TABLE test.t1 DISCARD TABLESPACE;
142 ALTER TABLE test.t1 IMPORT TABLESPACE;
143 ERROR HY000: Schema mismatch (Encryption attribute in the file does not match the dictionary.)
144 # Import got expected error.
145 DROP TABLE t1;
146 CREATE TABLE test.t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
147 ALTER TABLE test.t1 DISCARD TABLESPACE;
148 SET SESSION DEBUG="+d, fsp_header_rotate_encryption_failure";
149 ALTER TABLE test.t1 IMPORT TABLESPACE;
150 ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine
151 Warnings:
152 Note 1051 Unknown table 'test.t1'