Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.39, 10.6, 12.2.2
-
Unexpected results
-
Description
Summary
binlog_format=MIXED can produce primary/replica row divergence when an
AFTER INSERT trigger calls a stored procedure that writes two
AUTO_INCREMENT side-effect tables, one side-effect table has an FK to the
source table, and the transaction uses ROLLBACK TO SAVEPOINT before a later
witness insert.
Environment
- Product: MariaDB Server primary/replica replication
- Replication mode: file/position
- binlog_format: MIXED
- Observed affected versions: MariaDB 10.3, 10.6, 12.2
- Clean controls: MariaDB 10.3/10.6/12.2 with ROW; MySQL
5.6/5.7/8.0 with MIXED - Storage engine: InnoDB
Steps To Reproduce
1. Start a MariaDB primary/replica pair with binlog_format=MIXED.
2. Run the SQL in the Minimal Reproduction SQL section on the primary.
3. Wait for the replica to catch up.
4. Run this query on both primary and replica:
SELECT seq, src_id, frame_v, note |
FROM acl_repl_ai_proc_min_frame |
ORDER BY seq; |
Minimal Reproduction SQL
-- MariaDB primary/replica repro. Configure binlog_format=MIXED.
|
|
|
CREATE TABLE acl_repl_ai_proc_min_src( |
id INT PRIMARY KEY, |
v INT NOT NULL, |
note VARCHAR(32) NOT NULL |
) ENGINE=InnoDB;
|
|
|
CREATE TABLE acl_repl_ai_proc_min_audit( |
seq INT AUTO_INCREMENT PRIMARY KEY, |
src_id INT NOT NULL, |
seen_v INT NOT NULL, |
note VARCHAR(32) NOT NULL, |
KEY acl_repl_ai_proc_min_audit_src_idx(src_id), |
CONSTRAINT acl_repl_ai_proc_min_audit_fk |
FOREIGN KEY(src_id) REFERENCES acl_repl_ai_proc_min_src(id) |
ON UPDATE CASCADE ON DELETE CASCADE |
) ENGINE=InnoDB;
|
|
|
CREATE TABLE acl_repl_ai_proc_min_frame( |
seq INT AUTO_INCREMENT PRIMARY KEY, |
src_id INT NOT NULL, |
frame_v INT NOT NULL, |
note VARCHAR(64) NOT NULL |
) ENGINE=InnoDB;
|
|
|
DELIMITER //
|
CREATE PROCEDURE acl_repl_ai_proc_min_apply( |
IN p_src_id INT, |
IN p_seen_v INT, |
IN p_note VARCHAR(32) |
)
|
BEGIN
|
INSERT INTO acl_repl_ai_proc_min_audit(src_id, seen_v, note) |
VALUES (p_src_id, p_seen_v, p_note); |
|
|
INSERT INTO acl_repl_ai_proc_min_frame(src_id, frame_v, note) |
VALUES (p_src_id, p_seen_v + 7000, CONCAT('proc-', p_note)); |
END// |
|
|
CREATE TRIGGER acl_repl_ai_proc_min_ai |
AFTER INSERT ON acl_repl_ai_proc_min_src |
FOR EACH ROW |
BEGIN
|
CALL acl_repl_ai_proc_min_apply(NEW.id, NEW.v, NEW.note);
|
END// |
DELIMITER ;
|
|
|
INSERT INTO acl_repl_ai_proc_min_src(id, v, note) |
VALUES (1, 10, 'seed'); |
|
|
START TRANSACTION; |
INSERT INTO acl_repl_ai_proc_min_src(id, v, note) |
VALUES (2, 20, 'prefix'); |
SAVEPOINT acl_repl_ai_proc_min_sp;
|
INSERT INTO acl_repl_ai_proc_min_src(id, v, note) |
VALUES (3, 30, 'suffix'); |
ROLLBACK TO SAVEPOINT acl_repl_ai_proc_min_sp; |
COMMIT; |
|
|
INSERT INTO acl_repl_ai_proc_min_src(id, v, note) |
VALUES (4, 40, 'witness'); |
|
|
SELECT seq, src_id, frame_v, note |
FROM acl_repl_ai_proc_min_frame |
ORDER BY seq; |
Expected Result
The primary and replica should return the same committed rows.
Expected rows:
1, 1, 7010, proc-seed
|
2, 2, 7020, proc-prefix
|
4, 4, 7040, proc-witness
|
The seq=3 value is consumed by the rolled-back suffix insert on the primary,
so the later committed witness row uses seq=4.
Actual Result
The primary and replica diverge.
Primary:
1, 1, 7010, proc-seed
|
2, 2, 7020, proc-prefix
|
4, 4, 7040, proc-witness
|
Replica:
1, 1, 7010, proc-seed
|
2, 2, 7020, proc-prefix
|
3, 4, 7040, proc-witness
|
This is committed row-value divergence, not only AUTO_INCREMENT metadata
drift.
Separate Verification Request
Please verify this stored-procedure reproduction directly. I am filing the stored-function, witness-syntax, and FK-placement forms separately because those forms exercise different statement logging classifications and may need separate regression tests.
Additional Observation
The relevant primary binlog used statement-form
Query events for the trigger-driving DML in MIXED. The same SQL shape was
clean under MariaDB ROW.
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
-
- Confirmed
-
-
-
- Confirmed
-
-
-
- Confirmed
-