MySQL Bugs: #58192: User to KILL the time-out'ed sessions

Bug #58192	User to KILL the time-out'ed sessions
Submitted:	15 Nov 2010 5:47	Modified:	8 Dec 2010 6:46
Reporter:	Mikiya Okuno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a timeout is set using Statement#setQueryTimeout and a query takes long to complete, Connector/J attempts to KILL the query. However, if the query is stored routines which the effective user is not the invoker, KILL fails unless the user has SUPER privilege. As a result, long running queries may remain running until some intervention will be made.

I suggest to add properties which specify a credential for the user to KILL queries; the credential is used only when Connector/J attempts to KILL timeout'ed queries.

The expected properties would be like below:
- queryTimeoutKillingUser
- queryTimeoutKillingUserPassword

If root is specified as a user, Connector/J will never fail to KILL sessions due to lack of privilege.

How to repeat:
n/a

Suggested fix:
See description.

Yes, Mikiya, I agree with you but then again, what to do with, say, query that was load-balanced to another server instance? Maybe there even more such corner cases that can not be handled easily. My opinion is one simply should not use the timeout if he/she does not know what is actually going on.

IMO, this is a case for documentation.

Hi Tonci,

The problem is that a user cannot timeout queries if necessary. A user may think "this query should finish or timeout in 10 secounds." But Connector/J cannot KILL the query if the query is executing stored routines as a different user. This is bad.

One problem is a response. So, here's another solution. If the driver can close the session when timeout is reached, the client application can get back the control and decide what to do next. (Return an error to the user or retry etc) Closing a session does not require SUPER privilege.

Mikiya, courtesy of master Mark, here's the property you've been looking for (I hope):
queryTimeoutKillsConnection
(http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html)

Please see if it fits your needs.

Hi Tonci,

I'm sorry for my slow response.

Thank you for your pointing it out. The parameter perfectly fits!

Cheers.

Closing the report now.