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

8.0 - Duplicate error on Slave when drop temporary tables

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.x, Not 5.6.x, Not 5.7.x
    • Fix Version/s: 8.0.12-2rc1
    • Component/s: None
    • Labels:

      Description

      Testes on latest PS and Oracle MySQL 5.6 and 5.7. In RBR duplicate errors can be found on slave instances if drop temporary table is executed, then roll back is executed within the same transaction and a concurrent insert occurrs for one of the rows that is being rolled back. Please refer following example:

      – Session 1
      master [localhost]

      {msandbox} ((none)) > use test ;
      Database changed
      master [localhost] {msandbox}

      (test) > CREATE TABLE t1 (a INT, primary key (a)) ENGINE=INNODB;
      Query OK, 0 rows affected (0.00 sec)

      master [localhost]

      {msandbox} (test) > CREATE TEMPORARY TABLE t2 (a INT auto_increment, b int, primary key (a)) ENGINE=INNODB;
      Query OK, 0 rows affected (0.00 sec)

      master [localhost] {msandbox}

      (test) > CREATE TABLE t3 (a INT AUTO_INCREMENT, b INT, primary key (a)) ENGINE=INNODB;
      Query OK, 0 rows affected (0.00 sec)

      master [localhost]

      {msandbox} (test) > insert into t3 (b) values (1);
      Query OK, 1 row affected (0.00 sec)

      master [localhost] {msandbox}

      (test) > insert into t3 (b) select b from t3 ;
      Query OK, 1 row affected (0.00 sec)
      Records: 1 Duplicates: 0 Warnings: 0

      ...

      master [localhost]

      {msandbox} (test) > insert into t3 (b) select b from t3 ;
      Query OK, 2097152 rows affected (20.09 sec)
      Records: 2097152 Duplicates: 0 Warnings: 0

      master [localhost] {msandbox}

      (test) > BEGIN;
      Query OK, 0 rows affected (0.00 sec)

      master [localhost]

      {msandbox} (test) > INSERT INTO t1 SELECT A from t3;

      Query OK, 4194304 rows affected (37.65 sec)
      Records: 4194304 Duplicates: 0 Warnings: 0

      master [localhost] {msandbox}

      (test) > DROP TEMPORARY TABLE t2;
      Query OK, 0 rows affected (0.00 sec)

      master [localhost]

      {msandbox} (test) > ROLLBACK;
      Query OK, 0 rows affected, 1 warning (38.48 sec)

      – Session 2 - While the rollback statement is running following command is executed
      master [localhost] {msandbox}

      (test) > insert into t1 values (4587467);
      Query OK, 1 row affected (0.00 sec)

      – Slave Errors:

      Last_SQL_Error: Could not execute Write_rows event on table test.t1; Duplicate entry '4587467' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 58982461

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Unassigned
                Reporter:
                juan.arruti Juan Arruti
                Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved: