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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB encrypted=yes;
CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB;
CREATE TABLE t3 (id INT, a VARCHAR(255)) ENGINE=InnoDB encrypted=yes;
CREATE TABLE t4 (id INT, a VARCHAR(255)) engine=InnoDB;
CREATE TABLE t5 (id INT NOT NULL PRIMARY KEY, a TEXT(500), b VARCHAR(255), FULLTEXT(b)) ENGINE=InnoDB encrypted=yes;
CREATE TABLE t6 (id INT, a TEXT(500), b VARCHAR(255), FULLTEXT(b)) ENGINE=InnoDB;
CREATE TABLE t7 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB row_format=compressed encrypted=yes;
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',42));
insert into t2 values (current_num,repeat('temp', 42));
insert into t3 values (current_num,repeat('barfoo',42));
insert into t4 values (current_num,repeat('repeat',42));
insert into t5 values (current_num,substring('A BC DEF GHIJ KLM NOPQRS TUV WXYZ 012 3456789', rand()*36+1, 100), repeat('author new',22));
insert into t6 values (current_num,substring('A BC DEF GHIJ KLM NOPQRS TUV WXYZ 012 3456789', rand()*36+1, 100), repeat('mangled old',22));
insert into t7 values (current_num,repeat('mysql',42));
set current_num = current_num + 1;
end while;
end//
commit;
set autocommit=0;
call innodb_insert_proc(15000);
commit;
set autocommit=1;
# Wait max 10 min for key encryption threads to encrypt all spaces
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
# t4 ... on expecting NOT FOUND
NOT FOUND /repeat/ in t4.ibd
# t5 ... on expecting NOT FOUND
NOT FOUND /author/ in t5.ibd
# t6 ... on expecting NOT FOUND
NOT FOUND /mangled/ in t6.ibd
# t7 ... on expecting NOT FOUND
NOT FOUND /mysql/ in t7.ibd
ALTER TABLE t1 ADD COLUMN b int default 2;
ALTER TABLE t2 ADD COLUMN b int default 2;
ALTER TABLE t7 ADD COLUMN b int default 2;
ALTER TABLE t1 ADD KEY a(a), ADD KEY b(b);
ALTER TABLE t2 ADD KEY a(a), ADD KEY b(b);
ALTER TABLE t3 ADD COLUMN c int default 5;
ALTER TABLE t4 ADD COLUMN c int default 5;
ALTER TABLE t3 ADD KEY (a), ADD KEY c(c);
ALTER TABLE t4 ADD KEY (a), ADD KEY c(c);
ALTER TABLE t5 ADD FULLTEXT(a);
ALTER TABLE t6 ADD FULLTEXT(a);
ALTER TABLE t7 ADD KEY a(a), ADD key b(b);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` varchar(255) DEFAULT NULL,
`b` int(11) DEFAULT '2',
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` varchar(255) DEFAULT NULL,
`b` int(11) DEFAULT '2',
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE t3;
Table Create Table
t3 CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`a` varchar(255) DEFAULT NULL,
`c` int(11) DEFAULT '5',
KEY `a` (`a`),
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes
SHOW CREATE TABLE t4;
Table Create Table
t4 CREATE TABLE `t4` (
`id` int(11) DEFAULT NULL,
`a` varchar(255) DEFAULT NULL,
`c` int(11) DEFAULT '5',
KEY `a` (`a`),
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE t5;
Table Create Table
t5 CREATE TABLE `t5` (
`id` int(11) NOT NULL,
`a` text,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `b` (`b`),
FULLTEXT KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encrypted`=yes
SHOW CREATE TABLE t6;
Table Create Table
t6 CREATE TABLE `t6` (
`id` int(11) DEFAULT NULL,
`a` text,
`b` varchar(255) DEFAULT NULL,
FULLTEXT KEY `b` (`b`),
FULLTEXT KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE t7;
Table Create Table
t7 CREATE TABLE `t7` (
`id` int(11) NOT NULL,
`a` varchar(255) DEFAULT NULL,
`b` int(11) DEFAULT '2',
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED `encrypted`=yes
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
# t4 ... on expecting NOT FOUND
NOT FOUND /repeat/ in t4.ibd
# t5 ... on expecting NOT FOUND
NOT FOUND /author/ in t5.ibd
# t6 ... on expecting NOT FOUND
NOT FOUND /mangled/ in t6.ibd
# t7 ... on expecting NOT FOUND
NOT FOUND /mysql/ in t7.ibd
# Restarting server
# Done restarting server
select count(1) from t1;
count(1)
15000
select count(1) from t2;
count(1)
15000
select count(1) from t3;
count(1)
15000
select count(1) from t4;
count(1)
15000
select count(1) from t5;
count(1)
15000
select count(1) from t6;
count(1)
15000
select count(1) from t7;
count(1)
15000
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
NOT FOUND /temp/ in t2.ibd
# t3 ... on expecting NOT FOUND
NOT FOUND /barfoo/ in t3.ibd
# t4 ... on expecting NOT FOUND
NOT FOUND /repeat/ in t4.ibd
# t5 ... on expecting NOT FOUND
NOT FOUND /author/ in t5.ibd
# t6 ... on expecting NOT FOUND
NOT FOUND /mangled/ in t6.ibd
# t7 ... on expecting NOT FOUND
NOT FOUND /mysql/ in t7.ibd
DROP PROCEDURE innodb_insert_proc;
DROP TABLE t1, t2, t3, t4, t5, t6, t7;
|
