3 ms·
Generally in my experience it's been bundled schema changes. As an (dumb) example in an SQL-like pseudocode (I've been doing EE work recently; apologies for imp
by tonyarkles 7y ago
Generally in my experience it's been bundled schema changes. As an (dumb) example in an SQL-like pseudocode (I've been doing EE work recently; apologies for imperfect syntax):
ALTER TABLE user ADD COLUMN country; -- oops
CREATE TABLE user_email (blah blah);
-- process here to move data from user.email column to user_email table. This blows up because someone has a 256-byte long email address and the user_email table has a smaller varchar()
ALTER TABLE user ADD state_province;
Yes, this is sloppy and should be cleaner. The net result is still the same though; you have the user_email table and country column created, and due to MySQL DDL auto-commit, they're persisted even though the data copy process failed. The state_province table does not exist, and now if you want to re-run this migration after fixing the problem, you need to go drop the user_email table and country column.
With e.g. Postgres, you wrap the whole thing in a transaction and be done with it. It gets committed if it succeeds, or gets rolled back if it fails.
- evanelias 7y agoMakes sense, thanks. fwiw, generally this won't matter in larger-scale environments, as it eventually becomes impractical to bundle DDL and DML in a single system/process/transaction. In other words, the schema management system and the bulk row data copy/migration system tend to be separated after a certain point. Otherwise, if the table is quite large, the DML step to copy row data would result in a huge long-running transaction. This tends to be painful in all MVCC databases (MySQL/InnoDB and iiuc Postgres even more so) if there are other concurrent UPDATE operations... old row versions will accumulate and then even simple reads slow down considerably. A common solution is to have the application double-write to the old and new locations, while a backfill process copies row data in chunks, one transaction per chunk. But this inherently means the DDL and DML are separate, and cannot be atomically rolled back anyway.
- tonyarkles 7y agoYeah, definitely for larger scale systems the process gets quite a bit different, although "large scale" will remain undefined :). It's admittedly been a long time since I've used MySQL for anything significant, but I feel like teams in the past have run into issues where DDL operations on their own have succeeded in dev/staging and failed in prod, even though they're running on the same schema. Simply due to there being "weird" data in the rows. I don't know that for sure though. If I'm remembering right, one of those had something to do with the default "latin1_swedish_ci" vs utf8 thing...