CODEWITHSUNDEEP
mysql
lorm
lorm

Reputation: 3361

MySQL Incorrect datetime value: '0000-00-00 00:00:00'

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?

Upvotes: 273

Views: 592794

Answers (29)

Xjs
Xjs

Reputation: 33

Today I learned (and it seems not to be documented here) that if you have a table like

CREATE TABLE test (date DATE NOT NULL);

and you

INSERT IGNORE INTO test VALUES (NULL);

you will obtain a row containing the date value 0000-00-00 although it will be reported as invalid when not using the IGNORE specifier.

So, ultimately, you should be able, alternatively to the other possibilities noted in this thread, to convert your table by creating a new one with the same structure, using INSERT IGNORE INTO newtable SELECT * FROM oldtable, and renaming the table.

Upvotes: 0

GrowthCode
GrowthCode

Reputation: 55

This happened to me during a Rails migration when adding timestamps to an existing table.

There were many instances of the table already existing. Normally, it should have added a null value to the timestampes (created_at, updated_at) but for some reason was adding Time stamp of '0000-00-00 00:00:00' value.

I added a add_timestamps(:table_name, default: Date.today) to get around it. In hindsight, I should have added default: null to get around it. This only happened on our staging servers which has an older version of mysql.

Upvotes: 0

Majid Sayyed
Majid Sayyed

Reputation: 297

SET SQL_MODE='ALLOW_INVALID_DATES' 
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE','')) 
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',''))

Just run above queries on database. It will solve the issue permanently.

Upvotes: -2

Saleh Ibrahim
Saleh Ibrahim

Reputation: 17

You can use like:

like '0000-00-00 00:00:00'

Upvotes: -2

Oleksii Kuznietsov
Oleksii Kuznietsov

Reputation: 719

If you just want to set 0000-00-00 to your field without modifying mySQL configuration, then do following:

UPDATE your_table_name SET your_time_field=FROM_UNIXTIME(0) WHERE ...

FROM_UNIXTIME(0) doing the trick.

Upvotes: 1

Nikhil Mohadikar
Nikhil Mohadikar

Reputation: 1241

This issue is happening because MYSQL 5.7 introduced default modes like

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
=> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Among the default modes, there is a mode NO_ZERO_DATE which prevents you to put the value 0000-00-00 00:00:00 in the datetime column.

Temporary fix would be removing the mode NO_ZERO_DATE/NO_ZERO_IN_DATE from the default modes and keeping the other modes as it is.

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

But once you restart your mysql server, all your temporary settings will be gone and the default modes will be enabled again. To fix this on restart, change the default modes in my.cnf file on your machine.

Refer the docs :- https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#:~:text=To%20set%20the%20SQL%20mode,ini%20(Windows).

My personal opinion :- The right developmental practice would be putting a right datetime value in the column instead of 0000-00-00 00:00:00. So that you don't have to worry about the SQL modes.

Upvotes: 10

jawad846
jawad846

Reputation: 772

I tried

SET GLOBAL sql_mode = '';

and, restart

sudo service mysql restart

which works for me

Upvotes: 3

kcjonez
kcjonez

Reputation: 11

Reading through the answers, I tried many of them with no change. I still get the error.

I researched it for a bit and found one that actually worked; for me anyway. But if it worked for me, it'll most likely work for everyone. Cause I never find the answers! lol

I'm on Ubuntu 18.04, But I have used other Linux OS's like Fedora, Straight forward Linux, XAMPP, yadda. And this file seemed to be the same on all of them. I'm also using MySQL version 5.7.37-0.

On Ubuntu, I go to /etc/mysql/. In there, you have several conf files, but the one you want is my.cnf.

In my.cnf, you'll have a section labelled [mysqld]. Just below that (I put it before anything else), you'll enter the following:

sql_mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart you MySQL Server and you should no longer receive that error.

That is the permanent solution. There is a temporary solution by entering the SQL Command--

SET sql_mode="";

--before your INSERT or UPDATE QUERY which contains such date or datetime values as ‘0000-00-00 00:00:00’, but that's a temporary solution that remains in your current session and you'll have to enter it again the next time you log in and have the same issue.

So, the permanent solution seems the way to go.

Upvotes: 1

Heretic Sic
Heretic Sic

Reputation: 406

I think you should use current_timestamp() instead of '0000-00-00 00:00:00'

Upvotes: 0

Mees van Wel
Mees van Wel

Reputation: 1696

For Symfony users: Add a default to your orm column as follows:

/**
 * @var DateTime $updatedAt
 *
 * @ORM\Column(name="updated_at", type="datetime", nullable=false, options={"default" : "CURRENT_TIMESTAMP"})
 */

Upvotes: 3

Reynier
Reynier

Reputation: 949

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;

Upvotes: 38

Jaime Montoya
Jaime Montoya

Reputation: 7711

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

Upvotes: 15

VietPublic
VietPublic

Reputation: 39

My solution

SET sql_mode='';
UPDATE tnx_k2_items
SET created_by = 790
, modified = '0000-00-00 00:00:00'
, modified_by = 0

Upvotes: 2

felwithe
felwithe

Reputation: 2744

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

  1. Select the IDs of the tainted columns.

    select id from table where BadColumn='0000-00-00 00:00:00'

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

  3. 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)

Upvotes: 1

Isaac
Isaac

Reputation: 1

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.

Upvotes: 0

commonpike
commonpike

Reputation: 11185

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

Upvotes: 11

Shim-Sao
Shim-Sao

Reputation: 2126

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 > Variables > sql_mode

Upvotes: 23

Kalyan Halder
Kalyan Halder

Reputation: 1585

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.

Upvotes: 6

Lenon Tolfo
Lenon Tolfo

Reputation: 387

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.

Upvotes: 3

Don King
Don King

Reputation: 301

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 "default" 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 :)

Upvotes: 4

Tariq Khan
Tariq Khan

Reputation: 6136

I got it fixed by doing this before the query 

SET SQL_MODE='ALLOW_INVALID_DATES';

Upvotes: 203

Andrew March
Andrew March

Reputation: 101

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'

Upvotes: 10

Milind Chaudhary
Milind Chaudhary

Reputation: 1740

Make the sql mode non strict

if using laravel go to config->database, the go to mysql settings and make the strict mode false

Upvotes: 4

Abdallah Ajlouni
Abdallah Ajlouni

Reputation: 81

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.

Upvotes: 5

spencer7593
spencer7593

Reputation: 108500

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'

Upvotes: 223

Ivan Filho
Ivan Filho

Reputation: 763

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

Upvotes: 64

tekintian
tekintian

Reputation: 335

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

Upvotes: 2

obe
obe

Reputation: 7806

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'

Upvotes: 307

Lucia Pasarin
Lucia Pasarin

Reputation: 2308

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.

Upvotes: 10

Related Questions