3 ms·
It happens to be a slow and blocking action on MySQL, yes. That's yet another problem with MySQL. You can only make a new table and swap it in if you can stop
by tene 13y ago
It happens to be a slow and blocking action on MySQL, yes. That's yet another problem with MySQL. You can only make a new table and swap it in if you can stop writing to your old table, or don't care if records go missing. I've lost weeks trying increasingly convoluted methods to get an online schema migration safely in MySQL, with no satisfying conclusion. You can sometimes get away with pt-online-schema-change's method of adding triggers to copy new rows while copying over the existing table contents, but that failed pretty badly for us because of collisions with the innodb gap lock on the table, so we ended up having to do multiple passes, populating the new table once without the live updates, then copying it over again while the live updates were copied over with triggers. It was a mess.
- Dylan16807 13y agoI think I expressed myself poorly. Undoubtedly it would be good if it did the on-disk data changes in the background, and a schema update seemed like an instant atomic action. But it's still a sequence point, and I see no way that transactions after it could complete while the schema update is uncommitted. So what do you actually gain by being able to do the schema update in a transaction?
- saurik 13y agoThe original comment was about being able to roll back, not the ability to run things in parallel. With PostgreSQL, I can start a transaction in which I'm going to perform five separate "alter table" commands; if one of these fails (maybe because I change the type of a field in a way that is incompatible with some of the data, or maybe because this is some kind of "migration" that came with a project and it has a bug: maybe it tries to rename a column, but there's a typo in the name) the entire migration will roll back atomically.
- Dylan16807 13y agoYeah, I'm just saying that you can work around that pretty easily. There are more important things than rolling back schemas that mysql lacks. And if it got those things, schema updates would be pretty easy even without the ability to roll them back. Not extremely convenient, but convenient enough. Schema transactions are not nearly as useful/important as data transactions.