MySQL Bugs: #69243: Can't update InnoDB table with fulltext index defined

Bug #69243	Can't update InnoDB table with fulltext index defined
Submitted:	15 May 2013 11:56	Modified:	20 Jun 2013 16:53
Reporter:	Michał Bąkowski	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.6.11	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any

Description:
When fulltext index is defined, I can't update any rows. Of course it concerns only updates of columns indexed by the FT index.

Everytime I try to update single column in the table, I get:
  ERROR 1022: Can't write; duplicate key in table 't_patients'

When I remove FT, it works correctly. After updates I can create FT index and searches work correctly as well. But, can't update records when FT is defined.

How to repeat:
The table looks like this:

CREATE TABLE `t_patients` (
  `P_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `P_U_ID` int(10) unsigned NOT NULL,
  `P_IDENTIFIER_TYPE` enum('pesel','passport') DEFAULT NULL,
  `P_IDENTIFIER` varchar(45) DEFAULT NULL,
  `P_BIRTH_PLACE` varchar(45) DEFAULT NULL,
  `P_NATIONALITY` varchar(45) DEFAULT NULL,
  `P_BIRTH_DATE` date DEFAULT NULL,
  `P_REMARKS` text,
  `P_CARD_NUMBER` varchar(45) DEFAULT NULL,
  `P_PARENT_U_ID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`P_ID`),
  KEY `FK_P_U_ID_idx` (`P_U_ID`),
  KEY `FK_P_PARENT_U_ID_idx` (`P_PARENT_U_ID`),
  FULLTEXT KEY `FT_P_TEXTS` (`P_IDENTIFIER`,`P_BIRTH_PLACE`,`P_NATIONALITY`,`P_REMARKS`,`P_CARD_NUMBER`),
  CONSTRAINT `FK_P_PARENT_U_ID` FOREIGN KEY (`P_PARENT_U_ID`) REFERENCES `t_users` (`U_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_P_U_ID` FOREIGN KEY (`P_U_ID`) REFERENCES `t_users` (`U_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It contains almost 4000 rows. I tried to reproduce the problem on empty table, but it works correctly then... Most of the rows look like this:
  9, 11, NULL, NULL, NULL, NULL, '1964-07-20', NULL, NULL, NULL
i.e. only P_ID, P_U_ID and P_BIRTH_DATE columns are set. The column I tried to update and failed was P_IDENTIFIER.

Thank you for the bug report. We need here a repeatable test case. Would be nice if you provide the dump of tables involved with insert data (private if you wish). Thanks in advance.

I have added dump of t_patients table. Let me know, if you need dump of other tables too.

An example SQL command that fails:

UPDATE `t_patients` SET `P_IDENTIFIER_TYPE`='pesel', `P_IDENTIFIER`='58070942992' WHERE `P_ID`=7;

Interesting. After reboot of the server, now it is working fine.
I'll let you know if the problem repeats.

Thank you for the feedback. Please re-open if you get again the issue and provide the other table as well. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have also been experiencing this issue for some time, as have my clients. It does not appear to be isolated to a particular OS or server environment.

When performing an UPDATE against an InnoDB table that contains a FULLTEXT indexed column, randomly the update will fail with 'Can't write; duplicate key in table' even if the only column being updated is the FULLTEXT column. The issue is not reproducible consistently, because if you wait a moment and retry the same query, it will succeed. To me, this suggests some kind of synchronization problem, even though there are no masters/slaves to sync.

The affected table does not contain FOREIGN KEYs or CONSTRAINTs. The table does have a PRIMARY KEY (used by the UPDATE ... WHERE), and it does have a UNIQUE index. Regarding the unique index, the failing queries that I have seen did not attempt to change these columns to a conflicting unique value that already exists in another row; again, I have seen the issue occur when only the FULLTEXT column is updated.

It is possible that the UPDATE's new value for the FULLTEXT column matches exactly the value for the FULLTEXT column in another row. This is a common occurrence, and is the only "duplicate key" that I can imagine emerging.

MySQL has been restarted multiple times throughout the lifetime of the issue, but it still randomly occurs.

I believe this is still happening.  Searching around on the internet I think this issue is indeed a synchronization issue.  I have customers on 5.6.22 who get it and 5.6.34, but a customer with 5.6.35 does not (can't confirm its because it was fixed, possibly just didn't experience the race condition).

When it occurs, the error logs on the server will contain:
2019-08-14 13:23:19 13329 [ERROR] InnoDB: Duplicate FTS_DOC_ID value on table <databasename>/<tablename>
2019-08-14 13:23:19 13329 [ERROR] Cannot find index FTS_DOC_ID_INDEX in InnoDB index translation table.
2019-08-14 13:23:19 13329 [Warning] Find index FTS_DOC_ID_INDEX in InnoDB index list but not its MySQL index number It could be an InnoDB internal index.

I believe this is related to:
https://bugs.mysql.com/bug.php?id=70311 and/or https://bugs.mysql.com/bug.php?id=78423

I also saw this MariaDB report about the same issue in InnoDB that might be the same issue:
https://jira.mariadb.org/browse/MDEV-15237

Anyway, I think this is still an issue and we shouldn't close it just because the OP was unable to provide deterministic repro steps (since it happens so unpredictably).  I can confirm that for my customers that get it, retrying the query generally succeeds, and the tables its occurring on are not huge (2k-10k rows).  Size does seem to increase likelihood when I review automated error reporting logs I have.

I'd love to know whether this was actually fixed in some version that I could tell my users to update to.