Atitit does not respond when querying table modification table fields -- the unlocked Table query table does not respond when modifying table fields. If the gui does not respond, it is best to use the cmd method. If no gui is used, it is not necessary to lock the table .. ALTERTABLEt_mb_awardweixinMODIFYawardChoiceNumLeftint (11) DEFAULTNULL # ----- analysis .. 1. first, query by another table, if
Atitit does not respond when querying table modification table fields -- the unlocked Table query table does not respond when modifying table fields. If the gui does not respond, it is best to use the cmd method. If no gui is used, it is not necessary to lock the table .. alter table t_mb_awardweixin MODIFY awardChoiceNumLeft int (11) default null # ----- analysis .. 1. first, query by another table, if
Atitit does not respond when querying the table to modify the table field -- unlocking the locked table
No response is returned when modifying table fields in the query table.
If the gui does not respond, it is best to use the cmd method. No need to lock the gui ..
Alter table t_mb_awardweixin MODIFY awardChoiceNumLeft int (11) DEFAULT NULL
# ----- Analysis ..
1. First, query by another table. If it can be queried, it is not a problem with max conn. Maybe the table is locked ..
2. show full PROCESSLIST. If you see a Waiting for table metadata lock
3. query the locked table. show open tables where In_use> 0;
Author old wow's paw Attilax iron, EMAIL: 1466519819@qq.com
Reprinted please indicate Source: http://blog.csdn.net/attilax
# --------- Cause MDL :::
Table A is used for A non-committed transaction, and another session is used to alter table A. waiting for table metadata lock appears.
Execute alter table t add index (play_count) at the same time when the insert into t select * from share operation ),
The alter table statement Waiting for table metadata lock until insert... The select statement ends.
Overview
As MDL is introduced in 5.5.3, more queries are "Waiting for table metadata lock" to "latency ".
The output of show processlist also includes the previous "Locked" which becomes finer-grained 'Waiting for table metadata lock'
Introduce MDL. When you need to access and modify the table structure, You need to lock the metadata (read/write)
MDL protects the table data structure on the Server layer, not the data itself. Server is much busier than earlier versions
MDL means that once the DDL is blocked, all queries for the table will be suspended, including Select, but 5.6 has been improved, and 5.5 can be controlled by parameters.
3. The connection pool is used in the application... But ddl is required... the connection pool always starts some conn consecutively, and kill Li Korean has automatic start... the subsequent ddl cannot be executed ..
# ---- Solution
1. Set lock_wait_timeout = 50. The default value is 1 year. It takes effect only at the next time. Currently, only kill can be used ..
2. kill to lock conn (query SQL of DDM alter), and other query transaction playing can be continuous ..
3. to deal with the connection pool, you can set timeout to automatically close the connection .. but the restart takes effect .. if the application is important and cannot be restarted, you can use the firewall to prevent java processes from accessing the network... the batch processing kill id. Check that the connection pool will not be automatically connected. In the ddl operation, release .....
# ---- Conclusion:
When preparing alter table tbl, check whether the SQL statements that are running and cannot be terminated within a short period of time are operating on the tbl table.
Conclusion:
When you need to DDL "Hot tables", you need to be especially careful. Otherwise, it will easily cause MDL wait, resulting in connection depletion or Server suspension.
