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