Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-3845

Replication breaks if multi-table DELETE is used in conjunction with Foreign Key

Details

    Description

      This bug https://bugs.mysql.com/bug.php?id=80821 reported by Philip Stoev affects Percona Server as well.

      Quotation from his description and test case: 

      Description:
      If two tables are in a foreign-key relationship and a multi-table DELETE statement operates on both, replication will break.

      2016-03-17T07:35:58.415740Z 8 [ERROR] Slave SQL for channel '': Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 1692, Error_code: 1032
      2016-03-17T07:35:58.415776Z 8 [Warning] Slave: Can't find record in 't1' Error_code: 1032
      2016-03-17T07:35:58.415787Z 8 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 1378

      5.6 does not seem to be affected.

      How to repeat:
      Test case:

      
      
      --source include/not_ndb_default.inc
      --source include/not_group_replication_plugin.inc
      --source include/have_binlog_format_row.inc
      --source include/master-slave.inc
      
      --connection master
      
      CREATE TABLE t0 (
      f0 INT PRIMARY KEY
      ) engine=innodb;
      
      CREATE TABLE t1 (
      f1 INT PRIMARY KEY,
      f0 INTEGER,
      FOREIGN KEY (f0)
      REFERENCES t0(f0)
      ON DELETE CASCADE
      ) engine=innodb;
      
      INSERT INTO t0 VALUES (0), (1);
      INSERT INTO t1 VALUES (0, 0);
      INSERT INTO t1 VALUES (1, 0);
      
      DELETE t0.*, t1.* FROM t0, t1 WHERE t0.f0 = 0 AND t1.f1 = 0;
      
      _(After this command replication stop working)_
      
      SELECT COUNT(*) = 1 FROM t0;
      SELECT COUNT(*) = 0 FROM t1;
      
      --connection slave
      
      SELECT COUNT(*) = 1 FROM t0;
      SELECT COUNT(*) = 0 FROM t1;
      DROP TABLE t0;
      DROP TABLE t1;
      
      --source include/rpl_end.inc
      

      Attachments

        Activity

          People

            zsolt.parragi Zsolt Parragi
            seweryn Seweryn Ożóg
            Votes:
            8 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 1 day, 4 hours, 28 minutes
                1d 4h 28m

                Smart Checklist