Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2
3 #------------------------------------------------------------------------------
4 # FTS with FK and update cascade
5 #-------------------------------------------------------------------------------
6 set names utf8;
7
8 call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns.");
9 call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`");
10
11 # Create FTS table
12 CREATE TABLE t1 (
13 id1 INT ,
14 a1 VARCHAR(200) ,
15 b1 TEXT ,
16 FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1)
17 ) CHARACTER SET = utf8 , ENGINE = InnoDB;
18
19 CREATE TABLE t2 (
20 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
21 a2 VARCHAR(200),
22 b2 TEXT ,
23 FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
24 FULLTEXT KEY (b2,a2)
25 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
26
27 # Insert rows
28 INSERT INTO t1 (id1,a1,b1) VALUES
29 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
30 (2,'How To Use MySQL Well','After you went through a ...'),
31 (3,'Optimizing MySQL','In this tutorial we will show ...');
32
33 # Insert rows
34 INSERT INTO t1 (id1,a1,b1) VALUES
35 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
36 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
37 (6,'MySQL Security','When configured properly, MySQL ...');
38
39 # Insert rows in t2 fk table
40 INSERT INTO t2 (a2,b2) VALUES
41 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
42 ('How To Use MySQL Well','After you went through a ...'),
43 ('Optimizing MySQL','In this tutorial we will show ...');
44
45 # Insert rows t2 fk table
46 INSERT INTO t2 (a2,b2) VALUES
47 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
48 ('MySQL vs. YourSQL','In the following database comparison ...'),
49 ('MySQL Security','When configured properly, MySQL ...');
50
51 # error on violating fk constraint
52 --error 1452
53 INSERT INTO t2 (a2,b2) VALUES
54 ('MySQL Tricks','1. Never run mysqld as root. 2. ...');
55
56 # error on delete from parent table
57 --error 1451
58 DELETE FROM t1;
59
60 ANALYZE TABLE t1;
61 ANALYZE TABLE t2;
62
63 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
64 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
65
66 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
67 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
68
69 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
70 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
71
72
73 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
74 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
75
76 set global innodb_optimize_fulltext_only=1;
77 optimize table t1;
78 set global innodb_optimize_fulltext_only=0;
79 # Updating parent table hence child table should get updated due to 'update cascade' clause
80 UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE
81 MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
82
83 # no records expected
84 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
85 # InnoDB:Error child table shows records which is incorrect - UPADTE on Fix
86 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
87
88 # it shows updated record
89 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1;
90 # InnoDB:Error child table does not show the expected record
91 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2;
92 SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
93
94 DROP TABLE t2 , t1;
95
96 # on update cascade
97 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
98 create table t2 (s1 int, s2 varchar(200),
99 fulltext key(s2),
100 foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB;
101 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
102 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
103 update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
104 commit;
105 select * from t2 where match(s2) against ('Lollipops');
106 DROP TABLE t2 , t1;
107
108 # on delete cascade
109 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
110 create table t2 (s1 int, s2 varchar(200),
111 fulltext key(s2),
112 foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB;
113 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
114 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
115 delete from t1 where s2 <> 'Sunshine';
116 select * from t2 where match(s2) against ('Lollipops');
117 DROP TABLE t2 , t1;
118
119 # on delete set NULL
120 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
121 create table t2 (s1 int, s2 varchar(200),
122 fulltext key(s2),
123 foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB;
124 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
125 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
126 delete from t1 where s2 <> 'Sunshine';
127 select * from t2 where match(s2) against ('Lollipops');
128 DROP TABLE t2 , t1;
129
130
131 # on update set NULL
132 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
133 create table t2 (s1 int, s2 varchar(200),
134 fulltext key(s2),
135 foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB;
136 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
137 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
138 update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
139 commit;
140 select * from t2 where match(s2) against ('Lollipops');
141 DROP TABLE t2 , t1;
142
143 # When Doc ID is involved
144 create table t1 (s1 bigint unsigned not null, s2 varchar(200),
145 primary key (s1,s2)) ENGINE = InnoDB;
146 create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200),
147 foreign key (FTS_DOC_ID) references t1 (s1)
148 on update cascade) ENGINE = InnoDB;
149
150 create fulltext index idx on t2(s2);
151
152 show create table t2;
153
154 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
155 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
156
157 update t1 set s1 = 3 where s1=1;
158
159 select * from t2 where match(s2) against ('sunshine');
160
161 # FTS Doc ID cannot be reused
162 --error 1451
163 update t1 set s1 = 1 where s1=3;
164
165 DROP TABLE t2 , t1;
166
167 #------------------------------------------------------------------------------
168 # FTS with FK and delete casecade
169 #------------------------------------------------------------------------------
170
171 # Create FTS table
172 CREATE TABLE t1 (
173 id1 INT ,
174 a1 VARCHAR(200) PRIMARY KEY,
175 b1 TEXT character set utf8 ,
176 FULLTEXT KEY (a1,b1)
177 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
178
179 CREATE TABLE t2 (
180 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
181 a2 VARCHAR(200),
182 b2 TEXT character set utf8 ,
183 FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE,
184 FULLTEXT KEY (b2,a2)
185 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
186
187 # Insert rows
188 INSERT INTO t1 (id1,a1,b1) VALUES
189 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
190 (2,'How To Use MySQL Well','After you went through a ...'),
191 (3,'Optimizing MySQL','In this tutorial we will show ...'),
192 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
193 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
194 (6,'MySQL Security','When configured properly, MySQL ...');
195
196 # Insert rows in t2
197 INSERT INTO t2 (a2,b2) VALUES
198 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
199 ('How To Use MySQL Well','After you went through a ...'),
200 ('Optimizing MySQL','In this tutorial we will show ...'),
201 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
202 ('MySQL vs. YourSQL','In the following database comparison ...'),
203 ('MySQL Security','When configured properly, MySQL ...');
204
205 # delete records from parent
206 DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
207
208 # no records expected
209 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
210 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
211
212 SELECT * FROM t1 WHERE a1 LIKE '%tutorial%';
213 SELECT * FROM t2 WHERE a2 LIKE '%tutorial%';
214
215 DROP TABLE t2 , t1;
216
217 #------------------------------------------------------------------------------
218 # FTS with FK+transactions and UPDATE casecade with transaction
219 #-------------------------------------------------------------------------------
220
221 call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`");
222
223 # Create FTS table
224 CREATE TABLE t1 (
225 id1 INT ,
226 a1 VARCHAR(200) ,
227 b1 TEXT ,
228 FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1)
229 ) CHARACTER SET = utf8 , ENGINE = InnoDB;
230
231 CREATE TABLE t2 (
232 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
233 a2 VARCHAR(200),
234 b2 TEXT ,
235 FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
236 FULLTEXT KEY (b2,a2)
237 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
238
239 # Insert rows
240 INSERT INTO t1 (id1,a1,b1) VALUES
241 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
242 (2,'How To Use MySQL Well','After you went through a ...'),
243 (3,'Optimizing MySQL','In this tutorial we will show ...');
244
245 # Insert rows in t2 fk table
246 INSERT INTO t2 (a2,b2) VALUES
247 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
248 ('How To Use MySQL Well','After you went through a ...'),
249 ('Optimizing MySQL','In this tutorial we will show ...');
250
251 START TRANSACTION;
252 # Insert rows
253 INSERT INTO t1 (id1,a1,b1) VALUES
254 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
255 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
256 (6,'MySQL Security','When configured properly, MySQL ...');
257
258 # Insert rows t2 fk table
259 INSERT INTO t2 (a2,b2) VALUES
260 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
261 ('MySQL vs. YourSQL','In the following database comparison ...'),
262 ('MySQL Security','When configured properly, MySQL ...');
263
264 # error on violating fk constraint
265 --error 1452
266 INSERT INTO t2 (a2,b2) VALUES
267 ('MySQL Tricks','1. Never run mysqld as root. 2. ...');
268
269 # error on DELETE FROM parent table
270 --error 1451
271 DELETE FROM t1;
272
273 # records expected
274 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
275 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
276 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
277 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
278 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
279 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
280 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
281 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
282
283 # no records as data not COMMITted.
284 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ;
285 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ;
286 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ;
287 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ;
288 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ;
289 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ;
290 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
291 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
292
293 SELECT * FROM t1 ORDER BY id1;
294 SELECT * FROM t2 ORDER BY id2;
295
296 COMMIT;
297
298 START TRANSACTION;
299 # Updating parent table hence child table should get updated due to 'UPDATE cascade' clause
300 UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE
301 MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
302 COMMIT;
303
304 # no records expected
305 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
306 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
307
308 # it shows updated record
309 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1;
310 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2;
311 SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
312
313 DROP TABLE t2 , t1;
314
315
316 # FTS with FK+transactions - UPDATE cascade
317 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
318 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
319 FULLTEXT KEY(s2),
320 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
321 START TRANSACTION;
322 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
323 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
324 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
325 COMMIT;
326 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
327 DROP TABLE t2 , t1;
328
329 # FTS with FK+transactions - on DELETE cascade
330 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
331 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
332 FULLTEXT KEY(s2),
333 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
334 START TRANSACTION;
335 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
336 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
337 DELETE FROM t1 WHERE s2 <> 'Sunshine';
338 COMMIT;
339 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
340 DROP TABLE t2 , t1;
341
342 # FTS with FK+transactions - DELETE SET NULL
343 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
344 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
345 FULLTEXT KEY(s2),
346 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
347 START TRANSACTION;
348 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
349 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
350 DELETE FROM t1 WHERE s2 <> 'Sunshine';
351 COMMIT;
352 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
353 DROP TABLE t2 , t1;
354
355
356 # FTS with FK+transactions - UPDATE SET NULL
357 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
358 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
359 FULLTEXT KEY(s2),
360 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
361 START TRANSACTION;
362 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
363 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
364 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
365 COMMIT;
366 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
367 DROP TABLE t2 , t1;
368
369
370 #-----------------------------------------------------------------------------
371
372 # FTS with FK+transactions - UPDATE cascade
373 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
374 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
375 FULLTEXT KEY(s2),
376 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
377 START TRANSACTION;
378 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
379 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
380 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
381 ROLLBACK;
382 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
383 DROP TABLE t2 , t1;
384
385 # FTS with FK+transactions - DELETE cascade
386 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
387 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
388 FULLTEXT KEY(s2),
389 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
390 START TRANSACTION;
391 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
392 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
393 DELETE FROM t1 WHERE s2 <> 'Sunshine';
394 ROLLBACK;
395 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
396 DROP TABLE t2 , t1;
397
398 # FTS with FK+transactions - DELETE SET NULL
399 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
400 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
401 FULLTEXT KEY(s2),
402 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
403 START TRANSACTION;
404 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
405 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
406 DELETE FROM t1 WHERE s2 <> 'Sunshine';
407 ROLLBACK;
408 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
409 DROP TABLE t2 , t1;
410
411
412 # FTS with FK+transactions - UPDATE SET NULL
413 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
414 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
415 FULLTEXT KEY(s2),
416 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
417 START TRANSACTION;
418 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
419 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
420 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
421 ROLLBACK;
422 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
423 DROP TABLE t2 , t1;
424
425
426 #------------------------------------------------------------------------------
427 # FTS index with compressed row format
428 #------------------------------------------------------------------------------
429
430 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
431 SET GLOBAL innodb_read_only_compressed=OFF;
432
433 # Create FTS table
434 CREATE TABLE t1 (
435 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
436 a VARCHAR(200),
437 b TEXT
438 ) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB;
439
440 # Insert rows
441 INSERT INTO t1 (a,b) VALUES
442 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
443 ('How To Use MySQL Well','After you went through a ...'),
444 ('Optimizing MySQL','In this tutorial we will show ...');
445
446 # Create the FTS index Using Alter Table
447 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
448 EVAL SHOW CREATE TABLE t1;
449
450 # Check whether individual space id created for AUX tables
451 SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0;
452
453 # Insert rows
454 INSERT INTO t1 (a,b) VALUES
455 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
456 ('MySQL vs. YourSQL','In the following database comparison ...'),
457 ('MySQL Security','When configured properly, MySQL ...');
458
459 -- disable_result_log
460 ANALYZE TABLE t1;
461 -- enable_result_log
462
463 # Select word "tutorial" in the table
464 SELECT * FROM t1 WHERE MATCH (a,b)
465 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
466
467 # boolean mode
468 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
469 --error ER_PARSE_ERROR
470 select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE);
471 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
472 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
473 select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id;
474 select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE);
475 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
476
477 # query expansion
478 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
479
480 # Drop index
481 ALTER TABLE t1 DROP INDEX idx;
482
483 # Create the FTS index again
484 CREATE FULLTEXT INDEX idx on t1 (a,b);
485
486 -- disable_query_log
487 -- disable_result_log
488 ANALYZE TABLE t1;
489 -- enable_result_log
490 -- enable_query_log
491
492 # Select word "tutorial" in the table
493 SELECT * FROM t1 WHERE MATCH (a,b)
494 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
495
496 # boolean mode
497 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
498 select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE);
499 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
500 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
501 select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id;
502 # Innodb:Assert eval0eval.c line 148
503 #select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷWİ*" IN BOOLEAN MODE);
504 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
505
506 # query expansion
507 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
508
509
510 # insert for proximity search
511 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
512 # Insert into table with similar word of different distances
513 INSERT INTO t1 (a,b) VALUES
514 ('test proximity search, test, proximity and phrase',
515 'search, with proximity innodb');
516
517 INSERT INTO t1 (a,b) VALUES
518 ('test proximity fts search, test, proximity and phrase',
519 'search, with proximity innodb');
520
521 INSERT INTO t1 (a,b) VALUES
522 ('test more proximity fts search, test, more proximity and phrase',
523 'search, with proximity innodb');
524
525 # This should only return the first document
526 SELECT * FROM t1
527 WHERE MATCH (a,b)
528 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
529
530 # This would return no document
531 SELECT * FROM t1
532 WHERE MATCH (a,b)
533 AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
534
535 # This give you all three documents
536 SELECT * FROM t1
537 WHERE MATCH (a,b)
538 AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id;
539
540 # Similar boundary testing for the words
541 SELECT * FROM t1
542 WHERE MATCH (a,b)
543 AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id;
544
545 # Test with more word The last document will return, please notice there
546 # is no ordering requirement for proximity search.
547 SELECT * FROM t1
548 WHERE MATCH (a,b)
549 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
550
551 SELECT * FROM t1
552 WHERE MATCH (a,b)
553 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
554
555 # The phrase search will not require exact word ordering
556 SELECT * FROM t1
557 WHERE MATCH (a,b)
558 AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE);
559
560
561 UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ;
562 UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ;
563
564 select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE);
565 select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE);
566
567 SELECT * FROM t1 WHERE MATCH (a,b)
568 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
569
570 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
571 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE);
572
573
574 SELECT * FROM t1 WHERE MATCH (a,b)
575 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
576
577 SELECT * FROM t1 ORDER BY id;
578
579 DROP TABLE t1;
580 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
581
582 #------------------------------------------------------------------------------
583 # FTS index with utf8 character testcase
584 #------------------------------------------------------------------------------
585
586 # Create FTS table
587 EVAL CREATE TABLE t1 (
588 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
589 a VARCHAR(200),
590 b TEXT
591 ) CHARACTER SET = utf8, ENGINE=InnoDB;
592
593
594 # Insert rows from different languages
595 INSERT INTO t1 (a,b) VALUES
596 ('Я могу есть стекло', 'оно мне не вредит'),
597 ('Мога да ям стъкло', 'то не ми вреди'),
598 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
599 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
600 ('Sævör grét', 'áðan því úlpan var ónýt'),
601 ('うゐのおくやま','けふこえて'),
602 ('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
603
604 # insert english text
605 INSERT INTO t1 (a,b) VALUES
606 ('MySQL Tutorial','request [email protected] ...') ,
607 ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
608 ('when To Use MySQL Well','for free faq [email protected] ...');
609
610 # Create the FTS index again
611 CREATE FULLTEXT INDEX idx on t1 (a,b);
612
613 # FTS Queries
614 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id;
615 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION);
616
617 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id;
618 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE);
619 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE);
620
621 # Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set)
622 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
623 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION);
624
625 # Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set)
626 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE);
627 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE);
628 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
629
630 SELECT * FROM t1
631 WHERE MATCH (a,b)
632 AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE);
633
634 SELECT * FROM t1
635 WHERE MATCH (a,b)
636 AGAINST ('"query performace"@02' IN BOOLEAN MODE);
637
638 SELECT * FROM t1
639 WHERE MATCH (a,b)
640 AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE);
641
642 SELECT * FROM t1
643 WHERE MATCH (a,b)
644 AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE);
645
646 SELECT * FROM t1
647 WHERE MATCH (a,b)
648 AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE);
649
650 ALTER TABLE t1 DROP INDEX idx;
651 # Create the FTS index again
652 CREATE FULLTEXT INDEX idx on t1 (a,b);
653
654 # Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set)
655 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
656 # Update fails because where condition do not succeed which is incorrect (update result of query once fixed)
657 UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
658 UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE);
659 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
660
661 # Innodb error - no result returned
662 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
663 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem");
664 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE);
665 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id;
666 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
667
668 SELECT * FROM t1
669 WHERE MATCH (a,b)
670 AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE);
671
672 SELECT * FROM t1 ORDER BY id;
673 DROP TABLE t1;
674
675 # This is to test the update operation on FTS indexed and non-indexed
676 # column
677 CREATE TABLE t1(ID INT PRIMARY KEY,
678 no_fts_field VARCHAR(10),
679 fts_field VARCHAR(10),
680 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
681
682 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
683
684 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
685
686 # Update FULLTEXT indexed column, Doc ID will be updated
687 UPDATE t1 SET fts_field='anychange' where id = 1;
688
689 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
690
691 # Update non-FULLTEXT indexed column, Doc ID stay to be the same
692 UPDATE t1 SET no_fts_field='anychange' where id = 1;
693
694 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
695
696 # Update both FULLTEXT indexed and non-indexed column, Doc ID will be updated
697 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
698
699 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
700
701 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
702
703 # FTS index dropped, the DOC_ID column is kept, however, the ID will not
704 # change
705 DROP INDEX f on t1;
706
707 UPDATE t1 SET fts_field='anychange' where id = 1;
708
709 UPDATE t1 SET no_fts_field='anychange' where id = 1;
710
711 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
712
713 CREATE FULLTEXT INDEX f ON t1(FTS_FIELD);
714
715 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
716
717 DROP TABLE t1;
718
719 # Test on user supplied 'FTS_DOC_ID'
720 CREATE TABLE t1(`FTS_DOC_ID` serial,
721 no_fts_field VARCHAR(10),
722 fts_field VARCHAR(10),
723 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
724
725 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
726
727 # Doc ID must be updated as well (HA_FTS_INVALID_DOCID).
728 --error 182
729 UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1;
730
731 UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1;
732
733 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
734
735 # "BBB" should be marked as deleted.
736 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
737
738 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
739
740 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
741
742 # "HA_FTS_INVALID_DOCID"
743 --error 182
744 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2;
745
746 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
747
748 # Doc ID must be monotonically increase (HA_FTS_INVALID_DOCID)
749 --error 182
750 UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2;
751
752 DROP INDEX f ON t1;
753
754 # After FULLTEXT index dropped, we can update the fields freely
755 UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2;
756
757 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
758
759 SELECT * FROM t1;
760
761 DROP TABLE t1;
762
763 CREATE TABLE t1(ID INT PRIMARY KEY,
764 no_fts_field VARCHAR(10),
765 fts_field VARCHAR(10),
766 FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB;
767
768 CREATE TABLE t2(ID INT PRIMARY KEY,
769 no_fts_field VARCHAR(10),
770 fts_field VARCHAR(10),
771 FULLTEXT INDEX f(fts_field),
772 INDEX k2(fts_field),
773 FOREIGN KEY(fts_field) REFERENCES
774 t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB;
775
776 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
777
778 INSERT INTO t2 VALUES (1, 'AAA', 'BBB');
779
780 update t1 set fts_field='newchange' where id =1;
781
782 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
783 SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB");
784 SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange");
785 SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange");
786
787 DROP TABLE t2;
788
789 DROP TABLE t1;
790
791 # Testcases adopted from innodb_multi_update.test
792
793 CREATE TABLE t1(id INT PRIMARY KEY,
794 fts_field VARCHAR(10),
795 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
796
797
798 CREATE TABLE t2(id INT PRIMARY KEY,
799 fts_field VARCHAR(10),
800 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
801
802 INSERT INTO t1 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'),(9,'900'),(10,'1000'),(11,'1100'),(12,'1200');
803 INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800');
804
805 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo');
806
807 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo";
808
809 # Update two tables in the same statement
810 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'xoo'), t2.fts_field = CONCAT(t1.fts_field, 'xoo') where t1.fts_field=CONCAT(t2.fts_field, 'foo');
811
812 # Following selects shows whether the correct Doc ID are updated
813
814 # This row should present in table t1
815 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo");
816
817 # Following rows should be dropped
818 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo");
819 SELECT * FROM t1 WHERE MATCH(fts_field) against("100");
820
821 # This row should present in table t2
822 SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo");
823 SELECT * FROM t2 WHERE MATCH(fts_field) against("100");
824
825 # Follow rows should be marked as dropped
826 SELECT * FROM t2 WHERE MATCH(fts_field) against("200");
827 SELECT * FROM t2 WHERE MATCH(fts_field) against("400");
828
829
830 DROP TABLE t1;
831
832 DROP TABLE t2;
833
834
835 --echo
836 --echo BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY
837 --echo
838 # FTS setup did not track which tables it had already looked at to see whether
839 # they need initialization. Hilarity ensued when hitting circular dependencies.
840
841 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
842
843 CREATE TABLE t1 (
844 t1_id INT(10) UNSIGNED NOT NULL,
845 t2_id INT(10) UNSIGNED DEFAULT NULL,
846 PRIMARY KEY (t1_id),
847 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
848 ON DELETE CASCADE ON UPDATE CASCADE
849 ) ENGINE=InnoDB;
850
851 CREATE TABLE t2 (
852 t1_id INT(10) UNSIGNED NOT NULL,
853 t2_id INT(10) UNSIGNED NOT NULL,
854 t3_id INT(10) UNSIGNED NOT NULL,
855 t4_id INT(10) UNSIGNED NOT NULL,
856 PRIMARY KEY (t2_id),
857 FOREIGN KEY (t1_id) REFERENCES t1 (t1_id),
858 FOREIGN KEY (t3_id) REFERENCES t3 (t3_id)
859 ON DELETE CASCADE ON UPDATE CASCADE,
860 FOREIGN KEY (t4_id) REFERENCES t4 (t4_id)
861 ) ENGINE=InnoDB;
862
863 CREATE TABLE t3 (
864 t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
865 payload char(3),
866 PRIMARY KEY (t3_id)
867 ) ENGINE=InnoDB;
868
869 INSERT INTO t3 VALUES (1, '100');
870
871 CREATE TABLE t4 (
872 t2_id INT(10) UNSIGNED DEFAULT NULL,
873 t4_id INT(10) UNSIGNED NOT NULL,
874 PRIMARY KEY (t4_id),
875 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
876 ON DELETE CASCADE ON UPDATE CASCADE
877 ) ENGINE=InnoDB;
878
879 SET FOREIGN_KEY_CHECKS=1;
880
881 UPDATE t3 SET payload='101' WHERE t3_id=1;
882
883 SET FOREIGN_KEY_CHECKS=0;
884
885 DROP TABLE t1;
886 DROP TABLE t2;
887 DROP TABLE t3;
888 DROP TABLE t4;
889
890 --echo #
891 --echo # InnoDB: Failing assertion: result != FTS_INVALID in
892 --echo # fts_trx_row_get_new_state
893 --echo #
894 SET FOREIGN_KEY_CHECKS=1;
895 CREATE TABLE t1 (pk INT PRIMARY KEY,
896 f1 VARCHAR(10), f2 VARCHAR(10),
897 f3 VARCHAR(10), f4 VARCHAR(10),
898 f5 VARCHAR(10), f6 VARCHAR(10),
899 f7 VARCHAR(10), f8 VARCHAR(10),
900 FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4),
901 FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8),
902 INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
903 INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
904 INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
905 'mariadb', 'mariadb', 'mariadb', 'mariadb'),
906 (2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
907 'mariadb', 'mariadb', 'mariadb', 'mariadb'),
908 (3, 'innodb', 'innodb', 'innodb', 'innodb',
909 'innodb', 'innodb', 'innodb', 'innodb');
910 ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
911 START TRANSACTION;
912 DELETE FROM t1 where f1='mariadb';
913 SELECT * FROM t1;
914 ROLLBACK;
915
916 ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
917
918 START TRANSACTION;
919 DELETE FROM t1 where f3='mariadb';
920 SELECT * FROM t1;
921 ROLLBACK;
922
923 ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
924 --error ER_ROW_IS_REFERENCED_2
925 UPDATE t1 SET f6='update';
926
927 ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
928 --error ER_ROW_IS_REFERENCED_2
929 UPDATE t1 SET f6='cascade';
930 DROP TABLE t1;
931
932 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
933
934 --echo #
935 --echo # MDEV-25536 sym_node->table != NULL in pars_retrieve_table_def
936 --echo #
937 CREATE TABLE t1 (f1 TEXT,FULLTEXT (f1)) ENGINE=InnoDB;
938 ALTER TABLE t1 DISCARD TABLESPACE;
939 SET GLOBAL innodb_ft_aux_table='test/t1';
940 SELECT * FROM information_schema.innodb_ft_deleted;
941 DROP TABLE t1;
942 SET GLOBAL innodb_ft_aux_table=DEFAULT;
943
944 --echo #
945 --echo # MDEV-19522 InnoDB commit fails when FTS_DOC_ID value
946 --echo # is greater than 4294967295
947 --echo #
948 CREATE TABLE t1(
949 FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
950 f1 TEXT, f2 TEXT, PRIMARY KEY (FTS_DOC_ID),
951 FULLTEXT KEY (f1)) ENGINE=InnoDB;
952 INSERT INTO t1 VALUES (1,'txt','bbb');
953 UPDATE t1 SET FTS_DOC_ID = 4294967298;
954 SELECT * FROM t1 WHERE match(f1) against("txt");
955 SET @@session.insert_id = 100000000000;
956 INSERT INTO t1(f1, f2) VALUES ('aaa', 'bbb');
957 CREATE FULLTEXT INDEX i ON t1 (f2);
958 SELECT * FROM t1 WHERE match(f2) against("bbb");
959 # Cleanup
960 DROP TABLE t1;
961
962 --echo #
963 --echo # MDEV-30363 Failing assertion: trx->error_state == DB_SUCCESS
964 --echo # in que_run_threads
965 --echo #
966 CREATE TABLE server_stopword (value VARCHAR(1))engine=innodb;
967 SET GLOBAL innodb_ft_server_stopword_table='test/server_stopword';
968 CREATE TABLE t (t VARCHAR(1) COLLATE utf8_unicode_ci,
969 FULLTEXT (t))engine=innodb;
970 TRUNCATE TABLE t;
971 DROP TABLE t;
972 DROP TABLE server_stopword;
973 SET GLOBAL innodb_ft_server_stopword_table= default;