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:
java.net.SocketException: 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?
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.
Joined: Oct 10, 2005
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 ]
subject: Apache commons dbcp - SocketException: Too many open files