Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8, 12.1
-
Can result in unexpected behaviour
Description
Binlogging in ROW format of DML over tables with FK constraints can produce
redundant Table-map-log-events.
E.g in the following script (thanks to claudio.nanni) the slave creates unnecessary table map event
for a an FK-dependent child table:
-- Create master table
|
CREATE TABLE master ( |
id INT PRIMARY KEY AUTO_INCREMENT, |
name VARCHAR(50) NOT NULL, |
description VARCHAR(100) |
) ENGINE=InnoDB;
|
|
|
-- Create dependent table WITHOUT ON DELETE CASCADE
|
CREATE TABLE dependent_no_cascade ( |
id INT PRIMARY KEY AUTO_INCREMENT, |
master_id INT NOT NULL, |
data VARCHAR(50), |
FOREIGN KEY (master_id) REFERENCES master(id) |
) ENGINE=InnoDB;
|
|
|
-- Create dependent table WITH ON DELETE CASCADE
|
CREATE TABLE dependent_with_cascade ( |
id INT PRIMARY KEY AUTO_INCREMENT, |
master_id INT NOT NULL, |
data VARCHAR(50), |
FOREIGN KEY (master_id) REFERENCES master(id) ON DELETE CASCADE |
) ENGINE=InnoDB;
|
|
|
-- Insert 5 records into master table
|
INSERT INTO master (name, description) VALUES |
('Master 1', 'First master record'), |
('Master 2', 'Second master record'), |
('Master 3', 'Third master record'), |
('Master 4', 'Fourth master record'), |
('Master 5', 'Fifth master record'); |
|
|
-- Insert 5 records into dependent_no_cascade table
|
INSERT INTO dependent_no_cascade (master_id, data) VALUES |
(1, 'Dependent no cascade 1'), |
(2, 'Dependent no cascade 2'), |
(3, 'Dependent no cascade 3'), |
(4, 'Dependent no cascade 4'), |
(5, 'Dependent no cascade 5'); |
|
|
-- Insert 5 records into dependent_with_cascade table
|
INSERT INTO dependent_with_cascade (master_id, data) VALUES |
(1, 'Dependent with cascade 1'), |
(2, 'Dependent with cascade 2'), |
(3, 'Dependent with cascade 3'), |
(4, 'Dependent with cascade 4'), |
(5, 'Dependent with cascade 5'); |
|
|
-- Perform INSERT operation on master table
|
INSERT INTO master (name, description) VALUES ('Master 6', 'Sixth master record - newly inserted'); |
|
Upon replicating the master and slave binlog diverge in the Table-map part
M:
|
| master-bin.000001 | 2972 | Gtid | 1 | 3014 | BEGIN GTID 0-1-7 |
|
| master-bin.000001 | 3014 | Annotate_rows | 1 | 0 | INSERT INTO master (name, description) VALUES ('Master 6', 'Sixth master record - newly inserted') |
|
| master-bin.000001 | 3135 | Table_map | 1 | 0 | table_id: 18 (test.master) |
|
| master-bin.000001 | 3190 | Write_rows_v1 | 1 | 0 | table_id: 18 flags: STMT_END_F
|
vs
S:
|
| slave-bin.000001 | 3040 | Gtid | 1 | 3082 | BEGIN GTID 0-1-7 |
|
| slave-bin.000001 | 3082 | Annotate_rows | 1 | 0 | INSERT INTO master (name, description) VALUES ('Master 6', 'Sixth master record - newly inserted') |
|
| slave-bin.000001 | 3203 | Table_map | 1 | 0 | table_id: 18 (test.master) |
|
| slave-bin.000001 | 3258 | Table_map | 1 | 0 | table_id: 19 (test.dependent_with_cascade) |
|
| slave-bin.000001 | 3327 | Write_rows_v1 | 1 | 0 | table_id: 18 flags: STMT_END_F
|