[MDEV-38731] Replica updates table to wrong values, further replication failures - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.6, 10.11, 11.4, 11.8
Fix Version/s: 10.6, 10.11, 11.4, 11.8, 12.3
Component/s: Replication
Labels:
- rr-profile

Bug Category:
Can result in data loss
Epic Link:
Meaningful Replication Testing is Severely Limited due to General Instability
Sprint:
Q2/2026 Server Maintenance

Description

Important note: While the test case looks completely deterministic, the failure is sporadic and depends on the build/luck. On some builds (within the same server version) it happens every time, on others once in 5-10 attempts, yet on other never. I couldn't determine a pattern, except that it seems to happen on my release builds on average more willingly than on ASAN builds. It suggests some sort of corruption or lack of initialization, but neither MSAN, nor ASAN, nor UBSAN, nor valgrind show anything. Run with --repeat=N (be generous, the test is very fast), and I'll also try to create rr profile as a backup in case it doesn't fail on the developer's machine.

--source include/have_innodb.inc

--source include/have_binlog_format_row.inc

--source include/master-slave.inc

CREATE TABLE t (a int, b int, c int, d int, KEY (d)) ENGINE=InnoDB;

INSERT INTO t (a,b,d) VALUES

  (1,NULL,4),(2,NULL,1),(3,NULL,1),

  (4,NULL,1),(5,NULL,1),(6,NULL,1),

  (7,0,NULL),(8,NULL,NULL);

ALTER TABLE t ADD UNIQUE (b);

UPDATE t SET c = 1;

ALTER TABLE t ADD UNIQUE (a);

--sync_slave_with_master

--connection master

DROP TABLE t;

--source include/rpl_end.inc

main 5bcc115d773caac07d4afd42252e08f3905452b0

2026-02-02 17:52:49 7 [ERROR] Slave SQL: Error 'Duplicate entry '8' for key 'a'' on query. Default database: 'test'. Query: 'ALTER TABLE t ADD UNIQUE (a)', Gtid 0-1-5, Internal MariaDB error code: 1062

2026-02-02 17:52:49 7 [Warning] Slave: Duplicate entry '8' for key 'a' Error_code: 1062

So, it fails when we try to add a unique key on column a, which is supposed to be very straightforward, as we only inserted constant values 1..8 into it and never even updated it.

If we extend the test case with SELECTs, we'll see that the guilty statement is actually the previous UPDATE of column c:

--source include/have_innodb.inc

--source include/have_binlog_format_row.inc

--source include/master-slave.inc

CREATE TABLE t (a int, b int, c int, d int, KEY (d)) ENGINE=InnoDB;

INSERT INTO t (a,b,d) VALUES

  (1,NULL,4),(2,NULL,1),(3,NULL,1),

  (4,NULL,1),(5,NULL,1),(6,NULL,1),

  (7,0,NULL),(8,NULL,NULL);

ALTER TABLE t ADD UNIQUE (b);

--echo # Contents on the master before update

SELECT * FROM t ORDER BY a;

--sync_slave_with_master

--echo # Contents on the slave before update

SELECT * FROM t ORDER BY a;

--connection master

UPDATE t SET c = 1;

--echo # Contents on the master after update

SELECT * FROM t ORDER BY a;

--sync_slave_with_master

--echo # Contents on the slave after update

SELECT * FROM t ORDER BY a;

--connection master

ALTER TABLE t ADD UNIQUE (a);

--sync_slave_with_master

--connection master

DROP TABLE t;

--source include/rpl_end.inc

# Contents on the master before update

SELECT * FROM t ORDER BY a;

a	b	c	d

1	NULL	NULL	4

2	NULL	NULL	1

3	NULL	NULL	1

4	NULL	NULL	1

5	NULL	NULL	1

6	NULL	NULL	1

7	0	NULL	NULL

8	NULL	NULL	NULL

connection slave;

# Contents on the slave before update

SELECT * FROM t ORDER BY a;

a	b	c	d

1	NULL	NULL	4

2	NULL	NULL	1

3	NULL	NULL	1

4	NULL	NULL	1

5	NULL	NULL	1

6	NULL	NULL	1

7	0	NULL	NULL

8	NULL	NULL	NULL

# Contents on the master after update

SELECT * FROM t ORDER BY a;

a	b	c	d

1	NULL	1	4

2	NULL	1	1

3	NULL	1	1

4	NULL	1	1

5	NULL	1	1

6	NULL	1	1

7	0	1	NULL

8	NULL	1	NULL

connection slave;

