Grails reconnect to mysql when having communications link failure

Question

I have an error that has plagued for quite some time and still no solution

Whenever I receive :

2018-07-16 11:21:27,815 [Thread-4] WARN spi.SqlExceptionHelper - SQL Error: 0, SQLState: 08S01 2018-07-16 11:21:27,815 [Thread-4] ERROR spi.SqlExceptionHelper - Communications link failure

The last packet successfully received from the server was 197,301 milliseconds ago. The last packet sent successfully to the server was 197,301 milliseconds ago.

The application never seems to reconnect. Even though this is my datasource configuration (it's an external configuration):

dataSource.dbCreate=none
dataSource.driverClassName=com.mysql.jdbc.Driver
dataSource.url=jdbc:mysql://****/*****?autoReconnect=true&failOverReadOnly=false&maxReconnects=10
dataSource.username=***
dataSource.password=******
dataSource.properties.maxActive = 50
dataSource.properties.maxIdle = 25
dataSource.properties.minIdle = 1
dataSource.properties.initialSize = 1
dataSource.properties.numTestsPerEvictionRun = 3
dataSource.properties.maxWait = 10000
dataSource.properties.testOnBorrow = true
dataSource.properties.testWhileIdle = true
dataSource.properties.testOnReturn = true
dataSource.properties.validationQuery = "select now()"
dataSource.properties.minEvictableIdleTimeMillis = 300000
dataSource.properties.timeBetweenEvictionRunsMillis = 300000

I saw several questions on this issue, but nothing that actually solved it for me.

NOTE : I just want to emphasis that this occurs rarely, so i'm not trying to solve the issue of link connection failure, I just want that when it happens to reconnect after retry so the system won't hang (another C process handles this adequately so I suspect the issue is with Hibernate or Grails)

These are my mysql config

[client]
port=3306
socket=/data/db/mysql/mysql.sock

[mysqld]
port=3306
datadir=/data/db/mysql
socket=/data/db/mysql/mysql.sock
log-bin=/data/db/mysql/mysql-bin
binlog_format=row
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
max_connections=250

[mysqld_safe]
log-bin=/data/db/mysql/mysql-bin
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1
max_connections=250

Some other info

    | wait_timeout                            | 28800           
    | tx_isolation                            | REPEATABLE-READ             
    | thread_handling                         | one-thread-per-connection   
    | Table_locks_immediate                   | 15090998      |
    | Table_locks_waited                      | 7453          |     
    | table_definition_cache                  | 256                                                                                      
    | table_lock_wait_timeout                 | 50                                                                                        
    | table_open_cache                        | 64                                                                                       
    | table_type                              | MyISAM     

|Bytes_received                     | 2844964922    |
| Bytes_sent                        | 5623597485    |
| Com_select                        | 15003437      |
| Com_set_option                    | 35905         |
| Handler_commit                    | 10842183      |
| Handler_delete                    | 76            |
| Handler_discover                  | 0             |
| Handler_prepare                   | 410224        |
| Handler_read_first                | 90118         |
| Handler_read_key                  | 47411397      |
| Handler_read_next                 | 6354573941    |
| Handler_read_prev                 | 2900331388    |
| Handler_read_rnd                  | 449           |
| Handler_read_rnd_next             | 2183682054024 |
| Handler_rollback                  | 44            |                                                              |                                                      

Answer 1

This may cause because of initial datasource pool size and your mysql timeout config. I use following configuration:

dataSource {
    pooled = true
    jmxExport = true
    dbCreate = 'validate'
    driverClassName = 'com.mysql.jdbc.Driver'
    dialect = 'org.hibernate.dialect.MySQL5InnoDBDialect'

    properties {
        jmxEnabled = true
        maxActive = 50
        maxIdle = 25
        minIdle = 5
        initialSize = 5
        minEvictableIdleTimeMillis = 60000
        timeBetweenEvictionRunsMillis = 5000
        maxWait = 10000
        maxAge = 10 * 60000
        numTestsPerEvictionRun = 3
        testOnBorrow = true
        testWhileIdle = true
        testOnReturn = false
        ignoreExceptionOnPreLoad = true
        validationQuery = "SELECT 1"
        validationQueryTimeout = 3
        jdbcInterceptors = "ConnectionState;StatementCache(max=200)"
        defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
        abandonWhenPercentageFull = 100
        removeAbandonedTimeout = 120
        removeAbandoned = true
        logAbandoned = false
        dbProperties {
            autoReconnect = true
            jdbcCompliantTruncation = false
            zeroDateTimeBehavior = 'convertToNull'
        }
    }
}

along with:

dataSource {
    url = "jdbc:mysql://localhost:3306/dbname?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&autoReconnect=true"
    username = "user"
    password = "xxxxx"
    logSql = true
}