Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40227

Replication breaks when a function performs multiple INSERT statements with binlog_format=MIXED or STATEMENT

    XMLWordPrintable

Details

    • 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

          Activity

            People

              bar Alexander Barkov
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 4.7h Original Estimate - 4.7h
                  4.7h
                  Remaining:
                  Time Spent - 0.5h Remaining Estimate - 4.7h
                  4.7h
                  Logged:
                  Time Spent - 0.5h Remaining Estimate - 4.7h
                  0.5h

                  Git Integration

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