Mysql – Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”

full-text-searchindexinnodbMySQLmysql-5.6

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

Best Answer

The first thing I noticed is that the id column is not indexed. For all intents and purposes, your acting PRIMARY KEY is FTS_DOC_ID since it is the only unique key present without a PRIMARY KEY definition.

Since id is not indexed, I could see mysqld wanting to do a full table scan to fulfill the query. You should want to create a UNIQUE KEY on id as well if you intend on having two different unique keys and having two different references to one row.

Note that FTS_DOC_ID has the auto_increment attribute. There is no need to increment it manually, like you did with

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;

In fact, by doing this, you are actually taking the FTS_DOC_ID and changing it on id.

Looking back at your first query

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

Why would this be a problem (and essentially a bug) ? Again, this goes back to id not being indexed.

When a column is not indexed, the Clustered Index of the table (where the PRIMARY KEY resides) is unaware of the presence of id. How is this so?

According to the Book

oxcnd

Chapter 12 Pages 260,261 (Subheading Secondary Indexes) says the following:

What about other indexes? How are they structured, and what is their relationship with the data found in the clustered index?

These additional indexes are know as "secondary indexes," and you can create as many of them as you need for an InnoDB table. Because our sample table schema definition requests an index on the last_name column, InnoDB creates one secondary index on this value.Each entry in the secondary index contains the primary key value from the clustered index,thereby facilitating linkage back to the data.

Your First Query

Since id is not indexed your first update failed. MySQL (eh Oracle) should have caught this problem. So, it is a bug.

Your Second Query

The update works because you accessed the Clustered Index indirectly by trying to manually update the FTS_DOC_ID column.

Your Third Query

It worked because you accessed no indexes at all, neither on id nor on body. In that instance, a full table scan would easily fulfill this request.

Epilogue

If I were you, I would report this as a bug ASAP because MySQL 5.6's FULLTEXT indexing for InnoDB is what people want these days and query plans should be accommodating it with WHERE clauses whether accessing with the Clustered index or not.

Related Solutions

MySQL – ALTER TABLE on Large Table with Indexed Column

If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:

#
#  Script 1
#  Alter table structure of a single column of a large table
#
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:

#
#  Script 2
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#
SET SQL_LOG_BIN = 0;
CREATE TABLE WorkingTableNew LIKE WorkingTable;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:

#
#  Script 3
#  Alter table structure of a single column of a large table
#  while preventing it from replicating to slaves
#  and preventing new data from entering into the old table
#
SET SQL_LOG_BIN = 0;
ALTER TABLE WorkingTable RENAME WorkingTableOld;
CREATE TABLE WorkingTableNew LIKE WorkingTableOld;
ALTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50);
INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld;
ALTER TABLE WorkingTableNew RENAME WorkingTable;
DROP TABLE WorkingTableOld;

Script 1 can be executed on any slave that do not have binary logs enabled
Script 2 can be executed on any slave that does have binary logs enabled
Script 3 can be executed on a master or anywhere else

Give it a Try !!!

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.