[MySQL] ON CASCADE child operations are not replicated

[AdamKatzDev]

Not sure if this is a well known caveat but: https://bugs.mysql.com/bug.php?id=32506 - Foreign key cascades do not appear when binlog_format = 'ROW'

I hoped to use this connector for a project with a lot of ON CASCADE in schema. Probably the fact that ON CASCADE child operations are not replicated for MySQL should be mentioned somewhere in the connector documentation. There could even be some protection added to the connector code itself.

Other sources: https://dev.mysql.com/doc/refman/8.0/en/innodb-and-mysql-replication.html

Replication and CASCADE. Cascading actions for InnoDB tables on the source are executed on the replica only if the tables sharing the foreign key relation use InnoDB on both the source and replica. This is true whether you are using statement-based or row-based replication.

https://code.openark.org/blog/mysql/things-that-dont-work-well-with-mysqls-foreign-key-implementation

No binary log entries for cascaded writes

https://debezium.io/documentation/faq/

Why don’t I see DELETE events in some cases? This may be caused by the usage of CASCADE DELETE statements. In this case the deletion events generated by the database are not part of the binlog and thus cannot be captured by Debezium.

[aadant]

yes this is a known edge case for debezium and MySQL. here is a workaround.

https://stackoverflow.com/questions/57443976/debezium-does-not-capture-cascade-delete

[AdamKatzDev]

@aadant is there a complete solution for this issue? A script for Clickhouse or MySQL that keep the triggers in a synced state? For some removing cascade operations is not an option. I was really surprised when stumbled upon this bug, it wasn't a known issue for me at least.

[aadant]

@AdamKatzDev it would be a custom solution on your end.

[aadant]

confirmed here https://code.openark.org/blog/mysql/things-that-dont-work-well-with-mysqls-foreign-key-implementation#more-8226