In the yet to be released MySQL 5.6.6 DMR, there has been a change to the restriction of just one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP syntax. It is now possible for any TIMESTAMP to have either column defintion.
More information at http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html
Oh, that’s very nice.
I noticed the same a couple of days ago. One important thing to notice is this passage from http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html: “TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.” [and is not NULLable – my addition]
So the ‘silent’ behaviour of the server making definition of a TIMESTAMP [NOT NULL] –> TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP is kept as is on pre-5.6 for the first TIMESTAMP column in the table difinition. It was probably originally introduced like this in 5.0 (or 4.1 – I don’t remember) in order to keep some kind of compability with early MySQL (3.x, 4.0) implementation. There are still applications in use that were written for the early MySQL versions and often it was not necessary to change application code when upgrading the server to 5.x. And still lots of applications do not explicitly specify DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP but rely on the ‘silent’ mechanism.
However, this ‘silent’ is a behaviour that has received much criticism over time (for being ‘non-standard’ I think mostly). But I think it is necessary to keep backwards compability, so I agree with the implementation. At least as long as there is not a ‘switch’/option/variable that controls the behaviour.
@Peter: things are moving forward. Lets hope such a switch is made available in the near future, I think everybody agree’s the silent TIMESTAMP behavior is a pretty nasty gotcha. Then again, I’d probably want to see server-side SQL-mode enforcement for client connections first. That’s usually the deal-breaker for MySQL in the first place.