MySQL hangs on ALTER TABLE

Question

My not-so-big table hangs on an ALTER command. What could it be?

Only 150k rows, 42 fields 142 MByte total. InnoDB storage engine and Server version: 5.5.44-MariaDB MariaDB Server. 1 field, 'slotindex', is primary key: bigint(20) and BTREE type.

The command:

    MariaDB [mydb]> ALTER TABLE `runs` CHANGE `p_w_trans_x` `p_w_tran_x` FLOAT NOT NULL;
    Stage: 1 of 2 'copy to tmp table'   65.7% of stage done
    Stage: 2 of 2 'Enabling keys'      0% of stage done

Will completely hang forever in this stage 2.

The processlist is then as follows:

MariaDB [(none)]> show full processlist;
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+
| Id     | User | Host            | db        | Command | Time  | State                           | Info                                                                | Progress |
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+
| 274226 | root | localhost:45423 | edc_proxy | Sleep   | 16043 |                                 | NULL                                                                |    0.000 |
| 274319 | root | localhost       | myDB      | Query   |    99 | Waiting for table metadata lock | ALTER TABLE `runs` CHANGE `p_w_trans_x` `p_w_tran_x` FLOAT NOT NULL |    0.000 |
| 274416 | root | localhost       | NULL      | Query   |     0 | NULL                            | show full processlist                                               |    0.000 |
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+

This answer suggests checking the information_schema tables, not much there:

MariaDB [INFORMATION_SCHEMA]> SELECT * FROM INNODB_LOCK_WAITS;
Empty set (0.00 sec)

MariaDB [INFORMATION_SCHEMA]> SELECT * FROM INNODB_LOCKS ;
Empty set (0.00 sec)

MariaDB [INFORMATION_SCHEMA]> SELECT * FROM INNODB_TRX;
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| trx_id   | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
| 83A8B36E | RUNNING   | 2016-12-08 11:13:02 | NULL                  | NULL             |          0 |              274226 | NULL      | NULL                |                 0 |                 0 |                0 |                   376 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                     10000 |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+
1 row in set (0.00 sec)

And the section on transactions from show engine innodb status;:

------------
TRANSACTIONS
------------
Trx id counter 83A8F071
Purge done for trx's n:o < 83A8CA86 undo n:o < 0
History list length 1490
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 274543, OS thread handle 0x7fbb863e6700, query id 85356480 localhost root
show engine innodb status
---TRANSACTION 83A8EB07, not started
mysql tables in use 1, locked 2
MySQL thread id 274542, OS thread handle 0x7fbb843f6700, query id 85354935 localhost root Waiting for table metadata lock
ALTER TABLE `runs` CHANGE `p_w_trans_x` `p_w_tran_x` FLOAT NOT NULL
---TRANSACTION 83A8B36E, ACTIVE 24627 sec
MySQL thread id 274226, OS thread handle 0x7fbb845f5700, query id 85337236 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 83A8B36F, sees < 83A8B36D
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Any pointers for further investigation, for circumventing the problem and for solving are appreciated!

Answer 1

A metadata lock is an implicit (from the user perspective) lock that prevents DDL against the table because something else needs the table to remain in its current form. In this case, it's a transaction that has been left running.

Task 1: Your alter will succeed if you kill the connection on thread 274226.

mysql> KILL 274226;

The problem here, as indicated by information_schema.innodb_trx, is that this thread has left a transaction running for several hours and we can infer that this table has been referenced by that transaction. A table can't be altered until no transactioms still have an MVCC view or any locks involving the table. This transaction holds a view, which we can again infer could impact this table, as shown in the last line:

--TRANSACTION 83A8B36E, ACTIVE 24627 sec
MySQL thread id 274226, OS thread handle 0x7fbb845f5700, query id 85337236 localhost 127.0.0.1 root
Trx read view will not see trx with id >= 83A8B36F, sees < 83A8B36D

Note that Sleep is not a real command, in this context, it's just the placeholder status for any idle connection. All connections are doing something, and in this case the "something" is sleeping -- in other words, idle and waiting for another query. But an idle connection is still a connection, and if your code (or query browser tool) leaves a transaction running, it just keeps running.

Task 2: find the bug or mistake that left that transaction running. In a live application, leaving transactions running potentially make a much bigger mess.

Answer 2

I was having a very similar issue with table lock, but it ended up being MySQL Workbench out of all things. Any RENAME TABLE or ALTER TABLE command in MySQL Workbench would just sit claiming a meta lock. I logged onto the server and was able to execute those types of queries no problem. Current version of Workbench is 8.0.22.

I had sifted through all these queries and was totally stumped when they showed no issues:

SHOW OPEN TABLES;

SHOW ENGINE inndodb STATUS;

SELECT * FROM INNODB_LOCK_WAITS;

SELECT * FROM INNODB_LOCKS;

SELECT * FROM INNODB_TRX;

SHOW FULL PROCESSLIST;