2

I am converting a db from MyISAM to InnoDb. I am getting the error of #1292 Incorrect datetime value: '0000-00-00 00:00:00'. I have read about some of these errors but I can not change the way information is stored to the DB. It gets stored in the above format. IS there a good solution to this?

michael
  • 31
  • 6
  • 1
    Don't store such dates which don't exist anyway. Store `NULL` instead. – sticky bit Dec 20 '20 at 03:17
  • Thanks. I would like to but when using the following query: UPDATE `mytable` SET column_name= NULL WHERE column_name= '0000-00-00 00:00:00' I get the same error - that it is an incorrect time value. I can not change the column structure which is a DATETIME. NULL is the new default but was not when some data was entered – michael Dec 20 '20 at 03:26
  • 1
    From [https://stackoverflow.com/a/37780259/378779](https://stackoverflow.com/a/37780259/378779): `UPDATE users SET col = NULL WHERE CAST(col AS CHAR(20)) = '0000-00-00 00:00:00';` – kmoser Dec 20 '20 at 04:07
  • thanks @kmoser it doesnt work in this environment since the default is that bad format and because its WP's master table you have to be careful changing that – michael Dec 20 '20 at 18:17

1 Answers1

2

Here's the error:

mysql> update mytable set d = null where d = '0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'd' at row 1

Why is this a problem? Because '0000-00-00 00:00:00' violates the sql modes NO_ZERO_DATE and NO_ZERO_IN_DATE. In MySQL 8.0, these modes are implicit when you use STRICT_TRANS_TABLES or STRICT_ALL_TABLES.

Merely using that value in your WHERE clause when comparing to a datetime triggers the violation, because MySQL tries to convert that string into a datetime value.

Here's a solution: Temporarily change the sql mode to be non-strict, to allow invalid date values.

mysql> set sql_mode='';

This changes the sql_mode only during your current session. Once you quit the mysql client, session-scoped options will disappear, and the next time you open a session, it will take the option from the global setting.

That allows the UPDATE, so you can use that invalid datetime string in your query at least long enough to change the corresponding values to NULL.

mysql> update mytable set d = null where d = '0000-00-00 00:00:00';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mytable;
+----+------+
| id | d    |
+----+------+
|  1 | NULL |
+----+------+

Do not make your sql mode non-strict except for this cleanup operation. It's a good thing to use a strict sql mode, because it prevents the database from storing bogus data values. Strict mode also prevents data truncation, if you were to store a value that doesn't fit in a column.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    thank you for this. The default WP tables use the outdated an unnecessary TIMEFORMAT instead of NULL as their default hamstringing the possibility to change those. This solution makes sense. – michael Dec 20 '20 at 18:12
  • so the next issue is that this 0000-00-00 00:00:00 is the default for 4 columns in this WP master table. Ways around that other than changing the default which seems risky – michael Dec 20 '20 at 18:28
  • I would change the default. If you have no default, don't declare one. It's okay for a table to have no default and also be NOT NULL, it means any INSERT must specify a value or else get an error. Obviously this requires you test your app carefully first or else it you may have failures. – Bill Karwin Dec 20 '20 at 19:00
  • Alright I can give it a try. These are defaults as placed by WP, not by me which is why I was nervous. – michael Dec 20 '20 at 19:27
  • This is unfortunately a bad design decision in WP to rely on invalid date values. Many WP plugins also went along with this as a convention. It's hard to correct so many independent plugins now. Read https://core.trac.wordpress.org/ticket/41785 – Bill Karwin Dec 20 '20 at 19:34
  • appreciated. Thanks WP – michael Dec 20 '20 at 19:59