Debian Codegraph @

mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test

raw
1 # This is the DDL function tests for innodb FTS
2
3 -- source include/have_innodb.inc
4 -- source include/maybe_versioning.inc
5
6 # Create FTS table
7 CREATE TABLE fts_test (
8 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
9 title VARCHAR(200),
10 body TEXT
11 ) ENGINE=InnoDB;
12
13 # Insert six rows
14 INSERT INTO fts_test (title,body) VALUES
15 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
16 ('How To Use MySQL Well','After you went through a ...'),
17 ('Optimizing MySQL','In this tutorial we will show ...'),
18 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
19 ('MySQL vs. YourSQL','In the following database comparison ...'),
20 ('MySQL Security','When configured properly, MySQL ...');
21
22 # Table does rebuild when fts index builds for the first time
23 # Create the FTS index
24
25 if ($MTR_COMBINATION_ORIG) {
26 --error ER_ALTER_OPERATION_NOT_SUPPORTED
27 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
28 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=INPLACE;
29 }
30 if (!$MTR_COMBINATION_ORIG) {
31 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
32 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
33 --enable_info
34 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body);
35 --disable_info
36 }
37
38 # Select word "tutorial" in the table
39 SELECT * FROM fts_test WHERE MATCH (title, body)
40 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
41
42 # Drop the FTS idx
43 DROP INDEX idx ON fts_test;
44
45 # Continue insert some rows
46 INSERT INTO fts_test (title,body) VALUES
47 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
48 ('How To Use MySQL Well','After you went through a ...'),
49 ('Optimizing MySQL','In this tutorial we will show ...'),
50 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
51 ('MySQL vs. YourSQL','In the following database comparison ...'),
52 ('MySQL Security','When configured properly, MySQL ...');
53
54 # FTS_DOC_ID hidden column and FTS_DOC_ID index exist
55 if ($MTR_COMBINATION_ORIG) {
56 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
57 }
58 if (!$MTR_COMBINATION_ORIG) {
59 --enable_info
60 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body);
61 --disable_info
62 }
63
64 # Select word "tutorial" in the table
65 SELECT * FROM fts_test WHERE MATCH (title, body)
66 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
67
68 # Boolean search
69 # Select rows contain "MySQL" but not "YourSQL"
70 SELECT * FROM fts_test WHERE MATCH (title,body)
71 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
72
73 # Truncate table
74 let $vers=$MTR_COMBINATION_VERS+$MTR_COMBINATION_VERS_TRX;
75
76 if ($vers)
77 {
78 --disable_query_log
79 CREATE TABLE fts_test2 LIKE fts_test;
80 DROP TABLE fts_test;
81 RENAME TABLE fts_test2 TO fts_test;
82 --enable_query_log
83 --echo TRUNCATE TABLE fts_test;
84 }
85 if (!$vers)
86 {
87 TRUNCATE TABLE fts_test;
88 }
89
90 DROP INDEX idx ON fts_test;
91
92 # Continue insert some rows
93 INSERT INTO fts_test (title,body) VALUES
94 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
95 ('How To Use MySQL Well','After you went through a ...'),
96 ('Optimizing MySQL','In this tutorial we will show ...'),
97 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
98 ('MySQL vs. YourSQL','In the following database comparison ...'),
99 ('MySQL Security','When configured properly, MySQL ...');
100
101 # Recreate the FTS index
102 CREATE FULLTEXT INDEX idx on fts_test (title, body);
103
104 # Select word "tutorial" in the table
105 SELECT * FROM fts_test WHERE MATCH (title, body)
106 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
107
108 DROP TABLE fts_test;
109
110 # Create FTS table
111 CREATE TABLE fts_test (
112 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
113 title VARCHAR(200),
114 body TEXT
115 ) ENGINE=InnoDB;
116
117 # Insert six rows
118 INSERT INTO fts_test (title,body) VALUES
119 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
120 ('How To Use MySQL Well','After you went through a ...'),
121 ('Optimizing MySQL','In this tutorial we will show ...'),
122 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
123 ('MySQL vs. YourSQL','In the following database comparison ...'),
124 ('MySQL Security','When configured properly, MySQL ...');
125
126 # Create the FTS index
127 # We could support online fulltext index creation when a FTS_DOC_ID
128 # column already exists. This has not been implemented yet.
129 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
130 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE;
131 if ($MTR_COMBINATION_ORIG) {
132 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
133 }
134 if (!$MTR_COMBINATION_ORIG) {
135 --enable_info
136 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body);
137 --disable_info
138 }
139
140 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
141 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
142 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
143
144 SELECT * FROM fts_test WHERE MATCH (title, body)
145 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
146
147 # Drop and recreate
148 drop index idx on fts_test;
149
150 CREATE FULLTEXT INDEX idx on fts_test (title, body);
151
152 SELECT * FROM fts_test WHERE MATCH (title, body)
153 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
154
155 # Drop the FTS_DOC_ID_INDEX and try again
156 drop index idx on fts_test;
157
158 CREATE FULLTEXT INDEX idx on fts_test (title, body);
159
160 SELECT * FROM fts_test WHERE MATCH (title, body)
161 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
162
163 drop table fts_test;
164
165 # Test FTS_DOC_ID and FTS_DOC_ID_INDEX all in the create table clause
166 CREATE TABLE fts_test (
167 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
168 title varchar(255) NOT NULL DEFAULT '',
169 text mediumtext NOT NULL,
170 PRIMARY KEY (FTS_DOC_ID),
171 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
172 FULLTEXT KEY idx (title,text)
173 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
174
175 set @@auto_increment_increment=10;
176
177 INSERT INTO fts_test (title, text) VALUES
178 ('MySQL Tutorial','DBMS stands for DataBase ...'),
179 ('How To Use MySQL Well','After you went through a ...'),
180 ('Optimizing MySQL','In this tutorial we will show ...'),
181 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
182 ('MySQL vs. YourSQL','In the following database comparison ...'),
183 ('MySQL Security','When configured properly, MySQL ...');
184 -- disable_result_log
185 ANALYZE TABLE fts_test;
186 -- enable_result_log
187 set @@auto_increment_increment=1;
188
189 select *, match(title, text) AGAINST ('database') as score
190 from fts_test order by score desc;
191
192 drop index idx on fts_test;
193
194 drop table fts_test;
195
196 # This should fail:
197 # Create a FTS_DOC_ID of the wrong type (should be bigint)
198 --error 1166
199 CREATE TABLE fts_test (
200 FTS_DOC_ID int(20) unsigned NOT NULL AUTO_INCREMENT,
201 title varchar(255) NOT NULL DEFAULT '',
202 text mediumtext NOT NULL,
203 PRIMARY KEY (FTS_DOC_ID),
204 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
205 FULLTEXT KEY idx (title,text)
206 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
207
208 # This should fail:
209 # Create a FTS_DOC_ID_INDEX of the wrong type (should be unique)
210 --error ER_INNODB_FT_WRONG_DOCID_INDEX
211 CREATE TABLE fts_test (
212 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
213 title varchar(255) NOT NULL DEFAULT '',
214 text mediumtext NOT NULL,
215 PRIMARY KEY (FTS_DOC_ID),
216 KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
217 FULLTEXT KEY idx (title,text)
218 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
219
220 CREATE TABLE articles (
221 FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
222 title VARCHAR(200),
223 body TEXT
224 ) ENGINE=InnoDB;
225
226 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
227 (9, 'MySQL Tutorial','DBMS stands for DataBase ...'),
228 (10, 'How To Use MySQL Well','After you went through a ...'),
229 (12, 'Optimizing MySQL','In this tutorial we will show ...'),
230 (14,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
231 (19, 'MySQL vs. YourSQL','In the following database comparison ...'),
232 (20, 'MySQL Security','When configured properly, MySQL ...');
233
234 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
235 ALTER TABLE articles ADD FULLTEXT INDEX idx (title),
236 ADD FULLTEXT INDEX idx3 (title), ALGORITHM=INPLACE;
237 --enable_info
238 ALTER TABLE articles ADD FULLTEXT INDEX idx (title),
239 ADD FULLTEXT INDEX idx3 (title);
240 --disable_info
241
242 ALTER TABLE articles ADD INDEX t20 (title(20)), LOCK=NONE;
243 ALTER TABLE articles DROP INDEX t20;
244
245 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
246 (29, 'MySQL Tutorial','DBMS stands for DataBase ...'),
247 (30, 'How To Use MySQL Well','After you went through a ...'),
248 (32, 'Optimizing MySQL','In this tutorial we will show ...'),
249 (34,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
250 (39, 'MySQL vs. YourSQL','In the following database comparison ...'),
251 (40, 'MySQL Security','When configured properly, MySQL ...');
252
253 SELECT * FROM articles WHERE MATCH (title)
254 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
255
256 DROP INDEX idx ON articles;
257
258 SELECT * FROM articles WHERE MATCH (title)
259 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
260
261 CREATE FULLTEXT INDEX idx on articles (title, body);
262
263 SELECT * FROM articles WHERE MATCH (title, body)
264 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
265
266 DROP TABLE articles;
267
268 create table articles(`FTS_DOC_ID` serial,
269 `col32` timestamp not null,`col115` text) engine=innodb;
270
271 create fulltext index `idx5` on articles(`col115`) ;
272
273 alter ignore table articles add primary key (`col32`) ;
274
275 drop table articles;
276
277 # Create a table with FTS index, this will create hidden column FTS_DOC_ID
278 CREATE TABLE articles (
279 id INT UNSIGNED NOT NULL,
280 title VARCHAR(200),
281 body TEXT
282 ) ENGINE=InnoDB;
283
284 INSERT INTO articles VALUES
285 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
286 (2, 'How To Use MySQL Well','After you went through a ...'),
287 (3, 'Optimizing MySQL','In this tutorial we will show ...'),
288 (4, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
289 (5, 'MySQL vs. YourSQL','In the following database comparison ...'),
290 (6, 'MySQL Security','When configured properly, MySQL ...');
291
292 CREATE FULLTEXT INDEX idx on articles (title, body);
293
294 # Drop the FTS index, however, this will keep the FTS_DOC_ID hidden
295 # column (to avoid a table rebuild)
296 DROP INDEX idx ON articles;
297
298 # Now create cluster index on id online; The rebuild should still
299 # have the FTS_DOC_ID
300 CREATE UNIQUE INDEX idx2 ON articles(id);
301
302 # Recreate FTS index, this should not require a rebuild,
303 # since the FTS_DOC_ID is still there
304 CREATE FULLTEXT INDEX idx on articles (title, body);
305
306 SELECT * FROM articles WHERE MATCH (title, body)
307 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
308
309 DROP TABLE articles;
310
311 --echo #
312 --echo # MDEV-22811 DDL fails to drop and re-create FTS index
313 --echo #
314 CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY,
315 f1 VARCHAR(200),FULLTEXT fidx(f1))engine=innodb;
316 ALTER TABLE t1 DROP index fidx, ADD FULLTEXT INDEX(f1);
317 DROP TABLE t1;
318
319 --echo #
320 --echo # MDEV-21478 Inplace alter fails to report error when
321 --echo # FTS_DOC_ID is added
322
323 SET NAMES utf8;
324
325 CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB;
326 --error ER_WRONG_COLUMN_NAME
327 ALTER TABLE t1 ADD FTS_DOC_ıD BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
328 --error ER_WRONG_COLUMN_NAME
329 ALTER TABLE t1 ADD FTS_DOC_ıD BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
330 DROP TABLE t1;
331
332 CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
333 ALTER TABLE t1 ADD FTS_DOC_İD BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
334 DROP TABLE t1;
335
336 CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
337 ALTER TABLE t1 ADD FTS_DOC_İD BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
338 DROP TABLE t1;
339
340 CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
341
342 --error ER_WRONG_COLUMN_NAME
343 ALTER TABLE t1 ADD fts_doc_id INT, ALGORITHM=COPY;
344
345 --error ER_WRONG_COLUMN_NAME
346 ALTER TABLE t1 ADD fts_doc_id INT, ALGORITHM=INPLACE;
347
348 --error ER_WRONG_COLUMN_NAME
349 ALTER TABLE t1 ADD fts_doc_id BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
350
351 --error ER_WRONG_COLUMN_NAME
352 ALTER TABLE t1 ADD fts_doc_id BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
353
354 --error ER_WRONG_COLUMN_NAME
355 ALTER TABLE t1 ADD FTS_DOC_ID INT UNSIGNED NOT NULL, ALGORITHM=COPY;
356
357 --error ER_WRONG_COLUMN_NAME
358 ALTER TABLE t1 ADD FTS_DOC_ID INT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
359
360 SHOW CREATE TABLE t1;
361 DROP TABLE t1;
362
363 --echo #
364 --echo # MDEV-25271 Double free of table when inplace alter
365 --echo # FTS add index fails
366 --echo #
367 call mtr.add_suppression("InnoDB: Operating system error number .* in a file operation.");
368 call mtr.add_suppression("InnoDB: Error number .* means");
369 call mtr.add_suppression("InnoDB: Cannot create file");
370 call mtr.add_suppression("InnoDB: Failed to create");
371 call mtr.add_suppression("InnoDB: The file '.*test/FTS_[0-9a-f]*_BEING_DELETED\\.ibd' already exists");
372
373 let MYSQLD_DATADIR=`select @@datadir`;
374 CREATE TABLE t1(a TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL UNIQUE) ENGINE=InnoDB;
375 let $fts_aux_file= `select concat('FTS_',right(concat(repeat('0',16), lower(hex(TABLE_ID))),16),'_BEING_DELETED.ibd') FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1'`;
376 write_file $MYSQLD_DATADIR/test/$fts_aux_file;
377 EOF
378 --replace_regex /".*" from/"Resource temporarily unavailable" from/
379 if ($MTR_COMBINATION_ORIG) {
380 --error ER_GET_ERRNO
381 ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE;
382 }
383 if (!$MTR_COMBINATION_ORIG) {
384 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
385 ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE;
386 }
387 DROP TABLE t1;
388 remove_file $MYSQLD_DATADIR/test/$fts_aux_file;
389
390 # Add more than one FTS index
391 CREATE TABLE t1 (a VARCHAR(3)) ENGINE=InnoDB;
392 ALTER TABLE t1 ADD FULLTEXT KEY(a), ADD COLUMN b VARCHAR(3), ADD FULLTEXT KEY(b);
393
394 # Cleanup
395 DROP TABLE t1;
396
397 --echo #
398 --echo # MDEV-18152 Assertion 'num_fts_index <= 1' failed
399 --echo # in prepare_inplace_alter_table_dict
400 --echo #
401 CREATE TABLE t1
402 (a VARCHAR(128), b VARCHAR(128), FULLTEXT INDEX(a), FULLTEXT INDEX(b))
403 ENGINE=InnoDB;
404 ALTER TABLE t1 ADD c SERIAL;
405 DROP TABLE t1;
406
407 --echo # End of 10.3 tests
408
409 --echo #
410 --echo # MDEV-27582 Fulltext DDL decrements the FTS_DOC_ID value
411 --echo #
412 CREATE TABLE t1 (
413 f1 INT NOT NULL PRIMARY KEY,
414 f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
415 INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
416 connect(con1,localhost,root,,,);
417 START TRANSACTION WITH CONSISTENT SNAPSHOT;
418
419 connection default;
420 DELETE FROM t1 WHERE f1 = 2;
421 ALTER TABLE t1 DROP INDEX ft;
422 ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
423 INSERT INTO t1 VALUES (3, 'innodb fts search');
424 SET GLOBAL innodb_optimize_fulltext_only=ON;
425 OPTIMIZE TABLE t1;
426 SET GLOBAL innodb_ft_aux_table = 'test/t1';
427 SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
428 SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
429 DROP TABLE t1;
430 disconnect con1;
431 SET GLOBAL innodb_optimize_fulltext_only=OFF;
432 SET GLOBAL innodb_ft_aux_table = default;
433
434 --echo #
435 --echo # MDEV-32017 Auto-increment no longer works for
436 --echo # explicit FTS_DOC_ID
437 --echo #
438 CREATE TABLE t (
439 FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
440 f1 char(255), f2 char(255), f3 char(255), fulltext key (f3)
441 ) ENGINE=InnoDB;
442 INSERT INTO t (f1,f2,f3) VALUES ('foo','bar','baz');
443 ALTER TABLE t ADD FULLTEXT INDEX ft1(f1);
444 ALTER TABLE t ADD FULLTEXT INDEX ft2(f2);
445 INSERT INTO t (f1,f2,f3) VALUES ('bar','baz','qux');
446 DROP TABLE t;
447
448 --echo #
449 --echo # MDEV-31892 Server crash upon moving InnoDB table
450 --echo # with fulltext index between databases
451 --echo #
452 CREATE DATABASE db;
453 CREATE TABLE db.t (pk INT PRIMARY KEY, c CHAR(120), FULLTEXT KEY(c)) ENGINE=InnoDB;
454 INSERT INTO db.t VALUES (1,'foo'),(2,'bar');
455 ALTER TABLE db.t FORCE, RENAME to test.t;
456 # Cleanup
457 DROP DATABASE db;
458 DROP TABLE IF EXISTS test.t;