MySQL - Can aborting a query with Ctrl-C harm the table?

Question

Some of my queries were taking way too long, including some alter table queries, so I pressed Ctrl-c to abort them and continue with other matters.

One that I aborted was alter table labs modify ID int(11) first.

Now quite simple queries like alter table labs drop column ID are taking forever. I've been waiting for over 25 minutes. There are millions of records, but I've run similar alter table queries on the same table that only take a few seconds.

Can aborting queries harm the column that the query was operating on? If so, how can I recover from the problem?

---

Update: I am nuking a primary-key column. Could that have something to do with the insanely long running time?

Answer 1

If the transaction wasn't completed, no. MySQL does ALTER TABLE by creating a new table and then updates the reference to the old table. You shouldn't have altered your original table if you interrupted the original ALTER TABLE query.

Source.

Answer 2

Short answer: Not necessarily.

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.

https://stackoverflow.com/a/3442027/4148092