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.
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.