Bug #102833 Fulltext index error [ERROR] InnoDB: Duplicate FTS_DOC_ID value on table
Submitted: 5 Mar 18:04 Modified: 5 Mar 20:24
Reporter: Chris Baird Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S1 (Critical)
Version:5.7.33 OS:Ubuntu
Assigned to: CPU Architecture:x86

[5 Mar 18:04] Chris Baird
Description:
Creating a table then adding a fulltext index, insert some values and then add a virtual column causes the next insert to return to the screen:

Error Code: 1022. Can't write; duplicate key in table 'Test'

on the server it returns:
[ERROR] InnoDB: Duplicate FTS_DOC_ID value on table `Test2`.`Test`
[ERROR] Cannot find index FTS_DOC_ID_INDEX in InnoDB index translation table.
[Warning] Found index FTS_DOC_ID_INDEX in InnoDB index list but not its MySQL index number. It could be an InnoDB internal index.

...it seems that when the virtual column d is added the INNODB_FT_INDEX_CACHE is cleared and is never written too INNODB_FT_INDEX_TABLE. Then on the subsequent insert there is duplicate key causes the FTS_DOC_ID errors.

The following code will reproduce the error, if I run optimize table Test before the alter table it moves the Cache into the Table and all is fine.

When I add another record and then yet another VIRTUAL column it happens again.

The only workaround now is to run OPTIMIZE TABLE whenever a new VIRTUAL column is added.

How to repeat:
DROP DATABASE IF EXISTS FTSBug;
CREATE SCHEMA FTSBug DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE FTSBug;

DROP TABLE IF EXISTS `Test`;

CREATE TABLE `Test` (
  f21 varchar(16) GENERATED ALWAYS AS ('') VIRTUAL,
  f22 varchar(16) GENERATED ALWAYS AS ('') VIRTUAL,
  f20 text,
  KEY `IX_Test_r3` (`f22`)
) ENGINE=InnoDB;

INSERT INTO Test (f20) VALUES(null);

CREATE FULLTEXT INDEX IX_DocTract_Test_f20 ON Test(f20);

INSERT INTO Test (f20) VALUES('one');
INSERT INTO Test (f20) VALUES('two');

ALTER TABLE `Test` ADD COLUMN d varchar(128) GENERATED ALWAYS AS ('hi') VIRTUAL NULL;

INSERT INTO Test (f20) VALUES('three');

Suggested fix:
Don't clear the INNODB_FT_INDEX_CACHE table when adding a VIRTUAL column.

If I add a column that is STORED vs VIRTUAL then all works fine and it seems that MYSQL moves the INNODB_FT_INDEX_TABLE entries into the INNODB_FT_INDEX_CACHE so all works fine.
[5 Mar 20:24] MySQL Verification Team
Thank you for the bug report.