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

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

          Ascending order - Click to sort in descending order
          muhammad.irfan Muhammad Irfan created issue -
          alice Alice Sherepa made changes -
          Field Original Value New Value
          Fix Version/s 10.5 [ 23123 ]
          alice Alice Sherepa made changes -
          Component/s Replication [ 10100 ]
          alice Alice Sherepa made changes -
          Labels regression
          alice Alice Sherepa made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          alice Alice Sherepa made changes -
          Assignee Michael Widenius [ monty ]
          serg Sergei Golubchik made changes -
          Assignee Michael Widenius [ monty ] Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.5.10 [ 25204 ]
          Fix Version/s 10.5 [ 23123 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 121434 ] MariaDB v4 [ 159208 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 148814 162424

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.