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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
|
#
# MDEV-11415 AVOID INTERMEDIATE COMMIT WHILE DOING
# ALTER TABLE...ALGORITHM=COPY
#
CREATE TABLE t(a SERIAL, b INT, c INT, d INT) ENGINE=InnoDB;
CREATE TABLE t1(a INT, b TEXT, c TEXT,
FULLTEXT(b), FULLTEXT(c(3)), FULLTEXT(b,c)) ENGINE=InnoDB;
BEGIN;
COMMIT;
SELECT COUNT(*) FROM t;
COUNT(*)
999
UPDATE t SET b=a%7, c=a%11, d=a%13;
INSERT INTO t1 VALUES(1, 'This is a first b column', 'This is a first c column');
INSERT INTO t1 VALUES(2, 'This is a second b column', 'This is a second c column');
INSERT INTO t1(a) VALUES(3);
INSERT INTO t1 VALUES(4, 'This is a third b column', 'This is a third c column');
DELETE FROM t1 WHERE a = 2;
SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
a b c
1 This is a first b column This is a first c column
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
a b c
1 This is a first b column This is a first c column
SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
a b c
1 This is a first b column This is a first c column
4 This is a third b column This is a third c column
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` text DEFAULT NULL,
`c` text DEFAULT NULL,
FULLTEXT KEY `b` (`b`),
FULLTEXT KEY `c` (`c`),
FULLTEXT KEY `b_2` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
ALTER TABLE t1 FORCE, ALGORITHM=COPY;
connect hang,localhost,root;
SET DEBUG_SYNC='alter_table_copy_trans_commit SIGNAL hung WAIT_FOR ever';
# create 32 secondary indexes
ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX(b,a,d,c),
ADD INDEX(b,d,a,c),ADD INDEX(b,d,c,a),ADD INDEX(a,b,c,d),ADD INDEX(a,b,d,c),
ADD INDEX(a,c,b,d),ADD INDEX(a,c,d,b),ADD INDEX(a,d,b,c),ADD INDEX(a,d,c,b),
ADD INDEX(c,a,b,d),ADD INDEX(c,a,d,b),ADD INDEX(c,b,a,d),ADD INDEX(c,b,d,a),
ADD INDEX(c,d,a,b),ADD INDEX(c,d,b,a),ADD INDEX(d,a,b,c),ADD INDEX(d,a,c,b),
ADD INDEX(d,b,a,c),ADD INDEX(d,b,c,a),ADD INDEX(d,c,a,b),ADD INDEX(d,c,b,a),
ADD INDEX(a,b,c), ADD INDEX(a,c,b), ADD INDEX(a,c,d), ADD INDEX(a,d,c),
ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c),
ALGORITHM=COPY;
connection default;
SET DEBUG_SYNC='now WAIT_FOR hung';
# restart: --innodb-force-recovery=3
disconnect hang;
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTSBEING_DELETED.ibd
FTSBEING_DELETED_CACHE.ibd
FTSCONFIG.ibd
FTSDELETED.ibd
FTSDELETED_CACHE.ibd
db.opt
t.frm
t.ibd
t1.frm
t1.ibd
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT COUNT(*) FROM t;
COUNT(*)
999
CHECK TABLE t;
Table Op Msg_type Msg_text
test.t check status OK
SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
a b c
1 This is a first b column This is a first c column
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
a b c
1 This is a first b column This is a first c column
SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
a b c
1 This is a first b column This is a first c column
4 This is a third b column This is a third c column
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` text DEFAULT NULL,
`c` text DEFAULT NULL,
FULLTEXT KEY `b` (`b`),
FULLTEXT KEY `c` (`c`),
FULLTEXT KEY `b_2` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
# restart: --innodb-read-only
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTSBEING_DELETED.ibd
FTSBEING_DELETED_CACHE.ibd
FTSCONFIG.ibd
FTSDELETED.ibd
FTSDELETED_CACHE.ibd
db.opt
t.frm
t.ibd
t1.frm
t1.ibd
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT COUNT(*) FROM t;
COUNT(*)
999
CHECK TABLE t;
Table Op Msg_type Msg_text
test.t check status OK
SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
a b c
1 This is a first b column This is a first c column
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
a b c
1 This is a first b column This is a first c column
SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
a b c
1 This is a first b column This is a first c column
4 This is a third b column This is a third c column
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` text DEFAULT NULL,
`c` text DEFAULT NULL,
FULLTEXT KEY `b` (`b`),
FULLTEXT KEY `c` (`c`),
FULLTEXT KEY `b_2` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SET GLOBAL innodb_doublewrite=ON;
SELECT @@GLOBAL.innodb_doublewrite "OFF expected";
OFF expected
OFF
SET GLOBAL innodb_buf_flush_list_now=ON;
# restart
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTS_INDEX_1.ibd
FTS_INDEX_2.ibd
FTS_INDEX_3.ibd
FTS_INDEX_4.ibd
FTS_INDEX_5.ibd
FTS_INDEX_6.ibd
FTSBEING_DELETED.ibd
FTSBEING_DELETED_CACHE.ibd
FTSCONFIG.ibd
FTSDELETED.ibd
FTSDELETED_CACHE.ibd
db.opt
t.frm
t.ibd
t1.frm
t1.ibd
DROP TABLE t1,t;
|
