Debian Codegraph @

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

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