Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you. Bug reported (bugs.mysql.com/bug.php?id=69412). I needed the AUTO_INCREMENT on FTS_DOC_ID because it wouldn't allow me to create a PRIMARY KEY on it (can't recall the exact issue there but found from another post/forum to do that :) ) –  JScoobyCed Jun 6 '13 at 4:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.