Innodb error duplicate key writing word node to fts auxiliary index table

This error appears in your error log. It means there is a duplicate key in your full text index, sadly it doesn’t tell you which table or which full text index.

HINT for MySQL/MariaDB/Percona devs: just add the index_id to the error message.

SQL to find all text indexes in your db:

select table_schema,table_name,index_name, index_type from information_schema.statistics
where index_type = 'FULLTEXT' order by table_schema;

+-----------------+-----------------------+-------------+------------+
| table_schema    | table_name            | index_name  | index_type |
+-----------------+-----------------------+-------------+------------+
| db0001_fullname | Activity_fullname_log | message     | FULLTEXT   |
| db0002_fullname | Activity_fullname_log | message     | FULLTEXT   |
+-----------------+-----------------------+-------------+------------+
2 rows in set (0.715 sec)

So what is the next step?

If you follow the documentation it will tell you to rebuild the table or use OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON to rebuild just the text index.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-rebuild-innodb-indexes

https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-optimize

My recommendation: Rebuild the table (including the full text indexes)

-- Plain old ALTER TABLE
use db0001_fullname;
ALTER ONLINE TABLE Activity_fullname_log ENGINE=INNODB;

-- Or pt-osc equivalent

pt-online-schema-change --dry-run --alter="ENGINE=INNODB" 
--alter-foreign-keys-method=rebuild_constraints --max-lag=60 
--max-load Threads_running=50 --critical-load Threads_running=60 
h=localhost,D=db0001_fullname,t=Activity_fullname_log

pt-online-schema-change --execute --alter="ENGINE=INNODB" 
--alter-foreign-keys-method=rebuild_constraints --max-lag=60 
--max-load Threads_running=50 --critical-load Threads_running=60 
h=localhost,D=db0001_fullname,t=Activity_fullname_log

The OPTIMIZE TABLE method:

The trouble with this method is it will only do a set number of words per execution of OPTIMIZE TABLE.

SQL to set the specific table so you can query INNODB_FT_INDEX_TABLE

set global innodb_ft_aux_table = 'db0001_fullname/Activity_fullname_log';
Query OK, 0 rows affected (0.002 sec)

SQL to find the number of distinct words in a specific text index:

SELECT count(distinct word) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+----------------------+
| count(distinct word) |
+----------------------+
|               600000 |
+----------------------+
1 row in set (1 min 53.333 sec)

Depending how much impact you want per OPTIMIZE TABLE set innodb_ft_num_word_optimize appropriately.

The remaining steps to rebuilding your full text index using OPTIMIZE TABLE

set GLOBAL innodb_ft_num_word_optimize=10000;
set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE Activity_fullname_log; 
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
... -- repeat round(distinct words/innodb_ft_num_word_optimize)+1
OPTIMIZE TABLE Activity_fullname_log;

Until next time…

DBA Dojo

A place on the way of the DBA «Tao DBA» Oracle, MySQL databases and more on EC2.

Fixing Error: InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

This error appears in your error log. It means there is a duplicate key in your full text index, sadly it doesn’t tell you which table or which full text index.

HINT for MySQL/MariaDB/Percona devs: just add the index_id to the error message.

SQL to find all text indexes in your db:

So what is the next step?

If you follow the documentation it will tell you to rebuild the table or use OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON to rebuild just the text index.

My recommendation: Rebuild the table (including the full text indexes)

The OPTIMIZE TABLE method:

The trouble with this method is it will only do a set number of words per execution of OPTIMIZE TABLE.

SQL to set the specific table so you can query INNODB_FT_INDEX_TABLE

SQL to find the number of distinct words in a specific text index:

Depending how much impact you want per OPTIMIZE TABLE set innodb_ft_num_word_optimize appropriately.

The remaining steps to rebuilding your full text index using OPTIMIZE TABLE

Until next time…

Rate this:

Like this:

One thought on “ Fixing Error: InnoDB: (Duplicate key) writing word node to FTS auxiliary index table. ”

There is an open MySQL bug about this:
https://bugs.mysql.com/bug.php?id=78423 (filed in 2015)
And a MariaDB bug that was fixed by me in 2017:

commit 9a791c9c8d75116d3d15bebb4df198b122b98f97
Author: Marko Mäkelä
Date: Mon Oct 16 13:21:11 2017 +0300

MDEV-12676 MySQL#78423 InnoDB FTS duplicate key error

fts_get_next_doc_id(): Assign the first and subsequent FTS_DOC_ID
in the same way: by post-incrementing the cached value.
If there is a user-specified FTS_DOC_ID, do not touch the internal
sequence.

The FULLTEXT INDEX design and implementation in InnoDB is a mess. I was of that opinion when it was “finished” while I was part of the InnoDB group at Oracle. Nobody really reviewed the design or implementation, and it shows. Most of https://mariadb.com/resources/blog/initial-impressions-of-innodb-fulltext/ still holds today.

