MySQL Incorrect datetime value 0000-00-00 00 00 00

Question

I ve recently taken over an old project that was created 10 years ago  It uses MySQL 5 1   Among other things  I need to change the default character set from latin1 to utf8   As an example  I have tables such as this      CREATE TABLE  users         id  int 10  unsigned NOT NULL AUTO INCREMENT       first name  varchar 45  CHARACTER SET latin1 COLLATE latin1 general ci DEFAULT NULL       last name  varchar 45  CHARACTER SET latin1 COLLATE latin1 general ci DEFAULT NULL       username  varchar 127  CHARACTER SET latin1 COLLATE latin1 general ci NOT NULL       email  varchar 127  CHARACTER SET latin1 COLLATE latin1 general ci NOT NULL       pass  varchar 20  CHARACTER SET latin1 COLLATE latin1 general ci NOT NULL       active  char 1  CHARACTER SET latin1 COLLATE latin1 general ci NOT NULL DEFAULT  Y        created  datetime NOT NULL       last login  datetime DEFAULT NULL       author  varchar 1  CHARACTER SET latin1 COLLATE latin1 general ci DEFAULT  N        locked at  datetime DEFAULT NULL       created at  datetime DEFAULT NULL       updated at  datetime DEFAULT NULL       ripple token  varchar 36  CHARACTER SET latin1 COLLATE latin1 general ci DEFAULT NULL       ripple token expires  datetime DEFAULT  2014-10-31 08 03 55        authentication token  varchar 255  CHARACTER SET latin1 COLLATE latin1 general ci DEFAULT NULL      PRIMARY KEY   id        UNIQUE KEY  index users on reset password token    reset password token        UNIQUE KEY  index users on confirmation token    confirmation token        UNIQUE KEY  index users on unlock token    unlock token        KEY  users active    active        KEY  users username    username        KEY  index users on email    email       ENGINE InnoDB AUTO INCREMENT 1677 DEFAULT CHARSET utf8 CHECKSUM 1 DELAY KEY WRITE 1 ROW FORMAT DYNAMIC   I set up my own Mac to work on this  Without thinking too much about it  I ran  brew install mysql  which installed MySQL 5 7  So I have some version conflicts   I downloaded a copy of this database and imported it    If I try to run a query like this      ALTER TABLE users MODIFY first name varchar 45  CHARACTER SET utf8 COLLATE utf8 general ci    NOT NULL     I get this error      ERROR 1292  22007   Incorrect datetime value   0000-00-00 00 00 00  for column  created  at row 1   I thought I could fix this with      ALTER TABLE users MODIFY created datetime  NULL DEFAULT  1970-01-01 00 00 00     Query OK  0 rows affected  0 06 sec    Records  0  Duplicates  0  Warnings  0   but I get      ALTER TABLE users MODIFY first name varchar 45  CHARACTER SET utf8 COLLATE utf8 general ci    NOT NULL     ERROR 1292  22007   Incorrect datetime value   0000-00-00 00 00 00  for column  created  at row 1   Do I have to update every value

User · Answer

You can change the type of created field from datetime to varchar 255   then you can set  update  all records that have the value  0000-00-00 00 00 00  to NULL   Now  you can do your queries without error  After you finished  you can alter the type of the field created to datetime

User · Answer

According to MySQL 5 7 Reference Manual      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    Since 0000-00-00 00 00 00 is not a valid DATETIME value  your database is broken  That is why MySQL 5 7     which comes with NO ZERO DATE mode enabled by default     outputs an error when you try to perform a write operation   You can fix your table updating all invalid values to any other valid one  like NULL   UPDATE users SET created   NULL WHERE created  lt   0000-01-01 00 00 00    Also  to avoid this problem  I recomend you always set current time as default value for your created-like fields  so they get automatically filled on INSERT  Just do   ALTER TABLE users ALTER created SET DEFAULT CURRENT TIMESTAMP

User · Answer

If you are entering the data manually you may consider removing the values and the zeros on the TIMESTAMP 6  000000 so that it becomes TIMESTAMP  That worked fine with me

User · Answer

Make the sql mode non strict  if using laravel go to config- database   the go to mysql settings and make the strict mode false

User · Answer

