MySQL Bugs: #116267: OPTIMIZE LOCAL TABLE thread-hangs

Bug #116267	OPTIMIZE LOCAL TABLE thread-hangs
Submitted:	30 Sep 2024 0:32	Modified:	30 Sep 2024 5:19
Reporter:	Roel Van de Paar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S2 (Serious)
Version:	8.0.36, 8.0.39, 8.4.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	hang

Description:

MS MySQL8.0.36 49ef33f7edadef3ae04665e73d1babd40179a4f1 (Optimized)

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------+
| Id | User            | Host      | db   | Command | Time | State                           | Info                    |
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  592 | Waiting on empty queue          | NULL                    |
|  9 | root            | localhost | test | Query   |  590 | Waiting for table metadata lock | OPTIMIZE LOCAL TABLE t1 |
| 10 | root            | localhost | test | Query   |    0 | init                            | show processlist        |
+----+-----------------+-----------+------+---------+------+---------------------------------+-------------------------+
3 rows in set, 1 warning (0.00 sec)

How to repeat:
SET pseudo_slave_mode=1;
CREATE TABLE t1 (c INT);
CREATE TABLE t2 (c INT) ENGINE=MEMORY; 
XA START 'a';
INSERT INTO t1 VALUES (0);  
CREATE TEMPORARY TABLE t1 (c INT);
INSERT INTO t1 VALUES (0);
XA END 'a';
XA PREPARE 'a';
OPTIMIZE LOCAL TABLE t1;

Hello Roel,

Thank you for the report and test case.

regards,
Umesh

It's waiting for MDL lock, so putting the related outputs here:

mysql> select * from sys.schema_table_lock_waits \G
*************************** 1. row ***************************
               object_schema: test
                 object_name: t1
           waiting_thread_id: 48
                 waiting_pid: 7
             waiting_account: root@localhost
           waiting_lock_type: SHARED_NO_READ_WRITE
       waiting_lock_duration: TRANSACTION
               waiting_query: OPTIMIZE LOCAL TABLE t1
          waiting_query_secs: 25
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 48
                blocking_pid: 7
            blocking_account: root@localhost
          blocking_lock_type: SHARED_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 7
sql_kill_blocking_connection: KILL 7
1 row in set (0.04 sec)

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+---------------------------------+-------------------------+
| Id | User            | Host            | db   | Command | Time | State                           | Info                    |
+----+-----------------+-----------------+------+---------+------+---------------------------------+-------------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  |   43 | Waiting on empty queue          | NULL                    |
|  7 | root            | localhost:60523 | test | Query   |   36 | Waiting for table metadata lock | OPTIMIZE LOCAL TABLE t1 |
|  8 | root            | localhost:60524 | test | Query   |    0 | init                            | show processlist        |
+----+-----------------+-----------------+------+---------+------+---------------------------------+-------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> select * from performance_schema.metadata_locks \G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725917794960
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6143
      OWNER_THREAD_ID: 49
       OWNER_EVENT_ID: 7
*************************** 2. row ***************************
          OBJECT_TYPE: SCHEMA
        OBJECT_SCHEMA: test
          OBJECT_NAME: NULL
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725914315776
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: mdl.cc:3704
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 15
*************************** 3. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725914319040
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: mdl.cc:3704
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 15
*************************** 4. row ***************************
          OBJECT_TYPE: GLOBAL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725914329120
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: STATEMENT
          LOCK_STATUS: GRANTED
               SOURCE: sql_base.cc:5477
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 16
*************************** 5. row ***************************
          OBJECT_TYPE: BACKUP LOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725914326816
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_base.cc:5484
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 16
*************************** 6. row ***************************
          OBJECT_TYPE: SCHEMA
        OBJECT_SCHEMA: test
          OBJECT_NAME: NULL
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725914329216
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_base.cc:5464
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 16
*************************** 7. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 1725914313664
            LOCK_TYPE: SHARED_NO_READ_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE: sql_parse.cc:6143
      OWNER_THREAD_ID: 48
       OWNER_EVENT_ID: 16
7 rows in set (0.00 sec)