Grails – automatically reconnect to MySQL datasource

Well, I’m using Grails for a huge amount of quickly hacked apps.
Most of them use MySQL for persistence, because it just works πŸ™‚

There is one problem with the driver, though – sometimes, when nothing is happening, it allows the connections to disconnect.
It wasn’t a matter of one option somewhere, unfortunately πŸ™

According to MySQL JDBC driver configuration page, it might be enough to add

autoReconnect=true

to have

    development {
        dataSource {
            dbCreate = "create-drop"
            url = "jdbc:mysql://mysqlhost/database?useUnicode=true&autoReconnect=true"
            username = ""
            password = ""
        }
    }

But it doesn’t solve it ;(
After some searching, cursing and a beer, I’ve ended up with:

dataSource {
    pooled = true
    driverClassName = "com.mysql.jdbc.Driver"
    username = "secret"
    password = "santa"

   properties {
      maxActive = 50
      maxIdle = 25
      minIdle = 1
      initialSize = 1

      numTestsPerEvictionRun = 3
      maxWait = 10000

      testOnBorrow = true
      testWhileIdle = true
      testOnReturn = true

      validationQuery = "select now()"

      minEvictableIdleTimeMillis = 1000 * 60 * 5
      timeBetweenEvictionRunsMillis = 1000 * 60 * 5
   }
}

And this has been good enough to solve my pains. Thank you grails, for making me have problems I wouldn’t otherwise have.
Nice of you to allow me to fix them pretty fast at the same time πŸ™‚

Author: ags

bio

6 thoughts on “Grails – automatically reconnect to MySQL datasource”

  1. I hope there is cleaner way like what we do in c3p0 or dbcp. where you can pass parameters about testing the connection, and how frequent should the test be.

  2. this is great, I’ve looked all over for a solution to this, and I’ve tried several. I’m attempting this one now. BTW what is select now() doing? I was advised to use “select 1 as dbcp_connection_test”

Leave a Reply

Your email address will not be published. Required fields are marked *