MySQL Bugs: #63689: Cant update a row without changing a fts-indexed field (InnoDB)

Bug #63689	Cant update a row without changing a fts-indexed field (InnoDB)
Submitted:	9 Dec 2011 2:35	Modified:	15 Oct 2012 13:47
Reporter:	David Canos	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.6.4	OS:	Linux (Ubuntu 11.04)
Assigned to:	Jimmy Yang	CPU Architecture:	Any
Tags:	fts, innodb

Description:
I got an innodb table with 2 fields, one of them has a full-text index.
I got an error (180 Invalid InnoDB FTS Doc ID) while trying to update a row without changing the indexed field.

If I change the indexed field it works properly.

UPDATE foo SET fts_field='anychange' where id = 1;
# works ok

UPDATE foo SET no_fts_field='anychange' where id = 1;
# doesnt work
# 180 Invalid InnoDB FTS Doc ID 

UPDATE foo SET no_fts_field='anychange', fts_field='anychange' where id = 1;
# works ok

How to repeat:
install mysql-5.6.4-labs-innodb-fts-linux2.6-i686.tar.gz
in Ubuntu 11.04

create a simple foo table with two fields.
Create a full-text index in one field.

make some inserts.

try to update the non-index field without changing the indexed-field.

Suggested fix:
no idea

Thank you for the bug report. Verified with current mysql-trunk:

[openxs@chief trunk]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.5-m8-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table foo(id int primary key, no_fts_field varchar(10), fts_field varchar(10), fulltext index f(fts_field)) engine=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into foo values (1, 'aaa', 'bbb');
Query OK, 1 row affected (0.10 sec)

mysql> UPDATE foo SET fts_field='anychange' where id = 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE foo SET no_fts_field='anychange' where id = 1;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
mysql> UPDATE foo SET no_fts_field='anychange', fts_field='other' where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

There is a bogus check on the hidden Doc ID value while this update does not need to update the Doc ID (no FTS indexed column affected). This is fixed.

The go around for now is to do a no-op update on the indexed FTS column so that to it qualifies the Doc ID change requirement.

This is fixed for the FTS release in 5.6.4

Fixed in 5.6.6