Bug #46015 CallableStatement.setQueryTimeout(int) does not throw exception on timeout
Submitted: 7 Jul 2009 17:47 Modified: 9 Jul 2009 17:55
Reporter: charles kim Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.7 OS:Linux
Assigned to: CPU Architecture:Any
Tags: timeout

[7 Jul 2009 17:47] charles kim
Description:
After upgrading to mysql-connector-java-5.1.7-bin.jar from
mysql-connector-java-5.0.8-bin.jar we found that setting timeout for stored procedure call with CallableStatement.executeQuery() hangs the Java thread that running the query and does not throw any exceptions.

How to repeat:
Set a short timeout on a long query.
Execute a stored procedure with CallableStatement.
Example: rs = st.executeQuery("Call testProc()");

Suggested fix:
Throw some sort of SQLException on query timeout.
[8 Jul 2009 7:34] Tonci Grgin
Test case for our test framework.

Attachment: TestBug46015.java (text/x-java), 1.61 KiB.

[8 Jul 2009 7:38] Tonci Grgin
Hi Charles and thanks for your report.

Running attached test case against latest source tree I can not repeat the problem:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.1.31-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os                      : Windows Server 2008, 6.0, x86
sun.management.compiler : HotSpot Client Compiler
-------------------------------------------------

E
Time: 10,739
There was 1 error:
1) testBug46015(testsuite.simple.TestBug46015)java.sql.SQLException: Query execution was interrupted
...

Toggling useServerPrepStmts value makes no difference.

Please try latest snapshot from http://downloads.mysql.com/snapshots.php and see if the problem is still repeatable.
[9 Jul 2009 17:55] charles kim
Tried snapshots 5 and 6 from http://downloads.mysql.com/snapshots.php.  The problem was still repeatable.  It was not timing out.
However, instead of throwing timeout, it threw "You are not owner of the thread"  exception after hanging for some time.

Here is the output:
java.sql.SQLException: You are not owner of thread 949524
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3178)
        at com.mysql.jdbc.Statement.execute(Statement.java:711)
        at com.mysql.jdbc.Statement$1.run(Statement.java:92)

I took a closer look at how the stored procedure was created.  Figured out that the problem was that the stored procedure was created under different definer.

Realizing that the caller is using different account, it may have caused the "You are not owner of thread" exception.  

After looking through...I found couple of fixes. Fist option was synching the stored procedure's definer with the same account that client was using.  Second option was to give the caller user "SUPER" privilege which will allow to terminate other sessions.

After applying the second option which is giving the "SUPER" privilege, i was able to get it to timeout.
Here is the output:
java.sql.SQLException: Query execution was interrupted. Query Timedout : test_Sleep(1000)

I went back and downgrade the connector to 5.0.6/5.0.8/5.1.7 and tested again to verify that the behavior is same for previous drivers and verified that it does throw timeout exception with the fixes that I've mentioned with those driver versions.