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: | |
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 |
[19 Nov 2007 21:12]
Todd Farmer
[21 Nov 2007 10:34]
Andrei Elkin
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.
[21 Nov 2007 19:15]
Mats Kindahl
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.
[21 Nov 2007 21:14]
Todd Farmer
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.
[23 Nov 2007 14:30]
Mats Kindahl
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.
[23 Nov 2007 17:07]
Jon Stephens
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.