Skip navigation links

MySQL Forums :: Connector/JDBC and Java :: Undocumented time formatting changes in 5.1.8+?


Advanced Search

Undocumented time formatting changes in 5.1.8+?
Posted by: Michael Hoeffner ()
Date: February 11, 2010 01:45PM

We have MySQL 5.1.40 and 5.1.7 Connector/J deployed in several production environments and we have many more coming that we were hoping could look identical. All was working well until I did some profiling and found that we had some noticeable performance degradation in certain components due to a prepared statement parsing bug ( http://bugs.mysql.com/bug.php?id=41532 ) that was fixed in C/J 5.1.8. I went ahead and tried upgrading to the latest GA C/J which was 5.1.11. Performance improved noticeably in a few spots but I also found that certain queries that should have returned rows were returning none. After much research I finally figured out that it was a combination of a bug in MySQL 5.1.39/5.1.40 ( http://bugs.mysql.com/bug.php?id=47963 ) and as far as I can tell, an undocumented change that was made in C/J 5.1.8. There's nothing related in the release notes ( http://dev.mysql.com/doc/refman/5.1/en/cj-news-5-1-8.html ) but the timestamp generated when setting a Date/Timestamp on prepared statements appears to have changed in C/J 5.1.7 from a format of:

'2010-02-11 12:23:50'

to:

'2010-02-11 12:23:50.0'

in 5.1.8, 5.1.9, 5.1.10, and 5.1.11. Because of the ".0" that is appended certain queries that use an index on a date column now fail to return any rows due to the server bug. I did diffs on the 5.1.7 and 5.1.8 source and I saw quite a few changes that looks related to dates/times (PreparedStatement, ResultSetImpl, ResultSetRow, and UpdatableResultSet classes).

I also tried variations of useLegacyDatetimeCode which was introduced in 5.1.6 but the results were the same.

Another thing that added to the confusion was that Query Browser 1.2.17 and Workbench 5.2.15 would return different numbers of rows for the same queries due to the server bug mentioned above. Query Browser bundles C/J 5.0.4 but I'm not sure if it uses it. Workbench may bundle some non-Java version but that's not clear to me.

Upgrading all of the production environments to MySQL 5.1.41 or later isn't very practical and that parsing bug in C/J 5.1.7 is high impact for us. Fortunately it appears that using cachePrepStmts, which we were not using previously though we probably should have and maybe it should actually default to true, decreases the impact of the C/J 5.1.7 bug so I think we can continue to stick with MySQL 5.1.40 and C/J 5.1.7.

I guess I don't really have any questions but I wanted to give a heads up about this change and I was also open to alternative suggestions. I'm disappointed that date handling changed and there was no mention that I could find of it in the documentation. I specifically kept an eye out to see if date handling changed because we've run into various issues with that in previous 5.0.x server releases over the years.

Options: ReplyQuote


Subject Written By Posted
Undocumented time formatting changes in 5.1.8+? Michael Hoeffner 02/11/2010 01:45PM
Re: Undocumented time formatting changes in 5.1.8+? Mark Matthews 02/12/2010 12:42AM
Re: Undocumented time formatting changes in 5.1.8+? Michael Hoeffner 02/12/2010 05:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.