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: | |
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
[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 ...