[MDEV-30781] On duplicate key update makes duplicate records Created: 2023-03-03  Updated: 2024-01-12  Resolved: 2023-06-02

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marko Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-33178 Random slave replication error, other... Open

 Description   

When executing a query like:

insert into views (city, country, views) values (1, 1, 1) on duplicate key update views = views + 1;

to the table with primary auto increment key and unique constraint:

create table views
 
(
 
    id      int unsigned auto_increment primary key,
 
    city    int unsigned default 0 not null,
 
    country int unsigned default 0 not null,
 
    views   int unsigned default 0 not null,
 
    constraint listing_month unique (city, country)
 
)

it sometimes breaks the unique constraint and creates duplicate records on both master and slave.

Mariadb: Ver 15.1 Distrib 10.10.3-MariaDB, for debian-linux-gnu (x86_64)

 Comments   
Comment by Sergei Golubchik [ 2023-03-30 ]

I don't see how it could be possible. Can you share a test case?

Comment by Marko [ 2023-03-30 ]

We use 'mixed' based replication, but the mentioned query belongs to unsafe statements for 'statement' based replication.

https://mariadb.com/kb/en/unsafe-statements-for-statement-based-replication/

It shouldn't break things while using 'mixed' based replication.

Comment by Elena Stepanova [ 2023-05-02 ]

Given that, as you said, it creates duplicate records on the master as well, the (un)safety of replication should be irrelevant anyway.
So, can you share a test case as Sergei requested above?

Comment by Erlandas [ 2024-01-12 ]

Duplicate records continue appearing on indexes and sometimes in data itself, so far detected only on 3 tables which were cleaned up previously.

We first had an issue with the "on duplicate key update" statement. Then we switched to "insert ignore" and it seemed it solved the issue, but it was rediscovered when a dump was tried to import on another server. All nodes are using 11.2.2-MariaDB-1:11.2.2+maria~ubu2204, fresh duplicates were created by it just as with previous versions.

Will have to cleanup duplicates manually periodically as it does not break replication for some reason but does not allow importing dump on a new server. If structure is in place and dumping just data error is:

Duplicate entry '17-3933' for key 'requirement'

If dumping with create statements error on importing it is:

ERROR 1180 (HY000) at line 2148: Got error 1 "Operation not permitted" during COMMIT

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