Bug #28481 No Query Timeouts
Submitted: 16 May 2007 20:23 Modified: 24 May 2007 16:21
Reporter: Steven Cain Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Windows
Assigned to: CPU Architecture:Any

[16 May 2007 20:23] Steven Cain
We're running mysqld version 5.0.37 on FC5 and connecting to the database with MySQL Connector/ODBC 3.51.12.  The driver code does nothing when SQLSetStmtAttr is called with SQL_ATTR_QUERY_TIMEOUT.  We cannot run without query timeouts so we have tried to find a work around.  We looked at the JDBC driver code and saw that query timeouts are supported but the query monitor thread implementation would not work for us in the ODBC drivers because we connect to the database via a LocalDirector aren't guaranteed to connect to any specific database server.  So a monitor thread that connects and issues the KILL QUERY command would not necessarily be connected to the correct database server.

How to repeat:
Look at the Connector/ODBC code for SQLSetStmtAttr.

Suggested fix:
We added stored procs to register and unregister each of our stored proc calls and then we have a daemon that watches for stored procs that have run too long and kills them.  We would like to see SHOW PROCESSLIST return the current stored procedure and the stored procedure's duration along with the current statement and the statement's duration.  With that information we could poll SHOW PROCESSLIST to get the timeout information that we need.  Of course, built in support for query timeouts on the server side would be great.
[16 May 2007 21:28] Mark Matthews
Because of your situation, the only way this can be fixed is in the server, see http://forge.mysql.com/worklog/task.php?id=2814
[17 May 2007 14:35] Steven Cain
The server side solution specified at http://forge.mysql.com/worklog/task.php?id=2814 is limited to only select statements.  We use stored procedures for 99% of our database interaction and the stored procedures are a mixture of selects, updates, and inserts.  We would need an SQL_TIMEOUT on a stored procedure level to get the functionality that we need.  Is the current stored procedure readily available from any SQL calls?
[24 May 2007 16:21] MySQL Verification Team
Thank you for the bug report feature request.