MySQL Bugs: #79598: Client side Prepared Statement caching bypasses JDBC42 Java 8 Time conversion

Bug #79598	Client side Prepared Statement caching bypasses JDBC42 Java 8 Time conversion
Submitted:	11 Dec 2015 1:53	Modified:	27 Apr 2016 23:26
Reporter:	Christopher Mayne	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.38	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
When the MySQL Connector J configuration property 'cachePrepStmts' is enabled, once a PreparedStatment is cached, it no longer is able to apply Java Time to Java SQL logic.

As outlined in the code snippet below, on first execution of a statement, PreparedStatement.getInstance is able to determine that JDBC42 is enabled and create a JDBC42PreparedStatement by reflection, and add it to the cache. Once cached, and the same SQL is executed again on the same connection, the PreparedStatement is initialised using the explicit pre-JDBC4 PreparedStatement constructor.

The pre-JDBC4 PreparedStatement falls back onto setObject() (serialize), which fails to execute.

com.mysql.jdbc.ConnectionImpl:1448

if (getCachePreparedStatements()) {
            PreparedStatement.ParseInfo pStmtInfo = this.cachedPreparedStatementParams.get(nativeSql);

            if (pStmtInfo == null) {
                pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database);

                this.cachedPreparedStatementParams.put(nativeSql, pStmt.getParseInfo());
            } else {
                pStmt = new com.mysql.jdbc.PreparedStatement(getMultiHostSafeProxy(), nativeSql, this.database, pStmtInfo);
            }
        } else {
            pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getMultiHostSafeProxy(), nativeSql, this.database);
        }

How to repeat:
Turn on cachePrepStmts.

Create a table with a TIMESTAMP or DATETIME column.

Attempt to INSERT into this table, using a LocalDateTime (for example), on the same connection provided by the MySQL datasource.

Notice after the first execution the statement is cached an fails to execute.

Suggested fix:
Create a way of initialising a JDBC42PreparedStatement using the cached PreparedStatement.ParseInfo.

"The pre-JDBC4 PreparedStatement falls back onto setObject() (serialize), which fails to execute."

Should probably read "The pre-JDBC4 PreparedStatement falls back onto setObject() (serialize), for setting LocalDateTime (or other Java 8 Time) parameters, which fails to execute."

This looks like a one line fix in ConnectionImpl:1456.

Replace the explicit PreparedStatement 4 arg constructor call with the 4 arg PreparedStatement.getInstance call. This should enable a JDBC42PreparedStatement to be created in the same fashion, except using the cached ParseInfo.

Hi Christopher,

Thank you for this bug report. It was verified as described.

Thanks Filipe. Do you know if/when this will be scheduled for a fix? Thanks.

Added the following entry to the C/J 5.1.39 changelog:

"When using JDBC 4.2 and with the connection property cachePrepStmts set to “true,” after a prepared statement was cached, rerunning the SQL statement resulted in a pre-JDBC 4.2 PreparedStatment object being instantiated. This fix prevents the problem by having the PreparedStatement instantiated by a factory instead of a constructor method."