Ubuntu Xenial

Also, if the problem was a duplicate key, shouldn't the error message also display the problematic ID ?

Updated to 10.3.6, issue still present

johnkiller, I wonder if the duplicate could be reported for the hidden FTS_DOC_ID column? If you rebuild the table without any FULLTEXT INDEX, will you still get the errors? There was a related bug fix in MDEV-12676, but that fix is present in 10.3.6.

Note that the hidden FTS_DOC_ID nor the hidden unique FTS_DOC_ID_INDEX(FTS_DOC_ID) will not be dropped when you drop the fulltext indexes. You must rebuild the table, for example by specifying the FORCE keyword in the ALTER TABLE statement.

I'll try to disable fulltext and rebuild the tables, then report back if it happens again.
As I said, this is not always reproducible, so it may take a couple of days before it happens.
Also, I have set "ft_min_word_len = 3" in my.cnf, if that could help.
Thanks

Hi, after 4 days it seems that the error disappeared. What can I do now? Thanks

johnkiller, do you mean that the bug stopped repeating even before you dropped the fulltext index?

I do not think that duplicate key errors should be related to any FOREIGN KEY constraints, with the exception of ON UPDATE CASCADE that causes an update of a unique index or the primary key in a child table.

My best guess is that the error is somehow related to fulltext index, but because it is only a guess, I would want to verify it. InnoDB fulltext indexes are internally implemented in hidden tables. In most of these tables, the unique index (primary key) is on the FTS_DOC_ID column. The main table is indexed by (word,first_doc_id) where first_doc_id is a FTS_DOC_ID. The only exception are the CONFIG tables, where I believe the key to be one of the few hard-coded strings defined in include/fts0priv.h. So, if the problem is in the fulltext index, it should likely be either due to the FTS_DOC_ID or due to a bug in the management of the inverted index.

I mean that after doing what you requested (disabling fulltext & rebuild) has stopped producing the error. I'm not 100% sure because the error is random. Thanks

johnkiller, thank you for clarifying. I believe that the InnoDB fulltext index code is the most likely culprit. Now the only remaining problem is that we need to reproduce the issue.

Updated to 10.3.7 and the issue is still present. Issuing a SHOW WARNINGS produces this:

stdClass Object
(
[Level] => Error
[Code] => 1022
[Message] => Can't write; duplicate key in table 'subject'
)

Repeating the query then works as expected. Is there a way to enable more logging so I can help better? For example, is there a way to see the exact index that is failing, even if it's a system column?

I've tried to run a loop of inserts and updates but I can't reliably reproduce this issue

I got this error once when running ./mtr --parallel=auto --force --big-test --max-test-fail=0:

CURRENT_TEST: innodb_fts.fulltext2

mysqltest: At line 225: query 'update t1 set a='ab c d'' failed: 1022: Can't write; duplicate key in table 't1'