I have this error as well after upgrading MySQL from 5 6 to 5 7 I figured out that the best solution for me was to combine some of the solutions here and make something of it that worked with the minimum of input  I use MyPHPAdmin for the simplicity of sending the queries through the interface because then I can check the structure and all that easily  You might use ssh directly or some other interface  The method should be similar or same anyway      1  First check out the actual error when trying to repair the db   joomla jos menu Note       TIME TIMESTAMP DATETIME columns of old format have been upgraded to the new format  Warning    Incorrect datetime value   0000-00-00 00 00 00  for column  checked out time  at row 1 Error      Invalid default value for  checked out time  status    Operation failed  This tells me the column checked out time in the table jos menu needs to have all bad dates fixed as well as the  quot default quot  changed      2  I run the SQL query based on the info in the error message  UPDATE jos menu SET checked out time    1970-01-01 08 00 00  WHERE checked out time   0  If you get an error you can use the below query instead that seems to always work  UPDATE jos menu SET checked out time    1970-01-01 08 00 00  WHERE CAST checked out time AS CHAR 20      0000-00-00 00 00 00       3  Then once that is done I run the second SQL query  ALTER TABLE  jos menu  CHANGE  checked out time   checked out time  DATETIME NULL DEFAULT CURRENT TIMESTAMP   Or in the case it is a date that has to be NULL ALTER TABLE  jos menu  CHANGE  checked out time   checked out time  DATETIME NULL DEFAULT NULL       If I run repair database now I get  joomla jos menu               OK     Works just fine

User · Answer

I had a similar problem but in my case some line had the value NULL   so first I update the table   update  my table set modified    1000-01-01 00 00 00  WHERE modified is null   problem solved  at least in my case

User · Answer

I got it fixed by doing this before the query   SET SQL MODE  ALLOW INVALID DATES

User · Answer

Here what my solution PhpMyAdmin   Fedora 29    MySQL 8 0  for example    set sql mode  SOMETHING   doesn t work  command call successful but nothing was change   set GLOBAL sql mode  SOMETHING   change global configuration permanent change   set SESSION sql mode  SOMETHING   change session configuration SESSION variable affects only the current client   https   dev mysql com doc refman 8 0 en sql-mode html  So I do this     Get SQL MODE   SHOW VARIABLES LIKE  sql mode   Result    ONLY FULL GROUP BY STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO ENGINE SUBSTITUTION Remove on the result   NO ZERO IN DATE NO ZERO DATE Set new configuration   set GLOBAL SQL MODE  ONLY FULL GROUP BY STRICT TRANS TABLES ERROR FOR DIVISION BY ZERO NO ENGINE SUBSTITUTION    You can remove or add other mode in the same way   This is helpful to change global for using and testing frameworks or sql mode must be specified in each file or bunch of queries   Adapted from a question ask here   how-can-i-disable-mysql-strict-mode  Example   install latest Joomla 4 0-alpha content   Edit  In PhpMyadmin  if you have the control of the server  you can change the sql mode  and all others parameters  directly in Plus  gt  Variables  gt  sql mode

User · Answer

I found the solution at https   support plesk com hc en-us articles 115000666509-How-to-change-the-SQL-mode-in-MySQL  I had this   mysql gt  show variables like  sql mode    --------------- -------------------------------------------------------------------------------------------------------------------------------------------    Variable name   Value                                                                                                                                        --------------- -------------------------------------------------------------------------------------------------------------------------------------------    sql mode        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    --------------- -------------------------------------------------------------------------------------------------------------------------------------------  1 row in set  1 warning  0 01 sec    Notice the NO ZERO IN DATE NO ZERO DATE in the results above  I removed that by doing this   mysql gt  SET sql mode    ONLY FULL GROUP BY STRICT TRANS TABLES ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION   Query OK  0 rows affected  1 warning  0 00 sec    Then I had this   mysql gt  show variables like  sql mode    --------------- --------------------------------------------------------------------------------------------------------------    Variable name   Value                                                                                                           --------------- --------------------------------------------------------------------------------------------------------------    sql mode        ONLY FULL GROUP BY STRICT TRANS TABLES ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION    --------------- --------------------------------------------------------------------------------------------------------------  1 row in set  1 warning  0 01 sec    After doing that  I could use ALTER TABLE successfully and alter my tables

User · Answer

Check  SELECT   sql mode    if you see  ZERO DATE  stuff in there  try  SET GLOBAL sql mode  SELECT REPLACE   sql mode  NO ZERO DATE           SET GLOBAL sql mode  SELECT REPLACE   sql mode  NO ZERO IN DATE             Log out and back in again to your client  this is strange  and try again

User · Answer

