34

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

B T
  • 57,525
  • 34
  • 189
  • 207
  • 35
    Haha, I'm sorry, I have a hilarious mental image of a bunch of engineers huddling around a monitor, peering through their sweaty brows at this post - just waiting for an answer - while a mysql server in the background begins to smoke. – Peter Bailey Apr 28 '10 at 18:27
  • haha, we pretty much had to let it smoke until the query finished. But it'd be nice to know for next time.. god forbid – B T Apr 28 '10 at 18:42
  • Well, here I am. I was in the case were a copy was made, so stopping it was fine, but there was sweating involved. Thank god it wasn't in production but the write-lock is infamous. – Aki Jan 13 '14 at 14:48

2 Answers2

24

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.

davek
  • 22,499
  • 9
  • 75
  • 95
  • 2
    It looks like our case was one where "no temporary table is necessary" - but it looks like our tables still locked up, our open connections spiked, and we had a 5 to 10 minute outage. If we kill a query that does *not* create a copy... then what? – B T Apr 29 '10 at 18:51
  • @BT: How come no temporary table were created, what's your specific case? – Aki Jan 13 '14 at 14:49
  • 2
    BTW, I killed the process during the copy to temp table, it removed the write-lock and the data was still there and accessible. I have no idea where the temp copy went. – Aki Jan 13 '14 at 14:58
  • The temp copy is unlinked when the ALTER is interrupted. – dland Jan 26 '16 at 10:03
0

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.

dland
  • 4,319
  • 6
  • 36
  • 60