I'm running MySql Server 5.7.11 and this sentence:

updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

is not working. Giving the error:

ERROR 1067 (42000): Invalid default value for 'updated'

But the following:

updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

just works.

The same case for DATE.

As a sidenote, it is mentioned in the MySQL docs:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

even if they also say:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

Having also into account the second quote from MySQL documentation, could anyone let me know why it is giving that error?

I had this error with WAMP 3.0.6 with MySql 5.7.14.

Solution:

change line 70 (if your ini file is untouched) in c:\wamp\bin\mysql\mysql5.7.14\my.ini file from

sql-mode= "STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

to

sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

and restart all services.

This will disable strict mode. As per the documentation, “strict mode” means a mode with either or both STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled. The documentation says:

"The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION."

I got into a situation where the data was mixed between NULL and 0000-00-00 for a date field. But I did not know how to update the '0000-00-00' to NULL, because

 update my_table set my_date_field=NULL where my_date_field='0000-00-00'

is not allowed any more. My workaround was quite simple:

update my_table set my_date_field=NULL where my_date_field<'1000-01-01'

because all the incorrect my_date_field values (whether correct dates or not) were from before this date.

First select current session sql_mode:

SELECT @@SESSION.sql_mode;

Then you will get something like that default value:

'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

and then set sql_mode without 'NO_ZERO_DATE':

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

If you have grants, you can do it also for GLOBAL:

SELECT @@GLOBAL.sql_mode;
SET GLOBAL sql_mode = '...';

Config syntax issue

On some versions of MYSQL (tested 5.7.*) under *nix systems you should use this syntax:

[mysqld]

sql-mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_ENGINE_SUBSTITUTION"

These won't work:

dash no quotes

sql-mode=NO_ENGINE_SUBSTITUTION

underscore no quotes

sql_mode=NO_ENGINE_SUBSTITUTION

underscore and quotes

sql_mode="NO_ENGINE_SUBSTITUTION"

A more complete review of config values and sql-mode:

How to setup permanent Sql Mode flags

Just add the line: sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

inside file: /etc/mysql/mysql.conf.d/mysqld.cnf

then sudo service mysql restart

It works for 5.7.8:

mysql> create table t1(updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

You can create a SQLFiddle to recreate your issue.

http://sqlfiddle.com/

If it works for MySQL 5.6 and 5.7.8, but fails on 5.7.11. Then it probably is a regression bug for 5.7.11.

To solve the problem with MySQL Workbench (After applying the solution on the server side) :

Remove SQL_MODE to TRADITIONAL in the preferences panel.

This answer it's just for MySQL 5.7:

Best is not really set in blank the sql_mode, instead use in PHP a session variable with:

SET SESSION sql_mode= 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

So at least you keep the other default values.

It's crazy that mysql documentation is not clear, you need delete to these default values in sql_mode:

NO_ZERO_IN_DATE,NO_ZERO_DATE, I understand, but in the future versions this will be discontinued.

STRICT_ALL_TABLES, with this, before parameters will be ignored, so you need to delete it too.

Finally TRADITIONAL too, but documentation speaks about this parameter: “give an error instead of a warning” when inserting an incorrect value into a column", with this parameter, dates with zero values is not inserted, but without yes.

MySQL is not really organised with these parameters and combinations.

I've tested a fix as follow:

1). On the file "system/library/db/mysqli.php" search and comment the line: 
"$this->connection->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION'");"

2) Add the following line above the one you just commented:
// Correction by Added by A.benkorich
$this->connection->query("SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY'");

Option combinations for mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64).

Doesn't throw:

STRICT_TRANS_TABLES + NO_ZERO_DATE

Throws:

STRICT_TRANS_TABLES + NO_ZERO_IN_DATE

My settings in /etc/mysql/my.cnf on Ubuntu:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

In diretory xamp/mysql/bin Open "my.ini" and change line: Sql_node for ->

"sql_mode=NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE"

REMOVE "NO_ZERO_IN_DATE"

set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';