Differences

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

--- knowledge-base:database-migration:setup-and-troubleshooting:mysql:error-1067 [2018/05/07 15:15]
+++ knowledge-base:database-migration:setup-and-troubleshooting:mysql:error-1067 [2024/03/07 11:40] (current)
volha.zhyrava
@@ Line 1: / Line 1: @@
+\\
+[[https://www.ispirer.com|Ispirer Website]]
+[[https://www.ispirer.com/products/mysql-migration|Ispirer Capabilities: MySQL Migration]]
+[[https://www.ispirer.com/download/download-demo|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 |
+  +-----------------------------------------------------------------------------+
+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 |
+  +-----------------------------------------------------------------------------+
+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: [[[email protected]]]