Debian Codegraph @

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

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