Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.4.8
-
n.a.
Description
Either the content or the name of the column THREAD_ID in table information_schema.metadata_lock_info is wrong. Correct name would be CONNECTION_ID or SESSION_ID because content is the connection_id and not the thread id.
Proof:
1. Provoke a MDL:
1a) start transaction; select * from test where id < 10;
1b) alter table test add column c9 int;
2.)
SELECT * FROM information_schema.metadata_lock_info;
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
| 413 | MDL_BACKUP_ALTER_COPY | NULL | Backup lock | | |
|
| 414 | MDL_SHARED_READ | NULL | Table metadata lock | test | test |
|
| 413 | MDL_SHARED_UPGRADABLE | NULL | Table metadata lock | test | test |
|
| 413 | MDL_INTENTION_EXCLUSIVE | NULL | Schema metadata lock | test | |
|
+-----------+-------------------------+---------------+----------------------+--------------+------------+
|
|
|
SELECT *
|
-> FROM performance_schema.threads
|
-> WHERE THREAD_ID in (413, 414);
|
Empty set (0.001 sec)
|
|
|
SELECT * FROM performance_schema.threads WHERE processlist_ID in (413, 414);
|
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+
|
| THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED |
|
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+
|
| 429 | thread/sql/one_connection | FOREGROUND | 413 | root | localhost | test | Query | 41 | Waiting for table metadata lock | alter table test add column c9 int | 1 | NULL | YES |
|
| 430 | thread/sql/one_connection | FOREGROUND | 414 | root | localhost | test | Sleep | 51 | NULL | NULL | 1 | NULL | YES |
|
+-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+
|
2 rows in set (0.001 sec)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Either the content or the name of the column THREAD_ID in table information_schema.metadata_lock_info is wrong. Correct name would be CONNECTION_ID or SESSION_ID because content is the connection_id and not the thread id.
Proof: 1. Provoke a MDL: 1a) start transaction; select * from test where id < 10; 1b) alter table test add column c9 int; 2.) SELECT * FROM information_schema.metadata_lock_info; +-----------+-------------------------+---------------+----------------------+--------------+------------+ | THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME | +-----------+-------------------------+---------------+----------------------+--------------+------------+ | 413 | MDL_BACKUP_ALTER_COPY | NULL | Backup lock | | | | 414 | MDL_SHARED_READ | NULL | Table metadata lock | test | test | | 413 | MDL_SHARED_UPGRADABLE | NULL | Table metadata lock | test | test | | 413 | MDL_INTENTION_EXCLUSIVE | NULL | Schema metadata lock | test | | +-----------+-------------------------+---------------+----------------------+--------------+------------+ SELECT * -> FROM performance_schema.threads -> WHERE THREAD_ID in (413, 414); Empty set (0.001 sec) SELECT * FROM performance_schema.threads WHERE processlist_ID in (413, 414); +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+ | 429 | thread/sql/one_connection | FOREGROUND | 413 | root | localhost | test | Query | 41 | Waiting for table metadata lock | alter table test add column c9 int | 1 | NULL | YES | | 430 | thread/sql/one_connection | FOREGROUND | 414 | root | localhost | test | Sleep | 51 | NULL | NULL | 1 | NULL | YES | +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+ 2 rows in set (0.001 sec) |
Either the content or the name of the column THREAD_ID in table information_schema.metadata_lock_info is wrong. Correct name would be CONNECTION_ID or SESSION_ID because content is the connection_id and not the thread id.
Proof: 1. Provoke a MDL: 1a) start transaction; select * from test where id < 10; 1b) alter table test add column c9 int; 2.) {noformat} SELECT * FROM information_schema.metadata_lock_info; +-----------+-------------------------+---------------+----------------------+--------------+------------+ | THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME | +-----------+-------------------------+---------------+----------------------+--------------+------------+ | 413 | MDL_BACKUP_ALTER_COPY | NULL | Backup lock | | | | 414 | MDL_SHARED_READ | NULL | Table metadata lock | test | test | | 413 | MDL_SHARED_UPGRADABLE | NULL | Table metadata lock | test | test | | 413 | MDL_INTENTION_EXCLUSIVE | NULL | Schema metadata lock | test | | +-----------+-------------------------+---------------+----------------------+--------------+------------+ SELECT * -> FROM performance_schema.threads -> WHERE THREAD_ID in (413, 414); Empty set (0.001 sec) SELECT * FROM performance_schema.threads WHERE processlist_ID in (413, 414); +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+ | 429 | thread/sql/one_connection | FOREGROUND | 413 | root | localhost | test | Query | 41 | Waiting for table metadata lock | alter table test add column c9 int | 1 | NULL | YES | | 430 | thread/sql/one_connection | FOREGROUND | 414 | root | localhost | test | Sleep | 51 | NULL | NULL | 1 | NULL | YES | +-----------+---------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+---------------------------------+------------------------------------+------------------+------+--------------+ 2 rows in set (0.001 sec) {noformat} |
| Fix Version/s | 10.4 [ 22408 ] | |
| Assignee | Oleksandr Byelkin [ sanja ] | |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Valerii Kravchuk [ valerii ] |
| Assignee | Valerii Kravchuk [ valerii ] | Oleksandr Byelkin [ sanja ] |
| Workflow | MariaDB v3 [ 101822 ] | MariaDB v4 [ 141697 ] |