Bug #43602 specific year,month,day,hour combination throws MysqlDataTruncationException
Submitted: 12 Mar 2009 15:43 Modified: 12 Mar 2009 18:33
Reporter: neil chambers Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: jdbc, MysqlDataTruncationException, timestamp

[12 Mar 2009 15:43] neil chambers
Description:
I have a table that accepts a TIMESTAMP value. I have a Java program which uses mysql-connector-java-5.1.7-bin.jar to access this table.

CREATE TABLE `MyTable` (
  `id` varchar(20) NOT NULL,
  `whenChanged` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

//some values for the statement
String myId = "aKey1";
java.sql.Timestamp ts = Timestamp.valueOf("2009-03-08 02:00:00");
String MySql = "?,?";

//setup the java.sql.connection as con
PreparedStatement stmt = con.PreparedStatement(MySql);
stmt.setString(1, myId);
stmt.setTimestamp(2, ts);
stmt.executeUpdate();

the resulting statement looks like this:

INSERT INTO MyTable VALUES('MyKey1','2009-03-08 02:00:00');

upon execution the following exception is thrown:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2009-03-08 02:00:00' for column 'whenChanged' at row 1

The same insert statement on the mysql command line goes through as expected

Changing the year, month, day or hour of the timestamp within the java program allows the insert to go through without exception. 

How to repeat:
Create table with TIMESTAMP column

using mysql-connector-java-5.1.7-bin.jar setup a routine to insert a timestamp into the timestamp column with a value of '2009-03-08 02:00:00'
[12 Mar 2009 18:13] Tonci Grgin
Test case for our test framework.

Attachment: TestBug43602.java (text/x-java), 2.23 KiB.

[12 Mar 2009 18:15] Tonci Grgin
Hi Neil and thanks for your report.

I am unable to reproduce it with test case attached.
Test case output:
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.1.31-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os                      : Windows Server 2008, 6.0, x86
sun.management.compiler : HotSpot Client Compiler
-------------------------------------------------

At first I thought it was "jdbcCompliantTruncation" that is causing the problem, so I added it. But now I think it might be a question of SQL_MODE, mine is empty.

I also do not see details on MySQL server used, mine is 5.1.31GA.
[12 Mar 2009 18:33] neil chambers
Hi, I didn't try different sql modes as the date value being added should be valid. However, I see that the server is using: 

sql_mode='STRICT_TRANS_TABLES'

MySQL Version: 5.1.24-rc-log

cheers,
n
[3 Apr 2009 17:56] Linus Kamb
I apologize.  This isn't a simpler test case.

But I would just like to add that I have experienced the same error.  Running on 
mysql-5.0.67-solaris10-i386 with connector/J 5.1.7 I cannot enter a datetime value of '2008-09-08T18:52:10.100Z'

reported exception:
Incorrect datetime value: '2008-09-08T18:52:10.100Z' for column 'originTime' at row 1 

whereas the sql inserts properly at mysql prompt:
mysql> insert into rs_rsect set UID='1236904284977',parentId='1236904284976',rootId='1236904284976',EventID='14838547',channel='BHZ',filter='TELE_LONG',regionCode='186',regionName='VANUATU ISLANDS',originTime='2008-09-08T18:52:10.100Z',latitude='-13.4874',longitude='166.8754',Depth='130.5',value='7.0',type='MW';Query OK, 1 row affected, 1 warning (0.00 sec)

mysql  Ver 14.12 Distrib 5.0.67, for pc-solaris2.10 (i386) using readline 5.1

I had upgraded to 5.1.7 in order to be able to invoke stored procedures, but this failure is a blocker, as all my datetimes are formatted that way.  I had to revert back to mysql-connector-java-3.0.15-ga-bin.jar

thanks,
Linus
[6 Apr 2009 14:24] Tonci Grgin
Linus, this seems to be a different problem, one with ISO format... The guess is that MySQL server, when not in strict mode treats letters like ' ', and ignores the microseconds, so because we didn't use strict_trans_tables in 3.0.x, you got away with it. Now that we are, you're out of luck, I'm afraid.
[27 May 2009 13:45] Istvan Lorentz
Also occurs sporadically, in my app log:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '20007-01-01 00:00:00' for column 'ReleaseDate' at row 1

Client: mysql-connector-java-5.1.7-bin.jar
MySQL Server version: 5.0.45-log (Centos5.3 x64)
[27 May 2009 13:51] Istvan Lorentz
Nevermind my comment, 20007 *is* invalid date.
[27 May 2009 13:54] Tonci Grgin
:-)
[9 Jun 2009 13:27] Istvan Lorentz
I am also reproducing it in a small case

create table test (t timestamp);
insert into test set t='2009-03-29 03:32:26';
FAILS with MysqlDataTruncation from mysql-connector-java-5.1.7-bin.jar

SUCCEEDS from mysql console
mysql> insert into test set t='2009-03-29 03:32:26';
Query OK, 1 row affected, 1 warning (0.00 sec)

After I read back the value i get a different value!
mysql> select * from test;
+---------------------+
| t                   |
+---------------------+
| 2009-03-29 04:00:00 |
+---------------------+

My timezone is GMT+2 
Mysql Server version: 5.0.45-log Source distribution, 
Linux x64 CentOS 5.1
the packet capture contains
insert into test set t='2009-03-29 03:32:26'P......#22007Incorrect datetime value: '2009-03-29 03:32:26' for column 't' at row 1$....SET OPTION SQL_SELECT_LIMIT=DEFAULT...........
[3 Feb 2010 14:28] li cheng
I met the same error

Data truncation: Incorrect datetime value: '2009-03-08 02:29:50.0' for column 'created' at row 1

driver version : 5.1.10
server side var:

 sql_mode : empty
[14 Mar 2010 3:54] Sunny Saxena
I am also getting this error.

SQL_MODE is empty, with mysql-connector-java-5.1.10-bin.jar.

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2010-03-14 02:55:35' for column 'CREATED_TM' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3556)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
[1 Apr 2011 9:05] Christian Schab
This 'bug' is caused by winter -> summer time change ...