I tried to reproduce the problem based on innodb_fts.fulltext2 (see Marko's comment) with some brute force test.
But none of the setup combinations

• 10.2 or 10.3 (both compiled with debug)
• overloaded CPU or overloaded CPU+HDD
  replayed the problem.

custom.cnf This is my configuration. Some settings (like ft_min_word_len) affects the fulltext search, maybe this helps.

I am now giving up with the brute force tests based on innodb_fts.fulltext2. Even using most of the settings in custom.cnf did not replay.

Is there a way for me to get more debug info? I continue to get this error at least twice a day on 2 different servers, both in production.

Hi Gianni Angelozzi,

I assume that there is some problem with FOREIGN KEYs.
The duplicate key error message thrown because of PRIMARY KEY or
UNIQUE INDEX look different. In such cases the corresponding key
would be mentioned in the error message.
I am just experimenting in order to see how the messages for
violations of FOREIGN KEY relationship look like.

Regards
Matthias

Some news:

• No replay with some RQG test working on a table with similar layout like mentioned on top, 100000 rows and 12 concurrent
  sessions updating rows like mentioned on top in transactions. Some statements failed because of error 1213 (deadlock)
  or 1205 (Lock wait timeout). All other passed. (Debug build and also ASAN build)
• My tests with MTR, InnoDB table, sometimes forreign keys, unique indices, PRIMARY KEYs gave all time error messages
  mentioning the constraint/foreign key/... violated. (10.2 used). There was never a ER_DUP_ENTRY error message.
• Some search over our MTR tests showed a crowd of tests where statements are expected to harvest ER_DUP_ENTRY
  or ER_DUP_KEY. The reason seems to be that older software versions gave often the less detailed ER_DUP_KEY message and now the better ER_DUP_ENTRY.
  Aside of tests using partitioned tables/engines like federated, merge, blackhole which seem to be mostly irrelevant
  suite/perfschema/t/dml_setup_objects.test looked quite interesting.
  Some table is using the ENGINE=PERFORMANCE_SCHEMA which needs to invoke whatever (I assume its some builtin
  storage engine extended by additional properties) storage engine. One attempt to insert some record leads to ER_DUP_KEY.
  Also some other statements report violations of FOREIGN KEY. Both things happen even though the table definition does not
  mention any unique constraints or foreign keys. So it looks as if we could have hidden structures (not presented in SHOW TABLE) acting as unique constraint. And in case that gets violated than ER_DUP_KEY and not the better ER_DUP_ENTRY gets reported.
• One thing which springs into the eye when looking on the table definition is
  CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL,
  ...
  `user` varchar(250) NOT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`deleted`),
  FULLTEXT KEY `user`
  ...
  the heavy reuse of the same name for different objects like table, full text index, index etc.
  And I found in the Web several reports about hitting ER_DUP_KEY mostly when trying to alter the table definition.
  The workaround which usually finally helped was to make names unique per database.
  It would be interesting to know if making the name of fulltext and normal indexes unique could somehow improve the situation.

Hi Gianni Angelozzi,

you mention 'The table is referenced on other tables, but the error should occur on creation of them,
not by writing rows.'. You are of course right with this statement and in addition the error message should
tell in which object (foreign key/unique index/primary key) the duplicate happened.

Would you please upload the definition of these tables?
In case there are triggers involved than some information about them would be of interest too.

There are some other ways to get more debug info but they have usually very unfortunate properties
(extreme slow throughput and similar) at runtime.

Many thanks in advance

Matthias

