MySQL does not open the table problem solution

Do development time a table how also can not open, data also not many, on-line check the following this article perfect solution, but remember to log in with the root MySQL: MySQL Waiting for table metadata Lock resolution methodoriginal March 29, 2017 12:06:32

• Label:
• Mysql

• 5860

Database queries in recent projects are often suspended, and the operation times out after the application starts. Testers say that the database is again hanging (seemingly their eyes of the connection failure, query no results are hanging), through show processlist a look, full screen is Waiting for table metadata lock the state of the connection. The first reaction is to kill these connections, but the connection is too much, really kill, and then restart the service, it seems that restarting can solve the problem of 90%, but if you do not find the cause of the problem, the problem will definitely reappear.

On the internet to find out that MySQL is doing some DDL operations such as ALTER TABLE, if there are uncommitted transactions on the table will appear Waiting for table metadata lock , and once metadata lock is present, subsequent operations on the table will be blocked (see http:// www.bubuko.com/infodetail-1151112.html). So the problem needs to be solved in two ways:

1. Review UNCOMMITTED transactions

View the currently uncommitted transactions from the Information_schema.innodb_trx table

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G

• 1

(\g as a terminator, MySQL client will show the results in column mode, for the column longer table, the display more intuitive)

Field Meaning:

• Trx_state: Transaction status, typically running
• Trx_started: The start time of the transaction execution, if the time is longer, it is reasonable to analyze the transaction
• Trx_mysql_thread_id:mysql thread ID for the kill
• Trx_query: SQL in a transaction

In general, DDL operations do not waiting for table metadata lock as long as these threads are killed.

2. Adjust the lock timeout threshold

lock_wait_timeoutRepresents the time-out (in seconds) to get metadata lock, allowing values ranging from 1 to 31536000 (1 years). The default value is 31536000. See Https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lock_wait_timeout. The default value is one year!!! have been crying blind! Adjust it to 30 minutes

set session lock_wait_timeout = 1800;set global lock_wait_timeout = 1800;

• 1
• 2

To allow for rapid failure when this problem occurs (failfast)

MySQL does not open the table problem solution