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

Report bugs and workarounds
Post Reply
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

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

Post by henk »

EFA-3.0.2.5

/var/lib/mysql/FQDN(your EFA hostname).err contains error(s) like:

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

This is related to InnoDB FULLTEXT indexes and is solved in MariaDb 10.1.29-1.el6 ( https://jira.mariadb.org/browse/MDEV-12676)

To solve it I did an upgrade to mysqld 10.1.29-MariaDB ( from the current 10.1.28-MariaDB version used in EFA).
  • Stop services

Code: Select all

service mailscanner stop
service crond stop
  • update yum.conf - vi /etc/yum.conf

Code: Select all

#exclude=kernel* MariaDB* postfix* mailscanner* MailScanner* clamav* clamd* open-vm-tools*
exclude=kernel* postfix* mailscanner* MailScanner* clamav* clamd* open-vm-tools*
  • yum check-update
  • yum update

Code: Select all

Updating:
 MariaDB-client            
 MariaDB-common            
 MariaDB-compat            
 MariaDB-server            
 device-mapper             
 device-mapper-event       
 device-mapper-event-libs  
 device-mapper-libs        
 galera                    
 kexec-tools               
 kpartx                    
 libblkid                  
 libuuid                   
 lvm2                      
 lvm2-libs                 
 sos                       
 util-linux-ng             

Transaction Summary
===========================
Upgrade      17 Package(s)

Total download size: 165 M

  • Restore yum.conf to original - vi /etc/yum.conf
  • Start Services

Code: Select all

service mailscanner start
service crond start
Check /var/lib/mysql/FQDN(your EFA hostname).err

Like to know if there are EFA users having the same issue.
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
User avatar
shawniverson
Posts: 3644
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: Mysql: InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table

Post by shawniverson »

Thanks for the heads up, we will update MariaDB in next update as well ;)
henk
Posts: 517
Joined: 14 Dec 2015 22:16
Location: Netherlands
Contact:

Re: Mysql: InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table

Post by henk »

Somehow the error is back, filling the error log with messages, no clou why. It was gone for quite some time :doh:

view /var/lib/mysql/<FQDN>.err:
2018-01-22 12:35:24 7f138b5fe700 InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.

check affected db files

Code: Select all

ls -l /var/lib/mysql/mailscanner/F* 

The only way I could fix it (The desperate approach: (rebuild the FT index )

Stop mail flow

Code: Select all

service mailscanner stop
service crond stop
Login to mysql

Code: Select all

mysql -uroot 

ALTER TABLE mailscanner.maillog ENGINE=InnoDB;

Query OK, 2555 rows affected (1.06 sec)
Records: 2555  Duplicates: 0  Warnings: 0 
check affected db files

Code: Select all

ls -l /var/lib/mysql/mailscanner/F* 
view /var/lib/mysql/<FQDN>.err:
2018-01-22 15:10:47 7fec853f8700 InnoDB: FTS Optimize Removing table mailscanner/#sql2-1fbe-3d

Start mail flow

Code: Select all

service mailscanner start
service crond start
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

As i did try, without luck, to optimize the maillog table as it is the only table having a fulltextindex

Code: Select all

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE index_type LIKE 'FULLTEXT%';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mailscanner  | maillog    | 
To optimize the FULLTEXT index, you need to set:

Code: Select all

set GLOBAL innodb_optimize_fulltext_only=ON; 

Code: Select all

OPTIMIZE TABLE mailscanner.maillog; 

Code: Select all

set GLOBAL innodb_optimize_fulltext_only=OFF; 
“We are stuck with technology when what we really want is just stuff that works.” -Douglas Adams
Post Reply