# Contents on the slave after update

SELECT * FROM t ORDER BY a;

a	b	c	d

1	NULL	1	4

2	NULL	1	1

3	NULL	1	1

4	NULL	1	1

5	NULL	1	1

6	NULL	1	1

8	NULL	1	NULL

8	NULL	NULL	NULL

So, the slave "lost" the row (7,0,1,NULL) and added instead another row (8,NULL,NULL,NULL).
Naturally after this replication can fail in a variety of ways. ALTER leading to ER_DUP_ENTRY is just one of them, DELETE FROM t WHERE a = 7 will lead to ER_KEY_NOT_FOUND, etc.

In the binlog I don't see anything obviously wrong:

$ ../client/mysqlbinlog --verbose --base64-output=DECODE-ROWS var/log/bug.rpl1a-row/mysqld.1/data/master-bin.000001

../client/mysqlbinlog: Deprecated program name. It will be removed in a future release, use '/share8t/bld/main-asan-ubsan/client/mariadb-binlog' instead

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#260202 17:57:56 server id 1  end_log_pos 257 CRC32 0xeba840c5 	Start: binlog v 4, server v 12.3.0-MariaDB-asan-debug-log created 260202 17:57:56 at startup

ROLLBACK/*!*/;

# at 257

#260202 17:57:56 server id 1  end_log_pos 286 CRC32 0x10f37e1e 	Gtid list []

# at 286

#260202 17:57:56 server id 1  end_log_pos 330 CRC32 0xafd0c15b 	Binlog checkpoint master-bin.000001

# at 330

#260202 17:57:56 server id 1  end_log_pos 372 CRC32 0x11502bb6 	GTID 0-1-1 ddl thread_id=8

/*M!100101 SET @@session.skip_parallel_replication=0*//*!*/;

/*M!100001 SET @@session.gtid_domain_id=0*//*!*/;

/*M!100001 SET @@session.server_id=1*//*!*/;

/*M!100001 SET @@session.gtid_seq_no=1*//*!*/;

# at 372

#260202 17:57:56 server id 1  end_log_pos 514 CRC32 0x368c8171 	Query	thread_id=8	exec_time=0	error_code=0	xid=153

use `test`/*!*/;

SET TIMESTAMP=1770047876/*!*/;

SET @@session.pseudo_thread_id=8/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_for_timestamp=1, @@session.system_versioning_insert_history=0/*!*/;

SET @@session.sql_mode=1411383296/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C latin1 *//*!*/;

SET @@session.character_set_client=latin1,@@session.collation_connection=8,@@session.collation_server=2304/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE TABLE t (a int, b int, c int, d int, KEY (d)) ENGINE=InnoDB

/*!*/;

# at 514

#260202 17:57:56 server id 1  end_log_pos 556 CRC32 0xb8ac3d2c 	GTID 0-1-2 trans thread_id=8

/*M!100001 SET @@session.gtid_seq_no=2*//*!*/;

START TRANSACTION

/*!*/;

# at 556

# at 700

#260202 17:57:56 server id 1  end_log_pos 0 CRC32 0x47f838f4 	Annotate_rows:

#Q> INSERT INTO t (a,b,d) VALUES

#Q> (1,NULL,4),(2,NULL,1),(3,NULL,1),

#Q> (4,NULL,1),(5,NULL,1),(6,NULL,1),

#Q> (7,0,NULL),(8,NULL,NULL)

#260202 17:57:56 server id 1  end_log_pos 0 CRC32 0x1b2c38e1 	Table_map: `test`.`t` mapped to number 33

# at 747

#260202 17:57:56 server id 1  end_log_pos 0 CRC32 0x4ffe69af 	Write_rows: table id 33 flags: STMT_END_F

### INSERT INTO `test`.`t`

### SET

###   @1=1

###   @2=NULL

###   @3=NULL

###   @4=4

### INSERT INTO `test`.`t`

### SET

###   @1=2

###   @2=NULL

###   @3=NULL

###   @4=1

### INSERT INTO `test`.`t`

### SET

###   @1=3

###   @2=NULL

###   @3=NULL

###   @4=1

### INSERT INTO `test`.`t`

### SET

###   @1=4

###   @2=NULL

###   @3=NULL

###   @4=1

### INSERT INTO `test`.`t`

### SET

###   @1=5

###   @2=NULL

###   @3=NULL

###   @4=1

### INSERT INTO `test`.`t`

### SET

###   @1=6

###   @2=NULL

###   @3=NULL

###   @4=1

