Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 10.11.13
-
None
Description
The SQL replication thread now consistently fails when executing a Stored Procedure that runs successfully on the master.
This issue is a regression that started immediately after upgrading from MariaDB 10.6.22 to 10.11.13 (Issue not present in 10.6.22).
The breaking point appears to be related to how the binary log records the creation of and inserts into temporary tables when a DDL statement
that creates a BASE InnoDB table is executed in between two separate temporary table creations/updates within the same stored procedure.
It appears that the DDL for Temporary Table 2 is not written to the binlog, only the subsequent INSERT statement is.
When the slave's SQL thread attempts to execute this INSERT statement leads to an error and the sql replication thread halting.
Reproduction Case
While the issue was initially found in a complex stored procedure, I have created a minimal,
simple use-case that reliably reproduces this specific behavior to aid in debugging.
create schema andre; |
use andre |
|
|
delimiter //
|
CREATE OR REPLACE PROCEDURE `sp_test`() |
BEGIN
|
|
|
CREATE OR REPLACE TEMPORARY TABLE `andretmp1` ( |
id int primary key, |
`col1` varchar(100) DEFAULT NULL |
) ENGINE=innodb DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; |
|
|
|
|
INSERT INTO andretmp1 values (1,'www'); |
|
|
CREATE OR REPLACE TABLE andrebaset1 (id int primary key) ENGINE=innodb; |
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE `andretmp2` ( |
id int primary key, |
`col1` varchar(100) DEFAULT NULL |
) ENGINE=innodb DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; |
|
|
|
|
INSERT INTO andretmp2 values (1,'zzz'); |
|
|
END // |
delimiter ;
|
|
|
call sp_test();
|
(execute the SP again in case) |
binlog format MIXED
isolation level READ COMMITTED
REPLICA Error:
Last_Errno: 1146
|
Last_Error: Error 'Table 'andre.andretmp2' doesn't exist' on query. Default database: 'andre'. Query: 'INSERT INTO andretmp2 values (1,'zzz')' |
|
10.11.13 binlog events on master
| mysql-bin.000141 | 427 | Query | 224288002 | 675 | use `andre`; CREATE OR REPLACE TEMPORARY TABLE `andretmp1` ( |
| mysql-bin.000141 | 717 | Query | 224288002 | 850 | use `andre`; CREATE OR REPLACE TABLE andrebaset1 (id int primary key) | |
| mysql-bin.000141 | 892 | Query | 224288002 | 998 | use `andre`; INSERT INTO andretmp2 values (1,'zzz') |
|
on 10.6.22 binlog events on master
| mysql-bin.000014 | 427 | Query | 224329002 | 675 | use `andre`; CREATE OR REPLACE TEMPORARY TABLE `andretmp1` ( |
| mysql-bin.000014 | 717 | Query | 224329002 | 850 | use `andre`; CREATE OR REPLACE TABLE andrebaset1 (id int primary key) | |
Same issue if we use ROW and REPEATABLE READ:
10.11.13 binlog events on master
| mysql-bin.000143 | 46164 | Query | 224288002 | 46297 | use `andre`; CREATE OR REPLACE TABLE andrebaset1 (id int primary key) | |
| mysql-bin.000143 | 46339 | Query | 224288002 | 46445 | use `andre`; INSERT INTO andretmp2 values (1,'zzz') | |
10.6.22 binlog events on master
| mysql-bin.000017 | 17682 | Query | 224329002 | 17815 | use `andre`; CREATE OR REPLACE TABLE andrebaset1 (id int primary key) |