Here’s a fun issue I ran into a little while ago, while doing DB maintenance at Grooveshark.
MySQL has a handy atomic rename function, where you can do something like this:
RENAME TABLE CurrentTable TO OldTable, NewTable TO CurrentTable
Both renames are done as a single atomic action, so that the table CurrentTable is always there, and Nothing Has To Break*
A few days ago, I took advantage of this feature. I needed to make some schema changes to one table that was being read from but not written to.
CREATE TABLE NewTable LIKE CurrentTable;
ALTER TABLE NewTable DROP COLUMN UnnecessaryColumn;
...
INSERT INTO NewTable SELECT (...) FROM CurrentTable;
RENAME TABLE CurrentTable TO OldTable, NewTable TO CurrentTable;
That way we have a live, selectable backup of the table in case I really screwed something up, not to mention that moving the tables this way meant we could finally get that huge table into file per table, as that table had been around since before we had turned on the file per table setting in MySQL.
Several other tables have foreign key constraints pointing to CurrentTable, and unfortunately MySQL is smart enough to notice that CurrentTable is being renamed, so it “helpfully” updates all the foreign key constraints in those tables so that they are now pointing at OldTable. Without mentioning it. The problem didn’t become apparent until CurrentTable was being written to again, and then the other tables with the foreign keys were being written to pointing to the new data in CurrentTable, except the updated constraints meant MySQL tried to find the rows in OldTable, causing the inserts to fail.
This is definitely one of those cases where being smart and helpful can sure lead to a lot of misery. I had to hunt down every table that had referenced CurrentTable to change them back by hand. If MySQL had been just a little bit smarter it would have seen that CurrentTable wasn’t *really* going away and it wouldn’t have made those changes for me. If it was a little bit dumber, it would have done nothing and things would have just kept working, or it would have blocked me from doing my rename, forcing me to deal with the constraints before they broke anything.
* except when it does