Error in mysql when setting default value for DATE

Question

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?

Answer 1

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 be a regression bug for 5.7.11.

Answer 2

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 = '...';

Answer 3

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

Remove SQL_MODE to TRADITIONAL in the preferences panel.

Answer 4

The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation

MySQL Documentation 5.7 says:

Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

To Check MYSQL mode

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Disabling STRICT_TRANS_TABLES mode

However to allow the format 0000-00-00 00:00:00you have to disable STRICT_TRANS_TABLES mode in mysql config file or by command

By command

SET sql_mode = '';

or

SET GLOBAL sql_mode = '';

Using the keyword GLOBAL requires super previliges and it affects the operations all clients connect from that time on

if above is not working than go to /etc/mysql/my.cnf (as per ubuntu) and comment out STRICT_TRANS_TABLES

Also, if you want to permanently set the sql mode at server startup then include SET sql_mode='' in my.cnf on Linux or MacOS. For windows this has to be done in my.ini file.

Note

However strict mode is not enabled by default in MYSQL 5.6. Hence it does not produce the error as per MYSQL 6 documentation which says

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE SQL mode.

UPDATE

Regarding the bug matter as said by @Dylan-Su:

I don't think this is the bug it the way MYSQL is evolved over the time due to which some things are changed based on further improvement of the product.

However I have another related bug report regarding the NOW() function

Datetime field does not accept default NOW()

Another Useful note [see Automatic Initialization and Updating for TIMESTAMP and DATETIME]

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.

Update Regarding NO_ZERO_DATE

As of MySQL as of 5.7.4 this mode is deprecated. For previous version you must comment out the respective line in the config file. Refer MySQL 5.7 documentation on NO_ZERO_DATE

Answer 5

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"

Answer 6

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."