9

I have the main thread that spawns thread #2 which uses the same hibernate Session in the main thread. Thread #2 just does a "select 1" every few min to keep the db connection alive because of a long running process from the main thread. Once the main thread is done w/ the processing, it calls a commit but i get the error:

Caused by: org.hibernate.TransactionException: JDBC commit failed
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:161)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:655)
    ... 5 more
Caused by: java.sql.SQLException: Can't call commit when autocommit=true
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
    at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1602)
    at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:170)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:146)
    ... 6 more

Within the main thread, it creates inner transactions which are committed successfully, it's just the outer transaction when it commits that throws this error. I don't see what could be changing the autocommit boolean. Before I introduced the 2nd thread to keep the connection alive, this error had never occurred.

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
czer
  • 676
  • 3
  • 8
  • 20
  • 1
    Are you using a single connection from multiple threads at once? – Donal Fellows Mar 10 '11 at 16:31
  • Yes, that's correct. Thread #1 creates the sessions which opens the db connection, and Thread #2 uses that same session and connection. Thread #1 would create new transactions within that connection to perform updates, while thread #2 only does a select 1 from the DB to keep the connection alive. – czer Mar 10 '11 at 16:36

2 Answers2

6

Even though I think you should seriously reconsider the way you are using Hibernate, you can bypass this issue by adding a relaxAutoCommit parameter to the JDBC driver in its URL.

Details from MySQL documentation:

relaxAutoCommit

If the version of MySQL the driver connects to does not support transactions, still allow calls to commit(), rollback() and setAutoCommit() (true/false, defaults to 'false')?

Default: false

Since version: 2.0.13

Source: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

Chris Ballance
  • 33,810
  • 26
  • 104
  • 151
jpkroehling
  • 13,881
  • 1
  • 37
  • 39
5

found the answer in a blog, the solution quotes:

Setting the attribute relaxAutoCommit=true in the jdbc url we solved our problem.

jdbc:mysql://dbserver/database?rewriteBatchedStatements=true&relaxAutoCommit=true

Of course the blog is in another scenario, just skip the "rewriteBatchedStatements=true" part

kkzxak47
  • 462
  • 7
  • 16