MySQL Bugs: #56979: Improper connection closing logic leads to TIME

Bug #56979	Improper connection closing logic leads to TIME_WAIT sockets on server
Submitted:	23 Sep 2010 18:35	Modified:	28 Sep 2010 11:28
Reporter:	Gilles Rayrat	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When a JDBC client disconnects from a remote server using Connection.close(), the TCP connection will remain in TIME_WAIT state on the server side, where it should actually be on the client side.

How to repeat:
Do a simple connection from a remote client "C" to a MySQL server "S" and disconnect.

        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://S:3306/test", "user", "pass");
        conn.close();

On both client and server, check the current mysql TCP connections:

$ netstat -nat| grep 3306

You'll see that the connection will remain in TIME_WAIT state on the server:

$ netstat -nat| grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     
tcp        0      0 192.168.50.129:33510    192.168.50.128:3306     TIME_WAIT  

When running the same test with command line mysql client or with a perl DBI application, the TIME_WAIT state is found on the client side, as one would expect.

Suggested fix:
The problem seems to lie in the Connection.close() logic which sends a "quit" command, then closes its socket.
It appears that when the server receives the quit command, it pro-actively closes the connection just as if it was acting as a client, leading to the TIME_WAIT state on its end.
If the client properly shuts down its input (setting it to EOF), then sends the "quit" command, then shuts down its output before closing the socket, the TCP connection TIME_WAIT state stand on the client side, as expected (and as other client applications do)

The following patch fixes the problem:

--- MysqlIO.java	Thu Sep 23 20:32:48 2010
***************
*** 1661,1670 ****
--- 1661,1678 ----
       * @throws SQLException DOCUMENT ME!
       */
      final void quit() throws SQLException {
+         try {
+             mysqlConnection.shutdownInput();
+         } catch (IOException ignored) {
+         }
          Buffer packet = new Buffer(6);
          this.packetSequence = -1;
          packet.writeByte((byte) MysqlDefs.QUIT);
          send(packet, packet.getPosition());
+         try{
+             mysqlConnection.shutdownOutput();
+         }catch (IOException ignored) {
+         }
          forceClose();
      }

Fixed for 5.1.14. Fixed slightly differently, in that we go through forceClose(), so wanted to have input/output shutdown code there too, so shared some (shutting down output, and input just in case), and shutting down input in quit() since we're not going to read the response.

An entry has been added to the 5.1.14 changelog:

When a JDBC client disconnected from a remote server using Connection.close(), the TCP connection remained in the TIME_WAIT state on the server side, rather than on the client side.

Bug #36872 was closed as duplicate of this one.

Posted by developer:
 
It might look wrong that client enters TIMED_WAIT state upon closing a connection but actually this is the right thing to do. I am afraid that this "fix" changed the correct behavior into something wrong. See Bug #20222737 for explanations.

@Rafal
Please re-read the bug description. The problem described is that without the fix, the time_wait state shows at the server side, while it's expected on the client side as you find appropriate

Posted by developer:
 
Sorry for incorrect wording I used in my earlier comment. It should be:

It might look wrong that *server* enters TIMED_WAIT state upon closing a connection but actually this is the right thing to do. I am afraid that this "fix" changed the correct behavior into something wrong. See Bug #20222737/bug#75237 for explanations.

In particular see the detailed discussion in these blog articles:

http://blog.haproxy.com/2012/12/12/haproxy-high-mysql-request-rate-and-tcp-source-port-exh...
http://www.percona.com/blog/2014/12/08/what-happens-when-your-application-cannot-open-yet-...

While the Percona article explains the issue and doesn't give any opinion on where the time_wait should occur, I kind disagree with the HAProxy conclusion. Here is my response to Baptiste (moderation pending):

Hi Baptiste,
Thank you for this article. TIME_WAIT sockets for MySQL connection is something that has been bugging me (actually my customers) for years.
My analysis of this problem leads to the exact opposite conclusion: if you let the server side close the connection, the _server_ will end up with all the TIME_WAIT sockets. Put a few hundred client applications in the game and you will rapidly exhaust all server available ports, eventually bringing down your MySQL box.
All popular applications and protocols (ftp, ssh, telnet,…) work the same way: the client initiates the disconnection and gets the TIME_WAIT side of the socket. HAProxy acts as a “man in the middle” both as a client and as a server if you will. So at some point, it will get these TIME_WAIT state, no matter what.
Gilles.

PS: I like this solution of multiple source IP
PPS: I’d be very interested in getting your feedback on this. It’s a very interesting issue, whatever we put behind “interesting” :)

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

"For an SSL connection, after a client disconnected from a server by calling Connection.close(), the TCP connection remained in the TIME_WAIT state on the server side. With this fix, the connection remains in the TIME_WAIT state on the client side instead, in most cases."