MySQL Bugs: #84783: query timeout is not working(thread hang)

Bug #84783	query timeout is not working(thread hang)
Submitted:	2 Feb 2017 0:28	Modified:	19 Apr 2017 1:16
Reporter:	Jung-ho Choi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.35	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	MultiHostMySQLConnection CancelTask QueryTimeout

Description:

We are using MySQL DB for data management.
I am using loadbalance setting in 5.1.35 version of connector.
Ex) jdbc: mysql: loadbalance: //
However, after setting querytimeout in WAS, I expected MySQLTimeoutException to occur, but it did not occur.

So I tested MySQL 5.1.15 with the same setting as 5.1.35 and MySQLTimeoutException occurred.

The difference between the two is that MultiHostMySQLConnection is used in 5.1.35 and LoadBalancedMySQLConnection is used in 5.1.15

I confirmed that it was caused by other parts than the connector, but I was convinced by the Connector issue that com.mysql.jdbc.JDBC4PreparedStatement has the timeoutInMillis value properly set.

Also, I checked that MySQL Query Query was called, but it was not called.

After all, we confirmed that the "MySQL Statement Cancellation Timer" thread works, but the CancelTask thread does not work properly and hangs from the sources below.

108 line    if (StatementImpl.this.connection.getQueryTimeoutKillsConnection()) {

How to repeat:
Set query timeout, set loadbalance on URL, execute SQL that is longer than timeout

Suggested fix:
The actual query being executed is already synchronized by calling the getActiveMySQLConnection method of com.mysql.jdbc.MultiHostMySQLConnection. It is assumed that a hang occurred by calling the method again in CancelTask

Hi Jung-ho Choi,

Thank you for your interest in Connector/J.

This behavior was verified as described.

Thank you for confirming.

When will it be fixed?
First, I am using socket timeout instead, but I want to use it as query timeout ASAP.

Hi Jung-ho Choi,

We can't make any promises, we'll look into this when possible. We understand it is important for you and we'll take that into account.

Thank you,

Posted by developer:
 
Added the following entry to the Connector/J 5.1.42 changelog:

"In a multi-host connection, query timeouts did not occur as configured. It was because the CancelTask thread, when trying to access the top level, virtual connection object, ran into a race condition with the connection monitor and then hung. With this fix, the CancelTask thread is passed a direct reference to the underlying physical connection, with which it can execute the cancellation. "