On: How I Fired Myself

[MBCook]

Ug. MyISAM.

More than once (over the last few years) I've been doing some important update. I tend to do it the same way.

  START TRANSACTION;
  --run SQL--
  --check results--
  COMMIT; or ABORT TRANSACTION;

Of course, if you happen to run into the 1 or 2 MyISAM tables that no one knew were MyISAM, abort doesn't do anything. You've screwed up the data and need a backup.

So you always have to make a backup and check that the tables are defined they way they should be. Nothing is quite as much fun as the first time you delete a bunch of critical data off a production system. Luckily the table was small, basically static, and we had backups so it was only a problem for ~5 minutes.

[mst → MBCook]

Any CREATE, ALTER or DROP on mysql does an implicit, silent COMMIT (not rollback) before running it.

PostgreSQL can do DDL inside a transaction though.

[MBCook → mst]

Yep, that's another one to watch out for. But I'm used to that, it's when an update or delete that can't be reversed I get surprised.