Bug #77087 INNODB FTS: error duplicate entry in FTS_DOC_ID_INDEX ON UPDATE CASCADE
Submitted: 19 May 2015 8:32 Modified: 25 May 2015 12:20
Reporter: Shaohua Wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.8 OS:Any
Assigned to: CPU Architecture:Any

[19 May 2015 8:32] Shaohua Wang 
Description:
query 'update t1 set a = 'database' where a = 'mysql'' failed: 1761: Foreign key constraint for table 't1', record 'database' would lead to a duplicate entry in table 't1_fk', key 'FTS_DOC_ID_INDEX'

How to repeat:
CREATE TABLE t1 (
   a    varchar(40),
   KEY  a(a)
) ENGINE=InnoDB;

CREATE TABLE t1_fk (
        a       varchar(40),
        KEY     a(a),
        FULLTEXT KEY (a),
        CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 (a) ON UPDATE CASCADE
) ENGINE=InnoDB;

insert into t1 values('mysql');

insert into t1_fk values('mysql');
insert into t1_fk values('mysql');

update t1 set a = 'database' where a = 'mysql';

drop table t1_fk;
drop table t1
[19 May 2015 10:45] Shaohua Wang 
Posted by developer:
 
It's a regression of rb#2089: make cascade operation iterative.

trx->fts_next_doc_id is used by two cascade operations, so we have duplicated entry error.

The fix is simple: allocated a doc id from cascade heap for each operation.
[25 May 2015 12:20] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.8, 5.8.0 release, and here's the changelog entry:

A cascade operation resulted in a duplicate entry error in
"FTS_DOC_ID_INDEX". The same document ID was used by two cascade
operations. 

Thank you for the bug report.