| Bug #61501 | PreparedStatement.cancel() fails | ||
|---|---|---|---|
| Submitted: | 13 Jun 2011 12:09 | Modified: | 16 Dec 2011 7:08 |
| Reporter: | Barry Mcgillin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jun 2011 14:57]
Tonci Grgin
Hi Barry and thanks for your report. Looking at the log, I see everything's in order: 110613 16:39:40 1 Connect root@localhost on test 1 Query /* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: mark.matthews@oracle.com-20110603224721-c44rqd1mekhu3l62 ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 1 Query /* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: mark.matthews@oracle.com-20110603224721-c44rqd1mekhu3l62 ) */SELECT @@session.auto_increment_increment 1 Query SHOW COLLATION 1 Query SET NAMES latin1 1 Query SET character_set_results = NULL 1 Query SET autocommit=1 1 Query SELECT VERSION() 1 Query DROP TABLE IF EXISTS bug61501 1 Query CREATE TABLE bug61501 (id int, name varchar(100)) 1 Query INSERT INTO bug61501 values(1,'barry') 1 Query INSERT INTO bug61501 values(2,'lisa') 1 Query INSERT INTO bug61501 values(3,'Rebecca') 1 Query INSERT INTO bug61501 values(4,'Katie-Ellen') 1 Query SELECT * FROM bug61501 WHERE id > 2 2 Connect root@localhost on test 2 Query /* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: mark.matthews@oracle.com-20110603224721-c44rqd1mekhu3l62 ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 2 Query /* mysql-connector-java-5.1.17-SNAPSHOT ( Revision: mark.matthews@oracle.com-20110603224721-c44rqd1mekhu3l62 ) */SELECT @@session.auto_increment_increment 2 Query SHOW COLLATION 2 Query SET NAMES latin1 2 Query SET character_set_results = NULL 2 Query SET autocommit=1 2 Query KILL QUERY 1 2 Quit 1 Query SELECT * FROM bug61501 WHERE id > 0 1 Query DROP TABLE IF EXISTS bug61501 1 Quit You are spawning ThreadId 2 to KILL the query in ThreadId 1 which IS your intention by calling Statement.Cancel(). The fact that some prior version worked differently is of no consequence here. However, I will test this a bit more and see if the: java.sql.SQLException: Query execution was interrupted at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281) at testsuite.simple.TestBug61501.testBug61501(TestBug61501.java:56) can be avoided.
[13 Jun 2011 15:16]
Tonci Grgin
There's a race condition in "KILL QUERY"... If no query is in process, the *next* query issued will be killed by the server and that is the bug which we'll fix. Workaround here is simple, do *NOT* unnecessarily KILL the queries when they are already finished running.
[4 Aug 2011 0:04]
Mark Matthews
Fixed for 5.1.18. The driver now guards against this condition, but it is an underlying server issue. The MySQL statement "KILL QUERY" (which is what the driver uses to implement Statement.cancel()) is rather non-deterministic, and thus the use of Statement.cancel() should be avoided if possible, see Bug#45679
[16 Dec 2011 7:08]
Philip Olson
Fixed as of 5.1.18: Changelog: Connector/J now guards against the condition where a call to KILL QUERY will kill the next query issued by the server, if no query is in process. Note: The MySQL statement KILL QUERY (which is what the driver uses to implement Statement.cancel()) is rather non-deterministic, and thus the use of Statement.cancel() should be avoided if possible. If no query is in process, the next query issued will be killed by the server. This race condition is guarded against as of Connector/J 5.1.18.
Description: calling cancel on a prepared statement before using again throws a SQL Exception. In our code, we always cancel a statement before closing it, and this has always worked. However, in Version 5.1.15 on connectorJ, we are seeing this issue. In our code we have a generic block if (_prepstmt != null) { // _prepstmt.cancel(); _prepstmt.close(); _prepstmt = null; } Which all drivers pass through. This driver version is causing failures. Specifically this exception Twice use prepared statement example! Kids Rebecca - 3 Katie-Ellen - 4 SQL statement is not executed! java.sql.SQLException: Query execution was interrupted at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281) at db.mysql.TestPrepare.main(TestPrepare.java:46) How to repeat: Table Create: DROP TABLE table1; CREATE TABLE table1(id int, name varchar(100)); INSERT INTO Table1 values(1,'barry'); INSERT INTO Table1 values(2,'lisa'); INSERT INTO Table1 values(3,'Rebecca'); INSERT INTO Table1 values(4,'Katie-Ellen'); SELECT * FROM table1; SELECT * FROM table1; Java TestCase /** * #TestPrepare.java * (c) McGillin Active Ltd. */ package db.mysql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author bamcgill * */ public class TestPrepare { /** * @param args */ public static void main(String[] args) { System.out.println("Two prepared statement\n"); Connection con = null; PreparedStatement prest; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/sandbox", "root", ""); try { String sql = "SELECT * FROM table1 WHERE id > ?"; prest = con.prepareStatement(sql); prest.setInt(1, 2); ResultSet rs1 = prest.executeQuery(); System.out.println("Kids"); while (rs1.next()) { String name = rs1.getString(2); int id = rs1.getInt(1); System.out.println(name + "\t- " + id); } prest.cancel(); prest.close(); prest = con.prepareStatement(sql); prest.setInt(1, 0); ResultSet rs2 = prest.executeQuery(); System.out.println("All people in the family"); while (rs2.next()) { String name = rs2.getString(2); int id = rs2.getInt(1); System.out.println(name + "\t- " + id); } } catch (SQLException s) { System.out.println("SQL statement is not executed!"); s.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } } }