- 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 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.