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?

see http://dev.mysql.com/doc/refman/5.6/en/server-default-configuration-file.html — didierc, Mar 29 '13 at 10:43

gagarine · Answer 1 · 2017-12-28T19:22:27.327

79

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

edited Dec 28 '17 at 19:22

answered Apr 09 '13 at 08:01

gagarine

4,190
2
30
39

1

According to https://dev.mysql.com/doc/refman/5.6/en/using-system-variables.html you have to set boolean values using 0/1 in an options file. (I wanted to edit the post but it's only allowed if you edit at least 6 chars.) – rudolfson Oct 15 '13 at 09:08
1

Just to clarify, the values True/False work just as well as 1/0. – Stefan Lasiewski Mar 12 '14 at 22:14
1

i have a my-default.ini file and after adding explicit_defaults_for_timestamp = TRUE it is still giving the same warning.What shoud i do.I am using windows 8 64 bit – Junaid Shirwani Apr 08 '14 at 15:36
3

In which of all the sections of my.cnf should I add that line? – alexk May 18 '14 at 01:04
1

`my.cnf` is `my.ini` in windows it should be in `pathTo\MySQL\data\MySQL Server 5.6` – Robert May 30 '15 at 22:48
@JunaidShirwani you can use search for `my.ini` inside mysql folder if you can't find it – Robert May 30 '15 at 22:50
i have done that . the only file i can find is my-deafault.ini – Junaid Shirwani Jun 06 '15 at 01:58

score 34 · Answer 2 · edited Dec 17 '19 at 22:55

34

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)

edited Dec 17 '19 at 22:55

shawncampbell

888
10
12

answered Oct 03 '18 at 07:30

Prasanna

1,617
1
17
22

3

@Prasanna please paste the text next time – Nimrod Morag Dec 10 '18 at 10:11
2

I second the motion to please paste the text next time ... including an image of text has got to be some kind of foul. It's definitely some kind of evil. – Mylo Stone Jan 24 '19 at 20:32
Worked for me on Ubuntu 18 – Nuno Sarmento Jul 05 '20 at 23:00
Though being able to read the current value being used is absolutely invaluable, setting it with `SET GLOBAL` does not survive a server restart AFAIK. – Stijn de Witt Mar 31 '22 at 14:28

score 12 · Answer 3 · edited Aug 05 '20 at 18:20

12

On a Windows platform,

Find your my.ini configuration file.
In my.ini go to the [mysqld] section.
Add explicit_defaults_for_timestamp=true without quotes and save the change.
Start mysqld

This worked for me (windows 7 Ultimate 32bit)

edited Aug 05 '20 at 18:20

pbarney

2,529
4
35
49

answered Jan 21 '14 at 11:42

miyaneha

309
3
3

it probably couldnt work - the argument is missing -> see https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp you have to add " = true/false" – Bernhard Jan 14 '16 at 19:33
edit `/etc/mysql/mysql.conf.d/mysqld.cnf` on debian buster. – ws_ May 12 '21 at 06:46

score 6 · Answer 4 · edited Jan 14 '18 at 17:19

6

For me it worked to add the phrase "explicit_defaults_for_timestamp = ON" without quotes into the config file my.ini.

Make sure you add this phrase right underneath the [mysqld] statement in the config file.

You will find my.ini under C:\ProgramData\MySQL\MySQL Server 5.7 if you had conducted the default installation of MySQL.

edited Jan 14 '18 at 17:19

Nicomedes E.

1,326
5
18
27

answered Jan 14 '18 at 13:33

simon

61
1
2

score 4 · Answer 5 · answered Mar 27 '15 at 13:38

4

On Windows you can run server with option key, no need to change ini files.

"C:\mysql\bin\mysqld.exe" --explicit_defaults_for_timestamp=1

answered Mar 27 '15 at 13:38

Andrey

41
2

score 4 · Answer 6 · edited Nov 30 '16 at 10:55

4

In your mysql command line: SET explicit_defaults_for_timestamp=1

edited Nov 30 '16 at 10:55

Floern

33,559
24
104
119

answered Nov 30 '16 at 10:53

akuma8

4,160
5
46
82

score 1 · Answer 7 · answered Oct 30 '14 at 15:01

On my system (Windows 8.1), the problem was with the server configuration. The server worked for the first time when I installed it. However, I forgot to check the "run as a service" option and this caused all the problem. I tried all possible solutions available on SO but nothing worked. So, I decided to reinstall MySQL Workbench. On executing the same msi file that I earlier used to install MySQL workbench, I reconfigured the server and allowed to run the server as a service.

Right clicking the MySQL Notifier and launching the Installer, reconfigured my server as a service worked. Thanks — House, Dec 10 '14 at 16:22

score 1 · Answer 8 · answered Jun 05 '15 at 08:58

1

On Windows -- open my.ini file, present at "C:\ProgramData\MySQL\MySQL Server 5.6", find "[mysqld]" (without quotes) in next line add explicit_defaults_for_timestamp and then save the changes.

answered Jun 05 '15 at 08:58

Vijay Nandwana

2,476
4
25
42

score 0 · Answer 9 · answered Jul 17 '14 at 16:56

0

I'm Using Windows 8.1 and I use this command

c:\wamp\bin\mysql\mysql5.6.12\bin\mysql.exe

instead of

 c:\wamp\bin\mysql\mysql5.6.12\bin\mysqld

and it works fine..

answered Jul 17 '14 at 16:56

How to enable explicit_defaults_for_timestamp?

9 Answers9

Linked