How to Fix Quickly the Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘your_column’ at row … Error in MySQL / MariaDB
In some settings MySQL / MariaDB treats empty or null ‘datetime’ and ‘timestamp’ fields/columns as simply null, in others it writes out zeros (0s) like 0000-00-00 00:00:00. If you designed your own database, you probably know what you’re dealing with, already. But if you are working on someone else’s database, like migrating data to or from, or just doing development for it, for example, you may encounter the error “Incorrect datetime value: ‘0000-00-00 00:00:00’ for column …”.
This error can become a bit of headache if a sync .sql script or dump file you need to run now and then or on a regular basis contains datetime values filled with zeros instead of null, or if you are like me and using a tool like Navicat to transfer or synchronize entire databases between two servers, you are prone to encounter this error – especially if you have no control on the settings of the remote database server such as that of a shared hosting. The root cause of the error is simply the executed SQL command does not conform the “SQL mode” of the server in dealing with empty date, date time or timestamp values.
While the section 5.1.10 Server SQL Modes section of the MySQL reference manual published at https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date explains what these SQL modes are and how they work, it may at first site look or sound a bit complicated in order to solve simply a date(time) or timestamp value such as ‘0000-00-00 00:00:00’ existing as actual data in one instance of a MySQL / MariaDb can’t simply be put in another instance.
A typical MySQL installation comes with a default mode denoted by MySQL’s ‘@@sqlmode’ notation which is queryable via an SQL command like below:
SELECT @@sql_mode;
and when run, it returns a result such as
@@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
I have marked the related mode values in bold, above. In such a case as the above setting which has NO_ZERO_DATE and/or NO_ZERO_IN_DATE, a query like the following produces the error (coded 1292) Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘blah_blah’ :
INSERT INTO users (id, email, username, created, modified) VALUES (123, '[email protected]', 'Hermanzef', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
Error produced:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
Temporary, Case-basis Solution :
Add or run 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’.
If you need the other settings such as ERROR_FOR_DIVISION_BY_ZERO or NO_ENGINE_SUBSTITUTION and only want to allow zero-filled date / datetime values then you can use something like the following:
SET sql_mode='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Permanent Solution :
If this is something you will encounter more often than once or twice, such as regular database synchronizations with another host or a dumped SQL file to be used more than once, then you may want to set the above SQL mode permanently to avoid the incorrect datetime value error.
In that case, simply edit your mysql.cnf options file, find the section starting with “[mysql]” (without quotes), and then after the [mysql] line at add following:
[mysqld] sql_mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
If your MySQL instance is in MAMP Pro,
- Open the main window of MAMP Pro and via the menu go to File > Edit Templates > MySQL > (your version).
This opens a text editor with your MySQL configuration. - In the configuration file, find the line starting with
[mysqld]
(be sure to locate thed
at the end, and not to confuse with [mysql]). - If sql_mode isn’t set, you can add it under the
[mysqld]
heading: Right after this line, add a new line containing:
sql_mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
- Save the file, and restart MySQL.
If you don’t have a GUI admin interface or simply just prefer getting things done in a command-line terminal:
$ mysql -u root -p -e "SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';"
If you are not happy with the other modes you can simply set the sql_mode to a blank string like so: sql_mode=”, but again if all we care about is zero-date-time tolerance and to avoid the error 1292, all we need is to take out
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
from the sql_mode of MySQL / MariaDb.