[MDEV-31351] [MariaDB 10.3 -> MariaDB 10.6][InnoDB] Duplicate entry '...' for key 'unique_index' Created: 2023-05-26  Updated: 2023-08-20  Resolved: 2023-08-20

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.6.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Luigi Troiano Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: innodb
Environment:

Ubuntu 22.04


 Description   

Recently we upgraded mariadb from 10.3 to 10.6 and we are facing a weird problem.
Although there is a unique key on 3 columns (`table_hash`,`table_id`,`meta_key`) there are multiple records with same data.
It didn't happen when we were on mariadb 10.3.

*Table structure:*
```

CREATE TABLE `ordini_meta` (
 
  `meta_id` int(11) NOT NULL,
 
  `table_hash` bigint(64) NOT NULL DEFAULT 0,
 
  `table_id` int(11) NOT NULL DEFAULT 0,
 
  `meta_key` varchar(50) DEFAULT NULL,
 
  `meta_value` longtext DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
 
 
 
ALTER TABLE `ordini_meta`
 
  ADD PRIMARY KEY (`meta_id`),
 
  ADD UNIQUE KEY `unique_index` (`table_hash`,`table_id`,`meta_key`),
 
  ADD KEY `table_hash` (`table_hash`),
 
  ADD KEY `table_id` (`table_id`),
 
  ADD KEY `meta_key` (`meta_key`);
 
 
 
ALTER TABLE `ordini_meta`
 
  MODIFY `meta_id` int(11) NOT NULL AUTO_INCREMENT;

```

*Example of duplicates*:
Duplicates

Rows in the table are managed with PHP and the query used is:
```

INSERT INTO ordini_meta (table_hash,table_id,meta_key,meta_value) VALUES (%d,%d,%s,%s) ON DUPLICATE KEY UPDATE meta_id=LAST_INSERT_ID(meta_id), meta_value=%s"

```
*SHOW VARIABLES 10.6(now):*
pastebin

*SHOW VARIABLES 10.3(before upgrade):*
pastebin

DB structure is the same that was in 10.3, unique key was already there before upgrade. In 10.3 there weren't any duplicate rows. We restored the database from a backup(mysqldump) and everyday we need to delete duplicate rows. We also tried to re-create index many times. I suppose I just need to create a bug report

We really don't understand why it happens.
Any help is really appreciated.

 Comments   
Comment by Marko Mäkelä [ 2023-05-26 ]

Do you have any recent (physical, not logical like mysqldump) backup of the database from before the ugrade? If yes, can you please execute CHECK TABLE ordini_meta on MariaDB Server 10.3?

I suspect that the InnoDB change buffer could have played a role in this. There was a bug in CREATE INDEX or ALTER TABLE…ADD INDEX that was rather recently found and fixed in MDEV-30009.

Whatever caused the secondary index corruption, the easiest way to fix it is to rebuild the table:

OPTIMIZE TABLE ordini_meta;

If the table is large and you wish to save some I/O, you can also try to drop and re-create all corrupted secondary indexes:

CHECK TABLE ordini_meta QUICK;
 
ALTER TABLE ordini_meta DROP INDEX unique_index;
 
ALTER TABLE ordini_meta ADD UNIQUE KEY `unique_index` (`table_hash`,`table_id`,`meta_key`);

Comment by Luigi Troiano [ 2023-05-26 ]

We already try "OPTIMIZE TABLE ordini_meta;" many times, it doesn't solve the issue.
We have other tables with same structure and we are facing same problem every day(multiple records with same unique data).
Of course we executed "OPTIMIZE TABLE" on them too but it doesn't help.
I should have a volume snapshot with 10.3, I will let you know.

Comment by Luigi Troiano [ 2023-06-07 ]

It seems problem has been solved after following tuning.

innodb_buffer_pool_size = 48G
 
innodb_buffer_pool_instances = 32
 
innodb_write_io_threads = 4
 
innodb_read_io_threads = 4
 
thread_pool_size = 8
 
table_open_cache_instances = 64
 
table_open_cache = 2048
 
table_definition_cache = 2048
 
join_buffer_size = 2MB

Comment by Elena Stepanova [ 2023-07-23 ]

Do you (or did you before the tuning) have innodb_change_buffering set to anything other than none? Marko mentioned it in the first comment as well.
The issue looks very much like MDEV-31120 and MDEV-31713, except that in those two cases we know for a fact that innodb_change_buffering was set to all, while here it remains unclear.

Generated at Thu Feb 08 10:23:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.