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?

The accepted answer is outdated please see @TariqKhan's answer below for mysql 8.0 — rubo77, Apr 19 '21 at 23:19

score 289 · Answer 1 · answered Jun 12 '16 at 23:56

289

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'

answered Jun 12 '16 at 23:56

obe

7,378
5
31
40

19

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'` – Brian Leishman Aug 02 '16 at 20:16
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. – Chrysweel Aug 09 '16 at 11:41
65

For me it worked like `UPDATE users SET created = NULL WHERE created=0` (without ' around zero) – KIR May 12 '17 at 16:25
1

For replacing a "0000-00-00" date only without timestamp, I used CHAR(11) – D.Tate May 18 '17 at 22:53
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)? – Mike Purcell Jun 15 '17 at 20:19
2

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. – Arvanitis Christos Jan 10 '18 at 00:30
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`. – Brian C Nov 08 '19 at 02:01
Just to add a little more info, using `WHERE YEAR(created) = "0000"` also works. – CJ Nimes Nov 29 '19 at 21:57
CAST(created AS CHAR(20)) this worked for me like a charm – PrafulPravin Apr 01 '20 at 15:58

score 212 · Answer 2 · edited Jul 13 '17 at 14:01

212

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'

edited Jul 13 '17 at 14:01

Yoan Tournade

623
10
15

answered Feb 22 '16 at 23:20

spencer7593

106,611
15
112
140

3

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 ! – mili Jan 29 '17 at 05:17
Set NO_ZERO_DATE: http://stackoverflow.com/questions/3891896/blocking-0000-00-00-from-mysql-date-fields – user 1007017 Jan 31 '17 at 14:27
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. – Mike Weir Oct 29 '19 at 20:30
1

@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: https://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, – spencer7593 Oct 30 '19 at 14:18
@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. – Mike Weir Oct 30 '19 at 19:20
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'`. – David Baucum Dec 18 '19 at 19:42
@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 Dec 18 '19 at 20:04
@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 ... – spencer7593 Dec 18 '19 at 20:21
@spencer7593 Agree completely. – David Baucum Dec 19 '19 at 18:50
> 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? – Dr. House Jan 10 '20 at 14:28
@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. – spencer7593 Jan 10 '20 at 15:14
Please note, that since MySQL 8.0 you also need to add `ALLOW_INVALID_DATES` – rubo77 Apr 19 '21 at 23:15

score 177 · Answer 3 · edited Mar 15 '19 at 08:34

177

I got it fixed by doing this before the query

SET SQL_MODE='ALLOW_INVALID_DATES';

edited Mar 15 '19 at 08:34

AamirR

11,672
4
59
73

answered Sep 14 '18 at 14:34

Tariq Khan

5,498
4
28
34

1

