MySQL query not going away after being killed

Question

I have a MySQL query that is copying data from one table to another for processing. For some reason, this query that normally takes a few seconds locked up overnight and ran for several hours. When I logged in this morning, I tried to kill the query, but it is still listed in the process list.

| Id      | User     | Host      | db   | Command | Time  | State        | Info                                                                                 |
+---------+----------+-----------+------+---------+-------+--------------+--------------------------------------------------------------------------------------+
| 1061763 | tb_admin | localhost | dw   | Killed  | 45299 | Sending data | INSERT INTO email_data_inno_stage SELECT * FROM email_data_test LIMIT 4480000, 10000 |
| 1062614 | tb_admin | localhost | dw   | Killed  |   863 | Sending data | INSERT INTO email_data_inno_stage SELECT * FROM email_data_test LIMIT 4480000, 10000 |

What could have caused this, and how can I kill this process so I can get on with my work?

Answer 1

If the table email_data_test is MyISAM and it was locked, that would have held up the the INSERT.

If the table email_data_test is InnoDB, then a lot of MVCC data was being written in ib_logfiles, which may not have occurred yet.

In both cases, you had the LIMIT clause scroll through 4,480,000 rows just to get to 10,000 rows you actually needed to INSERT.

Killing the query only causes the InnoDB table email_data_inno_stage to execute a rollback.