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?

Solution 1

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.

Solution 2

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'

Solution 3

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'

Solution 4

I got it fixed by doing this before the query

SET SQL_MODE='ALLOW_INVALID_DATES';

Solution 5

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 < '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

View more solutions

Comments

[rubo77]

The accepted answer is outdated please see @TariqKhan's answer below for mysql 8.0

[Tom]

Thank you that you asked before me :)

[lorm]

Lucia Pasarin, I like your idea very much, but won't the data get truncated? Does some UTF8 data take up more bytes than latin1? If something previously fit in varchar 255, perhaps now it won't? Should I, perhaps, change all varchars to "text" fields?

[Lucia Pasarin]

Yes, you are right. That could happen since latin1 uses 1 byte per char, whereas utf8 uses at most 4 bytes per char (depending on the version of MySQL and on the type of utf8 dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8.html). Therefore, you don't necessarily need TEXT type. I would assume that x4 your previously existing sizes should work.

[Brian Leishman]

I had the same problem, but setting the last part to just 0 fixed it for me like this: UPDATE users SET created = NULL WHERE created = '0'

[Chrysweel]

SELECT * FROM entity WHERE createdAt = "0000-00-00 00:00:00" work fine, but with an updated fail ! I had the same problem. Fix it with solution of @obe CAST(created AS CHAR(20)) ... I think that it is a bug.

[mili]

yes, this worked for me. I searched for sql-mode in my.ini file and removed NO_ZERO_IN_DATE and NO_ZERO_DATE . then restarted the service. thank you spencer7593 !

[user 1007017]

Set NO_ZERO_DATE: stackoverflow.com/questions/3891896/…

[KIR]

For me it worked like UPDATE users SET created = NULL WHERE created=0 (without ' around zero)

[D.Tate]

For replacing a "0000-00-00" date only without timestamp, I used CHAR(11)

[Mike Purcell]

Damn, I've only ever seen '0000-00-00 00:00:00' as a filler for "null" datetime columns. Before I go and change the 32 columns I have like this, is it ANSI standard to have null or an actual date (start of epoch)?

[GusDeCooL]

the problem is OP can't change the date because error. I guess it's error from NO_ZERO_DATE

[Arvanitis Christos]

That is pure genius. Why is this not the accepted answer? For date only without timestamp the minimum value is 1000-01-01. Consider using this as the default value for every date attribute you intent to leave empty or with 0000-00-00 value.

[KevinO]

This answer would be improved with discussion of why this solves the issue.

[Ask Xah]

is this affects the datetime storage.? or cause any problems

[Brandon]

This is the database equivalent of reformatting your hard drive when you want to delete a file. It's safe to say this solution is not acceptable in a production environment.

[Don King]

Can I do this in myphpadmin?

[Milind Chaudhary]

phpMyAdmin is just a interface to use the actual mysql server, it doesn't matter which interface you are using mysql commands won't change with the interface. Try this command (set sql_mode='';) or this (set global sql_mode='';) to turn it off.

[Don King]

yeah I found all thats needed to turn off strict mode is adding sql_mode= (and nothing after it), by bottom of my.cnf

[Konchog]

This is the only answer. Used as: --init-command='SET SESSION FOREIGN_KEY_CHECKS=0;SET SQL_MODE='ALLOW_INVALID_DATES'

[Smamatti]

This solution is working until I restart the MySQL server. Even after the restart the value NO_ENGINE_SUBSTITUTION is in all columns of SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode; but still I get an error for the invalid datetime 0000-00-00 00:00:00 until I execute the first query again set global sql_mode="NO_ENGINE_SUBSTITUTION"; Any ideas?

[Smamatti]

The solution in my case was to remove NO_ZERO_IN_DATE,NO_ZERO_DATE in your version of the my.ini line defining the sql_mode.

[Mike Weir]

When I do #2 "change the created column to allow NULL values", it won't let me because the column values still produce the error. Quite stuck.

[spencer7593]

@MikeWeir: presumably "won't let me" means that an error is returned when a SQL statement is executed. That's likely due to the setting of sql_mode. More recent versions of MySQL have default settings of sql_mode that are more strict that previous versions. Reference for 8.0 here: dev.mysql.com/doc/refman/8.0/en/sql-mode.html see NO_ZERO_DATE, ALLOW_INVALID_DATE, et al. note that some are included in STRICT mode e.g. STRICT_TRANS_TABLES, STRICT_ALL_TABLES and other combo modes. To workaround the restrictions, temporarily modify sql_mode for the session,

[Mike Weir]

@spencer7593 for sure. I didn't feel like that option either was best. I took your advice of setting a very old date value (1970) and my system will just ignore it. Thanks for all your detail.

[Brian C]

KIR's answer above - UPDATE users SET created = NULL WHERE created=0 - worked like a charm for me. This is the simplest answer and should be marked as such! See also comments below re NO_ZERO_DATE and NO_ZERO_IN_DATE.

[CJ Nimes]

Just to add a little more info, using WHERE YEAR(created) = "0000" also works.

[David Baucum]

I would argue there are almost zero instances where a date field should be NOT NULL and also default to '1970-01-02'. Is there really an instance where you want to record a date for something, and by default that date is '1970-01-02'? If you don't yet have a valid value for a date then it should be NULL until you have an actual date. This also makes querieing more sensable. e.g. where occurence is null vs something less obvious like where occurence = '1970-01-02'.

[spencer7593]

@DavidBaucum: you could make a similar argument for a DATE value of '0000-00-00'. You can make an argument for/against the NO_ZERO_DATE and NO_ZERO_IN_DATE in sql_mode. But that wasn't the question that was asked. OP asked about how to get the ALTER TABLE statement to execute. This answer described a scenario that allowed the "zero" dates to be loaded, and one possible approach as a workaround, replacing the zero dates with a valid date. We could set the column to NULL if it weren't for the NOT NULL constraint; to remove it we have to workaround the invalid zero date, w/sql_mode or upd

[spencer7593]

@DavidBaucum: option 2) in this answer suggests allowing NULL values for the column; we can temporarily adjust sql_mode to allow the zero dates , and then do an UPDATE to assign NULL in place of 0000-00-00 ...

