MySQL Timestamp and NULL - ANNOYING Part 2
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.
[...] I have two VERY annoying bugs to share with you in PHP. After this, I’ll make a post about an annoying thing I found in MySQL regarding NULL values too. [...]
Pingback by MichiKono.com » Isset and NULL, Array Keys and NULL - ANNOYING Part 1 — January 3, 2007 @ 5:58 pm