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            |                                                              |

@WilsonHauck I tried to put the most significant info from the tables. I just want to emphasis that this occurs rarely, so i'm not trying to solve the issue of 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) — Dany Y, Jul 19 '18 at 08:06
How much RAM is on your server? Since you have been plagued for some time, you can shorten the number of questions required by posting TEXT RESULTS of A) SHOW GLOBAL STATUS; after 24 hours of UPTIME and B) SHOW GLOBAL VARIABLES; . I suspect you will be able to significantly reduce handler_read_rnd_next after analysis of data you post. — Wilson Hauck, Jul 19 '18 at 12:39
https://stackoverflow.com/questions/21698675/analyzing-connection-closed-exception-in-spring-jpa-mysql-tomcat-app/43280357#43280357 might be of help — V H, Jul 19 '18 at 16:09
@DanyY - Well, many of the `STATUS` values are useless by themselves -- without knowing the `Uptime` to get "per second". `table_open_cache` looks very low, but the metrics to say "too low" are missing. `table_type` was removed long ago; what `version` of MySQL is it? — Rick James, Jul 20 '18 at 13:50
@RickJames it's mysql 5.6- I can't retrieve all the info from mysql. but as I said, I don't want to change the configuration of MySql, I'm fine having this error (since it occurs once a month) I just don't want the whole process to hang if it happens once, so I want the process to retry — Dany Y, Jul 20 '18 at 14:53
If it happens after long period of app inactivity, so this link should help you: https://cwiki.apache.org/confluence/display/OFBIZ/MySQL+JDBC+Timeout+and+AutoReconnect — Koloritnij, Jul 23 '18 at 14:19

score 2 · Answer 1 · answered Jul 17 '18 at 08:36

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
}

I know the reason of lost connection, it happens when there is a load on the system... but what I want is to reconnect afterwards and not keep hanging — Dany Y, Jul 18 '18 at 09:36

score 0 · Answer 2 · answered Jul 25 '18 at 09:58

Does the application work fine on another database? Kindly check your mysql settings and share what is in the following lines:

sudo nano /etc/mysql/my.cnf
wait_timeout = 28800
interactive_timeout = 28800

I am thinking this might not be a grails issue.

Grails reconnect to mysql when having communications link failure

2 Answers2