CREATE TABLE `user` (

  `id` int(11) UNSIGNED NOT NULL,

  `revision_date` int(14) NOT NULL,

  `revision_author` int(11) UNSIGNED NOT NULL,

  `creation_date` int(14) NOT NULL,

  `creation_author` int(11) UNSIGNED NOT NULL,

  `deleted` tinyint(1) NOT NULL,

  `user` varchar(250) NOT NULL,

  `password` varchar(255) NOT NULL,

  `name` varchar(250) NOT NULL,

  `surname` varchar(250) NOT NULL,

  `lastlogin` int(14) NOT NULL,

  `lastip` varchar(32) NOT NULL,

  `lastpsw` int(14) NOT NULL,

  `email` varchar(60) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_by_b2bmaster_to_b2b_master` (

  `user_id` int(11) UNSIGNED NOT NULL,

  `b2b_master` int(11) UNSIGNED NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_by_b2bstore_to_b2b_store` (

  `user_id` int(11) UNSIGNED NOT NULL,

  `b2b_store` int(11) UNSIGNED NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_by_group_to_user_group` (

  `user_id` int(11) UNSIGNED NOT NULL,

  `user_group` int(11) UNSIGNED NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_group` (

  `id` int(11) UNSIGNED NOT NULL,

  `revision_date` int(14) NOT NULL,

  `revision_author` int(11) UNSIGNED NOT NULL,

  `creation_date` int(14) NOT NULL,

  `creation_author` int(11) UNSIGNED NOT NULL,

  `deleted` tinyint(1) NOT NULL,

  `name` varchar(250) NOT NULL,

  `b2bmaster` tinyint(1) NOT NULL,

  `b2bstore` tinyint(1) NOT NULL,

  `special` tinyint(1) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_group_by_permission_to_user_group_permission` (

  `user_group_id` int(11) UNSIGNED NOT NULL,

  `user_group_permission` int(11) UNSIGNED NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_group_permission` (

  `id` int(11) UNSIGNED NOT NULL,

  `revision_date` int(14) NOT NULL,

  `revision_author` int(11) UNSIGNED NOT NULL,

  `creation_date` int(14) NOT NULL,

  `creation_author` int(11) UNSIGNED NOT NULL,

  `name` varchar(250) NOT NULL,

  `description` varchar(250) NOT NULL,

  `group` varchar(250) NOT NULL,

  `groupname` varchar(250) NOT NULL,

  `folder` varchar(250) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user`

  ADD PRIMARY KEY (`id`),

  ADD KEY `id` (`id`,`deleted`),

  ADD KEY `deleted` (`deleted`);

ALTER TABLE `user` ADD FULLTEXT KEY `ft_user` (`user`,`name`,`surname`);

ALTER TABLE `user_by_b2bmaster_to_b2b_master`

  ADD PRIMARY KEY (`user_id`,`b2b_master`),

  ADD KEY `user_id` (`user_id`),

  ADD KEY `b2b_master` (`b2b_master`);

ALTER TABLE `user_by_b2bstore_to_b2b_store`

  ADD PRIMARY KEY (`user_id`,`b2b_store`),

  ADD KEY `user_id` (`user_id`),

  ADD KEY `b2b_store` (`b2b_store`);

ALTER TABLE `user_by_group_to_user_group`

  ADD PRIMARY KEY (`user_id`,`user_group`),

  ADD KEY `user_id` (`user_id`),

  ADD KEY `user_group` (`user_group`);

ALTER TABLE `user_group`

  ADD PRIMARY KEY (`id`),

  ADD KEY `id` (`id`,`deleted`),

  ADD KEY `deleted` (`deleted`);

ALTER TABLE `user_group` ADD FULLTEXT KEY `ft_user_group` (`name`);

ALTER TABLE `user_group_by_permission_to_user_group_permission`

  ADD PRIMARY KEY (`user_group_id`,`user_group_permission`),

  ADD KEY `user_group_id` (`user_group_id`),

  ADD KEY `user_group_permission` (`user_group_permission`);

ALTER TABLE `user_group_permission`

  ADD PRIMARY KEY (`id`);

ALTER TABLE `user_group_permission` ADD FULLTEXT KEY `ft_user_group_permission` (`name`,`description`);

ALTER TABLE `user_by_b2bmaster_to_b2b_master`

  ADD CONSTRAINT `f1_user_by_b2bmaster_to_b2b_master` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `f2_user_by_b2bmaster_to_b2b_master` FOREIGN KEY (`b2b_master`) REFERENCES `b2b_master` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `user_by_b2bstore_to_b2b_store`

  ADD CONSTRAINT `f1_user_by_b2bstore_to_b2b_store` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `f2_user_by_b2bstore_to_b2b_store` FOREIGN KEY (`b2b_store`) REFERENCES `b2b_store` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `user_by_group_to_user_group`

  ADD CONSTRAINT `f1_user_by_group_to_user_group` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `f2_user_by_group_to_user_group` FOREIGN KEY (`user_group`) REFERENCES `user_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `user_group_by_permission_to_user_group_permission`

  ADD CONSTRAINT `f1_user_group_by_permission_to_user_group_permission` FOREIGN KEY (`user_group_id`) REFERENCES `user_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT `f2_user_group_by_permission_to_user_group_permission` FOREIGN KEY (`user_group_permission`) REFERENCES `user_group_permission` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I have many tables but they are very similar. There are no triggers, no views, nothing fancy. However almost all of them have a FULLTEXT index, possibily with multiple columns.

About the performance issue, I have a server that I don't care if it's slow, so if you can tell me how to enable some more debugging I can try it there.

Thanks

Many thanks for the table definitions sent.
I am discussing with Marko if we could generate some server binary giving more debugging
information on which object the duplicate was hit.

Hi Gianni Angelozzi,

would you please
1. Apply the uploaded patch to some fresh cloned 10.2 source and compile a binary with debug.
2. Add --plugin-load=sql_errlog.so to the [mysqld] section in your my.cnf file
or just start the server with "--plugin-load=sql_errlog.so"
(https://mariadb.com/kb/en/library/sql-error-log-plugin/)
This will collect errors sent to clients in a log file 'sql_errors.log' located within the server data directory.
The entries there in combination with the additional entries (caused by the patch) in the server error log
might help to identify in which operation of the workflow of executing a
INSERT or UPDATE in your tables 'users' or 'subject' ....
the trouble occurs.
3. Run your application on a testing box till the problem showed a few times up.
My hope is that we either get some

• more or less cryptic entry written by InnoDB which could be used for searching within the InnoDB internal data dictionary
  or
• [ERROR] get_dup_key: nnn which maybe than points to some temporary shortage of free resources.

What the patch does:

• The error messages sent by the server to the client will be not changed.
• In case the InnoDB storage engine hits a duplicate key on whatever object than it will most probably write a message based on its internal 'knowledge'.
  Examples:
• [ERROR] err_index (0) for uidx:test/city
  Client gets ERROR 23000: Duplicate entry 'Castelo de Vide' for key 'uidx' ...
• [ERROR] err_index (0) for PRIMARY:mysqld.1/#sql2f87_15_2
  Client gets ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
  The table was defined CREATE TEMPORARY TABLE t1 (..., PRIMARY KEY ...)
  which explains the strange schema and table name reported by InnoDB.
• [ERROR] err_index (0) for PRIMARY:test/t1#P#pNeg#SP#subp1
  The table test . t1 is partitioned ...
  into the server error log.
• In case the duplicate key does not happen within InnoDB than some routine in the upper layer will often
  but not all time write messages like
  [ERROR] get_dup_key: nnn.
  into the server error log.
  Example:
  [ERROR] get_dup_key: 135
  Client gets ERROR HY000: The table '... var/tmp/mysqld.1/#sql_1568_0' is full
  This means some internal temporary table (usually MyISAM/Aria storage engine)
  with limited size (server system variables tmp_disk_table_size,
  tmp_memory_table_size) and used internally for complex queries was full.
• The server error log might grow fast.
• In case you let our regression tests run then MTR will report a batch of fails like
  Found warnings/errors in server log file!
  Test ended at 2018-06-21 13:48:30
  line
  2018-06-21 13:48:29 140547810891520 [ERROR] err_index (0) for a:test/t1
  ^ Found warnings in .../var/1/log/mysqld.1.err
  ok
  but that is to be expected with the patch and harmless.

Many thanks in advance.

Hi,
I've successfully compiled the binary with your patch and I'm now waiting for the error to appear. Let's hope it's not an Heisenbug

Hi,
After some time running in debug mode, the error didn't come up, so I switched to a normal build with your patch applied.
This popped up in /var/log/syslog:

Jul 10 15:04:46 logico_ac mysqld[28840]: 2018-07-10 15:04:46 140332408940288 [ERROR] InnoDB: Duplicate FTS_DOC_ID value on table `logico_ac`.`anl_method`

Jul 10 15:04:46 logico_ac mysqld[28840]: 2018-07-10 15:04:46 140332408940288 [ERROR] Cannot find index FTS_DOC_ID_INDEX in InnoDB index translation table.

Jul 10 15:04:46 logico_ac mysqld[28840]: 2018-07-10 15:04:46 140332408940288 [Warning] Found index FTS_DOC_ID_INDEX in InnoDB index list but not its MariaDB index number. It could be an InnoDB internal index.

Jul 10 15:04:46 logico_ac mysqld[28840]: 2018-07-10 15:04:46 140332408940288 [ERROR] err_index (-1) for FTS_DOC_ID_INDEX:logico_ac/anl_method

This was in /var/lib/mysql/sql_errors.log:

2018-07-10 15:04:46 root[root] @ localhost [] ERROR 1022: Can't write; duplicate key in table 'anl_method' : INSERT INTO `anl_method` SET `id` = 1055, `revision_id` = 1, `revision_date` = 1531235086, `revision_author` = 7, `creation_date` = 1531235086, `creation_author` = 7, `tmp` = "", `code` = "EXT", `name` = "Metodi di Analisi per i Fertilizzanti - met. X.2", `official` = "", `date_start` = "1530403200", `date_end` = "0", `notes` = ""

Do you need anything else?

Hi,
many thanks for your input. Would you please keep the files with the snippets above
and if possible a raw backup (the files when the server is not running) of the database
at some safe place.
So it seems now more clear that the sporadic trouble comes from the fulltext indexes.
Now the InnoDB developers need to discuss these results and how to proceed.
Based on your experiences (... a retry of the same insert or update usually passes) and
my latest experiences with RQG based tests (certain bad effects do not show up except the
testing box is under under excessive load) I fear we are in the region of a Heisenbug.

Hi,
I'm keeping an eye on syslog, and I've noticed that it's now cluttered with

Jul 26 14:09:51 logico_ac mysqld[1137]: 2018-07-26 14:09:51 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:12:35 logico_ac mysqld[1137]: 2018-07-26 14:12:35 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:13:28 logico_ac mysqld[1137]: 2018-07-26 14:13:28 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:17:02 logico_ac mysqld[1137]: 2018-07-26 14:17:02 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:17:20 logico_ac mysqld[1137]: 2018-07-26 14:17:20 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:22:16 logico_ac mysqld[1137]: 2018-07-26 14:22:16 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:22:30 logico_ac mysqld[1137]: 2018-07-26 14:22:30 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:22:30 logico_ac mysqld[1137]: 2018-07-26 14:22:30 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:23:11 logico_ac mysqld[1137]: 2018-07-26 14:23:11 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:24:28 logico_ac mysqld[1137]: 2018-07-26 14:24:28 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:24:33 logico_ac mysqld[1137]: 2018-07-26 14:24:33 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:25:50 logico_ac mysqld[1137]: 2018-07-26 14:25:50 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:27:48 logico_ac mysqld[1137]: 2018-07-26 14:27:48 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:28:05 logico_ac mysqld[1137]: 2018-07-26 14:28:05 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:28:10 logico_ac mysqld[1137]: 2018-07-26 14:28:10 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:28:23 logico_ac mysqld[1137]: 2018-07-26 14:28:23 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:28:30 logico_ac mysqld[1137]: 2018-07-26 14:28:30 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:28:42 logico_ac mysqld[1137]: 2018-07-26 14:28:42 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:28:48 logico_ac mysqld[1137]: 2018-07-26 14:28:48 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:29:57 logico_ac mysqld[1137]: 2018-07-26 14:29:57 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:30:05 logico_ac mysqld[1137]: 2018-07-26 14:30:05 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:30:46 logico_ac mysqld[1137]: 2018-07-26 14:30:46 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:30:50 logico_ac mysqld[1137]: 2018-07-26 14:30:50 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:31:34 logico_ac mysqld[1137]: 2018-07-26 14:31:34 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:32:16 logico_ac mysqld[1137]: 2018-07-26 14:32:16 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:32:31 logico_ac mysqld[1137]: 2018-07-26 14:32:31 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:33:33 logico_ac mysqld[1137]: 2018-07-26 14:33:33 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:34:01 logico_ac mysqld[1137]: 2018-07-26 14:34:01 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:34:16 logico_ac mysqld[1137]: 2018-07-26 14:34:16 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Jul 26 14:34:26 logico_ac mysqld[1137]: 2018-07-26 14:34:26 139747516819200 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

If I issue

ALTER TABLE `user` ENGINE=InnoDB;

should the error go away?

Yes, because ALTER TABLE `user` ENGINE=InnoDB will rebuild all storage structures belonging to that table.

I came from ticket MDEV-12676
I have this line in /var/log/mariadb.log few hundred times. (MariaDB-server-10.1.37-1.el7.centos.x86_64)

2018-12-04 13:28:27 7f8413ffb700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

Since there is no name of the table which is responsible for this error, I'm a bit baffled. There are many databases and tables. How I can find the right one?

Hi Karl, if you have a FULLTEXT search index on a INNODB table, I'm afraid that this is the same bug I've encountered here.

I've since stopped using FULLTEXT search since the erros breaks Galera replication, so I don't know if in recent versions of MariaDB this error is still present or not.

The table that has the error is an internal index of InnoDB, it's not your fault or something that you can fix by yourself. Thanks.

Just a quick update.
I've tested 10.4.11 and the bug is still present.
Also since my production database has grown in size and traffic (about 20gb of data, 3 galera cluster nodes) the error appears more frequently (and breaks replication)

Folks, it's happening for Ubuntu Trusty (a little old, yeah) when running the MariaDB Server 10.2.18-MariaDB-10.2.18+maria~trusty-log.

2020-04-12 11:16:37 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 17:11:58 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 17:15:08 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 17:15:36 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 17:15:57 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 22:57:08 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 22:57:27 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-12 22:57:39 140382549571328 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Bianchi

Folks, it's happening also on CentOS Linux release 7.7.1908 (Core) running the 10.1.42-MariaDB for Linux on x86_64.

2020-04-16 13:20:11 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:12 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:13 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:13 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

2020-04-16 13:20:13 7ee682bfa700  InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

CentOS Linux 8.2.2004 / Server-Version: 10.3.17-MariaDB-log
Afer two years, every few minutes I have still this errors in my logfile /var/log/mariadb.log

2020-11-06 12:40:56 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-11-06 12:43:27 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-11-06 12:45:43 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

2020-11-06 12:47:15 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

Hi,
sorry for the long time without response.
Would it be possible to send me a complete logical dump of your database and some sample SQL's?
Please feel free to scramble sensitive data in strings as needed
Without some sample data and sample SQL's the chances to replay these problems are extreme low.

Many thanks in advance

Regards
Matthias

We are seeing this issue on Ubuntu 18.04.5 LTS LXC container with latest MariaDB 10.3.28 version. I'll check to see if we can share the information required for the investigation.

jrioux if there data and configuration is shareable and not exceptionally large - an upload would be appreciated https://mariadb.com/kb/en/meta/mariadb-ftp-server/

Otherwise the same information mleich has requested from other users.

This might share a root cause with MDEV-24608.

The fts_commit() call at transaction commit time is modifying persistent data related to fulltext indexes. If XA PREPARE is being used, either explicitly by the application, or implicitly by the use of the binlog (which internally generates distributed transactions of the form MySQLXid), problems could arise.

It seems that MDEV-25581 would convert the error message to a server crash, as reported in MDEV-29335.

johnkiller or jrioux or anyone who can reproduce this error, we would greatly appreciate something that would allow us to reproduce and fix this bug.

We unfortunately had to move to another engine because of this issue. I cannot reproduce it anymore.

There now is a test case in MDEV-29342.

It is possible that this bug was fixed in MDEV-29342. Given that this bug has been open so long, with relatively few votes and watchers, I think that we must wait several months for any feedback.

An unscheduled release of MariaDB Server 10.6.10, 10.7.6, 10.8.5, 10.9.3 is expected to be available soon. For older major versions, releases including the fix of MDEV-29342 should become available in the next scheduled quarterly release (October or November 2022).