Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Grails 2.3.0 and mysql connection timeout

Tags:

mysql

grails

I have a Grails 2.2.3 application using this configuration to connect to a mysql database:

 production {
    dataSource {
        dbCreate = "update" // one of 'create', 'create-drop','update'
        url = "jdbc:mysql://localhost/database?autoReconnect=true"

        pooled = true
        properties {
            maxActive = 50
            maxIdle = 25
            minIdle = 5
            initialSize = 5
            minEvictableIdleTimeMillis = 1800000
            timeBetweenEvictionRunsMillis = 1800000
            maxWait = 10000

        }


    }

}

After upgrading to Grails 2.3.0 this stopped working and after a weekend of inactivity on the app I get this exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 50,139,380 milliseconds ago.  The last packet sent successfully to the server was 50,139,380 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3352)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1971)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4997)
... 5 more
Caused by: java.net.SocketException: Write failed: Broken pipe
at jrockit.net.SocketNativeIO.socketWrite(SocketNativeIO.java:46)

Any ideas ?

like image 964
Luca Avatar asked Oct 18 '13 16:10

Luca


2 Answers

Try adding the following "testOn" properties to your connection pool config:

  properties {
    ...
    testOnBorrow = true
    testWhileIdle = true
    testOnReturn = false
    validationQuery = "SELECT 1"
  }

It's worth trying these flags in various combinations. Changes may be necessary for optimal performance in your environment.

like image 114
Andrew Avatar answered Sep 29 '22 09:09

Andrew


This has worked for us in the past

        properties {
            validationQuery="select 1"
            testWhileIdle=true
            timeBetweenEvictionRunsMillis=60000
        }
like image 24
visheshd Avatar answered Sep 30 '22 09:09

visheshd