Changing the default value for a column with an ALTER TABLE statement  e g    ALTER TABLE users MODIFY created datetime  NULL DEFAULT  1970-01-02        doesn t change any values that are already stored  The  default  value applies to rows that are inserted  and for which a value is not supplied for the column     As to why you are encountering the error  it s likely that the sql mode setting for your session includes NO ZERO DATE   Reference  http   dev mysql com doc refman 5 7 en sql-mode html sqlmode no zero date  When you did the  import   the SQL statements that did the INSERT into that table were run in a session that allowed for zero dates   To see the sql mode setting   SHOW VARIABLES LIKE  sql mode      -or-  SELECT   sql mode     As far as how to  fix  the current problem  so that the error won t be thrown when you run the ALTER TABLE statement   Several options   1  change the sql mode to allow zero dates  by removing NO ZERO DATE and NO ZERO IN DATE  The change can be applied in the my cnf file  so after a restart of MySQL Server  sql mode variable will be initialized to the setting in my cnf   For a temporary change  we can modify the setting with a single session  without requiring a global change    -- save current setting of sql mode SET  old sql mode      sql mode    -- derive a new value by removing NO ZERO DATE and NO ZERO IN DATE SET  new sql mode     old sql mode   SET  new sql mode    TRIM BOTH     FROM REPLACE CONCAT      new sql mode        NO ZERO DATE            SET  new sql mode    TRIM BOTH     FROM REPLACE CONCAT      new sql mode        NO ZERO IN DATE          SET   sql mode     new sql mode    -- perform the operation that errors due to  zero dates   -- when we are done with required operations  we can revert back -- to the original sql mode setting  from the value we saved SET   sql mode     old sql mode     2  change the created column to allow NULL values  and update the existing rows to change the zero dates to null values  3  update the existing rows to change the zero dates to a valid date    We don t need to run individual statements to update each row  We can update all of the rows in one fell swoop  assuming it s a reasonably sized table  For a larger table  to avoid humongous rollback undo generation  we can perform the operation in reasonably sized chunks     In the question  the AUTO INCREMENT value shown for the table definition assures us that the number of rows is not excessive   If we ve already changed the created column to allow for NULL values  we can do something like this   UPDATE   users  SET  created    NULL WHERE  created     0000-00-00 00 00 00    Or  we can set those to a valid date  e g  January 2  1970  UPDATE   users  SET  created     1970-01-02  WHERE  created     0000-00-00 00 00 00     Note that a datetime value of midnight Jan 1  1970   1970-01-01 00 00 00   is a  zero date   That will be evaluated to be  0000-00-00 00 00 00

User · Answer

My solution  SET sql mode     UPDATE tnx k2 items SET created by   790   modified    0000-00-00 00 00 00    modified by   0

User · Answer

SET sql mode    NO ZERO DATE   UPDATE  news  SET  d stop   2038-01-01 00 00 00  WHERE  d stop   0000-00-00 00 00 00

User · Answer

This is incredibly ugly  but it also fixed the problem quickly for me  Your table needs a unique key which you will use to fix the tainted columns  In this example  the primary key is called  id  and the broken timestamp column is called  BadColumn    Select the IDs of the tainted columns  select id from table where BadColumn  0000-00-00 00 00 00   Collect the IDs into a comma-delimited string  Example  1  22  33  I used an external wrapper for this  a Perl script  to quickly spit them all out   Use your list of IDs to update the old columns with a valid date  1971 through 2038   update table set BadColumn  2000-01-01 00 00 00  where id in  1  22  33

User · Answer

My suggestion if it is the case that the table is empty or not very very big is to export the create statements as a  sql file  rewrite them as you wish  Also do the same if you have any existing data  i e  export insert statements  I recommend doing this in a separate file as the create statements   Finally  drop the table and execute first create statement and then inserts   You can use for that either mysqldump command  included in your MySQL installation or you can also install MySQL Workbench  which is a free graphical tool that includes also this option in a very customisable way without having to look for specific command options

User · Answer

I also got      SQLSTATE 22007   Invalid datetime format  1292 Incorrect datetime   value   0000-00-00 00 00 00  for column   error info  Fix this by changing   0000-00-00 00 00 00  to  1970-01-01 08 00 00  1970-01-01 08 00 00  unix timestamp is 0

User · Answer

This is what I did to solve my problem  I tested in local MySQL 5 7 ubuntu 18 04    set global sql mode  NO ENGINE SUBSTITUTION     Before running this query globally I added a cnf file in   etc mysql conf d directory  The cnf file name is mysql cnf and codes   mysqld  sql mode STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ALLOW INVALID DATES ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION   Then I restart mysql   sudo service mysql restart   Hope this can help someone

User · Answer

For Symfony users  Add a default to your orm column as follows          var DateTime  updatedAt        ORM Column name  quot updated at quot   type  quot datetime quot   nullable false  options   quot default quot     quot CURRENT TIMESTAMP quot

User · Answer

Instead of  UPDATE your table SET your column   new valid value where your column    0000-00-00 00 00 00     Use  UPDATE your table SET your column   new valid value where your column   0

User · Answer

I wasn t able to do this   UPDATE users SET created   NULL WHERE created    0000-00-00 00 00 00     on MySQL 5 7 13    I kept getting the Incorrect datetime value   0000-00-00 00 00 00  error   Strangely  this worked  SELECT   FROM users WHERE created    0000-00-00 00 00 00   I have no idea why the former fails and the latter works    maybe a MySQL bug   At any case  this UPDATE query worked   UPDATE users SET created   NULL WHERE CAST created AS CHAR 20      0000-00-00 00 00 00

[mysql] MySQL Incorrect datetime value: '0000-00-00 00:00:00'

Examples related to mysql