MySQL Bugs: #32506: Foreign key cascades do not appear when binlog

Bug #32506	Foreign key cascades do not appear when binlog_format = 'ROW'
Submitted:	19 Nov 2007 21:12	Modified:	23 Nov 2007 17:07
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.1.22	OS:	Any
Assigned to:	Mats Kindahl	CPU Architecture:	Any
Tags:	cascade, innodb, replication

Description:
When using binlog_format = 'ROW', DML statements that cause cascading actions in InnoDB due to foreign key constraints do not get pushed to the binary log.  This is OK for RBR between two identical InnoDB tables, but causes significant problems where the storage engine on the slave differs from that of the master (a common use case).  Because the InnoDB cascading actions are not pushed to the binary log, they do not replicate, causing data on slave to be different than that on the master.

How to repeat:
Configure two 5.1 servers for replication, then run the following:

#  On master:

CREATE TABLE fc1 (i INT PRIMARY KEY, j INT) ENGINE = InnoDB;
CREATE TABLE fc2 (k INT PRIMARY KEY, l INT, FOREIGN KEY li (l) REFERENCES fc1 (i) ON DELETE CASCADE) ENGINE = InnoDB;

#  On slave:

DROP TABLE IF EXISTS fc1;
DROP TABLE IF EXISTS fc2;

CREATE TABLE fc1 (i INT PRIMARY KEY, j INT) ENGINE = MyISAM;
CREATE TABLE fc2 (k INT PRIMARY KEY, l INT, FOREIGN KEY li (l) REFERENCES fc1 (i) ON DELETE CASCADE) ENGINE = MyISAM;

#  On master:

INSERT INTO fc1 VALUES (1, 1), (2, 2);
INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1);

SET binlog_format = 'ROW';

DELETE FROM fc1 WHERE i = 1;

SELECT * FROM fc1;
SELECT * FROM fc2;

#  On slave:

SELECT * FROM fc1;
SELECT * FROM fc2;

Suggested fix:
Either fix so that cross-engine RBR is consistent, or document as limitation of RBR.

To check the claim on "actions are not pushed to the binary log"
one can do `show binlog events'. And indeed, there is no row event for
deleting from the child table.
The last events after delete are:

INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1)                                                                        |
| master-bin.000001 | 642 | Xid         |         1 |         669 | COMMIT /* xid=21 */                                                                                                              |
| master-bin.000001 | 669 | Table_map   |         1 |         712 | table_id: 16 (test.fc1)                                                                       
| master-bin.000001 | 712 | Delete_rows |         1 |         750 | table_id: 16 flags: STMT_END_F                                                                                                   |
| master-bin.000001 | 750 | Xid         |         1 |         777 | COMMIT /* xid=23 */                                                                                                              |
+-

This means the master side problem, not the slave side bug#32468.

The behavior is identical to the behavior is for statement-based replication, so there is no difference in behavior between statement-based or row-based replication, so the bug description is not correct.

The cascading deletes are internal to the InnoDB engine, and as such there is no way that the server can be informed about the fact that additional rows were affected internally. Any engine that creates or destroys additional rows in any tables under its control, for whatever reason, has to inform the server of that occurrence for the rows to be logged.

Yes, this behavior is identical to SBR, although I don't see how this makes the bug description incorrect.  The problem is that there are different expectations for RBR - specifically that all changes to the data get replicated.  This is true for triggers, for example, where there are differences in the RBR and SBR uses (SBR requires triggers to be implemented on both master and slave to get consistent results, RBR generates consistent results with triggers on master only).

I also understand that these cascading actions are managed inside the storage engine, and that no mechanism currently exists to push those changes back from the storage engine to the binary log.  Of course, the NDB storage engine injects binary log events, and I suspect that InnoDB could do something similar, if necessary.  Of course, there's also the option to document this as a known limitation of RBR.

The fact that cascading deletes for InnoDB is not replicated to the slave will be documented in the manual as a caveat under row-based replication and possibly under statement-based replication as well.

A new worklog has been created to handle the feature request, which means that it can be scheduled for inclusion in a future version of the MySQL server. Note, however, that this feature requires changes inside the storage engine and that MySQL can only provide and support interfaces allowing storage engine-internal changes of tables to be logged to the binary log.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.