Saturday, October 25, 2014

MySQL 5.6 TIMESTAMP changes

So you upgraded to MySQL 5.6 and there's a crazy warning in your error log about TIMESTAMP columns doing stuff, or not, when explicit_defaults_for_timestamp is enabled, or not?

It's actually pretty simple: TIMESTAMP columns without any DEFAULT nor ON UPDATE clause are going to change behavior in the future, and MySQL 5.6 has an option to allow choosing whether to opt-in to that future behavior at present.

MySQL 5.6 without the explicit_defaults_for_timestamp option set, which is default, will continue treating a column defined as simply TIMESTAMP (possibly also NOT NULL) as if it were defined TIMESTAMP [NOT NULL] DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

MySQL 5.6 with the explicit_defaults_for_timestamp option set will behave like the future planned versions of MySQL, where TIMESTAMP will be treated as TIMESTAMP DEFAULT NULL and TIMESTAMP NOT NULL will behave as TIMESTAMP NOT NULL DEFAULT 0.  Implied automatic updates will be no more.

That's all.

All of the other historic TIMESTAMP behaviors, such as assigning NULL to a column declared with NOT NULL actually assigning CURRENT_TIMESTAMP, remain unchanged by this update.  There are some brand-new capabilities such as fractional seconds, applying default/update clauses to DATETIME columns, and setting those clauses on more than one column in a table.  However, those features aren't a change of meaning for existing definitions, so they're unaffected by the option.

No comments: