Killing the mysqld process

Question

I have a table with ~800k rows. I ran an update users set hash = SHA1(CONCAT({about eight fields})) where 1;

Now I have a hung Sequel Pro process and I'm not sure about the mysqld process.

This is two questions:

1. What harm can possibly come from killing these programs? I'm working on a separate database, so no damage should come to other databases on the system, right?

2. Assume you had to update a table like this. What would be a quicker / more reliable method of updating without writing a separate script.

I just checked with phpMyAdmin and it appears as though the query is complete. I still have Sequel Pro using 100% of both my cores though...

Answer 1

To get the thread IDs (it'll show the query alongside):

mysqladmin proc

To safely kill the query thread:

mysqladmin kill [id]

You'll end up with a partially updated table unless you use innodb, but you should be fine. Details:

During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. Note that if you are not using transactions, the changes are not rolled back.

As for your second question, there is no better way to update a table if one is not allowed to write a separate script (to, say, throttle the updates).

Answer 2

If you're using InnoDB, which is backed by a transaction log for recovery and rollback purposes, then you can get away with a lot, especially in a non-production environment.

The easiest way to terminate a renegade query is to use the MySQL shell as the root user:

SHOW PROCESSLIST;

This will give you a list of the current connections and a process ID for each one. To terminate any given query, such as number 19, use:

KILL 19;

Usually this will undo and roll back the query. In some cases this is not sufficient and you may have to force-quit the MySQL server process with kill -9. Under most circumstances you should be able to restart the server right away, and the DB will be in the last fully committed state.