CODEWITHSUNDEEP
mysqlhibernate
Pavel Vlasov
Pavel Vlasov

Reputation: 4331

MySQLNonTransientConnectionException after server has not been accessed for hours

There is a Grails2.2/Hibernate/Tomcat7/MySQL5.5 web server. After none web client has requested it for hours, the first request to it fails as follows. (In fact, it happens every morning.) Any sequential requests are handled correctly.

I instructed MySQL driver to ?autoReconnect=true but it didn't help.

A rollback could be noticed below but I do not call it directly from my code. Instead it happens implicitly when trying to persist an object after the long pause.

Any idea to cure that?

Error 500: Executing action [actionName] of controller [MyController] caused exception: Runtime error executing action
Servlet: grails
URI: /appname/grails/appName/actionName.dispatch
Exception Message: Communications link failure during rollback(). Transaction resolution unknown. 
Caused by: Communications link failure during rollback(). Transaction resolution unknown. 
Class: MyController 
At Line: [139] 
Code Snippet:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4808)
    at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:368)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:323)
    at MyController.logRequest(...)
    ...
    at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:195)
    at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:636)

Tomcat's context.xml:

<Resource name="jdbc/JNDI_NAME" auth="Container"
 type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
 url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true"
 username="xxx" password="xxx" maxActive="20" maxIdle="10"
 removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
 maxWait="-1"/>

Upvotes: 3

Views: 8965

Answers (2)

Pavel Vlasov
Pavel Vlasov

Reputation: 4331

c3p0 is not used in my application so I end up with telling my data source to validate db connection first:

<Resource ...
   validationQuery="SELECT 1"
   testOnBorrow="true"/>

Upvotes: 0

gYanI
gYanI

Reputation: 511

Try adding the following c3po configuration to your hibernate.cfg.xml file. That should help; see this.

<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.acquireRetryAttempts">3</property>
<property name="hibernate.c3p0.acquireRetryDelay">1000</property>

Upvotes: 2

Related Questions