1set names utf8; 2call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); 3call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`"); 4CREATE TABLE t1 ( 5id1 INT , 6a1 VARCHAR(200) , 7b1 TEXT , 8FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1) 9) CHARACTER SET = utf8 , ENGINE = InnoDB; 10CREATE TABLE t2 ( 11id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 12a2 VARCHAR(200), 13b2 TEXT , 14FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE, 15FULLTEXT KEY (b2,a2) 16) CHARACTER SET = utf8 ,ENGINE = InnoDB; 17INSERT INTO t1 (id1,a1,b1) VALUES 18(1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 19(2,'How To Use MySQL Well','After you went through a ...'), 20(3,'Optimizing MySQL','In this tutorial we will show ...'); 21INSERT INTO t1 (id1,a1,b1) VALUES 22(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 23(5,'MySQL vs. YourSQL','In the following database comparison ...'), 24(6,'MySQL Security','When configured properly, MySQL ...'); 25INSERT INTO t2 (a2,b2) VALUES 26('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 27('How To Use MySQL Well','After you went through a ...'), 28('Optimizing MySQL','In this tutorial we will show ...'); 29INSERT INTO t2 (a2,b2) VALUES 30('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 31('MySQL vs. YourSQL','In the following database comparison ...'), 32('MySQL Security','When configured properly, MySQL ...'); 33INSERT INTO t2 (a2,b2) VALUES 34('MySQL Tricks','1. Never run mysqld as root. 2. ...'); 35ERROR 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) 36DELETE FROM t1; 37ERROR 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) 38ANALYZE TABLE t1; 39Table Op Msg_type Msg_text 40test.t1 analyze status Engine-independent statistics collected 41test.t1 analyze Warning Engine-independent statistics are not collected for column 'b1' 42test.t1 analyze status OK 43ANALYZE TABLE t2; 44Table Op Msg_type Msg_text 45test.t2 analyze status Engine-independent statistics collected 46test.t2 analyze Warning Engine-independent statistics are not collected for column 'b2' 47test.t2 analyze status OK 48SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; 49id1 501 513 52SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; 53id2 541 553 56SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; 57id1 581 592 603 614 625 636 64SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; 65id2 661 672 683 694 705 716 72SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; 73id1 741 752 763 774 785 796 80SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; 81id2 821 832 843 854 865 876 88SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; 89id1 901 91SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; 92id2 931 94set global innodb_optimize_fulltext_only=1; 95optimize table t1; 96Table Op Msg_type Msg_text 97test.t1 optimize status OK 98set global innodb_optimize_fulltext_only=0; 99UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE 100MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 101SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 102id1 103SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 104id2 1053 1066 107SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1; 108id1 1091 1102 1113 1124 1135 1146 115SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2; 116id2 1171 1182 1193 1204 1215 1226 123SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; 124id2 1251 1262 1273 1284 1295 1306 131DROP TABLE t2 , t1; 132create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; 133create table t2 (s1 int, s2 varchar(200), 134fulltext key(s2), 135foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB; 136insert into t1 values (1,'Sunshine'),(2,'Lollipops'); 137insert into t2 values (1,'Sunshine'),(2,'Lollipops'); 138update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine'; 139commit; 140select * from t2 where match(s2) against ('Lollipops'); 141s1 s2 142DROP TABLE t2 , t1; 143create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; 144create table t2 (s1 int, s2 varchar(200), 145fulltext key(s2), 146foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB; 147insert into t1 values (1,'Sunshine'),(2,'Lollipops'); 148insert into t2 values (1,'Sunshine'),(2,'Lollipops'); 149delete from t1 where s2 <> 'Sunshine'; 150select * from t2 where match(s2) against ('Lollipops'); 151s1 s2 152DROP TABLE t2 , t1; 153create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; 154create table t2 (s1 int, s2 varchar(200), 155fulltext key(s2), 156foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB; 157insert into t1 values (1,'Sunshine'),(2,'Lollipops'); 158insert into t2 values (1,'Sunshine'),(2,'Lollipops'); 159delete from t1 where s2 <> 'Sunshine'; 160select * from t2 where match(s2) against ('Lollipops'); 161s1 s2 162DROP TABLE t2 , t1; 163create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; 164create table t2 (s1 int, s2 varchar(200), 165fulltext key(s2), 166foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB; 167insert into t1 values (1,'Sunshine'),(2,'Lollipops'); 168insert into t2 values (1,'Sunshine'),(2,'Lollipops'); 169update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine'; 170commit; 171select * from t2 where match(s2) against ('Lollipops'); 172s1 s2 173DROP TABLE t2 , t1; 174create table t1 (s1 bigint unsigned not null, s2 varchar(200), 175primary key (s1,s2)) ENGINE = InnoDB; 176create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200), 177foreign key (FTS_DOC_ID) references t1 (s1) 178on update cascade) ENGINE = InnoDB; 179create fulltext index idx on t2(s2); 180show create table t2; 181Table Create Table 182t2 CREATE TABLE `t2` ( 183 `FTS_DOC_ID` bigint(20) unsigned NOT NULL, 184 `s2` varchar(200) DEFAULT NULL, 185 KEY `FTS_DOC_ID` (`FTS_DOC_ID`), 186 FULLTEXT KEY `idx` (`s2`), 187 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE 188) ENGINE=InnoDB DEFAULT CHARSET=latin1 189insert into t1 values (1,'Sunshine'),(2,'Lollipops'); 190insert into t2 values (1,'Sunshine'),(2,'Lollipops'); 191update t1 set s1 = 3 where s1=1; 192select * from t2 where match(s2) against ('sunshine'); 193FTS_DOC_ID s2 1943 Sunshine 195update t1 set s1 = 1 where s1=3; 196ERROR 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) 197DROP TABLE t2 , t1; 198CREATE TABLE t1 ( 199id1 INT , 200a1 VARCHAR(200) PRIMARY KEY, 201b1 TEXT character set utf8 , 202FULLTEXT KEY (a1,b1) 203) CHARACTER SET = utf8 ,ENGINE = InnoDB; 204CREATE TABLE t2 ( 205id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 206a2 VARCHAR(200), 207b2 TEXT character set utf8 , 208FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE, 209FULLTEXT KEY (b2,a2) 210) CHARACTER SET = utf8 ,ENGINE = InnoDB; 211INSERT INTO t1 (id1,a1,b1) VALUES 212(1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 213(2,'How To Use MySQL Well','After you went through a ...'), 214(3,'Optimizing MySQL','In this tutorial we will show ...'), 215(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 216(5,'MySQL vs. YourSQL','In the following database comparison ...'), 217(6,'MySQL Security','When configured properly, MySQL ...'); 218INSERT INTO t2 (a2,b2) VALUES 219('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 220('How To Use MySQL Well','After you went through a ...'), 221('Optimizing MySQL','In this tutorial we will show ...'), 222('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 223('MySQL vs. YourSQL','In the following database comparison ...'), 224('MySQL Security','When configured properly, MySQL ...'); 225DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 226SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 227id1 a1 b1 228SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 229id2 a2 b2 230SELECT * FROM t1 WHERE a1 LIKE '%tutorial%'; 231id1 a1 b1 232SELECT * FROM t2 WHERE a2 LIKE '%tutorial%'; 233id2 a2 b2 234DROP TABLE t2 , t1; 235call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`"); 236CREATE TABLE t1 ( 237id1 INT , 238a1 VARCHAR(200) , 239b1 TEXT , 240FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1) 241) CHARACTER SET = utf8 , ENGINE = InnoDB; 242CREATE TABLE t2 ( 243id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 244a2 VARCHAR(200), 245b2 TEXT , 246FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE, 247FULLTEXT KEY (b2,a2) 248) CHARACTER SET = utf8 ,ENGINE = InnoDB; 249INSERT INTO t1 (id1,a1,b1) VALUES 250(1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 251(2,'How To Use MySQL Well','After you went through a ...'), 252(3,'Optimizing MySQL','In this tutorial we will show ...'); 253INSERT INTO t2 (a2,b2) VALUES 254('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 255('How To Use MySQL Well','After you went through a ...'), 256('Optimizing MySQL','In this tutorial we will show ...'); 257START TRANSACTION; 258INSERT INTO t1 (id1,a1,b1) VALUES 259(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 260(5,'MySQL vs. YourSQL','In the following database comparison ...'), 261(6,'MySQL Security','When configured properly, MySQL ...'); 262INSERT INTO t2 (a2,b2) VALUES 263('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 264('MySQL vs. YourSQL','In the following database comparison ...'), 265('MySQL Security','When configured properly, MySQL ...'); 266INSERT INTO t2 (a2,b2) VALUES 267('MySQL Tricks','1. Never run mysqld as root. 2. ...'); 268ERROR 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) 269DELETE FROM t1; 270ERROR 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) 271SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; 272id1 a1 b1 2731 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2743 Optimizing MySQL In this tutorial we will show ... 275SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; 276id2 a2 b2 2771 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2783 Optimizing MySQL In this tutorial we will show ... 279SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; 280id1 a1 b1 2811 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2822 How To Use MySQL Well After you went through a ... 2833 Optimizing MySQL In this tutorial we will show ... 284SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; 285id2 a2 b2 2861 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2872 How To Use MySQL Well After you went through a ... 2883 Optimizing MySQL In this tutorial we will show ... 289SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; 290id1 a1 b1 2911 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2922 How To Use MySQL Well After you went through a ... 2933 Optimizing MySQL In this tutorial we will show ... 294SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; 295id2 a2 b2 2961 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2972 How To Use MySQL Well After you went through a ... 2983 Optimizing MySQL In this tutorial we will show ... 299SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; 300id1 a1 b1 3011 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 302SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; 303id2 a2 b2 3041 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 305SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ; 306id1 a1 b1 307SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ; 308id2 a2 b2 309SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ; 310id1 a1 b1 311SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ; 312id2 a2 b2 313SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ; 314id1 a1 b1 315SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ; 316id2 a2 b2 317SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; 318id1 a1 b1 319SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; 320id2 a2 b2 321SELECT * FROM t1 ORDER BY id1; 322id1 a1 b1 3231 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3242 How To Use MySQL Well After you went through a ... 3253 Optimizing MySQL In this tutorial we will show ... 3264 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 3275 MySQL vs. YourSQL In the following database comparison ... 3286 MySQL Security When configured properly, MySQL ... 329SELECT * FROM t2 ORDER BY id2; 330id2 a2 b2 3311 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3322 How To Use MySQL Well After you went through a ... 3333 Optimizing MySQL In this tutorial we will show ... 3344 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 3355 MySQL vs. YourSQL In the following database comparison ... 3366 MySQL Security When configured properly, MySQL ... 337COMMIT; 338START TRANSACTION; 339UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE 340MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 341COMMIT; 342SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 343id1 a1 b1 344SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; 345id2 a2 b2 3463 changing column - on UPDATE cascade In this tutorial we will show ... 3476 changing column - on UPDATE cascade When configured properly, MySQL ... 348SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1; 349id1 a1 b1 3501 changing column - on UPDATE cascade to check foreign constraint 3512 changing column - on UPDATE cascade to check foreign constraint 3523 changing column - on UPDATE cascade to check foreign constraint 3534 changing column - on UPDATE cascade to check foreign constraint 3545 changing column - on UPDATE cascade to check foreign constraint 3556 changing column - on UPDATE cascade to check foreign constraint 356SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2; 357id2 a2 b2 3581 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ... 3592 changing column - on UPDATE cascade After you went through a ... 3603 changing column - on UPDATE cascade In this tutorial we will show ... 3614 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ... 3625 changing column - on UPDATE cascade In the following database comparison ... 3636 changing column - on UPDATE cascade When configured properly, MySQL ... 364SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; 365id2 a2 b2 3661 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ... 3672 changing column - on UPDATE cascade After you went through a ... 3683 changing column - on UPDATE cascade In this tutorial we will show ... 3694 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ... 3705 changing column - on UPDATE cascade In the following database comparison ... 3716 changing column - on UPDATE cascade When configured properly, MySQL ... 372DROP TABLE t2 , t1; 373CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 374CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 375FULLTEXT KEY(s2), 376FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB; 377START TRANSACTION; 378INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 379INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 380UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; 381COMMIT; 382SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 383s1 s2 384DROP TABLE t2 , t1; 385CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 386CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 387FULLTEXT KEY(s2), 388FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB; 389START TRANSACTION; 390INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 391INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 392DELETE FROM t1 WHERE s2 <> 'Sunshine'; 393COMMIT; 394SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 395s1 s2 396DROP TABLE t2 , t1; 397CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 398CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 399FULLTEXT KEY(s2), 400FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB; 401START TRANSACTION; 402INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 403INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 404DELETE FROM t1 WHERE s2 <> 'Sunshine'; 405COMMIT; 406SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 407s1 s2 408DROP TABLE t2 , t1; 409CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 410CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 411FULLTEXT KEY(s2), 412FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB; 413START TRANSACTION; 414INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 415INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 416UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; 417COMMIT; 418SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 419s1 s2 420DROP TABLE t2 , t1; 421CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 422CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 423FULLTEXT KEY(s2), 424FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB; 425START TRANSACTION; 426INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 427INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 428UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; 429ROLLBACK; 430SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 431s1 s2 432DROP TABLE t2 , t1; 433CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 434CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 435FULLTEXT KEY(s2), 436FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB; 437START TRANSACTION; 438INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 439INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 440DELETE FROM t1 WHERE s2 <> 'Sunshine'; 441ROLLBACK; 442SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 443s1 s2 444DROP TABLE t2 , t1; 445CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 446CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 447FULLTEXT KEY(s2), 448FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB; 449START TRANSACTION; 450INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 451INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 452DELETE FROM t1 WHERE s2 <> 'Sunshine'; 453ROLLBACK; 454SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 455s1 s2 456DROP TABLE t2 , t1; 457CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; 458CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), 459FULLTEXT KEY(s2), 460FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB; 461START TRANSACTION; 462INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); 463INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); 464UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; 465ROLLBACK; 466SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); 467s1 s2 468DROP TABLE t2 , t1; 469set global innodb_file_per_table=1; 470CREATE TABLE t1 ( 471id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 472a VARCHAR(200), 473b TEXT 474) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB; 475INSERT INTO t1 (a,b) VALUES 476('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , 477('How To Use MySQL Well','After you went through a ...'), 478('Optimizing MySQL','In this tutorial we will show ...'); 479ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); 480SHOW CREATE TABLE t1; 481Table Create Table 482t1 CREATE TABLE `t1` ( 483 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 484 `a` varchar(200) DEFAULT NULL, 485 `b` text DEFAULT NULL, 486 PRIMARY KEY (`id`), 487 FULLTEXT KEY `idx` (`a`,`b`) 488) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED 489SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0; 490count(*) 49111 492INSERT INTO t1 (a,b) VALUES 493('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 494('MySQL vs. YourSQL','In the following database comparison ...'), 495('MySQL Security','When configured properly, MySQL ...'); 496ANALYZE TABLE t1; 497SELECT * FROM t1 WHERE MATCH (a,b) 498AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; 499id a b 5001 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 5013 Optimizing MySQL In this tutorial we will show ... 502select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); 503id a b 5041 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 505select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE); 506ERROR 42000: syntax error, unexpected '-' 507select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); 508id a b 5094 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 510select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; 511id a b 5121 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 5132 How To Use MySQL Well After you went through a ... 5143 Optimizing MySQL In this tutorial we will show ... 5155 MySQL vs. YourSQL In the following database comparison ... 5166 MySQL Security When configured properly, MySQL ... 517select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id; 518id a b x 5191 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143 5202 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 5213 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186 5224 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186 5235 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186 5246 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372 525select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE); 526id a b 5271 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 528select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); 529id a b 530select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; 531id a b 5321 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 5332 How To Use MySQL Well After you went through a ... 5343 Optimizing MySQL In this tutorial we will show ... 5354 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5365 MySQL vs. YourSQL In the following database comparison ... 5376 MySQL Security When configured properly, MySQL ... 538ALTER TABLE t1 DROP INDEX idx; 539CREATE FULLTEXT INDEX idx on t1 (a,b); 540SELECT * FROM t1 WHERE MATCH (a,b) 541AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; 542id a b 5431 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 5443 Optimizing MySQL In this tutorial we will show ... 545select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); 546id a b 5471 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 548select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE); 549id a b 5501 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 551select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); 552id a b 5534 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 554select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; 555id a b 5561 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 5572 How To Use MySQL Well After you went through a ... 5583 Optimizing MySQL In this tutorial we will show ... 5595 MySQL vs. YourSQL In the following database comparison ... 5606 MySQL Security When configured properly, MySQL ... 561select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id; 562id a b x 5631 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143 5642 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 5653 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186 5664 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186 5675 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186 5686 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372 569select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); 570id a b 571select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; 572id a b 5731 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 5742 How To Use MySQL Well After you went through a ... 5753 Optimizing MySQL In this tutorial we will show ... 5764 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5775 MySQL vs. YourSQL In the following database comparison ... 5786 MySQL Security When configured properly, MySQL ... 579INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); 580INSERT INTO t1 (a,b) VALUES 581('test proximity search, test, proximity and phrase', 582'search, with proximity innodb'); 583INSERT INTO t1 (a,b) VALUES 584('test proximity fts search, test, proximity and phrase', 585'search, with proximity innodb'); 586INSERT INTO t1 (a,b) VALUES 587('test more proximity fts search, test, more proximity and phrase', 588'search, with proximity innodb'); 589SELECT * FROM t1 590WHERE MATCH (a,b) 591AGAINST ('"proximity search"@2' IN BOOLEAN MODE); 592id a b 5938 test proximity search, test, proximity and phrase search, with proximity innodb 594SELECT * FROM t1 595WHERE MATCH (a,b) 596AGAINST ('"proximity search"@1' IN BOOLEAN MODE); 597id a b 598SELECT * FROM t1 599WHERE MATCH (a,b) 600AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id; 601id a b 6028 test proximity search, test, proximity and phrase search, with proximity innodb 6039 test proximity fts search, test, proximity and phrase search, with proximity innodb 60410 test more proximity fts search, test, more proximity and phrase search, with proximity innodb 605SELECT * FROM t1 606WHERE MATCH (a,b) 607AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id; 608id a b 6098 test proximity search, test, proximity and phrase search, with proximity innodb 6109 test proximity fts search, test, proximity and phrase search, with proximity innodb 61110 test more proximity fts search, test, more proximity and phrase search, with proximity innodb 612SELECT * FROM t1 613WHERE MATCH (a,b) 614AGAINST ('"more test proximity"@2' IN BOOLEAN MODE); 615id a b 616SELECT * FROM t1 617WHERE MATCH (a,b) 618AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); 619id a b 62010 test more proximity fts search, test, more proximity and phrase search, with proximity innodb 621SELECT * FROM t1 622WHERE MATCH (a,b) 623AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE); 624id a b 62510 test more proximity fts search, test, more proximity and phrase search, with proximity innodb 626UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ; 627UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ; 628select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE); 629id a b 6301 MYSQL TUTORIAL dbms stands for database vðʒwi... 631select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE); 632id a b 6331 MYSQL TUTORIAL dbms stands for database vðʒwi... 634SELECT * FROM t1 WHERE MATCH (a,b) 635AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; 636id a b 6371 MYSQL TUTORIAL dbms stands for database vðʒwi... 6383 OPTIMIZING MYSQL in this tutorial we will show ... 639DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); 640DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE); 641SELECT * FROM t1 WHERE MATCH (a,b) 642AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); 643id a b 644SELECT * FROM t1 ORDER BY id; 645id a b 6462 HOW TO USE MYSQL WELL after you went through a ... 6474 1001 MYSQL TRICKS 1. never run mysqld as root. 2. ... 6485 MYSQL VS. YOURSQL in the following database comparison ... 6496 MYSQL SECURITY when configured properly, mysql ... 6507 TEST QUERY EXPANSION for database ... 651DROP TABLE t1; 652SET GLOBAL innodb_file_per_table=1; 653CREATE TABLE t1 ( 654id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 655a VARCHAR(200), 656b TEXT 657) CHARACTER SET = utf8, ENGINE=InnoDB; 658INSERT INTO t1 (a,b) VALUES 659('Я могу есть стекло', 'оно мне не вредит'), 660('Мога да ям стъкло', 'то не ми вреди'), 661('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), 662('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'), 663('Sævör grét', 'áðan því úlpan var ónýt'), 664('うゐのおくやま','けふこえて'), 665('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず'); 666INSERT INTO t1 (a,b) VALUES 667('MySQL Tutorial','request docteam@oraclehelp.com ...') , 668('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') , 669('when To Use MySQL Well','for free faq mail@xyz.com ...'); 670CREATE FULLTEXT INDEX idx on t1 (a,b); 671SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id; 672id a b 6731 Я могу есть стекло оно мне не вредит 6743 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 675SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION); 676id a b 6771 Я могу есть стекло оно мне не вредит 678SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id; 679id a b 6801 Я могу есть стекло оно мне не вредит 6812 Мога да ям стъкло то не ми вреди 682SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE); 683id a b 684SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE); 685id a b 686SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); 687id a b 6887 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず 689SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION); 690id a b 6917 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず 692SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE); 693id a b 6947 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず 695SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE); 696id a b 6976 うゐのおくやま けふこえて 698SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); 699id a b 7005 Sævör grét áðan því úlpan var ónýt 701SELECT * FROM t1 702WHERE MATCH (a,b) 703AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE); 704id a b 7053 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 706SELECT * FROM t1 707WHERE MATCH (a,b) 708AGAINST ('"query performace"@02' IN BOOLEAN MODE); 709id a b 7109 Trial version query performace @1255 minute on 2.1Hz Memory 2GB... 711SELECT * FROM t1 712WHERE MATCH (a,b) 713AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE); 714id a b 7153 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 716SELECT * FROM t1 717WHERE MATCH (a,b) 718AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE); 719id a b 720SELECT * FROM t1 721WHERE MATCH (a,b) 722AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE); 723id a b 7247 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず 725ALTER TABLE t1 DROP INDEX idx; 726CREATE FULLTEXT INDEX idx on t1 (a,b); 727SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); 728id a b 7297 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず 730UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); 731UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE); 732DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); 733SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); 734id a b 735SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem"); 736id a b 7377 Pchnąć w tę łódź jeża lub osiem skrzyń fig 738SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); 739id a b 740SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id; 741id a b 7421 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 7432 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 744SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); 745id a b 746SELECT * FROM t1 747WHERE MATCH (a,b) 748AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE); 749id a b 7507 Pchnąć w tę łódź jeża lub osiem skrzyń fig 751SELECT * FROM t1 ORDER BY id; 752id a b 7531 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 7542 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 7553 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 7564 Příliš žluťoučký kůň úpěl ďábelské kódy 7576 うゐのおくやま けふこえて 7587 Pchnąć w tę łódź jeża lub osiem skrzyń fig 7598 MySQL Tutorial request docteam@oraclehelp.com ... 7609 Trial version query performace @1255 minute on 2.1Hz Memory 2GB... 76110 when To Use MySQL Well for free faq mail@xyz.com ... 762DROP TABLE t1; 763CREATE TABLE t1(ID INT PRIMARY KEY, 764no_fts_field VARCHAR(10), 765fts_field VARCHAR(10), 766FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; 767INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); 768SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); 769ID no_fts_field fts_field 7701 AAA BBB 771UPDATE t1 SET fts_field='anychange' where id = 1; 772SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); 773ID no_fts_field fts_field 7741 AAA anychange 775UPDATE t1 SET no_fts_field='anychange' where id = 1; 776SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); 777ID no_fts_field fts_field 7781 anychange anychange 779UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1; 780SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); 781ID no_fts_field fts_field 7821 anychange other 783SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); 784ID no_fts_field fts_field 785DROP INDEX f on t1; 786UPDATE t1 SET fts_field='anychange' where id = 1; 787UPDATE t1 SET no_fts_field='anychange' where id = 1; 788UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1; 789CREATE FULLTEXT INDEX f ON t1(FTS_FIELD); 790SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); 791ID no_fts_field fts_field 7921 anychange other 793DROP TABLE t1; 794CREATE TABLE t1(`FTS_DOC_ID` serial, 795no_fts_field VARCHAR(10), 796fts_field VARCHAR(10), 797FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; 798INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); 799UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1; 800ERROR HY000: Invalid InnoDB FTS Doc ID 801UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1; 802SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); 803FTS_DOC_ID no_fts_field fts_field 8042 AAA anychange 805SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); 806FTS_DOC_ID no_fts_field fts_field 807UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2; 808SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); 809FTS_DOC_ID no_fts_field fts_field 8102 anychange anychange 811UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2; 812ERROR HY000: Invalid InnoDB FTS Doc ID 813SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); 814FTS_DOC_ID no_fts_field fts_field 815UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2; 816ERROR HY000: Invalid InnoDB FTS Doc ID 817DROP INDEX f ON t1; 818UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2; 819UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2; 820SELECT * FROM t1; 821FTS_DOC_ID no_fts_field fts_field 8222 anychange newchange 823DROP TABLE t1; 824CREATE TABLE t1(ID INT PRIMARY KEY, 825no_fts_field VARCHAR(10), 826fts_field VARCHAR(10), 827FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB; 828CREATE TABLE t2(ID INT PRIMARY KEY, 829no_fts_field VARCHAR(10), 830fts_field VARCHAR(10), 831FULLTEXT INDEX f(fts_field), 832INDEX k2(fts_field), 833FOREIGN KEY(fts_field) REFERENCES 834t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB; 835INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); 836INSERT INTO t2 VALUES (1, 'AAA', 'BBB'); 837update t1 set fts_field='newchange' where id =1; 838SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); 839ID no_fts_field fts_field 840SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB"); 841ID no_fts_field fts_field 842SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange"); 843ID no_fts_field fts_field 8441 AAA newchange 845SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange"); 846ID no_fts_field fts_field 8471 AAA newchange 848DROP TABLE t2; 849DROP TABLE t1; 850CREATE TABLE t1(id INT PRIMARY KEY, 851fts_field VARCHAR(10), 852FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; 853CREATE TABLE t2(id INT PRIMARY KEY, 854fts_field VARCHAR(10), 855FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; 856INSERT 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'); 857INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'); 858UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo'); 859UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo"; 860UPDATE 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'); 861SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo"); 862id fts_field 8631 100foofoo 864SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo"); 865id fts_field 866SELECT * FROM t1 WHERE MATCH(fts_field) against("100"); 867id fts_field 868SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo"); 869id fts_field 8704 400fooxoo 871SELECT * FROM t2 WHERE MATCH(fts_field) against("100"); 872id fts_field 8731 100 874SELECT * FROM t2 WHERE MATCH(fts_field) against("200"); 875id fts_field 876SELECT * FROM t2 WHERE MATCH(fts_field) against("400"); 877id fts_field 878DROP TABLE t1; 879DROP TABLE t2; 880 881BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY 882 883SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; 884CREATE TABLE t1 ( 885t1_id INT(10) UNSIGNED NOT NULL, 886t2_id INT(10) UNSIGNED DEFAULT NULL, 887PRIMARY KEY (t1_id), 888FOREIGN KEY (t2_id) REFERENCES t2 (t2_id) 889ON DELETE CASCADE ON UPDATE CASCADE 890) ENGINE=InnoDB; 891CREATE TABLE t2 ( 892t1_id INT(10) UNSIGNED NOT NULL, 893t2_id INT(10) UNSIGNED NOT NULL, 894t3_id INT(10) UNSIGNED NOT NULL, 895t4_id INT(10) UNSIGNED NOT NULL, 896PRIMARY KEY (t2_id), 897FOREIGN KEY (t1_id) REFERENCES t1 (t1_id), 898FOREIGN KEY (t3_id) REFERENCES t3 (t3_id) 899ON DELETE CASCADE ON UPDATE CASCADE, 900FOREIGN KEY (t4_id) REFERENCES t4 (t4_id) 901) ENGINE=InnoDB; 902CREATE TABLE t3 ( 903t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 904payload char(3), 905PRIMARY KEY (t3_id) 906) ENGINE=InnoDB; 907INSERT INTO t3 VALUES (1, '100'); 908CREATE TABLE t4 ( 909t2_id INT(10) UNSIGNED DEFAULT NULL, 910t4_id INT(10) UNSIGNED NOT NULL, 911PRIMARY KEY (t4_id), 912FOREIGN KEY (t2_id) REFERENCES t2 (t2_id) 913ON DELETE CASCADE ON UPDATE CASCADE 914) ENGINE=InnoDB; 915SET FOREIGN_KEY_CHECKS=1; 916UPDATE t3 SET payload='101' WHERE t3_id=1; 917SET FOREIGN_KEY_CHECKS=0; 918DROP TABLE t1; 919DROP TABLE t2; 920DROP TABLE t3; 921DROP TABLE t4; 922# 923# InnoDB: Failing assertion: result != FTS_INVALID in 924# fts_trx_row_get_new_state 925# 926SET FOREIGN_KEY_CHECKS=1; 927CREATE TABLE t1 (pk INT PRIMARY KEY, 928f1 VARCHAR(10), f2 VARCHAR(10), 929f3 VARCHAR(10), f4 VARCHAR(10), 930f5 VARCHAR(10), f6 VARCHAR(10), 931f7 VARCHAR(10), f8 VARCHAR(10), 932FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4), 933FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8), 934INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4), 935INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB; 936INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb', 937'mariadb', 'mariadb', 'mariadb', 'mariadb'), 938(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb', 939'mariadb', 'mariadb', 'mariadb', 'mariadb'), 940(3, 'innodb', 'innodb', 'innodb', 'innodb', 941'innodb', 'innodb', 'innodb', 'innodb'); 942ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL; 943START TRANSACTION; 944DELETE FROM t1 where f1='mariadb'; 945SELECT * FROM t1; 946pk f1 f2 f3 f4 f5 f6 f7 f8 9472 NULL mariadb mariadb mariadb mariadb mariadb mariadb mariadb 9483 innodb innodb innodb innodb innodb innodb innodb innodb 949ROLLBACK; 950ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE; 951START TRANSACTION; 952DELETE FROM t1 where f3='mariadb'; 953SELECT * FROM t1; 954pk f1 f2 f3 f4 f5 f6 f7 f8 9553 innodb innodb innodb innodb innodb innodb innodb innodb 956ROLLBACK; 957ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL; 958UPDATE t1 SET f6='update'; 959ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL) 960ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE; 961UPDATE t1 SET f6='cascade'; 962ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL) 963DROP TABLE t1; 964SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 965# 966# MDEV-25536 sym_node->table != NULL in pars_retrieve_table_def 967# 968CREATE TABLE t1 (f1 TEXT,FULLTEXT (f1)) ENGINE=InnoDB; 969ALTER TABLE t1 DISCARD TABLESPACE; 970SET GLOBAL innodb_ft_aux_table='test/t1'; 971SELECT * FROM information_schema.innodb_ft_deleted; 972DOC_ID 973DROP TABLE t1; 974SET GLOBAL innodb_ft_aux_table=DEFAULT; 975# 976# MDEV-19522 InnoDB commit fails when FTS_DOC_ID value 977# is greater than 4294967295 978# 979CREATE TABLE t1( 980FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 981f1 TEXT, f2 TEXT, PRIMARY KEY (FTS_DOC_ID), 982FULLTEXT KEY (f1)) ENGINE=InnoDB; 983INSERT INTO t1 VALUES (1,'txt','bbb'); 984UPDATE t1 SET FTS_DOC_ID = 4294967298; 985SELECT * FROM t1 WHERE match(f1) against("txt"); 986FTS_DOC_ID f1 f2 9874294967298 txt bbb 988SET @@session.insert_id = 100000000000; 989INSERT INTO t1(f1, f2) VALUES ('aaa', 'bbb'); 990CREATE FULLTEXT INDEX i ON t1 (f2); 991SELECT * FROM t1 WHERE match(f2) against("bbb"); 992FTS_DOC_ID f1 f2 9934294967298 txt bbb 994100000000000 aaa bbb 995DROP TABLE t1; 996