MySQL hangs on ALTER TABLE

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.