MySQL Bugs: #90753: Wait_timeout exceeded message not triggered

Bug #90753	Wait_timeout exceeded message not triggered
Submitted:	4 May 2018 14:42	Modified:	23 Jun 2018 18:37
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Connector/Java has code which - upon encountering a communications link failure error - compares the time the connection has been left idle to the wait_timeout (or interactive_timeout) server session variable obtained at connection.  If the connection has been left idle longer than those server timeouts, the resulting exception message is intended to highlight that.  However, this is not being triggered, apparently as a result of using the current (unsuccessful) client packet being sent as the point of comparison.

https://github.com/mysql/mysql-connector-j/blob/9131eefa398531c7dc98776e8a3fe839e544c5b2/s...

How to repeat:
1.  Set global wait_timeout and interactive_timeout to 2 (seconds)
2   Execute following code:

		try{
			Connection conn = getConnection();
			ResultSet rslt = conn.createStatement().executeQuery("SELECT @@global.wait_timeout, @@global.interactive_timeout");
			rslt.next();
			System.out.println("wait_timeout: " + rslt.getString(1));
			System.out.println("interactive_timeout: " + rslt.getString(1));		
			Thread.sleep(1000 * seconds);
			conn.createStatement().executeQuery("SELECT 1");
			System.out.println("Successfully executed query after sleeping " + seconds + " seconds.");
		} catch (Exception e){
			e.printStackTrace();
		}

Suggested fix:
Only use timestamps of successfully-sent client packets for purpose of comparison in generating the message.

Below is the output received running the test locally:

wait_timeout: 2
interactive_timeout: 2
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 5,010 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:172)

Hello Todd,

Thank you for the bug report and testcase.
Verified this behavior on MySQL Connector / J 8.0.11.

Thanks,
Chiranjeevi.

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

"If wait_timeout was set on the server and the Connector/J had the connection property interactiveClient=false, or if interactive_timeout was set on the server and Connector/J had the connection property interactiveClient=true, a connection is invalidated when it has idled for a longer time than the set timeout. When such a timeout occurred, Connector/J threw a . CJCommunicationsException: Communications link failure, without indicating it was a timeout. With this fix, the error message returned explains the issue and suggests how to avoid it."

Thank you Daniel and team!

It would be very useful to see this backported to any future 5.1 maintenance release, if possible.