Spring-hibernate db connection issue

[rizmerc]

Hi,
We are using spring and hibernate for our web based application. Till recently, all db related operations were handled by hibernate. But we wanted to speed up the retrieval of orders and decided to write a custom straight JDBC class to perform searches on the Order object.

In order to achieve the above, we simply inject the "dataSource" bean into the CustomOrderSearch.java class (using spring), create a connection explicitly from this dataSource, perform our search and call close() on the connection.
conn = dataSource.getConnection();
stmt = conn.createStatement();
.
.
rs.close();
conn.close();

this too was working fine whren we started up the app, but after a day or so we started seeing errors with regards to db connections. I have pasted snippets of the stack trace below (also pasted snippets from the applicationContext.xml). I am wondering if this error has anything to do with us using connections from the dataSource for straight JDBC and then closing that connection.
Or is it something else I am missing? Please help...

** 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(Stand ardSocketFactory.java:151)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:280)
at com.mysql.jdbc.Connection.createNewIO(Connection.j ava:1774)
at com.mysql.jdbc.Connection.<init>(Connection.java:4 37)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:268)
at org.apache.commons.dbcp.DriverConnectionFactory.cr eateConnection(DriverConnectionFactory.java:37)
at org.apache.commons.dbcp.PoolableConnectionFactory. makeObject(PoolableConnectionFactory.java:290)
at org.apache.commons.dbcp.BasicDataSource.validateCo nnectionFactory(BasicDataSource.java:877)
at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:851)
at org.apache.commons.dbcp.BasicDataSource.getConnect ion(BasicDataSource.java:540)
at org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider.getConnection(LocalDataSourceCo nnectionProvider.java:80)
at org.hibernate.cfg.SettingsFactory.buildSettings(Se ttingsFactory.java:72)
at org.hibernate.cfg.Configuration.buildSettings(Conf iguration.java:1881)
at org.hibernate.cfg.Configuration.buildSessionFactor y(Configuration.java:1174)
at org.springframework.orm.hibernate3.LocalSessionFac toryBean.newSessionFactory(LocalSessionFactoryBean .java:767)
at org.springframework.orm.hibernate3.LocalSessionFac toryBean.afterPropertiesSet(LocalSessionFactoryBea n.java:693)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.invokeInitMethods(Abstr actAutowireCapableBeanFactory.java:1003)
at org.springframework.beans.factory.support.Abstract AutowireCapableBeanFactory.createBean(AbstractAuto wireCapableBeanFactory.java:348)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:226)
at org.springframework.beans.factory.support.Abstract BeanFactory.getBean(AbstractBeanFactory.java:147)
at org.springframework.beans.factory.support.DefaultL istableBeanFactory.preInstantiateSingletons(Defaul tListableBeanFactory.java:269)
at org.springframework.context.support.AbstractApplic ationContext.refresh(AbstractApplicationContext.ja va:317)
at org.springframework.web.context.support.AbstractRe freshableWebApplicationContext.refresh(AbstractRef reshableWebApplicationContext.java:134)
at org.springframework.web.context.ContextLoader.crea teWebApplicationContext(ContextLoader.java:230)
at org.springframework.web.context.ContextLoader.init WebApplicationContext(ContextLoader.java:156)
at org.springframework.web.context.ContextLoaderListe ner.contextInitialized(ContextLoaderListener.java: 48)
at org.apache.catalina.core.StandardContext.listenerS tart(StandardContext.java:3729)
at org.apache.catalina.core.StandardContext.start(Sta ndardContext.java:4183)
at org.apache.catalina.core.ContainerBase.addChildInt ernal(ContainerBase.java:759)
at org.apache.catalina.core.ContainerBase.addChild(Co ntainerBase.java:739)
at org.apache.catalina.core.StandardHost.addChild(Sta ndardHost.java:524)
at org.apache.catalina.startup.HostConfig.deployDescr iptor(HostConfig.java:608)
at org.apache.catalina.startup.HostConfig.deployDescr iptors(HostConfig.java:535)
at org.apache.catalina.startup.HostConfig.deployApps( HostConfig.java:470)
at org.apache.catalina.startup.HostConfig.start(HostC onfig.java:1112)
at org.apache.catalina.startup.HostConfig.lifecycleEv ent(HostConfig.java:310)
at org.apache.catalina.util.LifecycleSupport.fireLife cycleEvent(LifecycleSupport.java:119)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1021)
at org.apache.catalina.core.StandardHost.start(Standa rdHost.java:718)
at org.apache.catalina.core.ContainerBase.start(Conta inerBase.java:1013)
at org.apache.catalina.core.StandardEngine.start(Stan dardEngine.java:442)
at org.apache.catalina.core.StandardService.start(Sta ndardService.java:450)
at org.apache.catalina.core.StandardServer.start(Stan dardServer.java:709)
at org.apache.catalina.startup.Catalina.start(Catalin a.java:551)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.catalina.startup.Bootstrap.start(Bootst rap.java:275)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.commons.daemon.support.DaemonLoader.sta rt(DaemonLoader.java:177)


