Hibernate SQL Error: 17002, SQLState: null Io exception: Connection reset

Question

We're using hibernate 3 and c3p0-0.9.1. We've an Hibernate criteria based query to check the keyed in user name and password from database tables as well as other queries. During execution of first query i.e the login page, we're facing the connecion reset issue with SQLState as null. If provide the details again in the page, everything is working on fine for some time. If we try after some time, again the same problem for the first time and when re-enter the user name and password, it is working fine for sometime. Please find the below error messages for reference.

00:12:42 WARN [org.hibernate.util.JDBCExceptionReporter:77 logExceptions] - SQL Error: 17002, SQLState: null 00:12:42 ERROR [org.hibernate.util.JDBCExceptionReporter:78 logExceptions] - Io exception: Connection reset

Here is the sample code...

public UserSample fetchUserDetails(final String loginId) throws DataAccessException {    
       return (UserSample) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {

                Criteria criteria = getSession().createCriteria(UserSample.class)
                    .createAlias("rolesample", "rolesample")
                    .createAlias("accountsample", "accountsample");
                try {
                    if(loginId!= null){
                        criteria.add(Restrictions.eq("loginName",loginId));
                    }

            criteria.setProjection(Projections.projectionList()
                .add(Projections.property("loginName").as("loginName2"))
                .add(Projections.property("password").as("password"))
                .add(Projections.property("name").as("name"))
                .add(Projections.property("roleId").as("roleId"))               );

                criteria.addOrder(Order.desc("loginName"));
                criteria.setFetchSize(1);
                criteria.setMaxResults(1);
                criteria.setResultTransformer(Transformers.aliasToBean(UserSample.class));

                ArrayList list = (ArrayList)criteria.list();
                if(list != null && list.size() > 0) {
                    return  (User)criteria.list().get(0);
                }else{
                    return new User();
                }
            }
                catch (Exception e) {
                    LogUtil.info(e.getMessage());
                    return null;
                }
            }
        }); 
   }

The connection pooling in applicationContext.xml is as below:

<bean id="sampleSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="sampleDataSource" />

        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
                <prop key="hibernate.query.substitutions">true 1, false 0, yes 'Y', no 'N'</prop>
                <prop key="hibernate.connection.pool_size">50</prop>
                <prop key="hibernate.show_sql">false</prop>                
                <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>                

                <!-- C3P0 Properties -->
                <prop key="hibernate.c3p0.min_size">3</prop>
                <prop key="hibernate.c3p0.max_size">50</prop>
                <prop key="hibernate.c3p0.timeout">1800</prop>
                <prop key="hibernate.c3p0.max_statements">100</prop>
                <prop key="hibernate.c3p0.idle_test_period">600</prop>
                <prop key="hibernate.c3p0.acquire_increment">1</prop>
                <prop key="hibernate.c3p0.preferredTestQuery">SELECT 1 FROM DUAL</prop>
            </props>            
        </property>

When I executed the generated query in oracle client such as SQL deverloper, able to get the result. But, the when run through the application, facing this issue randomly. Can you please assist? Thanks.

Best Regards, Lucky

Answer 1

You would have to provide more details in order to troubleshoot this.

1. What database are you connecting to?
2. Paste the hibernate query execution code.

By definition connection reset errors are due to

This exception appears when the remote connection is unexpectedly and forcefully closed due to various reasons like application crash, system reboot, hard close of remote host. Kernel from the remote system sends out a packets with RST bit to the local system. The local socket on performing any SEND (could be a Keep-alive packet) or RECEIVE operations subsequently fail with this error. Certain combinations of linger settings can also result in packets with RST bit set.

To aid debugging you could look at using a tool such as Wireshark to view the actual network packets.