[MDEV-9921] DELETE of rows in child tables are not logged in the binary log Created: 2016-04-14  Updated: 2016-04-16  Resolved: 2016-04-16

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Replication, Storage Engine - InnoDB
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: Karl E. Jørgensen Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: replication, upstream


 Description   

When we have InnoDB tables with foreign key constraints (ON DELETE CASCADE) defined, deleting rows from the parent table will (correctly) delete rows from the child table. But the rows deleted from the child table do not appear in the binary log, and will thus not be replicated downstream.

This is a problem, since downstream slaves may or may not have the same foreign keys defined. Or they may use a storage engine which does not do foreign keys (e.g. MyISAM or TokuDB...). As a result, the slave will drift out of sync with the master...

For example:

CREATE TABLE author (
 
	id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
	name VARCHAR(100) NOT NULL
 
) ENGINE = InnoDB;
 
 
 
CREATE TABLE book (
 
	id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
	title VARCHAR(200) NOT NULL,
 
	author_id SMALLINT UNSIGNED NOT NULL,
 
	CONSTRAINT `fk_book_author`
 
		FOREIGN KEY (author_id) REFERENCES author (id)
 
		ON DELETE CASCADE
 
		ON UPDATE RESTRICT
 
) ENGINE = InnoDB;
 
 
 
insert into author
 
  set name = 'H.P. Lovecraft'
 
;
 
 
 
insert into book
 
  set title = 'Dagon',
 
  author_id = @@last_insert_id;
 
 
 
select * from author;
 
 
 
select * from book;
 
 
 
delete from author where name = 'H.P. Lovecraft';
 
 
 
select * from author;
 
 
 
select * from book;

The above behaves as expected on the master - the row in the book table is correctly deleted.

But it does not appear in the binary log!

 Comments   
Comment by Karl E. Jørgensen [ 2016-04-14 ]

I could reproduce the problem both on mariadb 10.0.23 and mariadb-galera 10.0.16. In both cases with binlog_format = ROW. (haven't tried binlog_format=STATEMENT yet..)

Comment by Elena Stepanova [ 2016-04-15 ]

This is explicitly documented behavior of InnoDB in regard to replication: http://dev.mysql.com/doc/refman/5.7/en/innodb-and-mysql-replication.html

Replication and CASCADE. Cascading actions for InnoDB tables on the master are replicated on the slave only if the tables sharing the foreign key relation use InnoDB on both the master and slave.
...

(see the full explanation and examples in the manual).

The corresponding upstream bug report was https://bugs.mysql.com/bug.php?id=32506 , although it's marked as "closed", in fact it only caused the addition to the documentation mentioned above.

It also says that a worklog for a feature request had been created. I cannot find the worklog, it is probably internal at Oracle, but if it ever gets implemented, the change will be considered for merging into MariaDB. Doing it on MariaDB side only might cause more harm than good when it comes to cross-version replication, see for example a somewhat similar (yet different issue) here: http://bugs.mysql.com/bug.php?id=80821.

Comment by Karl E. Jørgensen [ 2016-04-15 ]

In that case, it may be worth documenting this in MariaDB too. Although I realise there is an enormous overlap between MySQL and MariaDB functionality (and oddities), I didn't think to search for only MySQL here... I'm sure others may be caught out too...

Comment by Ian Gilfillan [ 2016-04-16 ]

This has now been documented at https://mariadb.com/kb/en/mariadb/replication-and-foreign-keys/

Generated at Thu Feb 08 07:38:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.