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`.`articles`");
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...')  ,
('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 ...');
CREATE FULLTEXT INDEX idx on articles (title, body);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
3	Optimizing MySQL	In this tutorial we will show ...
SELECT COUNT(*) FROM articles
WHERE MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(*)
2
SELECT * FROM articles
WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
3	Optimizing MySQL	In this tutorial we will show ...
SELECT COUNT(IF(MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
AS count FROM articles;
count
2
ANALYZE TABLE articles;
Table	Op	Msg_type	Msg_text
test.articles	analyze	status	Engine-independent statistics collected
test.articles	analyze	Warning	Engine-independent statistics are not collected for column 'body'
test.articles	analyze	status	OK
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
id	title	body
6	MySQL Security	When configured properly, MySQL ...
1	MySQL Tutorial	DBMS stands for DataBase ...
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. ...
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('DBMS Security' IN BOOLEAN MODE);
id	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
6	MySQL Security	When configured properly, MySQL ...
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
id	title	body
5	MySQL vs. YourSQL	In the following database comparison ...
DROP INDEX idx ON articles;
CREATE FULLTEXT INDEX idx on articles (title, body);
CREATE FULLTEXT INDEX idx1 on articles (title);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
3	Optimizing MySQL	In this tutorial we will show ...
DROP INDEX idx ON articles;
DROP INDEX idx1 ON articles;
CREATE FULLTEXT INDEX idx1 on articles (title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
drop table articles;
CREATE TABLE articles (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
create unique index FTS_DOC_ID_INDEX on articles(FTS_DOC_ID);
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...')  ,
('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 ...');
CREATE FULLTEXT INDEX idx on articles (title, body);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
3	Optimizing MySQL	In this tutorial we will show ...
drop table articles;
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...')  ,
('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 ...');
CREATE FULLTEXT INDEX idx on articles (title);
CREATE FULLTEXT INDEX idx2 on articles (body);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Can't find FULLTEXT index matching the column list
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
1	MySQL Tutorial	DBMS stands for DataBase ...
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
3	Optimizing MySQL	In this tutorial we will show ...
drop index idx2 on articles;
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Can't find FULLTEXT index matching the column list
CREATE FULLTEXT INDEX idx2 on articles (body);
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
3	Optimizing MySQL	In this tutorial we will show ...
UPDATE articles set title = 'aaaa'
WHERE MATCH(title) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id	title	body
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('aaaa' IN NATURAL LANGUAGE MODE);
id	title	body
1	aaaa	DBMS stands for DataBase ...
drop table articles;
CREATE TABLE articles (
FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx on articles (title);
INSERT INTO articles  VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');
UPDATE articles set title = 'bbbb'  WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Invalid InnoDB FTS Doc ID
UPDATE articles set title = 'bbbb', FTS_DOC_ID=8  WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Invalid InnoDB FTS Doc ID
UPDATE articles set title = 'bbbb', FTS_DOC_ID=10  WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (title) AGAINST ('bbbb' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID	title	body
10	bbbb	DBMS stands for DataBase ...
SELECT * FROM articles WHERE MATCH (title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID	title	body
CREATE FULLTEXT INDEX idx2 ON articles (body);
SELECT * FROM articles WHERE MATCH (body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID	title	body
10	bbbb	DBMS stands for DataBase ...
UPDATE articles set body = 'bbbb', FTS_DOC_ID=11  WHERE MATCH(body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
drop table articles;
create table `articles`(`a` varchar(2) not null)engine=innodb;
create fulltext index `FTS_DOC_ID_INDEX` on `articles`(`a`);
ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
create unique index `a` on `articles`(`a`);
drop table articles;
CREATE TABLE wp(
FTS_DOC_ID bigint PRIMARY KEY,
title VARCHAR(255) NOT NULL DEFAULT '',
text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
(1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
(2, 'How To Use MySQL Well','After you went through a ...');
CREATE FULLTEXT INDEX idx ON wp(title, text);
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
DROP TABLE wp;
CREATE TABLE wp(
FTS_DOC_ID bigint unsigned PRIMARY KEY,
title VARCHAR(255) NOT NULL DEFAULT '',
text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
(1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
(2, 'How To Use MySQL Well','After you went through a ...');
CREATE FULLTEXT INDEX idx ON wp(title, text);
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database')
FROM wp;
FTS_DOC_ID	MATCH(title, text) AGAINST ('database')
1	0.0906190574169159
2	0
DROP TABLE wp;