Ispirer Ispirer
 

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

sqlways:troubleshooting-guide:mysql:import:zero-date-not-accepted [May 07, 2018, 03:15:34 PM] (current)
Line 1: Line 1:
 +\\
 +[[http://www.ispirer.com|Ispirer Home Page]]
 +[[http://www.ispirer.com/products/mysql-migration|Ispirer SQLWays Product Page - Migration to MySQL]]
 +[[http://www.ispirer.com/?click=RequestSQLWays&from=IspirerWIKI|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 <html><acronym title="Data Definition Language">DDL</acronym></html> 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.
 +
 +\\
 +[[http://www.ispirer.com|Ispirer Home Page]]
 +[[http://www.ispirer.com/products/mysql-migration|Ispirer SQLWays Product Page - Migration to MySQL]]
 +[[http://www.ispirer.com/?click=RequestSQLWays&from=IspirerWIKI|Request SQLWays]]
  
sqlways/troubleshooting-guide/mysql/import/zero-date-not-accepted.txt · Last modified: May 07, 2018, 03:15:34 PM (external edit)
 
© 1999-2019, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement