MySQL Bugs: #28481: No Query Timeouts

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:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:		OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
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.

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

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?

Thank you for the bug report feature request.