Description:
This seems to be a longstanding issue in which MySQL error 1022 "Can't write; duplicate key on table '<tablename>'" is reported when the user is doing an INSERT or UPDATE into an InnoDB table that has a fulltext index. The issue is rare and immediately retrying the query will generally succeed, implying a synchronization issue.
Error 1022 is generally thrown for DDL queries that ALTER/CREATE with a foreign key name that is not unique. However, in these circumstances, they are thrown in response to a DML query (INSERT), which should be impossible. My theory is that it has to do with FKs on internal/hidden FTS tables.
I believe this is the same as the previous report: https://bugs.mysql.com/bug.php?id=69243 which I commented on, but looks dead due to the OP not being able to provide deterministically reproducible steps to a non-deterministic issue.
When the client receives the 1022 error, the following is logged to the server error logs:
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
How to repeat:
This issue is somewhat non-deterministic, so I can't provide exact steps, but I have an automated error reporting database that logs what users/versions/tables have gotten this error and it has only ever occurred for 2 of my tables, both of which are InnoDB, both of which have a fulltext index on multiple fields, across the versions 5.6.22 and 5.6.34. I have a client on 5.6.35 which has never reported it, but if the number of cores or I/O speeds affect the issue, it may not be because it has been fixed.
Following is the schema of one of the tables in question and an example query that caused the issue. NOTE: the query does not necessarily throw the error every time. Research I've done on the subject seems to suggest it is 'bursty' and can sometimes occur a couple times a day to a client inserting ~100 rows/day, and for other users it would be more like once a week, doing ~500 rows/day.
DDL:
CREATE TABLE `vendor` (
`vendorid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`dunsnumber` int(10) unsigned DEFAULT NULL COMMENT 'the vendors DUNS number',
`company` varchar(50) NOT NULL DEFAULT '',
`name` varchar(50) NOT NULL DEFAULT '',
`street` varchar(100) NOT NULL DEFAULT '' COMMENT 'Main street address (line 1 and 2)',
`mailing` varchar(100) NOT NULL DEFAULT '' COMMENT 'Main mailing address (if different than street)',
`city` varchar(50) NOT NULL DEFAULT '',
`state` varchar(25) NOT NULL DEFAULT '',
`zip` varchar(15) NOT NULL DEFAULT '',
`country` varchar(50) NOT NULL DEFAULT '',
`lastvalidated` timestamp NULL DEFAULT NULL COMMENT 'Last time the billing address was successfully validated',
`phone` varchar(20) NOT NULL,
`fax` varchar(20) NOT NULL DEFAULT '',
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT 'An optional mobile # for contact',
`email` varchar(100) NOT NULL DEFAULT '',
`web` text,
`comments` text,
`type` varchar(50) NOT NULL,
`dateentered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`active` enum('False','True') NOT NULL DEFAULT 'True',
`taxitemid` int(10) unsigned DEFAULT NULL,
`defaulttermid` int(10) unsigned DEFAULT NULL COMMENT 'The vendor''s default AP terms',
`defaultpaymentmethodid` int(10) unsigned DEFAULT NULL COMMENT 'The vendor''s default AP payment method',
`code` varchar(8) NOT NULL DEFAULT '',
`minimumorderprice` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Must order at least this amount',
`minimumorderweight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Must order this weight',
`minimumorderquantity` decimal(15,6) NOT NULL DEFAULT '0.000000' COMMENT 'Must order this many',
`minimumprepaidprice` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'This must be passed in order to get free freight',
`minimumprepaidweight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'This must be passed to get free freight',
`minimumprepaidquantity` decimal(15,6) NOT NULL DEFAULT '0.000000' COMMENT 'This must be passed to get free freight',
`accountnumber` varchar(25) NOT NULL,
`priceclass` varchar(25) NOT NULL DEFAULT '',
`purchaseclass` varchar(25) NOT NULL DEFAULT '',
`glcategoryid` int(10) unsigned NOT NULL,
`stockvendor` enum('False','True') NOT NULL DEFAULT 'True' COMMENT 'Whether this vendor should be considered in reordering',
`orderinggroup` varchar(30) NOT NULL COMMENT 'An optional group used in reordering',
`defaultedocumentid` int(11) DEFAULT NULL COMMENT 'The vendors default edocument transmission method',
PRIMARY KEY (`vendorid`),
KEY `vendorcode` (`code`),
FULLTEXT KEY `company_name_fulltext` (`phone`,`company`,`name`)
) ENGINE=InnoDB;
Example DML:
INSERT INTO `vendor` SET `dunsnumber` = 0, `phone` = '<REDACTED>', `company` = '<REDACTED>', `name` = 'PARTS', `street` = '<REDACTED>', `city` = '<REDACTED>', `state` = 'XX', `zip` = '<REDACTED>', `country` = 'USA', `fax` = '', `email` = '', `web` = '', `comments` = '<REDACTED>', `type` = '', `active` = 'True', `mailing` = '', `taxitemid` = NULL, `code` = '', `minimumorderprice` = 0.00, `minimumorderweight` = 0.00, `accountnumber` = '', `priceclass` = '<REDACTED>', `purchaseclass` = '', `glcategoryid` = 4, minimumorderquantity = 0.000000, minimumprepaidprice = 0.00, minimumprepaidweight = 0.00, minimumprepaidquantity = 0.000000, stockvendor = 'False', orderinggroup = '', defaulttermid = NULL, defaultpaymentmethodid = NULL, `dateentered` = NOW();
Suggested fix:
I *could* add special corner-case management to my application to catch mysql error 1022 on the screens where I INSERT/UPDATE into InnoDB tables where a FTS index exists, but that sucks. I think there should be 2 fixes here:
1. The underlying race/synchronization issue with the internal FTS keys should be resolved.
2. If this issue DOES occur in the future, the DDL error for foreign keys (1022) should not be thrown. This makes people who get this error have a hard time to search the issue because they will see tons of people describing how to correctly do a CREATE TABLE statement with FKs, even though they're doing a DML query like INSERT/UPDATE. Maybe this means a new error # should be created if the underlying issue could not be resolved.
Potential other fix:
Since retrying the query IMMEDIATELY following seems to work, an internal MySQL module could catch this particular issue (DDL error 1022 for DML query) and then just auto-retry the query.
I know synchronization/race conditions are tough to track down, especially when it occurs more often on some hardware than others. If it helps, the user that has reported 80% of automated error reports is running on AWS (but the other 20% are on-prem MySQL installs).
