Hi,
We are using hibernate3 and spring framework with mysql5 on an Fedora Core 4 environment. Lately we have been noticing a problem with the connection pooling (we are using dbcp). The pool seems to run out of connections causing the entire system to eventually hang. This is definitely not a load issue as it happens at random times (a couple of times when the load is expected to be minimal).

the dbcp is configured as shown below. the stack trace is shown below that. The my.cnf file configuration is also shown. I have researched this and it seems it has something to do with hibernate/mysql settings, but I have not been able to figure this one out. Any help will be appreciated, need to get this stable. Please help.

#my.cnf file
[mysqld]
wait_timeout=600

<!-- datasource definition in applicationContext.xml-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${hibernate.connection.driver_class}" />
<property name="url" value="${hibernate.connection.url}" />
<property name="username" value="${hibernate.connection.username}" />
<property name="password" value="${hibernate.connection.password}" />
<property name="maxActive" value="30" />
<property name="maxWait" value="600" />
<property name="maxIdle" value="10" />
</bean>


<STACK TRACE>

[02:23:48,259] WARN - CommonsLoggingLogSystem.logVelocityMessage(46) | org.apache.velocity.runtime.exception.ReferenceException: reference : template = Notif
ySenderEmail.vm [line 10,column 18] : $order.masterTrackingNumber is not a valid reference.
[10:14:56,512] WARN - JDBCExceptionReporter.logExceptions(71) | SQL Error: 0, SQLState: null
[10:14:56,540] ERROR - JDBCExceptionReporter.logExceptions(72) | Cannot create PoolableConnectionFactory (Communications link failure due to underlying excep
tion:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: java.net.ConnectException: Connection refused

STACKTRACE:

java.net.SocketException: java.net.ConnectException: Connection refused
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:151)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:280)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1774)
at com.mysql.jdbc.Connection.<init>(Connection.java:437)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:268)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:80)
at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:72)
at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:1881)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1174)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:767)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:693)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1003)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:348)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:226)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:147)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:269)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:317)
at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:134)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:230)
:

Does this problem appear when those connections haven't been used for a quite long time (more than one hour, for example)?

In this case, I would recommend you to configure dbcp to test the connection before giving it to the requirer. See testOnBorrow and validationQuery attributes for this.

Anyway, imo, it's better to have this configured. This lowers the probability for your code to receive a stale connection (while it might not be your actual problem at the moment).

Thanks for the suggestion. Where can I configure the 'testonborrow' attribute? In the spring file (applicationContext.xml) where I create the dbcp bean?
Also, what would this do? Would it reject the stale connection and get me a new one?

Also, isn't the 'testOnBorrow' an expensive operation? The test will be performed *EACH* time a query is requested from the app..?