MySQL Forums
Forum List  »  Replication

Replication stops if master fails dropping a database with foreign key constraints
Posted by: Chehai WU
Date: May 09, 2014 03:51PM

If master fails dropping a database with foreign key constraint, Slave stops due to foreign key constraint.

Steps to Reproduce:

1, Set up a master-slave replication

2, On master:
create database d;

create table d.t1(id INT NOT NULL AUTO_INCREMENT, f_id INT NOT NULL, PRIMARY KEY(id));
create table d.t2(id INT NOT NULL AUTO_INCREMENT, f_id INT NOT NULL, PRIMARY KEY(id));
create table d.t3(id INT NOT NULL AUTO_INCREMENT, f_id INT NOT NULL, PRIMARY KEY(id));

insert into d.t1(id, f_id) values(1,1);
insert into d.t2(id, f_id) values(1,1);
insert into d.t3(id, f_id) values(1,1);

alter table d.t1 add foreign key (f_id) references t2(id);
alter table d.t2 add foreign key (f_id) references t3(id);
alter table d.t3 add foreign key (f_id) references t1(id);

3, Go to the data directory of databse d on master, touch a file.

4, On master:
drop database d;

5, On slave:
show slave status\G

6, Error 'Cannot delete or update a parent row: a foreign key constraint fails' on query. Default database: 'd'. Query: 'DROP TABLE IF EXISTS `t1`,`t2`,`t3`'

I expect slave to successfully drop the database. But it does not. The fix of http://bugs.mysql.com/bug.php?id=2167 is related to this issue.

Maybe MySQL should disable foreign key check on slave for this case.

Options: ReplyQuote


Subject
Views
Written By
Posted
Replication stops if master fails dropping a database with foreign key constraints
3696
May 09, 2014 03:51PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.