set names utf8; call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`"); CREATE TABLE t1 ( id1 INT , a1 VARCHAR(200) , b1 TEXT , FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1) ) CHARACTER SET = utf8 , ENGINE = InnoDB; CREATE TABLE t2 ( id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a2 VARCHAR(200), b2 TEXT , FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE, FULLTEXT KEY (b2,a2) ) CHARACTER SET = utf8 ,ENGINE = InnoDB; INSERT INTO t1 (id1,a1,b1) VALUES (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , (2,'How To Use MySQL Well','After you went through a ...'), (3,'Optimizing MySQL','In this tutorial we will show ...'); INSERT INTO t1 (id1,a1,b1) VALUES (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), (5,'MySQL vs. YourSQL','In the following database comparison ...'), (6,'MySQL Security','When configured properly, MySQL ...'); INSERT INTO t2 (a2,b2) VALUES ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'); INSERT INTO t2 (a2,b2) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); INSERT INTO t2 (a2,b2) VALUES ('MySQL Tricks','1. Never run mysqld as root. 2. ...'); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) DELETE FROM t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze Warning Engine-independent statistics are not collected for column 'b1' test.t1 analyze status OK ANALYZE TABLE t2; Table Op Msg_type Msg_text test.t2 analyze status Engine-independent statistics collected test.t2 analyze Warning Engine-independent statistics are not collected for column 'b2' test.t2 analyze status OK SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; id1 1 3 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; id2 1 3 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; id1 1 2 3 4 5 6 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; id2 1 2 3 4 5 6 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; id1 1 2 3 4 5 6 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; id2 1 2 3 4 5 6 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; id1 1 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; id2 1 set global innodb_optimize_fulltext_only=1; optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK set global innodb_optimize_fulltext_only=0; UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; id1 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; id2 3 6 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1; id1 1 2 3 4 5 6 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2; id2 1 2 3 4 5 6 SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; id2 1 2 3 4 5 6 DROP TABLE t2 , t1; create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; create table t2 (s1 int, s2 varchar(200), fulltext key(s2), foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB; insert into t1 values (1,'Sunshine'),(2,'Lollipops'); insert into t2 values (1,'Sunshine'),(2,'Lollipops'); update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine'; commit; select * from t2 where match(s2) against ('Lollipops'); s1 s2 DROP TABLE t2 , t1; create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; create table t2 (s1 int, s2 varchar(200), fulltext key(s2), foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB; insert into t1 values (1,'Sunshine'),(2,'Lollipops'); insert into t2 values (1,'Sunshine'),(2,'Lollipops'); delete from t1 where s2 <> 'Sunshine'; select * from t2 where match(s2) against ('Lollipops'); s1 s2 DROP TABLE t2 , t1; create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; create table t2 (s1 int, s2 varchar(200), fulltext key(s2), foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB; insert into t1 values (1,'Sunshine'),(2,'Lollipops'); insert into t2 values (1,'Sunshine'),(2,'Lollipops'); delete from t1 where s2 <> 'Sunshine'; select * from t2 where match(s2) against ('Lollipops'); s1 s2 DROP TABLE t2 , t1; create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; create table t2 (s1 int, s2 varchar(200), fulltext key(s2), foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB; insert into t1 values (1,'Sunshine'),(2,'Lollipops'); insert into t2 values (1,'Sunshine'),(2,'Lollipops'); update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine'; commit; select * from t2 where match(s2) against ('Lollipops'); s1 s2 DROP TABLE t2 , t1; create table t1 (s1 bigint unsigned not null, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200), foreign key (FTS_DOC_ID) references t1 (s1) on update cascade) ENGINE = InnoDB; create fulltext index idx on t2(s2); show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `FTS_DOC_ID` bigint(20) unsigned NOT NULL, `s2` varchar(200) DEFAULT NULL, KEY `FTS_DOC_ID` (`FTS_DOC_ID`), FULLTEXT KEY `idx` (`s2`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values (1,'Sunshine'),(2,'Lollipops'); insert into t2 values (1,'Sunshine'),(2,'Lollipops'); update t1 set s1 = 3 where s1=1; select * from t2 where match(s2) against ('sunshine'); FTS_DOC_ID s2 3 Sunshine update t1 set s1 = 1 where s1=3; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE) DROP TABLE t2 , t1; CREATE TABLE t1 ( id1 INT , a1 VARCHAR(200) PRIMARY KEY, b1 TEXT character set utf8 , FULLTEXT KEY (a1,b1) ) CHARACTER SET = utf8 ,ENGINE = InnoDB; CREATE TABLE t2 ( id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a2 VARCHAR(200), b2 TEXT character set utf8 , FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE, FULLTEXT KEY (b2,a2) ) CHARACTER SET = utf8 ,ENGINE = InnoDB; INSERT INTO t1 (id1,a1,b1) VALUES (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , (2,'How To Use MySQL Well','After you went through a ...'), (3,'Optimizing MySQL','In this tutorial we will show ...'), (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), (5,'MySQL vs. YourSQL','In the following database comparison ...'), (6,'MySQL Security','When configured properly, MySQL ...'); INSERT INTO t2 (a2,b2) VALUES ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; id2 a2 b2 SELECT * FROM t1 WHERE a1 LIKE '%tutorial%'; id1 a1 b1 SELECT * FROM t2 WHERE a2 LIKE '%tutorial%'; id2 a2 b2 DROP TABLE t2 , t1; call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`"); CREATE TABLE t1 ( id1 INT , a1 VARCHAR(200) , b1 TEXT , FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1) ) CHARACTER SET = utf8 , ENGINE = InnoDB; CREATE TABLE t2 ( id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a2 VARCHAR(200), b2 TEXT , FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE, FULLTEXT KEY (b2,a2) ) CHARACTER SET = utf8 ,ENGINE = InnoDB; INSERT INTO t1 (id1,a1,b1) VALUES (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , (2,'How To Use MySQL Well','After you went through a ...'), (3,'Optimizing MySQL','In this tutorial we will show ...'); INSERT INTO t2 (a2,b2) VALUES ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'); START TRANSACTION; INSERT INTO t1 (id1,a1,b1) VALUES (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), (5,'MySQL vs. YourSQL','In the following database comparison ...'), (6,'MySQL Security','When configured properly, MySQL ...'); INSERT INTO t2 (a2,b2) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); INSERT INTO t2 (a2,b2) VALUES ('MySQL Tricks','1. Never run mysqld as root. 2. ...'); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) DELETE FROM t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ; id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ; id2 a2 b2 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ; id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ; id2 a2 b2 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ; id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ; id2 a2 b2 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; id2 a2 b2 SELECT * FROM t1 ORDER BY id1; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... SELECT * FROM t2 ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... COMMIT; START TRANSACTION; UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; COMMIT; SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; id2 a2 b2 3 changing column - on UPDATE cascade In this tutorial we will show ... 6 changing column - on UPDATE cascade When configured properly, MySQL ... SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1; id1 a1 b1 1 changing column - on UPDATE cascade to check foreign constraint 2 changing column - on UPDATE cascade to check foreign constraint 3 changing column - on UPDATE cascade to check foreign constraint 4 changing column - on UPDATE cascade to check foreign constraint 5 changing column - on UPDATE cascade to check foreign constraint 6 changing column - on UPDATE cascade to check foreign constraint SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2; id2 a2 b2 1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ... 2 changing column - on UPDATE cascade After you went through a ... 3 changing column - on UPDATE cascade In this tutorial we will show ... 4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ... 5 changing column - on UPDATE cascade In the following database comparison ... 6 changing column - on UPDATE cascade When configured properly, MySQL ... SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; id2 a2 b2 1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ... 2 changing column - on UPDATE cascade After you went through a ... 3 changing column - on UPDATE cascade In this tutorial we will show ... 4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ... 5 changing column - on UPDATE cascade In the following database comparison ... 6 changing column - on UPDATE cascade When configured properly, MySQL ... DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; COMMIT; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); DELETE FROM t1 WHERE s2 <> 'Sunshine'; COMMIT; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); DELETE FROM t1 WHERE s2 <> 'Sunshine'; COMMIT; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; COMMIT; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; ROLLBACK; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); DELETE FROM t1 WHERE s2 <> 'Sunshine'; ROLLBACK; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); DELETE FROM t1 WHERE s2 <> 'Sunshine'; ROLLBACK; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), FULLTEXT KEY(s2), FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; ROLLBACK; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; set global innodb_file_per_table=1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB; INSERT INTO t1 (a,b) VALUES ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'); ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(200) DEFAULT NULL, `b` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPRESSED SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0; count(*) 11 INSERT INTO t1 (a,b) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); ANALYZE TABLE t1; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE); ERROR 42000: syntax error, unexpected '-' select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); id a b 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id; id a b x 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186 5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372 select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE); id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); id a b select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... ALTER TABLE t1 DROP INDEX idx; CREATE FULLTEXT INDEX idx on t1 (a,b); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE); id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); id a b 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id; id a b x 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186 5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); id a b select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); INSERT INTO t1 (a,b) VALUES ('test proximity search, test, proximity and phrase', 'search, with proximity innodb'); INSERT INTO t1 (a,b) VALUES ('test proximity fts search, test, proximity and phrase', 'search, with proximity innodb'); INSERT INTO t1 (a,b) VALUES ('test more proximity fts search, test, more proximity and phrase', 'search, with proximity innodb'); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE); id a b 8 test proximity search, test, proximity and phrase search, with proximity innodb SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@1' IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id; id a b 8 test proximity search, test, proximity and phrase search, with proximity innodb 9 test proximity fts search, test, proximity and phrase search, with proximity innodb 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id; id a b 8 test proximity search, test, proximity and phrase search, with proximity innodb 9 test proximity fts search, test, proximity and phrase search, with proximity innodb 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"more test proximity"@2' IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); id a b 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE); id a b 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ; UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ; select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE); id a b 1 MYSQL TUTORIAL dbms stands for database vðʒwi... select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE); id a b 1 MYSQL TUTORIAL dbms stands for database vðʒwi... SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; id a b 1 MYSQL TUTORIAL dbms stands for database vðʒwi... 3 OPTIMIZING MYSQL in this tutorial we will show ... DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); id a b SELECT * FROM t1 ORDER BY id; id a b 2 HOW TO USE MYSQL WELL after you went through a ... 4 1001 MYSQL TRICKS 1. never run mysqld as root. 2. ... 5 MYSQL VS. YOURSQL in the following database comparison ... 6 MYSQL SECURITY when configured properly, mysql ... 7 TEST QUERY EXPANSION for database ... DROP TABLE t1; SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = utf8, ENGINE=InnoDB; INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне не вредит'), ('Мога да ям стъкло', 'то не ми вреди'), ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'), ('Sævör grét', 'áðan því úlpan var ónýt'), ('うゐのおくやま','けふこえて'), ('いろはにほへど　ちりぬる','あさきゆめみじ　ゑひもせず'); INSERT INTO t1 (a,b) VALUES ('MySQL Tutorial','request docteam@oraclehelp.com ...') , ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') , ('when To Use MySQL Well','for free faq mail@xyz.com ...'); CREATE FULLTEXT INDEX idx on t1 (a,b); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id; id a b 1 Я могу есть стекло оно мне не вредит 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION); id a b 1 Я могу есть стекло оно мне не вредит SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id; id a b 1 Я могу есть стекло оно мне не вредит 2 Мога да ям стъкло то не ми вреди SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ　ゑひもせず"); id a b 7 いろはにほへど　ちりぬる あさきゆめみじ　ゑひもせず SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION); id a b 7 いろはにほへど　ちりぬる あさきゆめみじ　ゑひもせず SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ　+ゑひもせず" IN BOOLEAN MODE); id a b 7 いろはにほへど　ちりぬる あさきゆめみじ　ゑひもせず SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE); id a b 6 うゐのおくやま けふこえて SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); id a b 5 Sævör grét áðan því úlpan var ónýt SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE); id a b 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"query performace"@02' IN BOOLEAN MODE); id a b 9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB... SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE); id a b 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE); id a b 7 いろはにほへど　ちりぬる あさきゆめみじ　ゑひもせず ALTER TABLE t1 DROP INDEX idx; CREATE FULLTEXT INDEX idx on t1 (a,b); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); id a b 7 いろはにほへど　ちりぬる あさきゆめみじ　ゑひもせず UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE); DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ　ゑひもせず"); id a b SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem"); id a b 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id; id a b 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE); id a b 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig SELECT * FROM t1 ORDER BY id; id a b 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 4 Příliš žluťoučký kůň úpěl ďábelské kódy 6 うゐのおくやま けふこえて 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig 8 MySQL Tutorial request docteam@oraclehelp.com ... 9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB... 10 when To Use MySQL Well for free faq mail@xyz.com ... DROP TABLE t1; CREATE TABLE t1(ID INT PRIMARY KEY, no_fts_field VARCHAR(10), fts_field VARCHAR(10), FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); ID no_fts_field fts_field 1 AAA BBB UPDATE t1 SET fts_field='anychange' where id = 1; SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); ID no_fts_field fts_field 1 AAA anychange UPDATE t1 SET no_fts_field='anychange' where id = 1; SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); ID no_fts_field fts_field 1 anychange anychange UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1; SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); ID no_fts_field fts_field 1 anychange other SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); ID no_fts_field fts_field DROP INDEX f on t1; UPDATE t1 SET fts_field='anychange' where id = 1; UPDATE t1 SET no_fts_field='anychange' where id = 1; UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1; CREATE FULLTEXT INDEX f ON t1(FTS_FIELD); SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); ID no_fts_field fts_field 1 anychange other DROP TABLE t1; CREATE TABLE t1(`FTS_DOC_ID` serial, no_fts_field VARCHAR(10), fts_field VARCHAR(10), FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1; ERROR HY000: Invalid InnoDB FTS Doc ID UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1; SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); FTS_DOC_ID no_fts_field fts_field 2 AAA anychange SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); FTS_DOC_ID no_fts_field fts_field UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2; SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); FTS_DOC_ID no_fts_field fts_field 2 anychange anychange UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2; ERROR HY000: Invalid InnoDB FTS Doc ID SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); FTS_DOC_ID no_fts_field fts_field UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2; ERROR HY000: Invalid InnoDB FTS Doc ID DROP INDEX f ON t1; UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2; UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2; SELECT * FROM t1; FTS_DOC_ID no_fts_field fts_field 2 anychange newchange DROP TABLE t1; CREATE TABLE t1(ID INT PRIMARY KEY, no_fts_field VARCHAR(10), fts_field VARCHAR(10), FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB; CREATE TABLE t2(ID INT PRIMARY KEY, no_fts_field VARCHAR(10), fts_field VARCHAR(10), FULLTEXT INDEX f(fts_field), INDEX k2(fts_field), FOREIGN KEY(fts_field) REFERENCES t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB; INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); INSERT INTO t2 VALUES (1, 'AAA', 'BBB'); update t1 set fts_field='newchange' where id =1; SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); ID no_fts_field fts_field SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB"); ID no_fts_field fts_field SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange"); ID no_fts_field fts_field 1 AAA newchange SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange"); ID no_fts_field fts_field 1 AAA newchange DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1(id INT PRIMARY KEY, fts_field VARCHAR(10), FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; CREATE TABLE t2(id INT PRIMARY KEY, fts_field VARCHAR(10), FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; 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'); INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'); UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo'); UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo"; 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'); SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo"); id fts_field 1 100foofoo SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo"); id fts_field SELECT * FROM t1 WHERE MATCH(fts_field) against("100"); id fts_field SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo"); id fts_field 4 400fooxoo SELECT * FROM t2 WHERE MATCH(fts_field) against("100"); id fts_field 1 100 SELECT * FROM t2 WHERE MATCH(fts_field) against("200"); id fts_field SELECT * FROM t2 WHERE MATCH(fts_field) against("400"); id fts_field DROP TABLE t1; DROP TABLE t2; BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; CREATE TABLE t1 ( t1_id INT(10) UNSIGNED NOT NULL, t2_id INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (t1_id), FOREIGN KEY (t2_id) REFERENCES t2 (t2_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; CREATE TABLE t2 ( t1_id INT(10) UNSIGNED NOT NULL, t2_id INT(10) UNSIGNED NOT NULL, t3_id INT(10) UNSIGNED NOT NULL, t4_id INT(10) UNSIGNED NOT NULL, PRIMARY KEY (t2_id), FOREIGN KEY (t1_id) REFERENCES t1 (t1_id), FOREIGN KEY (t3_id) REFERENCES t3 (t3_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (t4_id) REFERENCES t4 (t4_id) ) ENGINE=InnoDB; CREATE TABLE t3 ( t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, payload char(3), PRIMARY KEY (t3_id) ) ENGINE=InnoDB; INSERT INTO t3 VALUES (1, '100'); CREATE TABLE t4 ( t2_id INT(10) UNSIGNED DEFAULT NULL, t4_id INT(10) UNSIGNED NOT NULL, PRIMARY KEY (t4_id), FOREIGN KEY (t2_id) REFERENCES t2 (t2_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; SET FOREIGN_KEY_CHECKS=1; UPDATE t3 SET payload='101' WHERE t3_id=1; SET FOREIGN_KEY_CHECKS=0; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;