You are here

TIMESTAMP with implicit DEFAULT value is deprecated

Hello all,

since MySQL 5.6 I get this nasty warning in my MySQL error log:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

How dangerous is this warning hand how can I prevent it?

Taxonomy upgrade extras: 

The default behaviour is and was so far:

mysql> CREATE TABLE test (id INT, ts TIMESTAMP);

CREATE TABLE `test` (
 `id` int(11) DEFAULT NULL,
 `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> INSERT INTO test VALUES (1, NULL);

mysql> SELECT * FROM test;
+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2014-12-28 18:00:36 |
+------+---------------------+

mysql> CREATE TABLE TEST (id INT, ts TIMESTAMP DEFAULT NULL);
ERROR 1067 (42000): Invalid default value for 'ts'

In MySQL 5.6 a new behaviour is introduced (not enabled by default) and the old behaviour will possibly deprecate sooner or later:

mysql> SHOW GLOBAL VARIABLES LIKE 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+

mysql> CREATE TABLE test (id INT, ts TIMESTAMP);

CREATE TABLE `test` (
 `id` int(11) DEFAULT NULL,
 `ts` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> INSERT INTO test VALUES (1, NULL);

mysql> SELECT * FROM test;
+------+------+
| id   | ts   |
+------+------+
|    1 | NULL |
+------+------+

The old behaviour can still be mimicked but we have to specify it explicitly:

mysql> CREATE TABLE test (id INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Our advice is to enable the variable explicit_defaults_for_timestamp now on your testing systems so you can see if your application behave well and then you are prepared for the next release when this feature will become the default.

In short term this warning is NOT dangerous. In the long term you have to be prepared for the deprecated functionality. You get rid of the warning my setting explicit_defaults_for_timestamp = 1 in your my.cnf [mysqld] section.

Shinguzcomment