File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Apache commons dbcp - SocketException: Too many open files Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Apache commons dbcp - SocketException: Too many open files" Watch "Apache commons dbcp - SocketException: Too many open files" New topic

Apache commons dbcp - SocketException: Too many open files

Martin Wingert

Joined: Oct 10, 2005
Posts: 16
I have a system using apache commons package dbcp for database connection pooling. We're using MySQL 5.0 (mysql-connector-java-5.1.6).

Just found that the system after running a couple of hours got an exception in the log files when trying to create a new database connection: Too many open files

Checked the list of open files (lsof) and got a long list:
java 10659 admin 6u sock 0,4 1555102 can't identify protocol

The PID is the jvm running our system.

Found that the MySQL setting wait_timeout (that closes idle connections after the configured time) was set to 60 seconds by accident. So it seems that when MySQL closes an idle connection somehow the JDBC connection in the database pool isn't closed properly leaving the file descriptor open (this is a test system so there are many idle connections).

Have anyone have encountered anything similar or have any suggestions on how to proceed?
Travis Hein
Ranch Hand

Joined: Jun 06, 2006
Posts: 161
is it possible something that would be using the commons-dbcp is not closing its connection, so the use of the connections grows indefinately.

if you have not set the "maxActive" property of the BasicDataSource, i think the pool would grow indefinately until the database or file handles resource gets exausted.

another option, if mysql was closing connections after some amount of time out, try to set the "validationQuery" property of the BasicDataSource. this will cause the commons-dbcp to invoke this SQL query before it would try to return a connection to the caller, and if the connection is found to be in a bad (closed) state, it might try to do a shutdown of the connection and cleanup resources used for it and then open it up again, where as if the connection was closed when the pool handed it over to the caller, it is possible (depending on the jdbc url, i..e. with mysql ?reconnect=true parameter) that the connection could be reopened by the caller (outside of the pool) when it is used,

Error: Keyboard not attached. Press F1 to continue.
Martin Wingert

Joined: Oct 10, 2005
Posts: 16
Thanks for the response Travis!

I am closing connections in a finally block whenever I get and use a connection.

I have set the property maxActive on the GenericObjectPool.

I have set a validation query.

I also had the thought that this could be a problem caused due to some strange interaction between the pool and the mysql connector. Actually we had the reconnect parameter set to true when I discovered this problem and I tried to set it to false but it did not help. Now I saw another parameter for the mysql connector called "autoReconnectForPools" that I'll look into a bit more.

This is how I setup the pool:

I know that the abandoned configuration is deprecated but it seems that it is that cause the authors of DBCP thinks that the functionality belongs in the pool package and not because something is wrong with it.

This is the first time I use DBCP so any comments about the setup are very welcome.
[ September 30, 2008: Message edited by: Martin Wingert ]
I agree. Here's the link:
subject: Apache commons dbcp - SocketException: Too many open files
It's not a secret anymore!