Error in MySQL when setting default value for DATE or DATETIME
Solution 1
The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation
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:00
you 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
Solution 2
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."
Solution 3
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<'0000-01-01'
because all the incorrect my_date_field
values (whether correct dates or not) were from before this date.
Solution 4
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 = '...';
Solution 5
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:
Related videos on Youtube
Evhz
Professional Software Engineer -- Software Projects -- Infrastructure Architecture -- Data Engineering Free Coder NLP Freak Underlined esteem to: Light Client side Generative algorithyms Fault tolerance
Updated on April 22, 2022Comments
-
Evhz about 2 years
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?
-
Tom H about 8 yearsWhy would you want a default that is obviously meaningless? If the date is unknown then that's exactly what
NULL
is for. -
Gordon Linoff about 8 yearsAs a note: This works in version 5.6 on SQL Fiddle -- sqlfiddle.com/#!9/02c98.
-
geeksal about 8 years@Karlos check the updated answer.
-
Evhz over 3 years@TomH The fact that ZERO in a date seems to you meaningless is out the interest of many readers. Obviously,
NULL
is always there as it is0000-00-00 00:00:00
. Both of them, seem to me very different. To answer your comment, also out of the scope of this post, and sure of the full website itself.
-
-
Afanasii Kurakin about 7 yearsUPDATE IGNORE is what I was looking for 👍🏻
-
Green almost 7 yearsWrong. I have
STRICT_TRANS_TABLES
for my both MySQL instances, local and server. However, I can easily insert0000-00-00
in my local instance, but cannot in my server instance - error is thrown. Why? Because my server MySQL config hasNO_ZERO_DATE
enabled. And my local doesn't have it. -
geeksal almost 7 yearsok @Green I will figure out and update the answer if applicable
-
arjen Stens over 6 yearsIn my case 'SET sql_mode = ""' did not work. 'SET GLOBAL sql_mode = "";' did the job for me.
-
Preshan Pradeepa over 6 yearsAlso
NO_ZERO_DATE
should be removed -
geeksal over 6 yearsThanks @PreshanPradeepa your concerns are addressed
-
user2513149 over 5 yearsWorks for 5.7.23.
-
ipkpjersi over 5 yearsI had to use
SET sql_mode = '';
andSET GLOBAL sql_mode = '';
on Ubuntu 16.04 with MySQL 5.7.24 for it to work. -
Ricky McMaster over 5 yearsPerfect quick solution. You could in fact also use
<'0000-01-01'
since it is of course a valid date. -
Radley Sustaire about 5 yearsI would probably suggest
SELECT @@SESSION.sql_mode;
first, and them removing NO_ZERO_IN_DATE, NO_ZERO_DATE, and STRICT_TRANS_TABLES from what it gives you. That way you keep whatever other settings you had enabled. I had a lot more than just those two items set for my sql mode. Don't know what they all do, but I don't want to risk removing them at this point. -
vlizana almost 4 yearsWelcome to stackoverflow, please use proper code formatting to improve the legibility of the answer.
-
Evhz over 3 yearsThis is actually, a very accurate answer
-
Edoardo Facchinelli over 2 yearsOne of the few solutions I found that gives a hint how to fix the data instead of changing the mode, which is not an option for me.