CODEWITHSUNDEEP
tomcatgrailsjdbcjndi
Kaleb Brasee
Kaleb Brasee

Reputation: 51925

Grails/Tomcat/MySQL stale connection error, even when using JNDI?

I have a couple of Grails 1.3.3 application running on Tomcat 6.0.18. I'm aware of the database connection staleness issue when using the default Grails datasource (connection gets killed after a period of inactivity), so I switched to JNDI provided by Tomcat.

The first app I deployed has never had any stale database connection problems. Now I deployed a second app to the same server with the same JNDI datasource configuration, and while the first continues to work fine, the second app gets the connection timeout error after about 8 or so hours of inactivity. (After the error the connection gets refreshed and it works just fine again)

The datasources are defined in Tomcat's context.xml as follows:

<Resource name="jdbc/firstDs" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="user1" password="password1" driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/firstApp" />

<Resource name="jdbc/secondDs" auth="Container" type="javax.sql.DataSource"
    maxActive="100" maxIdle="30" maxWait="10000"
    username="user2" password="password2" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/secondApp" />

Both of the apps use the JNDI datasource in the Datasource.groovy file as follows (everything is exactly the same, except for the jndiName):

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

The ONLY difference I'm aware of between these 2 situations is that working app uses MyISAM tables, while the non-working app uses InnoDB tables. Has anyone experienced an issue with InnoDB and Tomcat connection pooling? I may try switching to MyISAM if I can't find anything else to try.

Upvotes: 1

Views: 1480

Answers (1)

Stefan Armbruster
Stefan Armbruster

Reputation: 39915

For MySQL and Grails it's best practice to have a validation query on the db pool. You might append to your definitions:

validationQuery="SELECT 1"

See also http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html#JDBC_Data_Sources

Upvotes: 3

Related Questions