This is the fifth day of my participation in Gwen Challenge

Original: Telami’s Blog, welcome to share, reprint please reserve source.

Today, when I was synchronizing test data, the network was suddenly disconnected. When I reconnected, I found that the table could not be opened.

It can be seen that the data length of the table is 112192KB, but it cannot be opened.

Can’t open, ready to delete again.

Things are often not so simple, indeed can not delete, TRUNCate does not work, and then Navicat stuck, then boarded the database, dorp operation, or not.

I guess it was a network error that caused something strange to happen.

So let’s see what’s going on.

Enter the artifact.

show full processlist;
Copy the code

The result returned by show Full ProcessList changes in real time and is a live snapshot of mysql link execution, so it is useful for handling emergencies.

This SQL, in general, acts as a firefighter to solve some unexpected problems.

It can check the current mysql running status, whether it is under pressure, what SQL is being executed, how long the statement is taking, whether slow SQL is being executed, etc.

When you find some SQL that takes a long time to execute, you need to pay more attention to it and kill it if necessary.

Commands can be executed in three ways:

1. This is a direct query in the command line, with \G at the end means that the query result will be printed by column, so that each field can be printed to a separate line.

mysql> show full processlist;
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| Id     | User | Host                 | db    | Command | Time | State    | Info                  |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+59828 | | 449000 | root | 127.123.213.11: stark | Sleep | 1270 | | NULL | | 449001 | root | 127.123.213.11:59900 | stark 1241 | | Sleep | | NULL | | 449002 | root | 127.123.213.11:59958 | stark | Sleep | 1216 | | NULL | | 449003 | root | 127.123.213.11:60088 | stark | Sleep | 1159 | | NULL | | 449004 | root | 127.123.213.11:60108 | stark | Sleep | 1151 | | NULL | | 449005 | root | 127.123.213.11:60280 | stark | Sleep | 1076 | | NULL | | 449006 | root | 127.123.213.11:60286 1074 | | stark | Sleep | | NULL | | 449007 | root | 127.123.213.11:60344 | stark | Sleep | 1052 | | NULL | | 449008 | Root | 127.123.213.11:60450 | stark | Sleep | 1005 | | NULL | | 449009 | root | 127.123.213.11:60498 | stark | Sleep | 986 | | NULL | | 449013 | root | localhost | NULL | Query | 0 | starting |show full processlist |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
11 rows in set (0.01 sec)

mysql> show full processlist\G; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 449000 User: root Host: 127.123.213.11:59828 db: stark Command: Sleep Time: 1283 State: Info: NULL *************************** 2. row *************************** Id: 449001 User: root Host: 127.123.213.11:59900 DB: Stark Command: Sleep Time: 1254 State: Info: NULLCopy the code

2. View snapshots by querying tables related to linked threads

SELECT id, db, USER, HOST, command, time, state, info FROM information_schema. PROCESSLIST WHEREcommand ! ='Sleep' ORDER BY time DESC;
Copy the code

3, through navicat [tools] => [server monitoring] to view.

It’s a little more convenient, and you can sort it.

A brief introduction to the meaning of each column:

  • Id: unique identifier of the thread connected to the mysql server. You can kill this thread to terminate the link.

  • User: the User of the current thread to connect to the database

  • Host: shows which port on which IP the statement is issued from. Can be used to trace the user who produced the problem statement

  • Db: the database to which the thread is linked, or null if there is none

  • Command: Displays the execution of the current connection, usually sleep or idle, query, connect.

  • Time: indicates the duration of the thread in the current state, in seconds

  • The State: Note that state is just one state in the execution of a statement. An SQL statement, for example, has been queried, may need to go through copying to TMP tables, Sorting result, You can complete this task only when the status is Sending data

  • Info: SQL statement executed by the thread, or null if no statement is executed. This statement allows the execution statement sent from the client to be an internally executed statement

Once you’ve identified the problem, how do you solve it?

1. You can kill the offending rows alone

kill 449000
Copy the code

2. You can also batch end threads that take longer than 3 minutes

Query threads that take more than 3 minutes to execute and concatenate them into kill statements
select concat('kill '.id.'; ')
from information_schema.processlist
wherecommand ! ='Sleep'
and time > 3*60
order by time desc;
Copy the code

Truncate (truncate) and drop (truncate, truncate, truncate, truncate, truncate, truncate, truncate, truncate, truncate);

Of course, the above is not nonsense, this is similar to the methodology of things, just like the [Chinese Captain] inside, when encountering a flight accident, first according to the manual, check again, find out the cause, solve the problem.

Continue to

Waiting for table metadata lock Waiting for table metadata lock Waiting for table metadata lock

Waiting for table metadata lock is Waiting for table metadata lock when MySQL is performing DDL operations such as ALTER table.

Solutions:

1. View the current 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
Copy the code

Field Meaning:

  • Trx_state: transaction status, usually RUNNING
  • Trx_started: indicates the start time of the transaction. If the transaction takes a long time, analyze whether the transaction is reasonable
  • Trx_mysql_thread_id: indicates the ID of the MySQL thread, which is used to kill
  • Trx_query: SQL in a transaction

DDL operations will not wait for the table metadata lock if the thread is killed.

2. Adjust the lock timeout threshold

Lock_wait_timeout Indicates the timeout (unit: second) for obtaining metadata lock. The allowed value ranges from 1 to 31536000 (one year). The default value is 31536000.

See dev.mysql.com/doc/refman/…

The default value is one year…

Adjust it to 30 minutes

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

So they can failfast if the problem occurs.

All done