Published on Monday, February 15th, 2010 at 2:15 pm

For those using MYSQL and have either updated to grails version 2 or starting with grails 2 and have run into this exception then this solution works perfectly well for me.

The problem is releated to Grails running out of connections to MySQL over time, & not freeing stale connections.  I believe it happens when there hasn’t been activity for over 8 hours, which in my case happened everyday due to the overnight period.

So what we should be doing is managing our own database connection pool.   I use Tomcat 6 on my production servers so the following solution is specifically for tomcat 6.

What we need to do is first change our application configuration so it sets up a database connection pool through tomcat.  And secondly we need to tweak the configuration so that we validate the connection is present periodically and removed stale connections.

The first thing then is to define the connection pool in <your application dir>/web-app/META-INF/context.xml

A typical context.xml would be :

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/MyApplication">
<Resource
 auth="Container"
 driverClassName="com.mysql.jdbc.Driver"
 maxActive="20"
 maxIdle="10"
 maxWait="-1"
 removeAbandoned="true"
 name="jdbc/myAppsPool"
 type="javax.sql.DataSource"
 url="jdbc:mysql://localhost:3306/Database_Name"
 username="user"
 password="password"
 validationQuery="SELECT '1'"
 removeAbandonedTimeout="60"
 logAbandoned="true"/>
</Context>

This alone will create the necessary database connection pool when you application is deployed.

All thats left to do now is to change your datasource configuration to refer to the connection pool so in your DataSource.groovy file, locate the production section and alter it to refer to the new pool, e.g :

production {
  dataSource {
    pooled = false
    dbCreate = "update"
    jndiName = "java:comp/env/jdbc/myAppsPool"
  }
}

That should solve any of those DataAccessResourceFailureExceptions from now on.[

Be Sociable, Share!

Related Posts

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


About Me

Welcome to my blog. Here you'll find mainly work related content, a suppository for all my notes which otherwise would end up on a post it note.