### INSERT INTO `test`.`t`

### SET

###   @1=7

###   @2=0

###   @3=NULL

###   @4=NULL

### INSERT INTO `test`.`t`

### SET

###   @1=8

###   @2=NULL

###   @3=NULL

###   @4=NULL

# Number of rows: 8

# at 848

#260202 17:57:56 server id 1  end_log_pos 879 CRC32 0xf06bc4a3 	Xid = 154

COMMIT/*!*/;

# at 879

#260202 17:57:56 server id 1  end_log_pos 921 CRC32 0x68cb4c9c 	GTID 0-1-3 ddl thread_id=8

/*M!100001 SET @@session.gtid_seq_no=3*//*!*/;

# at 921

#260202 17:57:56 server id 1  end_log_pos 1025 CRC32 0x9452a003 	Query	thread_id=8	exec_time=1	error_code=0	xid=155

SET TIMESTAMP=1770047876/*!*/;

ALTER TABLE t ADD UNIQUE (b)

/*!*/;

# at 1025

#260202 17:57:57 server id 1  end_log_pos 1067 CRC32 0xd6956525 	GTID 0-1-4 trans thread_id=8

/*M!100001 SET @@session.gtid_seq_no=4*//*!*/;

START TRANSACTION

/*!*/;

# at 1067

# at 1108

#260202 17:57:57 server id 1  end_log_pos 0 CRC32 0x4b289253 	Annotate_rows:

#Q> UPDATE t SET c = 1

#260202 17:57:57 server id 1  end_log_pos 0 CRC32 0x04216732 	Table_map: `test`.`t` mapped to number 34

# at 1155

#260202 17:57:57 server id 1  end_log_pos 0 CRC32 0xa0452ccd 	Update_rows: table id 34 flags: STMT_END_F

### UPDATE `test`.`t`

### WHERE

###   @1=1

###   @2=NULL

###   @3=NULL

###   @4=4

### SET

###   @1=1

###   @2=NULL

###   @3=1

###   @4=4

### UPDATE `test`.`t`

### WHERE

###   @1=2

###   @2=NULL

###   @3=NULL

###   @4=1

### SET

###   @1=2

###   @2=NULL

###   @3=1

###   @4=1

### UPDATE `test`.`t`

### WHERE

###   @1=3

###   @2=NULL

###   @3=NULL

###   @4=1

### SET

###   @1=3

###   @2=NULL

###   @3=1

###   @4=1

### UPDATE `test`.`t`

### WHERE

###   @1=4

###   @2=NULL

###   @3=NULL

###   @4=1

### SET

###   @1=4

###   @2=NULL

###   @3=1

###   @4=1

### UPDATE `test`.`t`

### WHERE

###   @1=5

###   @2=NULL

###   @3=NULL

###   @4=1

### SET

###   @1=5

###   @2=NULL

###   @3=1

###   @4=1

### UPDATE `test`.`t`

### WHERE

###   @1=6

###   @2=NULL

###   @3=NULL

###   @4=1

### SET

###   @1=6

###   @2=NULL

###   @3=1

###   @4=1

### UPDATE `test`.`t`

### WHERE

###   @1=7

###   @2=0

###   @3=NULL

###   @4=NULL

### SET

###   @1=7

###   @2=0

###   @3=1

###   @4=NULL

### UPDATE `test`.`t`

### WHERE

###   @1=8

###   @2=NULL

###   @3=NULL

###   @4=NULL

### SET

###   @1=8

###   @2=NULL

###   @3=1

###   @4=NULL

# Number of rows: 8

# at 1357

#260202 17:57:57 server id 1  end_log_pos 1388 CRC32 0xc6292b1e 	Xid = 158

COMMIT/*!*/;

# at 1388

#260202 17:57:57 server id 1  end_log_pos 1430 CRC32 0x2af98722 	GTID 0-1-5 ddl thread_id=8

/*M!100001 SET @@session.gtid_seq_no=5*//*!*/;

# at 1430

#260202 17:57:57 server id 1  end_log_pos 1534 CRC32 0xfc99533f 	Query	thread_id=8	exec_time=0	error_code=0	xid=161

SET TIMESTAMP=1770047877/*!*/;

ALTER TABLE t ADD UNIQUE (a)

/*!*/;

# at 1534

#260202 17:57:57 server id 1  end_log_pos 1557 CRC32 0x599b2d2e 	Stop

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Attachments

Activity

People

Assignee:: Brandon Nesterenko

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2026-02-02 16:03

Updated:: 2026-02-02 20:29

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.