Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to MySQL Request SQLWays

ERROR 1067 (42000)/ERROR 1292 (22007) 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.


Ispirer Home Page Ispirer SQLWays Product Page - Migration to MySQL Request SQLWays

sqlways/troubleshooting-guide/mysql/import/zero-date-not-accepted.txt · Last modified: May 07, 2018, 03:15:34 PM (external edit)
 
© 1999-2018, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement