MySQL hangs on ALTER TABLE
<p>My not-so-big table hangs on an ALTER command. What could it be?</p>

<p>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.</p>

<p>The command:</p>

<pre><code>    MariaDB [mydb]&gt; 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
</code></pre>

<p>Will completely hang forever in this stage 2.</p>

<p>The processlist is then as follows:</p>

<pre><code>MariaDB [(none)]&gt; 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 |
+--------+------+-----------------+-----------+---------+-------+---------------------------------+---------------------------------------------------------------------+----------+
</code></pre>

<p>This <a href="https://stackoverflow.com/questions/13148630/how-do-i-find-which-transaction-is-causing-a-waiting-for-table-metadata-lock-s/13155778#13155778">answer</a> suggests checking the information_schema tables, not much there:</p>

<pre><code>MariaDB [INFORMATION_SCHEMA]&gt; SELECT * FROM INNODB_LOCK_WAITS;
Empty set (0.00 sec)

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

MariaDB [INFORMATION_SCHEMA]&gt; 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)
</code></pre>

<p>And the section on transactions from <code>show engine innodb status;</code>:</p>

<pre><code>------------
TRANSACTIONS
------------
Trx id counter 83A8F071
Purge done for trx's n:o &lt; 83A8CA86 undo n:o &lt; 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 &gt;= 83A8B36F, sees &lt; 83A8B36D
----------------------------
END OF INNODB MONITOR OUTPUT
============================
</code></pre>

<p>Any pointers for further investigation, for circumventing the problem and for solving are appreciated!</p>