Error in MySQL when setting default value for DATE or DATETIME

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

User · Answer

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

User · Answer

In diretory xamp mysql bin Open  quot my ini quot  and change line  Sql node for - gt   quot sql mode NO ENGINE SUBSTITUTION NO ZERO DATE quot   REMOVE  quot NO ZERO IN DATE quot

User · Answer

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

User · Answer

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

User · Answer

I ve tested a fix as follow  1   On the file  quot system library db mysqli php quot  search and comment the line    quot  this- gt connection- gt query  quot SET SESSION sql mode    NO ZERO IN DATE NO ZERO DATE NO ENGINE SUBSTITUTION  quot    quot   2  Add the following line above the one you just commented     Correction by Added by A benkorich  this- gt connection- gt query  quot SET SESSION sql mode    ONLY FULL GROUP BY  quot

User · Answer

It works for 5 7 8  mysql gt  create table t1 updated datetime NOT NULL DEFAULT  0000-00-00 00 00 00    Query OK  0 rows affected  0 01 sec   mysql gt  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 gt  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

User · Answer

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

User · Answer

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 lt  1000-01-01    because all the incorrect my date field values  whether correct dates or not  were from before this date

User · Answer

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 quot   with this parameter  dates with zero values is not inserted  but without yes  MySQL is not really organised with these parameters and combinations

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

To solve the problem with MySQL Workbench  After applying the solution on the server side     Remove SQL MODE to TRADITIONAL in the preferences panel

[mysql] Error in MySQL when setting default value for DATE or DATETIME

Examples related to mysql

Examples related to sql

Examples related to date

Examples related to datetime

Examples related to console