Description:
When server prepared statement is enabled, a PreparedStatement is pulled from a LRU cache (serverSideStatementCache) in ConnectionImpl.java upon requested. If another PreparedStatement of the same SQL is run and closed before the first one is closed, the first statement will be put back to the cache with the same key. Because realClose() is only called on cache eviction, the second statement will never be closed until the connection is closed. This will eventually lead to the error "Can't create more than max_prepared_stmt_count statements" if there are many connections and each connection holds a number of PreparedStatement.
----------------------------------------------------------
Here is the the pseudocode to describe the issue:
Try to prepare a Server Side Statement
Get a cache hit, so remove the Prepared Statement from the cache and get a hold of it
Execute the statement
Loop over the result {
Try to Prepare the same server side statement
No cache hit because we removed it early, so prepare a new statement
Execute the statement and get the result
Closing the statement will cache the statement
}
Closing the statement will cache the statement with the same key, losing track of the statement created.
How to repeat:
1) Run the following Java program.
2) While the program is running, observe that the Prepared_stmt_count keeps increasing by issuing [show global status like 'Prepared_stmt_count].
------------------------------------------------------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Creat a test database:
* <pre>
CREATE TABLE `test_table` (
`col1` int(10) unsigned NOT NULL,
`col2` int(10) unsigned NOT NULL,
`col3` int(10) unsigned NOT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `test_table` values (1,2,3);
insert into `test_table` values (2,2,3);
insert into `test_table` values (3,2,3);
insert into `test_table` values (4,5,6);
insert into `test_table` values (7,8,9);
* </pre>
*
*/
public class PreparedStatementLeak {
private static final String USERNAME = "root";
private static final String PASSWORD = "pass";
private static final String SQL1 = "select col1 from test_table where col1 = ?";
private static final String SQL2 = "select col1 from test_table where col2 = ?";
public static void main(final String[] args)
throws Exception {
final PreparedStatementLeak test = new PreparedStatementLeak();
test.init();
test.runTest();
}
private void init()
throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
private void runTest()
throws SQLException {
Connection conn = null;
try {
conn = DriverManager.getConnection(
"jdbc:mysql://localhost/testdb?prepStmtCacheSize=10&cachePrepStmts=true&useServerPrepStmts=true",
USERNAME, PASSWORD);
for (int i = 0; i < 100000; i++) {
// inner statement is the same as outer statement
prepareAndRun(conn, SQL1);
// inner statement is different from outer statement
//prepareAndRun(conn, SQL2);
}
}
finally {
if (conn != null) {
conn.close();
}
}
}
private void prepareAndRun(final Connection conn, final String sql)
throws SQLException {
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
try {
ps1 = conn.prepareStatement(SQL1);
ps1.setInt(1, 1);
rs1 = ps1.executeQuery();
while (rs1.next()) {
try {
ps2 = conn.prepareStatement(sql);
ps2.setInt(1, 2);
rs2 = ps2.executeQuery();
System.out.println("Outer: " + rs1.getInt(1));
while (rs2.next()) {
System.out.println("Inner: " + rs1.getInt(1));
}
}
finally {
if (rs2 != null) {
rs2.close();
}
ps2.close();
}
}
}
catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
ex.printStackTrace();
}
finally {
if (rs1 != null) {
rs1.close();
}
if (rs2 != null) {
rs2.close();
}
if (ps1 != null) {
ps1.close();
}
}
}
}
Suggested fix:
diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ConnectionImpl.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ConnectionImpl.java
--- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ConnectionImpl.java 2011-02-08 18:25:30.000000000 +0000
+++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ConnectionImpl.java 2014-11-06 18:07:13.000000000 +0000
@@ -4426,12 +4426,23 @@ public class ConnectionImpl extends Conn
}
- public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException {
+ /**
+ * @param pstmt
+ * @return true if the statement was not in the cache and was put into the cache as a result
+ * of this method. A return value of false means that given statement is already in the cache
+ * or the statement was not cached.
+ */
+ public boolean recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException {
if (pstmt.isPoolable()) {
synchronized (this.serverSideStatementCache) {
+ if (this.serverSideStatementCache.containsKey(pstmt.originalSql)) {
+ return false;
+ }
this.serverSideStatementCache.put(pstmt.originalSql, pstmt);
+ return true;
}
}
+ return false;
}
/**
diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java
--- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java 2011-02-08 18:25:30.000000000 +0000
+++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java 2014-11-05 02:29:00.000000000 +0000
@@ -2196,10 +2196,10 @@ public class LoadBalancedMySQLConnection
skipLocalTeardown, reason);
}
- public void recachePreparedStatement(ServerPreparedStatement pstmt)
+ public boolean recachePreparedStatement(ServerPreparedStatement pstmt)
throws SQLException {
- getActiveMySQLConnection().recachePreparedStatement(pstmt);
+ return getActiveMySQLConnection().recachePreparedStatement(pstmt);
}
public void registerQueryExecutionTime(long queryTimeMs) {
diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/MySQLConnection.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/MySQLConnection.java
--- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/MySQLConnection.java 2011-02-08 18:25:30.000000000 +0000
+++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/MySQLConnection.java 2014-11-05 02:28:06.000000000 +0000
@@ -167,7 +167,7 @@ public interface MySQLConnection extends
void realClose(boolean calledExplicitly, boolean issueRollback,
boolean skipLocalTeardown, Throwable reason) throws SQLException;
- void recachePreparedStatement(ServerPreparedStatement pstmt)
+ boolean recachePreparedStatement(ServerPreparedStatement pstmt)
throws SQLException;
void registerQueryExecutionTime(long queryTimeMs);
diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ServerPreparedStatement.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ServerPreparedStatement.java
--- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ServerPreparedStatement.java 2011-02-08 18:25:30.000000000 +0000
+++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ServerPreparedStatement.java 2014-11-06 18:07:56.000000000 +0000
@@ -594,7 +594,9 @@ public class ServerPreparedStatement ext
this.isClosed = true;
- this.connection.recachePreparedStatement(this);
+ if (this.isPoolable() && !this.connection.recachePreparedStatement(this)) {
+ realClose(true,true);
+ }
return;
}
Description: When server prepared statement is enabled, a PreparedStatement is pulled from a LRU cache (serverSideStatementCache) in ConnectionImpl.java upon requested. If another PreparedStatement of the same SQL is run and closed before the first one is closed, the first statement will be put back to the cache with the same key. Because realClose() is only called on cache eviction, the second statement will never be closed until the connection is closed. This will eventually lead to the error "Can't create more than max_prepared_stmt_count statements" if there are many connections and each connection holds a number of PreparedStatement. ---------------------------------------------------------- Here is the the pseudocode to describe the issue: Try to prepare a Server Side Statement Get a cache hit, so remove the Prepared Statement from the cache and get a hold of it Execute the statement Loop over the result { Try to Prepare the same server side statement No cache hit because we removed it early, so prepare a new statement Execute the statement and get the result Closing the statement will cache the statement } Closing the statement will cache the statement with the same key, losing track of the statement created. How to repeat: 1) Run the following Java program. 2) While the program is running, observe that the Prepared_stmt_count keeps increasing by issuing [show global status like 'Prepared_stmt_count]. ------------------------------------------------------------ import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * Creat a test database: * <pre> CREATE TABLE `test_table` ( `col1` int(10) unsigned NOT NULL, `col2` int(10) unsigned NOT NULL, `col3` int(10) unsigned NOT NULL, PRIMARY KEY (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `test_table` values (1,2,3); insert into `test_table` values (2,2,3); insert into `test_table` values (3,2,3); insert into `test_table` values (4,5,6); insert into `test_table` values (7,8,9); * </pre> * */ public class PreparedStatementLeak { private static final String USERNAME = "root"; private static final String PASSWORD = "pass"; private static final String SQL1 = "select col1 from test_table where col1 = ?"; private static final String SQL2 = "select col1 from test_table where col2 = ?"; public static void main(final String[] args) throws Exception { final PreparedStatementLeak test = new PreparedStatementLeak(); test.init(); test.runTest(); } private void init() throws Exception { Class.forName("com.mysql.jdbc.Driver").newInstance(); } private void runTest() throws SQLException { Connection conn = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/testdb?prepStmtCacheSize=10&cachePrepStmts=true&useServerPrepStmts=true", USERNAME, PASSWORD); for (int i = 0; i < 100000; i++) { // inner statement is the same as outer statement prepareAndRun(conn, SQL1); // inner statement is different from outer statement //prepareAndRun(conn, SQL2); } } finally { if (conn != null) { conn.close(); } } } private void prepareAndRun(final Connection conn, final String sql) throws SQLException { PreparedStatement ps1 = null; PreparedStatement ps2 = null; ResultSet rs1 = null; ResultSet rs2 = null; try { ps1 = conn.prepareStatement(SQL1); ps1.setInt(1, 1); rs1 = ps1.executeQuery(); while (rs1.next()) { try { ps2 = conn.prepareStatement(sql); ps2.setInt(1, 2); rs2 = ps2.executeQuery(); System.out.println("Outer: " + rs1.getInt(1)); while (rs2.next()) { System.out.println("Inner: " + rs1.getInt(1)); } } finally { if (rs2 != null) { rs2.close(); } ps2.close(); } } } catch (SQLException ex) { // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); ex.printStackTrace(); } finally { if (rs1 != null) { rs1.close(); } if (rs2 != null) { rs2.close(); } if (ps1 != null) { ps1.close(); } } } } Suggested fix: diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ConnectionImpl.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ConnectionImpl.java --- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ConnectionImpl.java 2011-02-08 18:25:30.000000000 +0000 +++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ConnectionImpl.java 2014-11-06 18:07:13.000000000 +0000 @@ -4426,12 +4426,23 @@ public class ConnectionImpl extends Conn } - public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException { + /** + * @param pstmt + * @return true if the statement was not in the cache and was put into the cache as a result + * of this method. A return value of false means that given statement is already in the cache + * or the statement was not cached. + */ + public boolean recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException { if (pstmt.isPoolable()) { synchronized (this.serverSideStatementCache) { + if (this.serverSideStatementCache.containsKey(pstmt.originalSql)) { + return false; + } this.serverSideStatementCache.put(pstmt.originalSql, pstmt); + return true; } } + return false; } /** diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java --- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java 2011-02-08 18:25:30.000000000 +0000 +++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/LoadBalancedMySQLConnection.java 2014-11-05 02:29:00.000000000 +0000 @@ -2196,10 +2196,10 @@ public class LoadBalancedMySQLConnection skipLocalTeardown, reason); } - public void recachePreparedStatement(ServerPreparedStatement pstmt) + public boolean recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException { - getActiveMySQLConnection().recachePreparedStatement(pstmt); + return getActiveMySQLConnection().recachePreparedStatement(pstmt); } public void registerQueryExecutionTime(long queryTimeMs) { diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/MySQLConnection.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/MySQLConnection.java --- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/MySQLConnection.java 2011-02-08 18:25:30.000000000 +0000 +++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/MySQLConnection.java 2014-11-05 02:28:06.000000000 +0000 @@ -167,7 +167,7 @@ public interface MySQLConnection extends void realClose(boolean calledExplicitly, boolean issueRollback, boolean skipLocalTeardown, Throwable reason) throws SQLException; - void recachePreparedStatement(ServerPreparedStatement pstmt) + boolean recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException; void registerQueryExecutionTime(long queryTimeMs); diff -rupN /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ServerPreparedStatement.java /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ServerPreparedStatement.java --- /home/developer/mysql-connector-java-5.1.15/src/com/mysql/jdbc/ServerPreparedStatement.java 2011-02-08 18:25:30.000000000 +0000 +++ /home/developer/mysql-connector-java-5.1.15-modified/src/com/mysql/jdbc/ServerPreparedStatement.java 2014-11-06 18:07:56.000000000 +0000 @@ -594,7 +594,9 @@ public class ServerPreparedStatement ext this.isClosed = true; - this.connection.recachePreparedStatement(this); + if (this.isPoolable() && !this.connection.recachePreparedStatement(this)) { + realClose(true,true); + } return; }