** END NESTED EXCEPTION **

)
[11:41:06,482] WARN - SettingsFactory.buildSettings(103) | Could not obtain connection metadata
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure due to underlying exception:

** 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(Stand ardSocketFactory.java:151)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:280)
at com.mysql.jdbc.Connection.createNewIO(Connection.j ava:1774)
at com.mysql.jdbc.Connection.<init>(Connection.java:4 37)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:268)
at org.apache.commons.dbcp.DriverConnectionFactory.cr eateConnection(DriverConnectionFactory.java:37)
at org.apache.commons.dbcp.PoolableConnectionFactory. makeObject(PoolableConnectionFactory.java:290)
at org.apache.commons.dbcp.BasicDataSource.validateCo nnectionFactory(BasicDataSource.java:877)
at org.apache.commons.dbcp.BasicDataSource.createData Source(BasicDataSource.java:851)
at org.apache.commons.dbcp.BasicDataSource.getConnect ion(BasicDataSource.java:540)
at org.springframework.orm.hibernate3.LocalDataSource ConnectionProvider.getConnection(LocalDataSourceCo nnectionProvider.java:80)
at org.hibernate.cfg.SettingsFactory.buildSettings(Se ttingsFactory.java:72)


<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}" />
</bean>


<!-- Hibernate SessionFactory -->
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
<property name="dataSource" ref="dataSource" />

<property name="mappingDirectoryLocations">
<list>
<value>classpath:com/cwsi/eshipper/model</value>
<value>classpath:com/cwsi/eshipper/carrier/purolator/model</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
${hibernate.dialect}
</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.dbcp.maxActive">30</prop>
<prop key="hibernate.dbcp.whenExhaustedAction">20</prop>
<prop key="hibernate.dbcp.maxWait">${hibernate.connectio n.maxWait}</prop>
<prop key="hibernate.dbcp.maxIdle">10</prop>
</props>

[Costin Leau]

MySql has the nice habit of closing connections after being idle for 8h or so (usually during the night). You can either configure the MySQL database so it doesn't close connections or you can configure your connection pool so that it verifies connections or that it has more or less the same idle time as mysql.
This way, if mysql behavior changes when you upgrade, you are isolated from the issue.

[manifoldronin]

These are, of course, kept in a finally block in the actual code, are they?

rs.close();
conn.close();

Did you try passing raw SQL through a Hibernate session? That way you'll probably know if there's anything wrong with your connection management.

[rizmerc]

Thanks for the response.

What I dont understand is that this was working fine all this time. Only change we made was use the dataSource defined in the applicationContext to query the db using straight JDBC in a special DAO class..
why would this happen suddenly? well for now I have restarted the server and it hasnt happened yet...
any more clues as to what it could be?

Also, do you think that our settings for connection pooling are ok for production?

Thanks again,
-Riz.

[Costin Leau]

If you are configuring the datasource yourself as a bean then move the configuration properties there from Hibernate factoryBean since hibernate will not create its own datasource (it will not need to) and it will silently ignore your settings.

[rizmerc]

Jing,
the calls to close connection are in a finally{} block. When the error occurred, hibernate was able to perform operation in the background that require db connections.
I havent seen the problem ever since I restarted the application, nonetheless this is disconcerting because once the application goes LIVE it would not be nice to see the same error !!

Costin,
Please could you explain what you meant by "move the configuration from the hibernate factorybean" with a small example? Also, please could you tell me if our db connection pooling configuration (in the original post) is good for production?

Thanks a ton guys!
-Riz.

[Costin Leau]

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSes sionFactoryBean">
<property name="dataSource" ref="dataSource" />

<property name="mappingDirectoryLocations">
<list>
<value>classpath:com/cwsi/eshipper/model</value>
<value>classpath:com/cwsi/eshipper/carrier/purolator/model</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
${hibernate.dialect}
</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.dbcp.maxActive">30</prop>
<prop key="hibernate.dbcp.whenExhaustedAction">20</prop>
<prop key="hibernate.dbcp.maxWait">${hibernate.connectio n.maxWait}</prop>
<prop key="hibernate.dbcp.maxIdle">10</prop>
</props>

The

<prop key="hibernate.dbcp.maxActive">30</prop>
<prop key="hibernate.dbcp.whenExhaustedAction">20</prop>
<prop key="hibernate.dbcp.maxWait">${hibernate.connectio n.maxWait}</prop>
<prop key="hibernate.dbcp.maxIdle">10</prop>

are suppose to configure the internal dbcp datasource created by hibernate. However, you are already providing one so the configuration settings should be on the datasource (move them from the properties to the datasource definition:

<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="maxWait" value="<someValue>" />
<property name="maxIdle" value="<someOtherValue>" />
<property name="whenExhaustedAction" value="<foo>" />
...
</bean>

As for how good they are - it depends a lot of on your configuration and requirements. Try with some generic, estimated values and then monitor and adjust in time.