Unexplained halts using mysql command line client

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.

Tagged with: Databases MySQL

Related Posts

Creating a More Realistic Benchmark

Creating a More Realistic Benchmark

Common benchmark approaches fall into two general categories, synthetic testing and realistic testing. You have the most generic operations from a synthetic test, starting with the most simple example using a single table, a single column, and for a single DML operation.

Read more
Testing, Benchmarking, Evaluating

Testing, Benchmarking, Evaluating

Testing and benchmarking are widely used terms in software technology, each serving a distinct purpose and goal. With the increasing adoption of AI in software development, the term evaluating has become significant and with this the re-emergence of what is quality assurance.

Read more
Your Attack Vector Extends Beyond Production Systems

Your Attack Vector Extends Beyond Production Systems

A common data security issue is the unprotected copying of production data to non-production environments without any redaction, masking, or filtering. This practice poses a serious risk. A malicious actor will target the weakest link in your infrastructure, including non-production accounts and the developer systems accessing them.

Read more