So I had just done a test in my development environment where I:
- ran a script to set NULL values in column X to something appropriate
- altered the table to make column X be NOT NULL and give it a sensible default
After I moved these changes to production, I suddenly realized that I had accidentally done (2) before (1), meaning that column X would have no NULLs and therefore my script would not have done its important work. But oddly, the production DB matched the correct values in my dev DB.
I then spent an hour discovering a “feature” of MySQL. Given a query like this:
ALTER TABLE `Foo` CHANGE `X` `X` INT(11) NOT NULL DEFAULT '69';
MySQL (5.0.26, in my case) will not set NULL values in column X to 69, instead it will convert the NULLs to 0. My script treated both 0 and NULL as “empty”, so it ran as I had intended.
On one hand, I’m not sure that I see the logic in doing a NULL-to-INT conversion on existing NULLs rather than setting them to the default I specified. On the other hand, at least I don’t have to clean up my database now. But on my third, invisible Gil-the-Arm hand, I probably spent the same amount of time trying to figure out why this worked out as I would have fixing the DB.