Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5
Description
I found that replication error when tables are dropped in parent, child tables order on master where tables contains foreign key relation between.
master:
master [localhost:22309] {root} (test) > SELECT @@foreign_key_checks; |
+----------------------+
|
| @@foreign_key_checks | |
+----------------------+
|
| 1 | |
+----------------------+
|
1 row in set (0.000 sec) |
|
|
master [localhost:22309] {root} (test) > DROP TABLE t1_parent, t1_child; |
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails |
|
|
master [localhost:22309] {root} (test) > show tables; |
+-------------------------+
|
| Tables_in_test |
|
+-------------------------+
|
| t1_parent |
|
+-------------------------+
|
As you noticed, parent table t1_parent is not dropped because of FK relation between tables (Note, it's first table in list to drop hence failed). However, child table is dropped as expected and that statement is binlogged and replicated to slave but will fail on slave eventually too because of FK and replication will fail.
slave:
|
slave1 [localhost:22310] {root} ((none)) > show slave status\G |
*************************** 1. row *************************** |
Slave_IO_State: Waiting for master to send event |
Master_Host: 127.0.0.1 |
Master_User: rsandbox
|
Master_Port: 22309 |
Connect_Retry: 60 |
Master_Log_File: mysql-bin.000003 |
Read_Master_Log_Pos: 5466 |
Relay_Log_File: mysql-relay.000008 |
Relay_Log_Pos: 5503 |
Relay_Master_Log_File: mysql-bin.000003 |
Slave_IO_Running: Yes
|
Slave_SQL_Running: No
|
Last_Errno: 0 |
Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Cannot delete or update a parent row: a foreign key constraint fails (%.192s)' error code=1451 ; Error on slave: actual message='no error', error code=0. Default database: 'test'. Query: 'DROP TABLE `t1_parent`,`t1_child` /* generated by server */' |
However, child table is also dropped same as master but error as mentioned because of foreign key relation between parent/child tables.
slave1 [localhost:22310] {root} (test) > show tables; |
+-------------------------+
|
| Tables_in_test |
|
+-------------------------+
|
| t1_parent |
|
+-------------------------+
|
1 row in set (0.000 sec) |
Now, there are couple of workarounds:
1-- Either, drop child tables first and then parent at last.
|
master [localhost:22309] {root} (test) > DROP TABLE t1_parent, t1_child; |
Query OK, 0 rows affected (0.026 sec) |
Works as expected and no errors and will replicate fine to slave too.
2-- Or, disable foreign key checks before drop.
|
master [localhost:22309] {root} (test) > SET SESSION FOREIGN_KEY_CHECKS=0; |
Query OK, 0 rows affected (0.000 sec) |
|
|
master [localhost:22309] {root} (test) > SELECT @@foreign_key_checks; |
+----------------------+
|
| @@foreign_key_checks | |
+----------------------+
|
| 0 | |
+----------------------+
|
1 row in set (0.000 sec) |
|
|
master [localhost:22309] {root} (test) > DROP TABLE t1_parent, t1_child; |
Query OK, 0 rows affected (0.026 sec) |
This also worked fine and will replicate to slave perfectly.