Bug #65418 Datetime and timestamp second fractions need removing the trailing zeros
Submitted: 25 May 2012 9:54 Modified: 26 Jul 2013 22:47
Reporter: Anders Thor Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1.11 OS:Microsoft Windows (64 bit)
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: 32bit, 64bit, regression, Timestamp fractions

[25 May 2012 9:54] Anders Thor
Description:
When running java-code on 64-bit windows 7 using ODBC connector 5.1.11 we get an error when inserting a row with a timestamp-value. The server runs on a 32-bit windows 7 machine.

java.sql.SQLException: [MySQL][ODBC 5.1 Driver][mysqld-5.5.15-log]Incorrect datetime value: '2012-05-24 15:51:00.055000000' for column 'created' at row 1
        at sun.jdbc.odbc.JdbcOdbcResultSet.setPos(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.insertRow(Unknown Source)

The column is of type timestamp.
The same set-up works fine when using 5.1.10

How to repeat:
- Create a timestamp in java (only?), new Timestamp(System.currentTimeMillis())
- Run the code on a win7, 64-bit machine.
- Connect to a 32-bit win7 MySQL instance.
- Insert the timestamp value into a column of type timestamp.
[25 May 2012 11:54] Valeriy Kravchuk
This result is probably related to SQL mode set upon connection, and engine used for the table you are inserting into. Look:

mysql> create table tts(c1 timestamp);
Query OK, 0 rows affected (0.91 sec)

mysql> insert into tts values('2012-05-24 15:51:00.055000000');
Query OK, 1 row affected, 1 warning (0.32 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'c1' at row 1
1 row in set (0.08 sec)

mysql> select * from tts;
+---------------------+
| c1                  |
+---------------------+
| 2012-05-24 15:51:00 |
+---------------------+
1 row in set (0.05 sec)

Now, let's set strict mode:

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.40 sec)

mysql> insert into tts values('2012-05-24 15:51:00.055000000');
ERROR 1292 (22007): Incorrect datetime value: '2012-05-24 15:51:00.055000000' for column 'c1' at row 1

and we get the same error as your code. So, please, check what SQL mode is set in your code (or for your server in my.ini).
[26 Jun 2012 1:00] Bugs System
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".
[11 Jul 2012 1:13] Adrien de Croy
This is reproduceable on other platforms as well.  It's critical for us.

In order to prevent the error, we need to set the fraction part of any SQL_TIMESTAMP_STRUCT member to 0.

We are using SQLBindParameter to put it in.

this is with MyODBC 5.1.11 talking to MySQL 5.5.25a.

It's preventing our clients from using our product with this version of MyODBC, so I think severity is critical actually. 

Change log for 5.1.11 has 2 entries related to fraction part of this struct.  Previous versions set the fraction part to 0 internally in the driver.

The server itself needs to be updated to accept it.

TIMESTAMP_STRUCT was defined with nanoseconds in fraction part years and years ago.  Docs for MySQL state there is a precision on the seconds part, but it's not nanoseconds.  The driver therefore needs to convert precision before passing it to the server.
[12 Jul 2012 3:38] Bogdan Degtyariov
Can you provide a small java or C test case to help us reproduce the problem?
[12 Jul 2012 3:47] Adrien de Croy
Hi

It's quite a lot of work, since we have a lot of framework code to get it working (e.g. ODBC classes, pooling, prepared statements etc etc).

However, if you download WinGate 7.2.2, and try it with MyODBC 5.1.11 against a database in 5.5.25a, you will see the problem. 

To get WinGate, go to www.wingate.com and download.  To configure it to use MySQL, check out a 4 minute video at http://www.youtube.com/watch?v=5koTe3GYCwk

Then just point a web browser at WinGate, and you'll see the problem.
[12 Jul 2012 3:50] Adrien de Croy
p.s.

I tried it against MySQL 5.6 m8 and it works fine (problem resolved). This is because fractional second support was added to timestamp and datetime in MySQL 5.6

So we have a work-around, we tell customers to use MyODBC 5.1.10 or MySQL 5.6, but 5.6 is not in GA yet.
[12 Jul 2012 7:29] Bogdan Degtyariov
The version 5.1.11 had this bug fixed:

  * ODBC prepared statements ignore fractional part of temporal data types.
    (Bug #12767761/60648)

However, the driver does not check the server version, which is wrong.
[12 Jul 2012 13:15] Bogdan Degtyariov
patch for the bug v1

Attachment: patch65418.diff (application/octet-stream, text), 5.97 KiB.

[12 Jul 2012 13:32] Bogdan Degtyariov
patch v2 with improvements

Attachment: patch65418v2.diff (application/octet-stream, text), 5.80 KiB.

[10 Dec 2012 22:34] Adrien de Croy
Hi

The fix didn't make it into MyODBC 5.2, so we are still having quite a few support issues with customers.  They don't understand that the latest DB and connector are buggy together.

Can you guys please roll out the fix?  Please?
[7 Feb 2013 6:07] Bogdan Degtyariov
Increased the severity because it affects working with MySQL 5.6.
[19 Mar 2013 11:40] Bogdan Degtyariov
The patch went to:

v5.1 revision 1090
v5.2 revision 1128
[26 Jul 2013 22:46] Daniel So
Added an entry to the Connector/ODBC 5.2.5 and 5.1.13 changelog:

Trailing zeros for the fractional part of the second for datetime and timestamp values were not removed for MySQL server version 5.5 or earlier, which do not support fractional second.