path: root/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result

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	OK
ANALYZE TABLE t2;
Table	Op	Msg_type	Msg_text
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
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=utf8 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;