C3P0, MySQL and ‘Error Reading Communication Packets’
February 24, 2009 Leave a comment
I have been using C3P0 as my connection pool for iBatis to connect to a MySQL database (via the MySQL connector for Java) and have been dogged by an annoying issue where the client (presumably iBatis) drops the connection without closing it first causing MySQL to complain with the error message “Got an error reading communication packets”. Not a major issue because it was not causing the application to fail, but it was generating about 2GB of unnecessary log every month.
Basically what is going on is that the connection times out at the MySQL server end because it is idle (more than 60 seconds in this case). I set the idle timeout in C3P0 to 50 seconds to ensure that the connection was closed there before the timeout triggered in MySQL, obviously that was not working. As I mentioned above what I think is going on is that iBatis is dropping the connection without closing it properly first:
I get pages of error logs with this message –
“Got an error reading communication packets”. And the aborted clients
are easily in hundreds.
You get that message in the log every time the server closes a client
connection because it was inactive too long. Too long is “longer than
Sometimes client applications are sloppy about close()ing all Connections they check out. Eventually, the pool grows to maxPoolSize, and then runs out of Connections, because of these bad clients.
I was not about to go grubbing around in the iBatis code (and whatever else) to figure out where the issue was, and found that ‘propertyCycle’ would probably help me:
Maximum time in seconds before user configuration constraints are enforced. Determines how frequently maxConnectionAge, maxIdleTime, maxIdleTimeExcessConnections, unreturnedConnectionTimeout are enforced. c3p0 periodically checks the age of Connections to see whether they’ve timed out. This parameter determines the period. Zero means automatic: A suitable period will be determined by c3p0. [You can call getEffectivePropertyCycle…() methods on a c3p0 PooledDataSource to find the period automatically chosen.]
The ‘suitable period’ chosen by C3P0 here was 12.5 seconds, which means that it is very likely for the cycle to fall outside my window of 50 and 60 seconds (see above.) Changing this to 5 seconds fixed the issue (10 still generated errors once in a while.) Interestingly the ‘suitable period’ is stored internally as a float (as retrieved by getEffectivePropertyCycleDefaultUser()) but is set as an int (as set by setPropertyCycle()), go figure.