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.