Bug #66430 setCatalog on connection leaves ServerPreparedStatement cache for old catalog
Submitted: 17 Aug 2012 12:09 Modified: 18 Apr 2017 23:44
Reporter: Pete Hendry Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.21 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[17 Aug 2012 12:09] Pete Hendry
Description:
Having obtained a connection to MySQL (ConnectionImpl) I use some PreparedStatements, I then change the catalog using setCatalog() and use the same SQL for further statements against the new catalog. The statements execute against the original catalog instead of the new catalog.

The issue is that although the ConnectionImpl.database value is updated correctly and the USE command is issued by the setCatalog method to update the server-side fo the connection, each ServerPreparedStatement has its own database defined. The cache therefore has the ServerPreparedStatements from the original catalog and they are looked up by the SQL as key. This means that if the same SQL is issued on the new catalog the old ServerPreparedStatements are used which are hardwired to the old catalog.

The connection should either clear the server prepared statement cache maps on a call to setCatalog or the key should include the database name in it.

How to repeat:

Connection conn = ...;
conn.setCatalog("catalog1");
PreparedStatement st = conn.prepareStatement("SELECT class FROM details WHERE class < ?");
st.setLong(1, 100);
ResultSet rs = st.executeQuery();
....
rs.close();
st.close();

conn.setCatalog("catalog2");
st = conn.prepareStatement("SELECT class FROM details WHERE class < ?");
st.setLong(1, 100);
ResultSet rs = st.executeQuery();

At this point you have the results from catalog1 if it used a server prepared statement.

Suggested fix:
Clear 

	private LRUCache serverSideStatementCheckCache;
	private LRUCache serverSideStatementCache;

as part of setCatalog if the catalog name is changed would seem to be the simplest, closing any cached statements for the other catalog.

Alternately (better for performance perhaps) make the key 

  this.database + "--" + sql

instead of just sql.
[17 Aug 2012 13:32] Mark Matthews
Are you actually using the prepared statement cache?
[17 Aug 2012 22:15] Pete Hendry
Yes. I debugged through the code and we are getting a statement out of the cache within ConnectionImpl. I can see the statement has database set to a previous catalog and is an instance of ServerPreparedStatement.

Why do you ask? It shouldn't really matter as it seems like this is a pretty obvious bug with the key being the SQL and the value being hardwired to a specific database/catalog - it will obviously be a problem if you change catalog and use the same SQL on the new catalog will it not?
[24 Apr 2013 10:48] Alexander Soklakov
Hi Pete,

Verified by code review.
[18 Apr 2017 23:44] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 5.1.42 changelog:

"After a connection had already switched catalog with setCatalog(), cached data from the old catalog was returned for a reused server-side prepared statement. With this fix, the cache of a server-side prepared statement cache now includes the catalog in its key to avoid wrong cache hits when the statement is reused on another catalog."
[8 Feb 2018 16:28] Filipe Silva
Duplicate Bug#89552 filed after this fix.