How to enable explicit_defaults_for_timestamp?

Question

When I try to start my mySQL server I get message:

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

I find answer on:
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp

But how to enable it? Where?

Answer 1

First you don't need to change anything yet.

Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup

Now if you want to move to new behaviors you have to add this line in your my.cnf in the [mysqld] section.

explicit_defaults_for_timestamp = 1 

The location of my.cnf (or other config files) vary from one system to another. If you can't find it refer to https://dev.mysql.com/doc/refman/5.7/en/option-files.html

Answer 2

In your mysql command line do the following:

mysql> SHOW GLOBAL VARIABLES LIKE '%timestamp%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | OFF   | | log_timestamps                  | UTC   | +---------------------------------+-------+ 2 rows in set (0.01 sec)  mysql> SET GLOBAL explicit_defaults_for_timestamp = 1; Query OK, 0 rows affected (0.00 sec)  mysql> SHOW GLOBAL VARIABLES LIKE '%timestamp%'; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | ON    | | log_timestamps                  | UTC   | +---------------------------------+-------+ 2 rows in set (0.00 sec)