I recently came across an issue trying to connect to a MySQL server using the mysql client. It appeared as through the connection was hanging.
A subsequent connection using the -A option highlighted the problem with the previous connection stuck in the state “Waiting for table metadata lock”.
mysql> SHOW PROCESSLISTG
*************************** 1. row ***************************
Id: 37
User: root
Host: localhost
db: tmp
Command: Query
Time: 90
State: preparing
Info: create table missing as select id from AK where id not in (select id ..
*************************** 2. row ***************************
Id: 38
User: root
Host: localhost
db: tmp
Command: Field List
Time: 50
State: Waiting for table metadata lock
Info:
*************************** 3. row ***************************
Id: 39
User: root
Host: localhost
db: tmp
Command: Query
Time: 0
State: init
Info: show processlist
In this example you can see a long running CREATE TABLE … SELECT statement as the cause of the problem. The -A or –no-auto-rehash argument is a means of disabling the tab completion in the mysql command line client.
