Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25530

Error 1451 on slave: Cannot delete or update a parent row: a foreign key constraint fails

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            muhammad.irfan Muhammad Irfan
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: