MySQL Bugs: #69412: Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

Bug #69412	Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”
Submitted:	6 Jun 2013 4:51	Modified:	14 Jun 2013 15:00
Reporter:	Cedric Rochefolle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	full-text search, INDEX, UPDATE

Description:
MySQL documentation suggests to create the FTS_DOC_ID (with the right syntax) to prevent a full table rebuild.

I did it and all is good when querying using the MATCH...AGAINST to use the FTS index. However when I need to update an indexed column I get an error:

Error code 182, SQL state HY000: Invalid InnoDB FTS Doc ID

If I manually take care of the column FTS_DOC_ID like this:

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

Then the update is done. Also updating another (non-fts indexed) column is fine.

How to repeat:
---------------------- START --------------------------------
DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
  FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id INT NOT NULL ,
  title VARCHAR(200),
  body TEXT,
  UNIQUE KEY (FTS_DOC_ID)
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx on articles (title);
INSERT INTO articles(id,title,body) 
  VALUES (1, 'MySQL Tutorial','DBMS stands for DataBase ...');
UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=1;
---------------------- END --------------------------------

Here is the execution:
mysql> DROP TABLE IF EXISTS articles;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> CREATE TABLE articles (
    ->   FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   id INT NOT NULL ,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   UNIQUE KEY (FTS_DOC_ID)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.56 sec)

mysql> CREATE FULLTEXT INDEX idx on articles (title);
Query OK, 0 rows affected (2.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO articles(id,title,body) 
    ->   VALUES (1, 'MySQL Tutorial','DBMS stands for DataBase ...');
Query OK, 1 row affected (0.11 sec)

mysql> UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=1;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

Suggested fix:
I have been told on forums that I should create a unique index on the 'id' column to have another column to use as index but I don't think it is a fix, more a workaround

Hello Cedric,

Thank you for the report.
Verified as described.

Thanks,
Umesh

I *believe* the MySQL Documentation being referred to in this bug is this blog article: https://blogs.oracle.com/mysqlinnodb/entry/innodb_full_text_search_performance (but I couldn be wrong).

Regardless, an update has been made to the MySQL reference manual for 5.6 and 5.7:
http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html
http://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html

The revised content will appear shortly, with the next doc build.

Thank you for the bug report.

It also fails when the table has a PRIMARY KEY.

CREATE TABLE `fts` (
  `id` varchar(36) NOT NULL,
  `reason` varchar(500) NOT NULL,
  `FTS_DOC_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
  FULLTEXT KEY `reason` (`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

mysql> INSERT IGNORE INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

mysql> INSERT INTO fts (id, reason) VALUES (UUID(), 'unknown');
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

Using 5.6.25