July 29, 2014

Zero-Downtime Schema Changes In MySQL

The recording of yesterday’s webinar, as well as a PDF of my slide deck, is now available. You can watch the recording and get the slides here.

I got a lot of questions, and did not have enough time to answer all of them, so let me try to answer some of the remaining questions here.

Q: Isn’t it possible to take all the locks required in the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that would cause blocking that would defeat the purpose of the tool.

Q: When you say bail out, what does that mean? Does the tool clean up (i.e., remove table / triggers?) About cleanup.. What about manual aborting a run. will that cleanup ? Q: dose the tool exit gracefully while interupted? The tool doesn’t clean up the temporary table or triggers. It is designed to be resumable, although we don’t yet have a “resume” feature implemented.

Q: periodically the ALTER isn’t written to the bin-log and thus isn’t replicated, why ? without any replication filters or relevant args, the replication breaks reliably on the __tmp_x table why ? The first question sounds like a potential MySQL bug. Are you sure this is really happening? I would be extremely surprised for such a bug to be happening. In reply to the second question, the 2.1 version of the tool does NOT have any features to disable the binary log; my experience has been that this is a very dangerous thing to do and causes way too many bugs in the server and the tool, so there is no code in the tool that disables binary logging at all. Are you using the 2.0 version of this tool? It was completely rewritten in version 2.1.

Q: qw() as use of () is deprecated That sounds like you’re using an old version of the tool.

Q: Old version of PT OSC said risk in replication environment, I didn’t see that in the new version, does it mean the tool has been well tested in replication environment? Yes.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.


  1. Asher Feldman says:

    It looks like the requirement on both a single column unique key, or indeed any unique key has been lifted in 2.1. Are there any known gotchas when using pt-osc in such a case? I tested 2.02 with the hope of using at wikipedia but was limited by having many tables with composite primary keys (or no pk / unique key at all.)

  2. That limitation is lifted, yes. It does need some kind of a unique key. The 2.1 version is a complete rewrite of the tool, though, and your experiences with 2.0 probably won’t indicate how 2.1 will work for you.

Speak Your Mind