At MariaDB, we have been trying to fix bugs also in the area of fulltext indexes. I would hope that at some point, we can have a more solid solution in MariaDB.

Источник

Innodb error duplicate key writing word node to fts auxiliary index table

Подскажите пожалуйста, что не так и как это лучше исправить?

Ошибки в логе мускула, как с этим бороться и из-за чего они могут происходить?

На сервере несколько баз данных.

Вообще же — кто-то пытается записать что-то в БД с уже существующим ключом, а ключик должен быть уникальным.

Вообще же — кто-то пытается записать что-то в БД с уже существующим ключом, а ключик должен быть уникальным.

Что за ключ, индекс что ле?

baas, Индекс нужен для быстрого поиска, а ключ, чтобы не было абсолютно одинаковых записей.

В вашем слувае это, скорее всего, первичный ключ. Я бы сделал бэкап и mysqldadmin —all-databases —auto-repair —optimize и после уже искал откуда ноги растут. Но, скорее всего, это какая-то гадость с первичным ключом.

lonelywoolf:
baas, Индекс нужен для быстрого поиска, а ключ, чтобы не было абсолютно одинаковых записей.

В вашем слувае это, скорее всего, первичный ключ. Я бы сделал бэкап и mysqldadmin —all-databases —auto-repair —optimize и после уже искал откуда ноги растут. Но, скорее всего, это какая-то гадость с первичным ключом.

Все равно не пойму, что за первичный ключ?

Кто его добавлял?

Разработчик, по всей видимости.. ))

Посмотрите по логам веб-сервера, что в этот момент происходит..

А поля с фуллтекстом есть?

Разработчик софта, который работает с сервером БД. Прогоните репэйр и задавайте вопросы разрабам вашего софта. Ну или DBAdmin. Я вот так на вскидку не видя сервера не могу сказать, какой конкретно скрипт пытается страдать хренью. Найдите запрос проблемный, найдите место в коде, откуда он вызывается.

Источник

«Can’t write; duplicate key in table» when updating some rows in a transaction

Details

Description

I have an issue on InnoDB with both MariaDB versions 10.3.4 and 10.2.12
This problem is very difficult for me to reproduce since it doesn’t always happen. As you can see, that error can’t be true because the ID 275 is already set for that row, so I don’t know why it is failing. Also, this is a pretty busy server, and this error pops up randomly on INSERTs and UPDATEs of various tables, but only 2-3 times a day (there are about 10.000 writes every day)

I don’t even know how to debug it properly since that particular error message is associated with the creation of foreign keys. The table is referenced on other tables, but the error should occur on creation of them, not by writing rows.

The query is run inside a transaction. Please let me know if you need anything else to help diagnose the issue.

Attachments

Attachments

Issue Links

MDEV-12676 InnoDB FTS duplicate key error

• Closed «>Closed

MDEV-14141 mysqld got exception 0xc0000005

• Closed «>Closed

MDEV-14970 MariaDB crashed with signal 11 and Aria table

• Closed «>Closed

MDEV-15304 Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY

• Closed «>Closed

MDEV-18868 FTS Breaking Replication

• Closed «>Closed

MDEV-24608 Failing assertion: error == DB_SUCCESS || error == DB_DUPLICATE_KEY OR Assertion `error == DB_DUPLICATE_KEY || error == DB_LOCK_WAIT_TIMEOUT’ failed IN trx_t::commit_low

• Open «>Open

MDEV-25581 MS FTS Bug 103523 also applicable to MariaDB Server (Was: Test MS Bug 103523 against MD010521-bb-10.5-thiru)

• Closed «>Closed

MDEV-29335 Crash after Duplicate Key error

• Closed «>Closed

Источник

«Can’t write; duplicate key in table» when updating some rows in a transaction

Details

Description

I have an issue on InnoDB with both MariaDB versions 10.3.4 and 10.2.12
This problem is very difficult for me to reproduce since it doesn’t always happen. As you can see, that error can’t be true because the ID 275 is already set for that row, so I don’t know why it is failing. Also, this is a pretty busy server, and this error pops up randomly on INSERTs and UPDATEs of various tables, but only 2-3 times a day (there are about 10.000 writes every day)

I don’t even know how to debug it properly since that particular error message is associated with the creation of foreign keys. The table is referenced on other tables, but the error should occur on creation of them, not by writing rows.

The query is run inside a transaction. Please let me know if you need anything else to help diagnose the issue.

Attachments

Attachments

Issue Links

MDEV-12676 InnoDB FTS duplicate key error

• Closed «>Closed

MDEV-14141 mysqld got exception 0xc0000005

• Closed «>Closed

MDEV-14970 MariaDB crashed with signal 11 and Aria table

• Closed «>Closed

MDEV-15304 Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY

• Closed «>Closed

MDEV-18868 FTS Breaking Replication

• Closed «>Closed

MDEV-24608 Failing assertion: error == DB_SUCCESS || error == DB_DUPLICATE_KEY OR Assertion `error == DB_DUPLICATE_KEY || error == DB_LOCK_WAIT_TIMEOUT’ failed IN trx_t::commit_low

