[MDEV-21820] create table ... select is metadata locking information_schema Created: 2020-02-26  Updated: 2022-01-07  Resolved: 2022-01-07

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Rick Pizzi Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-21602 CREATE TABLE…PRIMARY KEY…SELECT worka... Closed

 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;



 Comments   
Comment by Marko Mäkelä [ 2020-02-27 ]

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.
Comment by Sergei Golubchik [ 2022-01-07 ]

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.

Generated at Thu Feb 08 09:10:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.