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?
1 Answers
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.
Related