Re: mysql, number of TCP connections does not match on server / client machines

[Todd Farmer]

Hi gigi,

There are a number of reasons you could see more TCP connections than are seen via PROCESSLIST on the MySQL server. The most likely root cause are connections left idle beyond wait_timeout, and which the server believes are abandoned and thus closes on its end. The client side, though, may stay active (there's no signal sent from server to client to ping or announce termination), leaving the netstat output you see. You can check the global status variable, Aborted_clients, as this counter increases each time the server times out a client connection.

There are also times when Connector/J opens more than one physical connection for a single Connection object. One such scenario is reflected in your error messages - when one uses Statement.setTimeout(). MySQL client/server protocol has no mechanism to set timeouts directly, so if the timeout event is reached, a second Java Thread opens a new physical connection to the server and issues KILL QUERY to terminate the query being executed in the original Thread. That second physical connection is transient, so it's possible that reporting via netstat and PROCESSLIST would differ, although it's a fully-authenticated client connection and would show there for the duration of it's (short) lifecycle.

I hope that helps.

--
Todd Farmer
MySQL @ Oracle
http://www.oracle.com/mysql/