Cannot update a column if I created FTS_DOC_ID myself

[Cedric Rochefolle]

Environment:

- Ubuntu 12.04 (and 13.04)
- MySQL 5.6.11

I have a table which has a full text index on it (real table has much more columns and rows):

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 (9, 'MySQL Tutorial','DBMS stands for DataBase ...');

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

So far all is good and I can query using the `MATCH...AGAINST` to use the FTS index. However when I need to update an indexed column:

UPDATE articles set title = 'New MySQL Tutorial' WHERE id=9;

I get a:

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

If I manually take care of this column 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. But this is not acceptable because I have several processes in parallel that update this table (though all different rows) and the risk is to get the same `ftsid` in different processes.
Note that updating the `body` column which is not indexed by the FTS index doesn't have this behaviour. I.e.:

UPDATE articles set body = 'Info: DBMS stands for DataBase ...' WHERE id=9;

successfully update the database.

Is it the expected behaviour? Or is it a bug?

I found a bug (http://bugs.mysql.com/bug.php?id=63689) reported in the MySQL buglist about the opposite case (cannot update a non-fts indexed column but can on an fts indexed one) but not this case.



Edited 1 time(s). Last edit at 06/05/2013 04:23AM by Cedric Rochefolle.