This is an article discussing about how to solve a specific MySQL Error Message. The error message is in the title of this article. To be more specific, the error message is ‘ERROR 1292 (22007) : Incorrect datetime value: ‘0000-00-00 00:00:00′ for column’. It can appear when a restore process of a database fails. It is in the following output of command execution :
[root@hostname ~]# mysql -uroot db_app < db_app.sql ERROR 1292 (22007) at line XXX: Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateupdate' at row 1 [root@hostname ~]#
As in the above output command execution, the error message appears. It happens in the middle of restoring a database. In order to solve the problem, there is a specific setting of the sql_mode. The following is the step to solve it :
1. First, logging in to MySQL command console.
[root@hostname ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
2. Show the value of ‘sql_mode’ variable. Type the following query :
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 (0,01 sec) mysql>
3. Execute the following query to implement the solution temporarily :
mysql> set sql_mode=''; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> show variables like 'sql_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0,00 sec) mysql> quit Bye
4. Unfortunately, after quitting from the MySQL command console, the value return back to the original value. In order to change it permanently, edit the MySQL configuration file. Basically, it is located /etc/my.cnf. Add the following entry :
[mysqld] # # * Basic Settings # ... sql-mode = ""
The most important entry to solve the problem is the ‘sql-mode = “”‘ line.
- Finally, don’t forget to restart MySQL Database Server’s service. Do it as follows :
[root@hostname ~]# systemctl restart mysql [root@hostname ~]#
6. Last but not least, recheck the value of ‘sql-mode’ by typing the following command :
[root@hostname ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'sql-mode'; Empty set (0.01 sec) mysql> show variables like 'sql_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.00 sec) mysql>
7. Don’t forget to retry restoring the database after changing the sql_mode value.