MySQL Bugs: #65418: Datetime and timestamp second fractions need removing the trailing zeros

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:	Windows (64 bit)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	32bit, 64bit, regression, Timestamp fractions

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.

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).

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".

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.

Can you provide a small java or C test case to help us reproduce the problem?

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.

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.

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.

patch for the bug v1

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

patch v2 with improvements

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

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?

Increased the severity because it affects working with MySQL 5.6.

The patch went to:

v5.1 revision 1090
v5.2 revision 1128

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.