Debian Codegraph @

mysql-test/suite/innodb_fts/t/crash_recovery.test

raw
1 # Crash recovery tests for FULLTEXT INDEX.
2 # Note: These tests used to be part of a larger test, innodb_fts_misc_debug
3 # or innodb_fts.misc_debug. The part of the test that actually needs debug
4 # instrumentation been moved to innodb_fts.misc_debug.
5
6 --source include/have_innodb.inc
7 # The embedded server tests do not support restarting.
8 --source include/not_embedded.inc
9 --source include/maybe_debug.inc
10 if ($have_debug) { source include/have_debug_sync.inc; }
11 --source include/maybe_versioning.inc
12
13 FLUSH TABLES;
14 # Following are test for crash recovery on FTS index, the first scenario
15 # is for bug Bug #14586855 INNODB: FAILING ASSERTION: (DICT_INDEX_GET_N_UNIQUE(
16 # PLAN->INDEX) <= PLAN->N_EXAC
17
18 # Scenario 1: Hidden FTS_DOC_ID column, and FTS index dropped
19 # Create FTS table
20 CREATE TABLE articles (
21 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
22 title VARCHAR(200),
23 body TEXT,
24 FULLTEXT (title,body)
25 ) ENGINE=InnoDB;
26
27 let $vers= $MTR_COMBINATION_VERS + $MTR_COMBINATION_VERS_TRX;
28 if ($vers)
29 {
30 --disable_query_log
31 INSERT INTO articles (title,body) VALUES
32 ('history','Deleted row ...');
33 DELETE FROM articles;
34 --enable_query_log
35 }
36
37 # Drop the FTS index before more insertion. The FTS_DOC_ID should
38 # be kept
39 DROP INDEX title ON articles;
40
41 # Insert six rows
42 INSERT INTO articles (title,body) VALUES
43 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
44 ('How To Use MySQL Well','After you went through a ...'),
45 ('Optimizing MySQL','In this tutorial we will show ...'),
46 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
47 ('MySQL vs. YourSQL','In the following database comparison ...'),
48 ('MySQL Security','When configured properly, MySQL ...');
49
50 BEGIN;
51 INSERT INTO articles (title,body) VALUES
52 ('MySQL Tutorial','DBMS stands for DataBase ...');
53
54 --echo # Make durable the AUTO_INCREMENT in the above incomplete transaction.
55 --connect(ddl1, localhost, root,,)
56 CREATE TABLE t1(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
57 if ($have_debug)
58 {
59 --disable_query_log
60 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL 1 WAIT_FOR ever';
61 --enable_query_log
62 }
63 send ALTER TABLE t1 ADD FULLTEXT INDEX(b);
64 --connection default
65 if ($have_debug)
66 {
67 --disable_query_log
68 SET DEBUG_SYNC='now WAIT_FOR 1';
69 --enable_query_log
70 }
71
72 --connect(ddl2, localhost, root,,)
73 CREATE TABLE t2(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
74 if ($have_debug)
75 {
76 --disable_query_log
77 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL 2 WAIT_FOR ever';
78 --enable_query_log
79 }
80 send ALTER TABLE t2 DROP INDEX a, ADD FULLTEXT INDEX(b), FORCE;
81 --connection default
82 if ($have_debug)
83 {
84 --disable_query_log
85 SET DEBUG_SYNC='now WAIT_FOR 2';
86 --enable_query_log
87 }
88
89 --connect(ddl3, localhost, root,,)
90 CREATE TABLE t3(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
91 if ($have_debug)
92 {
93 --disable_query_log
94 SET DEBUG_SYNC='alter_table_before_rename_result_table SIGNAL 3 WAIT_FOR ever';
95 --enable_query_log
96 }
97 send ALTER TABLE t3 DROP INDEX a, ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
98
99 --connection default
100 if ($have_debug)
101 {
102 --disable_query_log
103 SET DEBUG_SYNC='now WAIT_FOR 3';
104 --enable_query_log
105 }
106
107 let $shutdown_timeout=0;
108 --source include/restart_mysqld.inc
109
110 disconnect ddl1;
111 disconnect ddl2;
112 disconnect ddl3;
113
114 # Wait for purge, so that any #sql-ib.ibd files from the previous kill
115 # will be deleted.
116 SET GLOBAL innodb_max_purge_lag_wait=0;
117
118 CHECK TABLE t1,t2,t3;
119 DROP TABLE t1,t2,t3;
120
121 # This insert will re-initialize the Doc ID counter, it should not crash
122 INSERT INTO articles (title,body) VALUES
123 ('MySQL Tutorial','DBMS stands for DataBase ...');
124
125 # Recreate fulltext index to see if everything is OK
126 CREATE FULLTEXT INDEX idx ON articles (title,body);
127
128 if ($vers)
129 {
130 --disable_query_log
131 UPDATE articles SET id= id - 1;
132 --enable_query_log
133 }
134
135 # Should return 3 rows
136 SELECT * FROM articles
137 WHERE MATCH (title,body)
138 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
139
140 # Scenario 2: Hidden FTS_DOC_ID column, with FTS index
141 # Now let's do more insertion and test a crash with FTS on
142 INSERT INTO articles (title,body) VALUES
143 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
144 ('How To Use MySQL Well','After you went through a ...'),
145 ('Optimizing MySQL','In this tutorial we will show ...'),
146 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
147 ('MySQL vs. YourSQL','In the following database comparison ...'),
148 ('MySQL Security','When configured properly, MySQL ...');
149
150 connect(dml, localhost, root,,);
151 BEGIN;
152
153 INSERT INTO articles (title,body) VALUES
154 ('MySQL Tutorial','DBMS stands for DataBase ...');
155 connection default;
156
157 --echo # Make durable the AUTO_INCREMENT in the above incomplete transaction.
158 --connect (flush_redo_log,localhost,root,,)
159 SET GLOBAL innodb_flush_log_at_trx_commit=1;
160 SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
161 BEGIN;
162 UPDATE articles SET id=0 WHERE id=1;
163 UPDATE articles SET id=1 WHERE id=0;
164 COMMIT;
165 --disconnect flush_redo_log
166 --connection default
167
168 --source include/restart_mysqld.inc
169
170 disconnect dml;
171
172 # This insert will re-initialize the Doc ID counter, it should not crash
173 INSERT INTO articles (title,body) VALUES
174 ('MySQL Tutorial','DBMS stands for DataBase ...');
175
176 if ($vers)
177 {
178 --disable_query_log
179 UPDATE articles SET id= id - 1 WHERE id > 8;
180 --enable_query_log
181 }
182
183 # Should return 6 rows
184 SELECT * FROM articles
185 WHERE MATCH (title,body)
186 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
187
188 DROP TABLE articles;
189
190 # Scenario 3: explicit FTS_DOC_ID column with FTS index
191 # Now let's test user defined FTS_DOC_ID
192
193 CREATE TABLE articles (
194 id int PRIMARY KEY,
195 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
196 title VARCHAR(200),
197 body TEXT
198 ) ENGINE=InnoDB;
199
200 CREATE FULLTEXT INDEX idx1 on articles (title, body);
201
202 # Note the FTS_DOC_ID is not fully ordered with primary index
203 INSERT INTO articles VALUES
204 (1, 10, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
205 (2, 1, 'How To Use MySQL Well','After you went through a ...'),
206 (3, 2, 'Optimizing MySQL','In this tutorial we will show ...'),
207 (4, 11, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
208 (5, 6, 'MySQL vs. YourSQL','In the following database comparison ...'),
209 (7, 4, 'MySQL Security','When configured properly, MySQL ...');
210
211 connect(dml, localhost, root,,);
212 BEGIN;
213
214 # Below we do not depend on the durability of the AUTO_INCREMENT sequence,
215 # so we can skip the above flush_redo_log trick.
216 INSERT INTO articles VALUES
217 (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...');
218
219 if ($vers)
220 {
221 --disable_query_log
222 DELETE FROM articles WHERE id = 100;
223 INSERT INTO articles VALUES
224 (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...');
225 --enable_query_log
226 }
227
228 connect(dml2, localhost, root,,);
229
230 --echo #
231 --echo # MDEV-19073 FTS row mismatch after crash recovery
232 --echo #
233
234 CREATE TABLE mdev19073(id SERIAL, title VARCHAR(200), body TEXT,
235 FULLTEXT(title,body)) ENGINE=InnoDB;
236 INSERT INTO mdev19073 (title, body) VALUES
237 ('MySQL Tutorial', 'DBMS stands for Database...');
238 CREATE FULLTEXT INDEX idx ON mdev19073(title, body);
239 CREATE TABLE mdev19073_2 LIKE mdev19073;
240 if ($have_debug)
241 {
242 --disable_query_log
243 SET @saved_dbug = @@debug_dbug;
244 SET DEBUG_DBUG = '+d,fts_instrument_sync_debug';
245 --enable_query_log
246 }
247 INSERT INTO mdev19073_2 (title, body) VALUES
248 ('MySQL Tutorial', 'DBMS stands for Database...');
249 if ($have_debug)
250 {
251 --disable_query_log
252 SET DEBUG_DBUG = @saved_dbug;
253 --enable_query_log
254 }
255
256 INSERT INTO mdev19073 (title, body) VALUES
257 ('MariaDB Tutorial', 'DB means Database ...');
258 INSERT INTO mdev19073_2 (title, body) VALUES
259 ('MariaDB Tutorial', 'DB means Database ...');
260
261 # Should return 2 rows
262 SELECT * FROM mdev19073 WHERE MATCH (title, body)
263 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
264 SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
265 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
266
267 connection default;
268 --source include/restart_mysqld.inc
269 disconnect dml;
270 disconnect dml2;
271
272 # This would re-initialize the FTS index and do the re-tokenization
273 # of above records
274 INSERT INTO articles VALUES (8, 12, 'MySQL Tutorial','DBMS stands for DataBase ...');
275
276 SELECT * FROM articles WHERE MATCH (title, body)
277 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
278
279 DROP TABLE articles;
280
281 # Should return 2 rows
282 SELECT * FROM mdev19073 WHERE MATCH (title, body)
283 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
284 SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
285 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
286 DROP TABLE mdev19073, mdev19073_2;
287
288 let $shutdown_timeout=;
289
290 --echo #
291 --echo # MDEV-28706 Redundant InnoDB table fails during alter
292 --echo #
293
294 SET @@global.innodb_file_per_table = 0;
295 CREATE TABLE t1 (
296 col_int INTEGER, col_text TEXT,
297 col_text_1 TEXT
298 ) ENGINE = InnoDB ROW_FORMAT = Redundant ;
299 ALTER TABLE t1 ADD FULLTEXT KEY `ftidx` ( col_text ) ;
300 INSERT INTO t1 VALUES ( 1255, "mariadb", "InnoDB");
301 --source include/restart_mysqld.inc
302 ALTER TABLE t1 ADD FULLTEXT(col_text_1);
303 DROP TABLE t1;
304
305 SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE 'test/%';