The first improvement in MySQL 5.6.5 is that we can now define more than one TIMESTAMP column type with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. And it's possible to have DATETIME columns with such attributes. Thanks to my colleague Martin Hansson, who made this possible and described it in his blog.
Secondly, in MySQL versions prior to 5.6.10, when we create tables using "CREATE TABLE ... SELECT" and SELECT returns a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP, we fail to inherit the proper DEFAULT value attribute. Thanks to my colleague Guilhem Bichot, who has fixed this issue. Check out this blog for more details.
The third TIMESTAMP improvement concerns DEFAULT values. In MySQL versions prior to 5.6.6 TIMESTAMP columns are treated differently from other column types (like INT or DATETIME) when it comes to DEFAULT values.
1. Behavior prior to MySQL 5.6.6:-
CASE 1.1: Unlike all the other types, TIMESTAMP columns which are not explicitly specified as NULLable automatically get NOT NULL as attribute. e.g.:-
mysql> CREATE TABLE t1 (
-> f1 TIMESTAMP DEFAULT '0000-00-00 00:00:00',
-> f2 DATETIME DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.36 sec)
mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`f2` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------+
1 row in set (0.00 sec)
We see that DATETIME does not get 'NOT NULL' attribute, only TIMESTAMP gets it!
CASE 1.2: If the first TIMESTAMP column in a table is not specified as NULLable and doesn't have explicit DEFAULT or ON UPDATE value specified, it automatically gets DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP as attributes. e.g.:-mysql> CREATE TABLE t1 ( f1 TIMESTAMP, f2 DATETIME);
Query OK, 0 rows affected (0.36 sec)
mysql> SHOW CREATE TABLE t1;
+-------+----------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`f2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
CASE 1.3: After the first TIMESTAMP column, other TIMESTAMP columns which are non-NULLable and don't have explicit default value specified, get '0000-00-00 00:00:00' as default value. e.g.:-
mysql> CREATE TABLE t1 ( f1 TIMESTAMP NOT NULL, f2 TIMESTAMP NOT NULL, f3 DATETIME NOT NULL, f4 DATETIME NOT NULL);
Query OK, 0 rows affected (0.40 sec)
mysql> SHOW CREATE TABLE t1;
+-------+----------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`f2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`f3` datetime NOT NULL,
`f4` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
A side effect of CASE3 is that these columns are treated as having explicit default values, even if user did not indent this. e.g., if the user doesn't provide an explicit value for such a column when inserting into the table, MySQL does not issue a warning or an error. e.g.:-
mysql> INSERT INTO t1 (f1,f4) VALUES (NOW(),NOW());
Query OK, 1 row affected, 1 warning (0.13 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'f3' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT f2,f3 FROM t1;
+---------------------+---------------------+
| f2 | f3 |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
1 row in set (0.01 sec)
2. New behavior in MySQL 5.6.6
The old behavior described above may be in use by many MySQL applications (embedded applications, web applications, discussion forum/blogging software, monitoring software, etc). So it may not be acceptable to change the behavior of TIMESTAMP to make it similar to other data types. Hence, MySQL 5.6.6 introduces a new server option called --explicit_defaults_for_timestamp for users who explicitly wish to update their applications to adopt the new behavior. We have kept the old behavior as default, but we have deprecated it, which means the --explicit_defaults_for_timestamp will be new default in future versions. Therefore users will get a warning when using the old behavior from MySQL 5.6.6 onwards.
With the --explicit_defaults_for_timestamp option enabled, we get the following new behavior for the cases mentioned above.
CASE 2.1) TIMESTAMP columns which are not explicitly specified as NOT NULL become NULLable. e.g. :-
mysql> CREATE TABLE t1 (
-> f1 TIMESTAMP DEFAULT '0000-00-00 00:00:00',
-> f2 DATETIME DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.40 sec)
mysql> SHOW CREATE TABLE t1;
+-------+-----------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` timestamp NULL DEFAULT '0000-00-00 00:00:00',
`f2` datetime DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------+
1 row in set (0.00 sec)
CASE 2.2) TIMESTAMP columns do not get DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes automatically. They now have to be explicitly specified. e.g.:-
mysql> CREATE TABLE t1 (f1 TIMESTAMP, f2 DATETIME);
Query OK, 0 rows affected (0.32 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` timestamp NULL DEFAULT NULL,
`f2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------+
1 row in set (0.01 sec)
CASE 2.3) Non-NULLable TIMESTAMP columns without explicit default value are treated as having no default value. e.g.:-
mysql> CREATE TABLE t1 ( f1 TIMESTAMP NOT NULL, f2 TIMESTAMP NOT NULL, f3 DATETIME NOT NULL, f4 DATETIME NOT NULL);
Query OK, 0 rows affected (0.52 sec)
mysql> SHOW CREATE TABLE t1;
+-------+------------------------------+
| Table | Create Table |
+-------+------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` timestamp NOT NULL,
`f2` timestamp NOT NULL,
`f3` datetime NOT NULL,
`f4` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------+
1 row in set (0.00 sec)
If we insert a row without providing an explicit value for such a column, a warning/error is emitted (depending on sql_mode). In case when a warning is emitted and not an error, such a column will still get '0' if no explicit value was specified for it. e.g.:-
mysql> INSERT INTO t1 (f1,f4) VALUES (NOW(),NOW());
Query OK, 1 row affected, 2 warnings (0.05 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'f2' doesn't have a default value |
| Warning | 1364 | Field 'f3' doesn't have a default value |
+---------+------+-----------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT f2,f3 FROM t1;
+---------------------+---------------------+
| f2 | f3 |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
3. Backup and restore
Backups which store table descriptions in the form of SHOW CREATE TABLE (for example those created with mysqldump), should not be affected by these changes. That is because SHOW CREATE TABLE adds NOT NULL/NULL attributes for each TIMESTAMP column, irrespective of whether it was explicit or implicitly specified in the CREATE TABLE. So it should be possible to use old backups with MySQL 5.6.6 server irrespective of compatibility mode of using --explicit_defaults_for_timestamp or not. A table from an old backup will behave in the same way as it did on the old server. For our discussion let us assume 'old server' represents behavior in section 1) and 'new server' represents behavior in section 2).
Using backups of the form SHOW CREATE TABLE from a new server on an old server is also possible. But one has to take into account that on the old server all non-NULLable TIMESTAMP columns without explicit default will automatically get '0' as default, as described in CASE 1.3.
As demonstrated in CASE 1.2 above TIMESTAMP column is by default NOT NULL. So user has to explicitly specify 'NULL' in CREATE TABLE to make a TIMESTAMP column Nullable, otherwise we get an error. e.g.:-
mysql> CREATE TABLE t1 ( f1 DATETIME DEFAULT NULL, f2 TIMESTAMP DEFAULT NULL);
ERROR 1067 (42000): Invalid default value for 'f2'
SHOW CREATE TABLE adds a explicit 'NULL' for the TIMESTAMP column, if CREATE TABLE did explicitly specify the column as 'NULL'. e.g.:-
mysql> CREATE TABLE t1 ( f1 DATETIME DEFAULT NULL, f2 TIMESTAMP NULL DEFAULT NULL);
Query OK, 0 rows affected (0.42 sec)
mysql> SHOW CREATE TABLE t1;
+-------+--------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------+
| t1 | CREATE TABLE `t1` (
`f1` datetime DEFAULT NULL,
`f2` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------+
1 row in set (0.00 sec)
This is why we see an extra 'NULL' in CASE 2.1 and CASE 2.2. It is important to retain this extra NULL displayed by SHOW CREATE TABLE in new server, to enable the backups from a new server to be restored on an old server.
Backups which store .FRM files should not be affected either. The implemented changes doesn't affect .FRM files at all. This is because .FRM files store full description of columns. After moving .FRMs from an old server to a new server the behavior does not change.
Moving .FRMs from a new server to an old server should be possible too. Moreover tables from new server will behave on the old server in the same way as they did on the a new server.
4. Documentation of MySQL 5.6.6
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html
We believe that with all the above changes in MySQL 5.6.6 the behavior of TIMESTAMP columns and DEFAULT values is closer to the SQL standard. And the behavior of TIMESTAMP now becomes similar to how other column types are treated.
Hope you enjoy using MySQL.
-Gopal
