From: Date: September 15 2006 6:42pm Subject: Connector/J commit: r5735 - in branches/branch_5_0/connector-j: . src/com/mysql/jdbc src/testsuite/regression List-Archive: http://lists.mysql.com/commits/12051 X-Bug: 22297 Message-Id: <200609151642.k8FGg1Gi014592@bk-internal.mysql.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Modified: branches/branch_5_0/connector-j/CHANGES branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java Log: Workaround for server crash when calling stored procedures via a server-side prepared statement (driver now detects prepare(stored procedure) and substitutes client-side prepared statement), addresses BUG#22297. Modified: branches/branch_5_0/connector-j/CHANGES =================================================================== --- branches/branch_5_0/connector-j/CHANGES 2006-09-15 00:01:26 UTC (rev 5734) +++ branches/branch_5_0/connector-j/CHANGES 2006-09-15 16:41:58 UTC (rev 5735) @@ -29,6 +29,11 @@ Statement.setQueryTimeout() when specification says argument is to be in seconds. + - Workaround for server crash when calling stored procedures + via a server-side prepared statement (driver now detects + prepare(stored procedure) and substitutes client-side prepared + statement), addresses BUG#22297. + 07-26-06 - Version 5.0.3 - Fixed BUG#20650 - Statement.cancel() causes NullPointerException Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java =================================================================== --- branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java 2006-09-15 00:01:26 UTC (rev 5734) +++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/Connection.java 2006-09-15 16:41:58 UTC (rev 5735) @@ -1742,6 +1742,12 @@ private boolean canHandleAsServerPreparedStatementNoCache(String sql) throws SQLException { + + // Can't use server-side prepare for CALL + if (StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "CALL")) { + return false; + } + boolean canHandleAsStatement = true; if (!versionMeetsMinimum(5, 0, 7) && @@ -2085,18 +2091,25 @@ */ public PreparedStatement clientPrepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { + return clientPrepareStatement(sql, resultSetType, resultSetConcurrency, true); + } + + protected PreparedStatement clientPrepareStatement(String sql, + int resultSetType, int resultSetConcurrency, + boolean processEscapeCodesIfNeeded) throws SQLException { checkClosed(); + String nativeSql = processEscapeCodesIfNeeded && getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql; + PreparedStatement pStmt = null; if (getCachePreparedStatements()) { synchronized (this.cachedPreparedStatementParams) { PreparedStatement.ParseInfo pStmtInfo = (PreparedStatement.ParseInfo) this.cachedPreparedStatementParams - .get(sql); + .get(nativeSql); if (pStmtInfo == null) { - pStmt = new com.mysql.jdbc.PreparedStatement(this, - getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql, + pStmt = new com.mysql.jdbc.PreparedStatement(this, nativeSql, this.database); PreparedStatement.ParseInfo parseInfo = pStmt.getParseInfo(); @@ -2125,17 +2138,17 @@ } } - this.cachedPreparedStatementParams.put(sql, pStmt + this.cachedPreparedStatementParams.put(nativeSql, pStmt .getParseInfo()); } } else { pStmtInfo.lastUsed = System.currentTimeMillis(); - pStmt = new com.mysql.jdbc.PreparedStatement(this, sql, + pStmt = new com.mysql.jdbc.PreparedStatement(this, nativeSql, this.database, pStmtInfo); } } } else { - pStmt = new com.mysql.jdbc.PreparedStatement(this, sql, + pStmt = new com.mysql.jdbc.PreparedStatement(this, nativeSql, this.database); } @@ -4576,14 +4589,14 @@ if (pStmt == null) { try { pStmt = new com.mysql.jdbc.ServerPreparedStatement(this, nativeSql, - this.database); + this.database, resultSetType, resultSetConcurrency); if (sql.length() < getPreparedStatementCacheSqlLimit()) { ((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true; } } catch (SQLException sqlEx) { // Punt, if necessary if (getEmulateUnsupportedPstmts()) { - pStmt = clientPrepareStatement(nativeSql); + pStmt = clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false); if (sql.length() < getPreparedStatementCacheSqlLimit()) { this.serverSideStatementCheckCache.put(sql, Boolean.FALSE); @@ -4597,24 +4610,20 @@ } else { try { pStmt = new com.mysql.jdbc.ServerPreparedStatement(this, nativeSql, - this.database); + this.database, resultSetType, resultSetConcurrency); } catch (SQLException sqlEx) { // Punt, if necessary if (getEmulateUnsupportedPstmts()) { - pStmt = clientPrepareStatement(nativeSql); + pStmt = clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false); } else { throw sqlEx; } } } } else { - pStmt = clientPrepareStatement(nativeSql); + pStmt = clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false); } - - pStmt.setResultSetType(resultSetType); - pStmt.setResultSetConcurrency(resultSetConcurrency); - return pStmt; } @@ -5152,7 +5161,12 @@ */ public ServerPreparedStatement serverPrepare(String sql) throws SQLException { - return new ServerPreparedStatement(this, sql, this.getCatalog()); + + String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql; + + return new ServerPreparedStatement(this, nativeSql, this.getCatalog(), + java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, + java.sql.ResultSet.CONCUR_READ_ONLY); } protected boolean serverSupportsConvertFn() throws SQLException { Modified: branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java =================================================================== --- branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java 2006-09-15 00:01:26 UTC (rev 5734) +++ branches/branch_5_0/connector-j/src/com/mysql/jdbc/ServerPreparedStatement.java 2006-09-15 16:41:58 UTC (rev 5735) @@ -284,7 +284,8 @@ * @throws SQLException * If an error occurs */ - public ServerPreparedStatement(Connection conn, String sql, String catalog) + public ServerPreparedStatement(Connection conn, String sql, String catalog, + int resultSetType, int resultSetConcurrency) throws SQLException { super(conn, catalog); @@ -324,6 +325,9 @@ throw SQLError.createSQLException(ex.toString(), SQLError.SQL_STATE_GENERAL_ERROR); } + + setResultSetType(resultSetType); + setResultSetConcurrency(resultSetConcurrency); } /** Modified: branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java =================================================================== --- branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java 2006-09-15 00:01:26 UTC (rev 5734) +++ branches/branch_5_0/connector-j/src/testsuite/regression/CallableStatementRegressionTest.java 2006-09-15 16:41:58 UTC (rev 5735) @@ -27,6 +27,7 @@ import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; +import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Types; import java.util.Properties; @@ -712,6 +713,77 @@ } } } + + /** + * Tests workaround for server crash when calling stored procedures + * via a server-side prepared statement (driver now detects + * prepare(stored procedure) and substitutes client-side prepared statement). + * + * @throws Exception if the test fails + */ + public void testBug22297() throws Exception { + if (versionMeetsMinimum(5, 0)) { + this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug22297"); + + createTable("tblTestBug2297_1", "(" + + "id varchar(20) NOT NULL default ''," + + "Income double(19,2) default NULL)"); + + createTable("tblTestBug2297_2", "(" + + "id varchar(20) NOT NULL default ''," + + "CreatedOn datetime default NULL)"); + + this.stmt.executeUpdate("CREATE PROCEDURE testBug22297(pcaseid INT)" + + "BEGIN" + + "\nSET @sql = \"DROP TEMPORARY TABLE IF EXISTS tmpOrders\";" + + " PREPARE stmt FROM @sql;" + + " EXECUTE stmt;" + + " DEALLOCATE PREPARE stmt;" + + "\nSET @sql = \"CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM tblTestBug2297_1 GROUP BY id\";" + + " PREPARE stmt FROM @sql;" + + " EXECUTE stmt;" + + " DEALLOCATE PREPARE stmt;" + + "\n SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income" + + "\n FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id" + + "\n WHERE e.CreatedOn > '2006-08-01') AS Final ORDER BY id;" + + "\nEND"); + + this.stmt.executeUpdate("INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES " + + "('a',4094.00)," + + "('b',500.00)," + + "('c',3462.17)," + + " ('d',500.00)," + + " ('e',600.00)"); + + this.stmt.executeUpdate("INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES " + + "('d','2006-08-31 00:00:00')," + + "('e','2006-08-31 00:00:00')," + + "('b','2006-08-31 00:00:00')," + + "('c','2006-08-31 00:00:00')," + + "('a','2006-08-31 00:00:00')"); + + try { + this.pstmt = this.conn.prepareStatement("{CALL testBug22297(?)}"); + this.pstmt.setInt(1, 1); + this.rs =this.pstmt.executeQuery(); + + String[] ids = new String[] { "a", "b", "c", "d", "e"}; + int pos = 0; + + while (this.rs.next()) { + assertEquals(ids[pos++], rs.getString(1)); + assertEquals(100, rs.getInt(2)); + } + + assertEquals(this.pstmt.getClass().getName(), + com.mysql.jdbc.PreparedStatement.class.getName()); + + } finally { + closeMemberJDBCResources(); + } + } + } + public void testHugeNumberOfParameters() throws Exception { if (versionMeetsMinimum(5, 0)) { this.stmt