CODEWITHSUNDEEP
mysqlgrailsspring-jdbc
Michael J. Lee
Michael J. Lee

Reputation: 12406

Connection already closed

I'm using Grails 2.5.3 and Tomcat7 and after 8 hours of app deployment our logs start blowing up with connection already closed issues. A good assumption is that MySql is killing the connection after the default wait time of 8 hrs.

By way of the docs my pool seems to be configured correctly to keep the idle connections open but it doesn't seem to be the case.

What might be wrong with my connection pool setting?

dataSource {
  pooled = true
  url = 'jdbc:mysql://******.**********.us-east-1.rds.amazonaws.com/*****'
  driverClassName = 'com.mysql.jdbc.Driver'
  username = '********'
  password = '******************'
  dialect = org.hibernate.dialect.MySQL5InnoDBDialect
  loggingSql = false
  properties {
    jmxEnabled = true
    initialSize = 5
    timeBetweenEvictionRunsMillis = 10000
    minEvictableIdleTimeMillis = 60000
    validationQuery = "SELECT 1"
    initSQL = "SELECT 1"
    validationQueryTimeout = 10
    testOnBorrow = true
    testWhileIdle = true
    testOnReturn = true
    testOnConnect = true
    removeAbandonedTimeout = 300
    maxActive=100 
    maxIdle=10 
    minIdle=1
    maxWait=30000
    maxAge=900000
    removeAbandoned="true"
    jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementCache;"

  }
}

hibernate {
  cache.use_second_level_cache=true
  cache.use_query_cache=true
  cache.region.factory_class = 'org.hibernate.cache.ehcache.EhCacheRegionFactory'
}

Also, I have confirmed that the dataSource at runtime is an instance of (org.apache.tomcat.jdbc.pool.DataSource)

UPDATE 1 (NOT FIXED) We think we may have found the problem! We were storing a domain class in the http session and after reading a bit about how the session factory works we believe that the stored http object was somehow bound to a connection. When a user accessed the domain class form the http session after 8 hours we think that hibernate stored a reference to the dead connection. It's in production now and we are monitoring.

UPDATE 2 (FIXED) We finally found the problem. Removing removeAbandoned and removeAbandonedTimeout resolved all our problems. We're not entirely sure why this resolved the issue as our assumption was that these two properties exist to prevent exactly what was occurring. The only thought is that our database was more aggressively managing the abandoned connections. It's been over 4 weeks with no issues.

Upvotes: 4

Views: 5041

Answers (3)

Reimius
Reimius

Reputation: 5712

I've had this issue with a completely different setup. It's really not fun to deal with. Basically it boils down to this:

  1. You have some connection somewhere in your application just sitting around while Java is doing some sort of "other" processing. Here's a really basic way to reproduce:

    Connection con = (get connection from pool); Sleep(330 seconds); con.close();

  2. The code is not doing anything with the database connection above, so tomcat detects it as abandoned and returns it to the pool at 300 seconds.

  3. Your application is high traffic enough that the same connection (both opened and abandoned in the above code) is opened somewhere else in the application in a different part of code.

  4. Either the original code hits 330 seconds and closes the connection, or the new code picks up the connection and finished and closes it. At this point there are two places using the same connection and one of them has closed it.

  5. The other location of code using the same connection then tries to either use or close the same connection

  6. The connection is already closed. Producing the above error.

Suggested route to fix:

Use the setting logAbandoned="true" to find where the connections are being abandoned from.

Upvotes: 3

Michael J. Lee
Michael J. Lee

Reputation: 12406

(see update 2 on question)

Removing removeAbandoned and removeAbandonedTimeout resolved all our problems. Someone may want to provide a more detailed answer on why this did because we are not entirely sure.

Upvotes: 0

Michal_Szulc
Michal_Szulc

Reputation: 4177

Our url usually looks alike:

url = "jdbc:mysql://localhost/db?useUnicode=yes&characterEncoding=UTF-8&autoReconnect=true"

Check out also encoding params if you don't want to face such an issue.

Upvotes: 2

Related Questions