Today I discovered a new bug/feature in MySQL that is very annoying and ALSO involves NULL (see previous post). In short, a NULL-enabled column can’t be set to NULL if it is of the type timestamp.
So here’s the run down.
If you create a timestamp column that is…
- NULL enabled and has a default value to NULL, things work exactly as you imagine. You can set it to NULL, you can set it to other time values, and then set it back to NULL. No problem.
- NULL enabled and has a default value of “0000-00-00 00:00:00” or CURRENT_TIMESTAMP, it can NOT be set to NULL. Ever. If you try to set it to NULL, it will instead *DANGER DANGER* set it to CURRENT_TIMESTAMP!
Did you catch that? NULL is converted to CURRENT_TIMESTAMP in a NULL enabled table without NULL set as the default value. Can you say insanely stupid bug? Well, supposedly this is a legacy feature, but you know how I feel about that.
To fix this, make sure your NULL enabled timestamp columns are correctly defaulted to NULL.
I confirmed this DOES NOT apply to datetime columns. Great.
well my friend, you should use PostgreSQL when you can. even the author of MySQL said that it’s a big mess (talking about the code of mysql)…