This is the only answer. Used as: --init-command='SET SESSION FOREIGN_KEY_CHECKS=0;SET SQL_MODE='ALLOW_INVALID_DATES' – Konchog Sep 17 '19 at 10:06
2

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 Apr 19 '21 at 23:13
I would like to click on UP 10 more times! – Harun Baris Bulut Sep 16 '21 at 15:59
3

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` – John Skiles Skinner Oct 07 '21 at 00:15
1

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 – Cfreak Apr 25 '22 at 21:13
To all MySQL Newbies: This command overwrites your current SQL_MODE Settings and can bring you in some serious trouble, – Wolfgang Blessen Mar 02 '23 at 13:10

score 56 · Answer 4 · answered Jan 25 '17 at 17:56

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

score 32 · Answer 5 · answered Jun 18 '20 at 07:36

32

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;

answered Jun 18 '20 at 07:36

Reynier

790
9
20

3

Worked like a charm. This one should be marked as answer in my opinion. Thanks! – Michael Oct 10 '20 at 14:22
1

This saved my day. Thanks! – mixable Apr 02 '21 at 18:01

Shim-Sao · Answer 6 · 2019-08-03T12:25:08.467

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

plus 1 Thanks. if this is being reset when restart Ubuntu Machine, add this in /etc/mysql/conf.d — Bhavin Rana, Sep 12 '20 at 14:33

Jaime Montoya · Answer 7 · 2020-04-17T15:44:08.700

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.

in the fly: SET sql_mode=(SELECT REPLACE(@@sql_mode, 'NO_ZERO_IN_DATE', '')); and same with second option — djdance, Dec 07 '20 at 06:36

score 10 · Answer 8 · edited Sep 11 '18 at 16:54

10

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'

edited Sep 11 '18 at 16:54

Zoe

27,060
21
118
148

answered Sep 11 '18 at 16:44

Andrew March

101
1
2

4

This answer would be improved with discussion of why this solves the issue. – KevinO Sep 11 '18 at 18:09
is this affects the datetime storage.? or cause any problems – Ask Xah Sep 17 '18 at 09:21

score 10 · Answer 9 · answered Dec 14 '19 at 19:34

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

score 9 · Accepted Answer · answered Feb 22 '16 at 22:36

9

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.

answered Feb 22 '16 at 22:36

Lucia Pasarin

2,268
1
21
37

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? – lorm Feb 22 '16 at 23:04
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 http://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. – Lucia Pasarin Feb 22 '16 at 23:11
2

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. – Brandon Sep 26 '18 at 16:33
1

Answer in comment below is the best UPDATE users SET created = NULL WHERE created=0; – Datbates Jul 30 '20 at 05:55

Nikhil Mohadikar · Answer 11 · 2022-04-08T14:45:59.907

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.

score 5 · Answer 12 · edited Jan 31 '18 at 10:40

5

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.

edited Jan 31 '18 at 10:40

Shahriar

13,460
8
78
95

answered Jan 31 '18 at 09:49

Abdallah Ajlouni

81
1
3

score 5 · Answer 13 · answered Jul 16 '19 at 05:08

5

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.

answered Jul 16 '19 at 05:08

Kalyan Halder

1,485
24
28

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 Oct 27 '19 at 17:21
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`. – Smamatti Oct 27 '19 at 17:31

score 4 · Answer 14 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 15 · edited Jan 23 '17 at 12:14

3

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

edited Jan 23 '17 at 12:14

afxentios

2,502
2
21
24

answered Jan 23 '17 at 11:02

tekintian

277
3
3

1

the problem is OP can't change the date because error. I guess it's error from `NO_ZERO_DATE ` – GusDeCooL Dec 11 '17 at 07:06

score 3 · Answer 16 · answered Jul 13 '18 at 10:44

3

Make the sql mode non strict

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

answered Jul 13 '18 at 10:44

Milind Chaudhary

1,632
1
17
16

Can I do this in myphpadmin? – Don King Apr 10 '19 at 10:28
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. – Milind Chaudhary Apr 10 '19 at 17:01
1

yeah I found all thats needed to turn off strict mode is adding sql_mode= (and nothing after it), by bottom of my.cnf – Don King Apr 10 '19 at 19:45

score 3 · Answer 17 · answered Apr 11 '19 at 14:23

3

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.

answered Apr 11 '19 at 14:23

Lenon Tolfo

359
4
9

score 3 · Answer 18 · answered Feb 03 '21 at 10:08

3

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"})
 */

answered Feb 03 '21 at 10:08

Mees van Wel

1,393
1
7
13

score 3 · Answer 19 · answered Mar 17 '22 at 10:37

3

I tried

SET GLOBAL sql_mode = '';

and, restart

sudo service mysql restart

which works for me

answered Mar 17 '22 at 10:37

jawad846

683
1
9
21

score 2 · Answer 20 · answered Mar 27 '20 at 15:20

2

My solution

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

answered Mar 27 '20 at 15:20

VietPublic

39
2

score 1 · Answer 21 · edited Jun 20 '20 at 09:12

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)

score 1 · Answer 22 · edited Feb 18 '22 at 19:08

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.

score 0 · Answer 23 · answered Jan 30 '20 at 09:32

0

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.

answered Jan 30 '20 at 09:32

Isaac

1

score 0 · Answer 24 · answered Nov 05 '21 at 08:34

0

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

answered Nov 05 '21 at 08:34

Heretic Sic

364
2
9

score 0 · Answer 25 · answered Nov 07 '22 at 15:41

0

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.

answered Nov 07 '22 at 15:41

Aleksey Kuznetsov

623
6
10

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

25 Answers25

Linked

Related