CODEWITHSUNDEEP
mysqlkillalter
B T
B T

Reputation: 60935

What happens if you kill a long-running alter query?

What happens if you kill a long-running alter query? Will the alter query simply revert? How long could that take (as a proportion of the time it has already been running)?

What if that query is being replicated onto another server? Will killing the process on the other server revert the original server's alter query?

We're running mysql

Upvotes: 34

Views: 31228

Answers (3)

Mohamad Hamouday
Mohamad Hamouday

Reputation: 2783

Start by executing the command:

SHOW FULL PROCESSLIST;

Next, carefully examine the process list and look for any entries with the status copying to temp table. If you identify queries with this status, it indicates that they are less risky to terminate. In such cases, you can safely terminate the query by executing the following command:

KILL QUERY_ID;

Replace 'QUERY_ID' with the actual ID of the query you wish to terminate. You can find the query's ID in the list displayed by the SHOW FULL PROCESSLIST command.

When you kill a query in MySQL using the KILL QUERY command, it instructs MySQL to stop the execution of the specified query.

This usually results in a change of the query's state from "copying to temp table" or another state to "killed" in the SHOW FULL PROCESSLIST.

Additionally, MySQL initiates a rollback process to undo any changes made by the query. The impact of the rollback may vary depending on the query's progress. Killing a query can free up server resources, resolve deadlocks, and improve the performance of other queries on the server.

However, it's essential to use this command judiciously, as it can disrupt ongoing operations and potentially affect data consistency.

Upvotes: -1

dland
dland

Reputation: 4419

What if that query is being replicated onto another server?

The ALTER will be executed on that server as well, with the associated impacts.

Will killing the process on the other server revert the original server's alter query?

Nope. The original server has no back channel to learn about what occurred (or didn't) on the slave. If you kill the ALTER on the slave, then you will wind up in the situation where the master has the new constraint or index, and the slave doesn't. This is rarely a recipe for happiness :)

Once an ALTER enters the replication log, you either have to let it run everywhere, or kill it everywhere.

Upvotes: 0

davek
davek

Reputation: 22925

It depends what you're doing. If you're running an alter table...add index command on an InnoDB table (not so sure about MyISAM), then it will just run and run as it copies the whole darn table lock-stock-and-barrel first: if it's in the middle of "copy to temp table" then it's pretty much unstoppable.

See here:

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

Upvotes: 26

Related Questions