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

LP #1533364: 5.7 XA with InnoDB allows a table to be dropped while an XA transaction has DML active in prepared transaction

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by George Ormond Lorch III last update 13-01-2016 05:53:50

      Consider the following sequence on two sessions:

      mysql-1> create table t1 (a int) engine=innodb;
      Query OK, 0 rows affected (0.09 sec)
      mysql-1> insert t1 values (10);
      Query OK, 1 row affected (0.01 sec)
      mysql-1> insert t1 values (20);
      Query OK, 1 row affected (0.00 sec)
      mysql-1> select * from t1;
      ------

      a

      ------

      10
      20

      ------
      2 rows in set (0.00 sec)

      mysql-2> xa start 'test';
      Query OK, 0 rows affected (0.00 sec)
      mysql-2> insert t1 values (40);
      Query OK, 1 row affected (0.00 sec)
      mysql-2> xa end 'test';
      Query OK, 0 rows affected (0.00 sec)
      mysql-2> xa prepare 'test';
      Query OK, 0 rows affected (0.01 sec)
      mysql-2> quit;
      Bye

      mysql-1> select * from t1;
      ------

      a

      ------

      10
      20

      ------
      2 rows in set (0.00 sec)
      mysql-1> drop table t1;
      Query OK, 0 rows affected (6.73 sec)

      mysql-1> show tables;
      Empty set (0.00 sec)

      mysql-1> xa recover;
      ---------------------------------------+

      formatID gtrid_length bqual_length data

      ---------------------------------------+

      1 4 0 test

      ---------------------------------------+
      1 row in set (0.00 sec)

      mysql-1> xa commit 'test';
      Query OK, 0 rows affected (0.01 sec)

      mysql-1> xa recover;
      Empty set (0.00 sec)

      We can see here that if a session creates an XA transaction, does some work, prepares and terminates, it seems to release all locks on the tables referenced within the XA transaction. This allows another session to drop those tables when in fact the drops should wait for the table locks held by the XA transaction to be relinquished.

      Later, when the XA transaction is committed, it receives no error or indication that the data was not committed, thus, potential data loss.

      This seems to have been introduced in 5.7.7 for replication according to this blog : http://mysqlserverteam.com/the-mysql-5-7-7-release-candidate-is-available/
      Referencing links:
      http://dev.mysql.com/worklog/task/?id=6860
      http://bugs.mysql.com/bug.php?id=12161

      In 5.6, if the session terminated with an XA transaction in the prepared state, the transaction would be aborted and locks freed and thus there would be no way for any other session to drop or alter the table until all locks were released.

      I believe this is an upstream bug, I am reporting this here first to track the upstream issue.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  lpjirasync lpjirasync (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: