Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21820

create table ... select is metadata locking information_schema

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.4.8
    • N/A
    • Server
    • None

    Description

      When issuing a CREATE TABLE .... SELECT, the information schema becomes unusable because it gets metadata-locked. This is creating issues to monitoring systems that normally query information_schema for monitoring purposes (queries stack up in processlist waiting for metadata lock).

      How to reproduce:

      In terminal 1: 

      create table test (id int not null primary key default 0) select 1, sleep(100);

      In termninal 2:

      SELECT SUM((DATA_LENGTH+INDEX_LENGTH)) FROM INFORMATION_SCHEMA.TABLES;

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21602 CREATE TABLE…PRIMARY KEY…SELECT workaround causes DROP TABLE to ignore locks

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            I would guess that CREATE TABLE…SELECT is unnecessarily holding MDL_EXCLUSIVE on the table name for the duration of the entire operation. That ought to block SHOW TABLES as well.

            Related to this, we have another problem with CREATE TABLE…SELECT, which requires a problematic work-around in InnoDB, as mentioned in MDEV-21602. I think that we should consider the following fix:

            1. Acquire MDL on the table name, similar to what is held during ALTER TABLE.
            2. Create a table with a temporary #sql name. (This is important for InnoDB crash recovery. If the server is killed before the operation completes, we want the table to be dropped!)
            3. Copy the data. Undo logging should be disabled, like in MDEV-11415 for ALTER TABLE…ALGORITHM=COPY.
            4. Upgrade the MDL to MDL_EXCLUSIVE and rename the table to the final name.
            marko Marko Mäkelä added a comment - I would guess that CREATE TABLE…SELECT is unnecessarily holding MDL_EXCLUSIVE on the table name for the duration of the entire operation. That ought to block SHOW TABLES as well. Related to this, we have another problem with CREATE TABLE…SELECT , which requires a problematic work-around in InnoDB, as mentioned in MDEV-21602 . I think that we should consider the following fix: Acquire MDL on the table name, similar to what is held during ALTER TABLE . Create a table with a temporary #sql name. (This is important for InnoDB crash recovery. If the server is killed before the operation completes, we want the table to be dropped!) Copy the data. Undo logging should be disabled, like in MDEV-11415 for ALTER TABLE…ALGORITHM=COPY . Upgrade the MDL to MDL_EXCLUSIVE and rename the table to the final name.
            serg Sergei Golubchik added a comment -

            It cannot be done, the table isn't completely created yet, so an exclusive metadata must be held to protect table metadata.

            If you want to query INFORMATION_SCHEMA.TABLES while some tables are being created, you can use

            SET STATEMENT LOCK_WAIT_TIMEOUT=0 SELECT SUM(DATA_LENGTH+INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES;

            this will be fast, it'll skip all exclusively locked tables, which you might like better than waiting.

            serg Sergei Golubchik added a comment - It cannot be done, the table isn't completely created yet, so an exclusive metadata must be held to protect table metadata. If you want to query INFORMATION_SCHEMA.TABLES while some tables are being created, you can use SET STATEMENT LOCK_WAIT_TIMEOUT=0 SELECT SUM (DATA_LENGTH+INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES; this will be fast, it'll skip all exclusively locked tables, which you might like better than waiting.

            People

              serg Sergei Golubchik
              rpizzi Rick Pizzi (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.