Mysql – How to diagnose a hanging MySQL UPDATE

MySQL

I have a simple UPDATE statement running on a MySQL 5.6.34 database that's seemingly hanging. The statement only updates a single row with a timestamp and looks like:

UPDATE mytable SET last_updated='2017-05-31 02:13:43' WHERE id=123;

Yet after an hour, this was still running.

Running SHOW FULL PROCESSLIST; doesn't show any other processes accessing the table, so I don't think it's a deadlock. I tried killing and re-running the process, but it still hangs.

What other reasons would cause a simple UPDATE statement like this to hang? How do I diagnose this?

Best Answer

Just set slow_query_log = 1 (if >=5.6.1) or log_slow_queries = 1(if <5.6.1) and slow_query_log_file = /path/to/logfile and long_query_time = 0 in my.cnf, then restart mysql and have a look at the logfile. I won't keep it on on production envs, such a logging is a performance killer and may result in bad things if you are on heavy load. It may also get quickly very big as long_query_time 0 results in logging everything, so "/path/to" should have plenty of free space.

Related Question