Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1067 (42000) Invalid Default Value for / ERROR 1292 (22007) Incorrect Datetime Value: - Zero Date is Not Accepted by Timestamp
Symptoms
There might be two of the following symptoms:
- On the import of DDL to the MySQL database the following message may arise: “ERROR 1067 (42000): Invalid default value for 'column_name'”.
- On the import of data to the MySQL database the following message may arise: “ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'column_name' at row 1”.
Causes
Although as the default zero dates are allowed in MySQL database, this is controlled by the sql_mode variable that may or may not contain the parameter NO_ZERO_DATE. If NO_ZERO_DATE parameter is added all TIMEASTAMP values are not allowed to store the zero date. The issue may arise in two cases:
- On the table creation, when zero date like '0000-00-00 00:00:00' is specified in the default clause of the TIMESTAMP column. For example:
mysql> select @@sql_mode; +-----------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE department -> ( -> id INT, -> name VARCHAR(100), -> creation_dt TIMESTAMP NOT NULL default '0000-00-00 00:00:00' -> ); ERROR 1067 (42000): Invalid default value for 'creation_dt'
- On data load, when zero date like '0000-00-00 00:00:00' is inserted into the TIMESTAMP column either by insert or load command. For example:
mysql> select @@sql_mode; +-----------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE department -> ( -> id INT, -> name VARCHAR(100), -> creation_dt TIMESTAMP -> ); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO department VALUES (1, 'DBA', '0000-00-00 00:00:00'); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'creation_dt' at row 1
Solutions
In order to fix this issue you may consider changing the MySQL database variable sql_mode by excluding the NO_ZERO_DATE parameter from it.
To check if the parameter is currently in sql_mode please use the next query:
SELECT @@sql_mode
If mode NO_ZERO_DATE is present, please remove it from the list of modes by running the set command with all the modes except NO_ZERO_DATE specified, see the example below:
If result of the query 'SELECT @@sql_mode' is:
+—————————————————————————–+
| @@sql_mode | +-----------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------+
just copy this result to SET sql_mode command excluding NO_ZERO_DATE:
set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Now zero values are allowed for the TIMESTAMP columns in the database.
If you have any other questions, please contact us: support@ispirer.com