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
Triage: Needs Triage: D2 (Serious)

[9 Dec 2011 2:35] David Canos
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
[9 Dec 2011 11:16] Valeriy Kravchuk
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
[9 Dec 2011 14:35] Jimmy Yang
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.
[12 Dec 2011 15:35] Jimmy Yang
This is fixed for the FTS release in 5.6.4
[15 Oct 2012 13:47] Erlend Dahl
Fixed in 5.6.6