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

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.