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

    • Technical task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 10.0.10
    • None
    • 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

          monty Michael Widenius added a comment -

          Fixed by releasing meta data locks and doing automatic UNLOCK TABLE if there are no more locked tables after table was dropped.

          monty Michael Widenius added a comment - Fixed by releasing meta data locks and doing automatic UNLOCK TABLE if there are no more locked tables after table was dropped.

          People

            monty Michael Widenius
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.