JDBC rollback failed

[tarjoadi]

Hi,

I have a Spring +Hibernate +MySql applicatie. After it runs for 20 hours, when I try to run it I have the next error:

org.springframework.web.util.NestedServletExceptio n: Request processing failed; nested exception is org.hibernate.TransactionException: JDBC rollback failed
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:487)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:430)
javax.servlet.http.HttpServlet.service(HttpServlet .java:690)
javax.servlet.http.HttpServlet.service(HttpServlet .java:803)


root cause

org.hibernate.TransactionException: JDBC rollback failed
org.hibernate.transaction.JDBCTransaction.rollback (JDBCTransaction.java:170)
business.VersionManager.getVersion(VersionManager. java:37)
web.Telefonbuch.handleRequest(Telefonbuch.java:99)
org.springframework.web.servlet.mvc.SimpleControll erHandlerAdapter.handle(SimpleControllerHandlerAda pter.java:48)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:857)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:792)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:475)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:430)
javax.servlet.http.HttpServlet.service(HttpServlet .java:690)
javax.servlet.http.HttpServlet.service(HttpServlet .java:803)


root cause

com.mysql.jdbc.exceptions.MySQLNonTransientConnect ionException: Connection.close() has already been called. Invalid operation in this state.
com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:888)
com.mysql.jdbc.Connection.getMutex(Connection.java :3714)
com.mysql.jdbc.Connection.rollback(Connection.java :5186)
org.hibernate.transaction.JDBCTransaction.rollback AndResetAutoCommit(JDBCTransaction.java:183)
org.hibernate.transaction.JDBCTransaction.rollback (JDBCTransaction.java:162)
business.VersionManager.getVersion(VersionManager. java:37)
web.Telefonbuch.handleRequest(Telefonbuch.java:99)
org.springframework.web.servlet.mvc.SimpleControll erHandlerAdapter.handle(SimpleControllerHandlerAda pter.java:48)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:857)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:792)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:475)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:430)
javax.servlet.http.HttpServlet.service(HttpServlet .java:690)
javax.servlet.http.HttpServlet.service(HttpServlet .java:803)


This is the hibernate.cfg.xml :
<property name="connection.url">jdbc:mysql://localhost/telefon</property>
<property name="connection.username">root</property>
<property name="connection.driver_class">com.mysql.jdbc.Driv er</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.password">*****</property>
<property name="current_session_context_class">thread</property>
<property name="hibernate.show_sql">true</property>

<!-- mapping files -->
<mapping resource="Version.hbm.xml" />
<mapping resource="Firmen.hbm.xml" />
<mapping resource="Telefon.hbm.xml" />
<mapping resource="Addr.hbm.xml" />
<mapping resource="Abt.hbm.xml" />

And here is my HibernateUtil class:

private static final SessionFactory sessionFactory;
static
{
try
{
// Create the SessionFactory from hibernate.cfg.xml
sessionFactory = new Configuration().configure()
.buildSessionFactory();
}
catch (Throwable ex)
{
// Make sure you log the exception, as it might be swallowed
System.err.println("Initial SessionFactory creation failed." + ex);
throw new ExceptionInInitializerError(ex);
}
}

public static SessionFactory getSessionFactory()
{

return sessionFactory;
}

And here is where the exception appears:
Session session = HibernateUtil.getSessionFactory().getCurrentSessio n();
try {
session.beginTransaction();

List lista=session.createQuery("select DATE_FORMAT(lastupdate,'%e. %b %Y, %T') from Version where tablename = 'telefon'").list();
version=lista.get(0).toString();
session.getTransaction().commit();
} catch (HibernateException e) {
session.getTransaction().rollback();
System.out.println("A aparut o eroare la preluarea versiunii");
throw e;
}

Why do I have this error?

10x

[Andreas Senft]

Probably the database has timed out the connection. I suggest using a connection pool which allows for checking the connections and removing them if they become invalid. I recommend c3p0.

And please use [ code] [ /code] tags to improve the readability of your postings.

Regards,
Andreas

[tarjoadi]

Do you mean this:

<code><property name="connection.pool_size">2</property></code>

?

[tarjoadi]

or you say it's better to use this format:

<!-- configuration pool via c3p0--> 
<property name="c3p0.acquire_increment">1</property> 
<property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
<property name="c3p0.max_size">100</property> 
<property name="c3p0.max_statements">0</property> 
<property name="c3p0.min_size">10</property> 
<property name="c3p0.timeout">100</property> <!-- seconds --> 
<!-- DEPRECATED very expensive property name="c3p0.validate>-->

?

10x

[karldmoore]

I would have thought it was the test methods Andreas was refering to.
http://www.mchange.com/projects/c3p0... kin(boolean)

[Andreas Senft]

Indeed. Try enabling the check before checkout (testConnectionOnCheckout) and/or the check of idle connections (idleConnectionTestPeriod). For the test query you might specify something simple like "SELECT 1 FROM DUAL".

Regards,
Andreas

[tarjoadi]

And this is all I have to do?
Is there else to configure in applicationcontext or some code to write?

[Andreas Senft]

If the problem is caused by the database closing your connections, then this should be sufficient.

[tarjoadi]

Hi,

I have inserted this in my XXX-servlet.xml :

<bean id="DataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost/telefon"/>
<property name="username" value="root"/>
<property name="password" value="XXXX"/>
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="select 1 from dual"/>
<property name="maxWait" value="60000"/>
<property name="maxActive" value="50"/>
</bean>

...and after 10 hours I still have the same problem.
Doesn't dbcp have the same results like c3p0?
Should I try to use only c3p0 and to put it's configuration in hibernate.cfg.xml ?

Adrian

[Andreas Senft]

DBCP does conceptually the same as C3P0. It is just not useful to mix and match. If you pass in a configured DBCP datasource to hibernate it makes no sense to specify C3P0 configuration parameters to the SessionFactory. So I would just omit the C3P0 parameters as the datasource is enough.
BTW: I would recommend to use xxx-servlet.xml files only for view-specific beans.

Regards,
Andreas