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