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
|
call mtr.add_suppression("InnoDB: Tablespace for table .* is set as discarded.");
call mtr.add_suppression("InnoDB: Cannot calculate statistics for table .* because the .ibd file is missing. Please refer to .* for how to resolve the issue.");
SET GLOBAL innodb_compression_algorithm = 1;
create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=4;
show warnings;
Level Code Message
create table t2(c1 bigint not null, b char(200)) engine=innodb page_compressed=1 encrypted=yes encryption_key_id=4;
show warnings;
Level Code Message
create table t3(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=4;
show warnings;
Level Code Message
create table t4(c1 bigint not null, b char(200)) engine=innodb page_compressed=1;
show warnings;
Level Code Message
create procedure innodb_insert_proc (repeat_count int)
begin
declare current_num int;
set current_num = 0;
while current_num < repeat_count do
insert into t1 values(current_num, repeat('foobar',30));
insert into t2 values(current_num, repeat('barfoo',30));
insert into t3 values(current_num, repeat('tmpres',30));
insert into t4 values(current_num, repeat('mysql',30));
set current_num = current_num + 1;
end while;
end//
commit;
set autocommit=0;
call innodb_insert_proc(2000);
commit;
set autocommit=1;
select count(*) from t1;
count(*)
2000
select count(*) from t2;
count(*)
2000
select count(*) from t3;
count(*)
2000
select count(*) from t4;
count(*)
2000
FLUSH TABLE t1,t2,t3,t4 FOR EXPORT;
# List before copying files
t1.cfg
t1.frm
t1.ibd
t2.cfg
t2.frm
t2.ibd
t3.cfg
t3.frm
t3.ibd
t4.cfg
t4.frm
t4.ibd
backup: t1
backup: t2
backup: t3
backup: t4
t1.cfg
t1.frm
t1.ibd
t2.cfg
t2.frm
t2.ibd
t3.cfg
t3.frm
t3.ibd
t4.cfg
t4.frm
t4.ibd
UNLOCK TABLES;
ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t3 DISCARD TABLESPACE;
ALTER TABLE t4 DISCARD TABLESPACE;
restore: t1 .ibd and .cfg files
restore: t2 .ibd and .cfg files
restore: t3 .ibd and .cfg files
restore: t4 .ibd and .cfg files
ALTER TABLE t1 IMPORT TABLESPACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` bigint(20) NOT NULL,
`b` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes `encryption_key_id`=4
SELECT COUNT(*) FROM t1;
COUNT(*)
2000
ALTER TABLE t2 IMPORT TABLESPACE;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c1` bigint(20) NOT NULL,
`b` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1 `encrypted`=yes `encryption_key_id`=4
SELECT COUNT(*) FROM t2;
COUNT(*)
2000
ALTER TABLE t3 IMPORT TABLESPACE;
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TABLE `t3` (
`c1` bigint(20) NOT NULL,
`b` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED `encrypted`=yes `encryption_key_id`=4
SELECT COUNT(*) FROM t3;
COUNT(*)
2000
ALTER TABLE t4 IMPORT TABLESPACE;
SHOW CREATE TABLE t4;
Table Create Table
t4 CREATE TABLE `t4` (
`c1` bigint(20) NOT NULL,
`b` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `page_compressed`=1
SELECT COUNT(*) FROM t4;
COUNT(*)
2000
# tables should be still either encrypted and/or compressed
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 yes on expecting NOT FOUND
NOT FOUND /barfoo/ in t2.ibd
# t3 yes on expecting NOT FOUND
NOT FOUND /tmpres/ in t3.ibd
# t4 yes on expecting NOT FOUND
NOT FOUND /mysql/ in t4.ibd
DROP PROCEDURE innodb_insert_proc;
DROP TABLE t1,t2,t3,t4;
|
