--source include/have_innodb.inc

CREATE TABLE t1 (

        f1 INT NOT NULL PRIMARY KEY,

        f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1,'foo'),(2,'bar');

connect(con1,localhost,root,,,);

START TRANSACTION WITH CONSISTENT SNAPSHOT;

connection default;

DELETE FROM t1 WHERE f1 = 2;

ALTER TABLE t1 DROP INDEX ft;

ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);

INSERT INTO t1 VALUES (3, 'innodb fts search');

SET GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE t1;

SET GLOBAL innodb_ft_aux_table = 'test/t1';

SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);

DROP TABLE t1;

disconnect con1;

SET GLOBAL innodb_optimize_fulltext_only=OFF;

SET GLOBAL innodb_ft_aux_table = default;

By inplace algorithm:

SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

max(DOC_ID)

3

By copy algorithm:

SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

max(DOC_ID)

2

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 ...');

--error ER_INNODB_FT_WRONG_DOCID_COLUMN

CREATE FULLTEXT INDEX idx ON wp(title, text);

DROP TABLE wp;

By inplace alorithm:

CREATE FULLTEXT INDEX idx ON wp(title, text);

ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index

By Copy algorithm:

CREATE FULLTEXT INDEX idx ON wp(title, text);

- Succeeds without failing