Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
13.1
-
None
-
Not for Release Notes
-
Q3/2026 Server Maintenance
Description
Replication breaks when a function performs multiple INSERT statements with binlog_format=MIXED or STATEMENT. binlog_format= ROW looks good.
MTR Test case
--source include/have_binlog_format_statement.inc
|
--source include/master-slave.inc
|
--connection master
|
CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, v INT); |
DELIMITER $$;
|
CREATE FUNCTION f() RETURNS INT |
BEGIN
|
EXECUTE IMMEDIATE 'INSERT INTO t1(v) VALUES (100)'; |
EXECUTE IMMEDIATE 'INSERT INTO t1(v) VALUES (200)'; |
RETURN 0; |
END$$ |
CREATE PROCEDURE p() BEGIN DECLARE n INT DEFAULT f(); END$$ |
DELIMITER ;$$
|
CALL p();
|
SELECT id, v FROM t1 ORDER BY id; |
--sync_slave_with_master
|
--connection slave
|
SELECT id, v FROM t1 ORDER BY id; |
--connection master
|
DROP PROCEDURE p; |
DROP FUNCTION f; |
DROP TABLE t1; |
--sync_slave_with_master
|
--source include/rpl_end.inc |
Error info
Last_SQL_Errno 1062
|
Last_SQL_Error Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO t1(v) VALUES (200)'
|
Binlog events from master and replica. It seems that binary logging does not advance the auto-increment value properly.
**** SHOW BINLOG EVENTS on default ****
|
binlog_name = 'slave-bin.000001'
|
SHOW BINLOG EVENTS IN 'slave-bin.000001' LIMIT 200;
|
Log_name Pos Event_type Server_id End_log_pos Info
|
slave-bin.000001 4 Format_desc 2 257 Server ver: 13.1.0-MariaDB-debug-log, Binlog ver: 4
|
slave-bin.000001 257 Gtid_list 2 286 []
|
slave-bin.000001 286 Binlog_checkpoint 2 329 slave-bin.000001
|
slave-bin.000001 329 Gtid 1 371 GTID 0-1-1
|
slave-bin.000001 371 Query 1 505 use `test`; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, v INT)
|
slave-bin.000001 505 Gtid 1 547 GTID 0-1-2
|
slave-bin.000001 547 Query 1 802 use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS int(11)
|
BEGIN
|
EXECUTE IMMEDIATE 'INSERT INTO t1(v) VALUES (100)';
|
EXECUTE IMMEDIATE 'INSERT INTO t1(v) VALUES (200)';
|
RETURN 0;
|
END
|
slave-bin.000001 802 Gtid 1 844 GTID 0-1-3
|
slave-bin.000001 844 Query 1 997 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
|
BEGIN DECLARE n INT DEFAULT f(); END
|
slave-bin.000001 997 Gtid 1 1039 BEGIN GTID 0-1-4
|
slave-bin.000001 1039 Intvar 1 0 INSERT_ID=1
|
slave-bin.000001 1071 Query 1 0 use `test`; INSERT INTO t1(v) VALUES (100)
|
slave-bin.000001 1168 Query 1 1241 COMMIT
|
 |
**** SHOW RELAYLOG EVENTS on default ****
|
relaylog_name = 'slave-relay-bin.000002'
|
SHOW RELAYLOG EVENTS IN 'slave-relay-bin.000002' LIMIT 200;
|
Log_name Pos Event_type Server_id End_log_pos Info
|
slave-relay-bin.000002 4 Format_desc 2 257 Server ver: 13.1.0-MariaDB-debug-log, Binlog ver: 4
|
slave-relay-bin.000002 257 Rotate 1 0 master-bin.000001;pos=286
|
slave-relay-bin.000002 305 Format_desc 1 0 Server ver: 13.1.0-MariaDB-debug-log, Binlog ver: 4
|
slave-relay-bin.000002 558 Gtid_list 1 286 []
|
slave-relay-bin.000002 587 Binlog_checkpoint 1 330 master-bin.000001
|
slave-relay-bin.000002 631 Gtid 1 372 GTID 0-1-1
|
slave-relay-bin.000002 673 Query 1 506 use `test`; CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, v INT)
|
slave-relay-bin.000002 807 Gtid 1 548 GTID 0-1-2
|
slave-relay-bin.000002 849 Query 1 819 use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS int(11)
|
BEGIN
|
EXECUTE IMMEDIATE 'INSERT INTO t1(v) VALUES (100)';
|
EXECUTE IMMEDIATE 'INSERT INTO t1(v) VALUES (200)';
|
RETURN 0;
|
END
|
slave-relay-bin.000002 1120 Gtid 1 861 GTID 0-1-3
|
slave-relay-bin.000002 1162 Query 1 1030 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p`()
|
BEGIN DECLARE n INT DEFAULT f(); END
|
slave-relay-bin.000002 1331 Gtid 1 1072 BEGIN GTID 0-1-4
|
slave-relay-bin.000002 1373 Intvar 1 0 INSERT_ID=1
|
slave-relay-bin.000002 1405 Query 1 0 use `test`; INSERT INTO t1(v) VALUES (100)
|
slave-relay-bin.000002 1502 Query 1 1274 COMMIT
|
slave-relay-bin.000002 1575 Gtid 1 1316 BEGIN GTID 0-1-5
|
slave-relay-bin.000002 1617 Intvar 1 0 INSERT_ID=1
|
slave-relay-bin.000002 1649 Query 1 0 use `test`; INSERT INTO t1(v) VALUES (200)
|
slave-relay-bin.000002 1746 Query 1 1518 COMMIT
|
connection default;
|
Attachments
Issue Links
- is caused by
-
MDEV-39518 Allow prepared statements in stored functions in assignment right hand
-
- In Testing
-