• Open «>Open

MDEV-25581 MS FTS Bug 103523 also applicable to MariaDB Server (Was: Test MS Bug 103523 against MD010521-bb-10.5-thiru)

• Closed «>Closed

MDEV-29335 Crash after Duplicate Key error

• Closed «>Closed

Источник

«Can’t write; duplicate key in table» when updating some rows in a transaction

Details

Description

I have an issue on InnoDB with both MariaDB versions 10.3.4 and 10.2.12
This problem is very difficult for me to reproduce since it doesn’t always happen. As you can see, that error can’t be true because the ID 275 is already set for that row, so I don’t know why it is failing. Also, this is a pretty busy server, and this error pops up randomly on INSERTs and UPDATEs of various tables, but only 2-3 times a day (there are about 10.000 writes every day)

I don’t even know how to debug it properly since that particular error message is associated with the creation of foreign keys. The table is referenced on other tables, but the error should occur on creation of them, not by writing rows.

The query is run inside a transaction. Please let me know if you need anything else to help diagnose the issue.

Attachments

Attachments

Issue Links

MDEV-12676 InnoDB FTS duplicate key error

• Closed «>Closed

MDEV-14141 mysqld got exception 0xc0000005

• Closed «>Closed

MDEV-14970 MariaDB crashed with signal 11 and Aria table

• Closed «>Closed

MDEV-15304 Server crash in print_keydup_error / key_unpack or unexpected ER_DUP_KEY

• Closed «>Closed

MDEV-18868 FTS Breaking Replication

• Closed «>Closed

MDEV-24608 Failing assertion: error == DB_SUCCESS || error == DB_DUPLICATE_KEY OR Assertion `error == DB_DUPLICATE_KEY || error == DB_LOCK_WAIT_TIMEOUT’ failed IN trx_t::commit_low

• Open «>Open

MDEV-25581 MS FTS Bug 103523 also applicable to MariaDB Server (Was: Test MS Bug 103523 against MD010521-bb-10.5-thiru)

• Closed «>Closed

MDEV-29335 Crash after Duplicate Key error

• Closed «>Closed

Источник

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

• table metadata
• MVCC data
• data pages (with innodb_file_per_table enabled)
• index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, … ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer’s web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000 

Please notice that innodb_log_file_size is there twice. Look carefully…

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000 

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That’s why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What’s interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

You didn’t say, in your question what the «file not present» message was, but I’m guessing it’s this:

ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' 
(errno: 0 /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so: cannot open shared 
object file: No such file or directory)
ERROR: unable to install the UDF

That makes sense, because you’re telling gcc to write the file to /usr/lib/lib_mysqludf_sys.so… that’s the -o option in your command line. Give gcc the path MySQL is expecting and the rest of the installation should work.

gcc -m64 -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c  
-o /usr/local/mysql/lib/plugin/lib_mysqludf_sys.so 
-L/usr/lib/x86_64-linux-gnu/libstdc++.so.6

Also, whatever you’re planning to do with this… don’t say I didn’t recommend against it. You’re introducing both a potential security vulnerability and potentional performance and stability liability if these tools are deployed. This is not because there’s anything wrong with the utility, but because of the amount of somewhat unorthodox functionality it opens up.

It’s pretty cool, I admit. The lib_mysqludf_sys group of user-defined functions allow some interesting but easily-misappropriated capabilities, letting you spawn system commands and get either their generated output with sys_eval() or their return value with sys_exec() but really, I tend to suspect there’s a reason why MySQL doesn’t have these capabilities built in.

mysql> select sys_eval('df -k | grep xvda | tr -d "n"') as cool_function_but_bad_idea;
+-----------------------------------------------------+
| cool_function_but_bad_idea                          |
+-----------------------------------------------------+
| /dev/xvda1       8256952   1216636   6620888  16% / |
+-----------------------------------------------------+
1 row in set (0.81 sec)

mysql> select sys_exec('/bin/false'), sys_exec('/bin/true');
+------------------------+-----------------------+
| sys_exec('/bin/false') | sys_exec('/bin/true') |
+------------------------+-----------------------+
|                    256 |                     0 |
+------------------------+-----------------------+
1 row in set (1.59 sec)

Update to address this error message:

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o 
/usr/lib/lib_mysqludf_sys.so -fPIC gcc: error: lib_mysqludf_sys.c: No such 
file or directory gcc: fatal error: no input files compilation terminated

The statement in question is directing «gcc» to compile a 64-bit version from the source code, which is found in lib_mysqludf_sys.c. This is one of the files you downloaded, so, to compile it, you need to be inside the directory where you downloaded the lib_mysqludf_sys files. This error suggests that you aren’t. The download package isn’t limited to 32 bit, it’s just that it only builds a 32 bit version unless you use the gcc -m64 -fPIC ... statement.