Description:
For unexpected MySQL server down/hung, Connector/J has socketTimeout option.
But due to this option is applied for all Query execution, it must be set to a value corresponding to the longest Query execution time.
In our case, it is about 1 minutes, and the detection of OS crashes is delayed by that amount.
There are two ways to detect that a server has become unavailable.
1) Heart beat in Application Layer.
2) Enable TCP Keep Alive.
It's hard to implement Heart Beat.
However, with the support of the OS, TCP Keep Alive can be used with only a configuration change, and it can efficiently tell when a server is down.
If we enable TCP Keep Alive on socket, client side OS sends heart beat packet automatically and server side OS responds automatically.
Generally, many documents recommends that use TCP Keep Alive with caution. https://en.wikipedia.org/wiki/Keepalive#:~:text=and%20if%20included%20must%20default%20to%....
But those documents targeted for internet connection, and I think TCP Keep Alive is good for LAN between Application Server to MySQL Server.
How to repeat:
1. Start mysql server.
2. Connect MySQL Server from Java Application using Connector/J.
3. Execute long running query.
4. Unplug the server.
5. Java Application waiting response and connection is hung.
Suggested fix:
1. Add new option for enabling TCP Keep Alive. like tcpKeepAlive=(int)
2. Enable TCP Keep Alive for socket in Connector/J
