MySQL Bugs: #73456: Error using a value

Bug #73456	Error using a value
Submitted:	3 Aug 2014 2:34	Modified:	3 Sep 2014 15:09
Reporter:	Jose Jaimes	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.0.9.11.421 build 1170	OS:	Windows (Win 7)
Assigned to:		CPU Architecture:	Any
Tags:	Error insert data using INSERT INTO

Description:
The error ocurrs when use the value: '2004-09-05 00:00:00.000000000'

The definition of each field is timestamp.

--------------------------------------------------------------------------------
EXAMPLE 1:

INSERT INTO ORDERS VALUES(10288,'2004-09-05 00:00:00.000000000','2004-09-11 00:00:00.000000000','2004-09-05 00:00:00.000000000','Shipped',NULL,166)

ERROR CODE: 1292. Incorrect datetime value: '2004-09-05 00:00:00.000000000' for column 'ORDERDATE' at row 1

Changing the same value to another field:
INSERT INTO ORDERS VALUES(10288,'2004-09-01 00:00:00.000000000','2004-09-05 00:00:00.000000000','2004-09-05 00:00:00.000000000','Shipped',NULL,166)

ERROR CODE: 1292. Incorrect datetime value: '2004-09-05 00:00:00.000000000' for column 'REQUIREDDATE' at row 1

Changing the same value to other field with the same definition:
INSERT INTO ORDERS VALUES(10288,'2004-09-01 00:00:00.000000000','2004-09-11 00:00:00.000000000','2004-09-05 00:00:00.000000000','Shipped',NULL,166)

Error Code: 1292. Incorrect datetime value: '2004-09-05 00:00:00.000000000' for column 'SHIPPEDDATE' at row 1

--------------------------------------------------------------------------------

How to repeat:
Only create the same table in other schema: 

DROP TABLE IF EXISTS ORDERS;
CREATE TABLE ORDERS (
  ORDERNUMBER int(11) NOT NULL default '0',
  ORDERDATE timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  REQUIREDDATE timestamp NOT NULL default '0000-00-00 00:00:00',
  SHIPPEDDATE timestamp NOT NULL default '0000-00-00 00:00:00',
  STATUS varchar(15) collate latin1_general_cs NOT NULL default '',
  COMMENTS mediumtext collate latin1_general_cs,
  CUSTOMERNUMBER int(11) NOT NULL default '0',
  PRIMARY KEY  (ORDERNUMBER)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

And to run the sentence above describes.

See also.
http://bugs.mysql.com/bug.php?id=73458

@Jose, I very much doubt that what you are facing is an issue with Workbench. It is a server error and server error message. Any client would return the same error with same server and same statement.

-- Peter
-- not a MySQL/Oracle person

Please try version 6.1.7 and check the below. Thanks.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -p test
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE ORDERS (
    ->   ORDERNUMBER INT(11) NOT NULL DEFAULT '0',
    ->   ORDERDATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   REQUIREDDATE TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   SHIPPEDDATE TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   STATUS VARCHAR(15) COLLATE latin1_general_cs NOT NULL DEFAULT '',
    ->   COMMENTS MEDIUMTEXT COLLATE latin1_general_cs,
    ->   CUSTOMERNUMBER INT(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY  (ORDERNUMBER)
    -> ) ENGINE=INNODB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
Query OK, 0 rows affected (0.21 sec)

mysql> INSERT INTO ORDERS VALUES(10288,'2004-09-01 00:00:00.000000000','2004-09-05 00:00:00.000000000','2004-09-05 00:00:00.000000000','Shipped',NULL,166)
    -> ;
Query OK, 1 row affected (0.41 sec)

mysql> select * from orders;
+-------------+---------------------+---------------------+---------------------+---------+----------+----------------+
| ORDERNUMBER | ORDERDATE           | REQUIREDDATE        | SHIPPEDDATE         | STATUS  | COMMENTS | CUSTOMERNUMBER |
+-------------+---------------------+---------------------+---------------------+---------+----------+----------------+
|       10288 | 2004-09-01 00:00:00 | 2004-09-05 00:00:00 | 2004-09-05 00:00:00 | Shipped | NULL     |            166 |
+-------------+---------------------+---------------------+---------------------+---------+----------+----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE ORDERS MODIFY COLUMN ORDERDATE TIMESTAMP(6);
Query OK, 1 row affected (1.89 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ORDERS MODIFY COLUMN REQUIREDDATE TIMESTAMP(6);
Query OK, 1 row affected (0.64 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> TRUNCATE ORDERS;
Query OK, 0 rows affected (0.39 sec)

mysql> INSERT INTO ORDERS VALUES(10288,'2004-09-01 00:00:00.000000000','2004-09-05 00:00:00.000000000','2004-09-05 00:00:00.000000000','Shipped',NULL,166)
    -> ;
Query OK, 1 row affected (0.06 sec)

mysql> select * from orders;
+-------------+----------------------------+----------------------------+---------------------+---------+----------+----------------+
| ORDERNUMBER | ORDERDATE                  | REQUIREDDATE               | SHIPPEDDATE         | STATUS  | COMMENTS | CUSTOMERNUMBER |
+-------------+----------------------------+----------------------------+---------------------+---------+----------+----------------+
|       10288 | 2004-09-01 00:00:00.000000 | 2004-09-05 00:00:00.000000 | 2004-09-05 00:00:00 | Shipped | NULL     |            166 |
+-------------+----------------------------+----------------------------+---------------------+---------+----------+----------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE ORDERS;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-+
| Table  | Create Table

 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-+
| ORDERS | CREATE TABLE `orders` (
  `ORDERNUMBER` int(11) NOT NULL DEFAULT '0',
  `ORDERDATE` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `REQUIREDDATE` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `SHIPPEDDATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `STATUS` varchar(15) COLLATE latin1_general_cs NOT NULL DEFAULT '',
  `COMMENTS` mediumtext COLLATE latin1_general_cs,
  `CUSTOMERNUMBER` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ORDERNUMBER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-+
1 row in set (0.00 sec)

mysql>

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".