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.