MySQL Forums
Forum List  »  Full-Text Search

Cannot update a column if I created FTS_DOC_ID myself
Posted by: Cedric Rochefolle
Date: June 05, 2013 04:22AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Cannot update a column if I created FTS_DOC_ID myself
4362
June 05, 2013 04:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.