Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5491 CREATE OR REPLACE TABLE
  3. MDEV-5619

CREATE OR REPLACE does not release MDL_EXCLUSIVE upon failure

    Details

    • Type: Technical task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.10
    • Component/s: None
    • Labels:
      None

      Description

      If CREATE OR REPLACE, executed under lock, fails in the process, it keeps MDL_EXCLUSIVE lock on the table it attempted to re-create. It might happen both when the drop part fails, or when the create part fails (in the latter case, the connection holds a lock for a non-existing table).
      After it happens, the connection itself does not realize it keeps the lock, and cannot use it any longer.

      UNLOCK solves the problem, so it's not critical.

      Example 1: failure upon creation.
      We lock the table, attempt to re-create it, it gets dropped but not created; we still have MDL_EXCLUSIVE for it, but cannot create it until UNLOCK.

      MariaDB [db]> create table t1 (i int);
      Query OK, 0 rows affected (0.59 sec)
       
      MariaDB [db]> lock table t1 write;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db]> select * from information_schema.metadata_lock_info;
      +-----------+--------------------------+---------------+----------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE                | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
      +-----------+--------------------------+---------------+----------------------+--------------+------------+
      |         5 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Global read lock     |              |            |
      |         5 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Schema metadata lock | db           |            |
      |         5 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT  | Table metadata lock  | db           | t1         |
      +-----------+--------------------------+---------------+----------------------+--------------+------------+
      3 rows in set (0.00 sec)
       
      MariaDB [db]> create or replace table t1;
      ERROR 1113 (42000): A table must have at least 1 column
      MariaDB [db]> show tables;
      Empty set (0.00 sec)
       
      MariaDB [db]> select * from information_schema.metadata_lock_info;
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE               | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      |         5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Global read lock     |              |            |
      |         5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Schema metadata lock | db           |            |
      |         5 | MDL_EXCLUSIVE           | MDL_EXPLICIT  | Table metadata lock  | db           | t1         |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      3 rows in set (0.00 sec)
       
      MariaDB [db]> create table t1 (i int);
      ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES
      MariaDB [db]> unlock tables;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db]> select * from information_schema.metadata_lock_info;
      Empty set (0.00 sec)

      Example 2: failure upon drop.
      We lock the table, attempt to re-create it, it does not get dropped; we still have MDL_EXCLUSIVE for it, but cannot read from it.

      MariaDB [db]> create table t1 (a int, index(a)) engine=InnoDB;
      Query OK, 0 rows affected (0.98 sec)
       
      MariaDB [db]> create table t2 (b int, foreign key (b) references t1(a) on delete cascade) engine=InnoDB;
      Query OK, 0 rows affected (0.79 sec)
       
      MariaDB [db]> insert into t1 values (1),(2);
      Query OK, 2 rows affected (0.07 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> insert into t2 values (1),(2);
      Query OK, 2 rows affected (0.09 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [db]> lock table t1 write;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [db]> create or replace table t1 (a int, index(a)) engine=InnoDB;
      ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
      MariaDB [db]> show tables;
      +--------------+
      | Tables_in_db |
      +--------------+
      | t1           |
      | t2           |
      +--------------+
      2 rows in set (0.00 sec)
       
      MariaDB [db]> select * from information_schema.metadata_lock_info;
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE               | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      |         5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Global read lock     |              |            |
      |         5 | MDL_INTENTION_EXCLUSIVE | MDL_EXPLICIT  | Schema metadata lock | db           |            |
      |         5 | MDL_EXCLUSIVE           | MDL_EXPLICIT  | Table metadata lock  | db           | t1         |
      +-----------+-------------------------+---------------+----------------------+--------------+------------+
      3 rows in set (0.00 sec)
       
      MariaDB [db]> select * from t1;
      ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

      revision-id: monty@askmonty.org-20140205193616-7vcabbq3nrpsvpjj
      revno: 3981
      branch-nick: 10.0-monty

        Attachments

          Activity

            People

            • Assignee:
              monty Michael Widenius
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: