Friday 1 March 2013

MySQL 5.6.6 TIMESTAMP columns and DEFAULT values

In MySQL versions prior to 5.6, behavior of columns with default values for the TIMESTAMP column type is pretty much nonstandard. The nonstandard (MySQL specific) behavior is not so intuitive for users to understand and the community has often expressed unhappiness, and there are lots of blogs about this. MySQL 5.6 has many improvements in this area. This blog focuses on describing these improvements with examples, and also discusses about backward compatibility, backup and restore.

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)
We see that DATETIME does not get 'NOT NULL' and 'DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' attributes, and only TIMESTAMP gets it!

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)
We see that DATETIME does not get attribute DEFAULT '0000-00-00 00:00:00' after the first DATETIME column, but for TIMESTAMP we do get it!

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)
We see that a warning is generated for the f3 DATETIME column type, and not for f2. So in strict mode such inserts on f2 might succeed without an error too!

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

10 comments:

  1. To be more compatible with my mysql applications, it´s a good idea to disable this option?

    ReplyDelete
    Replies
    1. Disabling this option is OK as a short term (only for 5.6) measure if one experiences compatibility issues. However, as the old behavior is deprecated, it is recommended to make MySQL application adopt to new behavior, as old behavior will eventually go away.

      Delete
  2. I'm unable to use default_timestamp if I want milliseconds precision:

    Alter table wallet add last_update timestamp(3) default current_timestamp

    gives:

    #1067 - Invalid default value for 'last_update'

    ReplyDelete
  3. I ran into an issue in 5.6.24 with regard to this. I have a table with one timestamp field defined as "not null." In our previous system (we're porting from an old version of mysql) a *replace* command, with a value of null for that field, would cause the current time to be entered. There are instances of this all over our code I'm afraid. Turns out, doing a replace (as opposed to an insert) with null for the "not null" timestamp field causes an error. Due to our settings, this means the script aborts. You didn't comment here about replace commands. Is there a way to modify this behavior?

    ReplyDelete
  4. Nice Article !

    Really this will help to people of MySQL Community.
    I have also prepared small note on this, How to set default value for a datatime column in MySQL.

    http://www.dbrnd.com/2015/10/mysql-set-default-value-for-a-datetime-column/

    ReplyDelete
  5. The problem is due to microseconds added in default value in mysql new versions. See http://tekina.info/default-datetime-timestamp-issue-mysql-upgrading-5-6/ for solutions.

    ReplyDelete
  6. The most effective method to Solve Error 1067 in MySQL with the assistance of MySQL Technical Support
    The Error 1067 signifies "The procedure ends startlingly" couldn't begin the MySQL benefit on nearby PC. To determine this issue it is possible that you can comprehend it physically or connect with MySQL Remote Support. To settle it physically: First tap on Start at that point run and sort "cmd". After that go to MySQL double index and execute the accompanying summon "mysqld-nt.exe-comfort" now you can begin the MySQL Service.
    In any case, in the event that you discover any trouble to do this procedure then without supposing anything contact to MySQL Remote Service and investigate your issues.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete