mysql - 通信链接失败时Grails重新连接到mysql

Question

我有一个错误困扰了很长一段时间，仍然没有解决

每当我收到:

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.

应用程序似乎永远不会重新连接。尽管这是我的数据源配置(这是一个外部配置):

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

我看到了几个关于这个问题的问题，但没有一个真正为我解决。

注意:我只是想强调这种情况很少发生，所以我不是要解决链接连接失败的问题，我只是想在重试后碰巧重新连接时系统不会挂起(另一个 C 进程可以充分处理这个问题，所以我怀疑问题出在 Hibernate 或 Grails 上)

这是我的mysql配置

[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

一些其他信息

    | 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

这可能是由于初始数据源池大小和您的 mysql 超时配置引起的。我使用以下配置:

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

连同:

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