[David Baucum]

@spencer7593 Agree completely.

[Dr. House]

> ALTER TABLE users MODIFY created datetime NULL DEFAULT '1970-01-02' If my created column was an index as well - wouldn't this remove the index?

[spencer7593]

@Mikey No. Altering a column to change the default value would not remove any index from the table. In the more general case, for columns that are included in an index, we could encounter errors for some column alterations, for example, if increasing the maximum size (length) for a character column would violate the maximum length allowed for an index. But we would expect MySQL to return an error, not remove an index.

[PrafulPravin]

CAST(created AS CHAR(20)) this worked for me like a charm

[Datbates]

Answer in comment below is the best UPDATE users SET created = NULL WHERE created=0;

[Bhavin Rana]

plus 1 Thanks. if this is being reset when restart Ubuntu Machine, add this in /etc/mysql/conf.d

[Michael]

Worked like a charm. This one should be marked as answer in my opinion. Thanks!

[djdance]

in the fly: SET sql_mode=(SELECT REPLACE(@@sql_mode, 'NO_ZERO_IN_DATE', '')); and same with second option

[mixable]

This saved my day. Thanks!

[rubo77]

This is needed in MySQL 8.0, also add this and remove NO_ZERO_IN_DATE and NO_ZERO_DATE from sql mode in the corresponding file inside /etc/mysql/conf.d/

[rubo77]

Please note, that since MySQL 8.0 you also need to add ALLOW_INVALID_DATES

[Harun Baris Bulut]

I would like to click on UP 10 more times!

[John Skiles Skinner]

YES! Lifesaving answer. I used it in this way: mysql -u username -p database --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET SQL_MODE='ALLOW_INVALID_DATES';" < filename.sql

[Frik]

Also add ALLOW_INVALID_DATES

[Cfreak]

do not do SET FOREIGN_KEY_CHECKS=0! You don't need that to fix the date problem and you really shouldn't do it unless you know what you're doing. Disabling foreign keys will lead to data integrity errors