mysql - 通信链路故障时 Grails 重新连接到 mysql

问题描述

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

每当我收到：

2018-07-16 11:21:27,815 [Thread-4] WARN spi.SqlExceptionHelper - SQL 错误：0，SQLState：08S01 2018-07-16 11:21:27,815 [Thread-4] 错误 spi.SqlExceptionHelper - 通信链接失败

从服务器成功接收到的最后一个数据包是 197,301 毫秒前。最后一个成功发送到服务器的数据包是在 197,301 毫秒前。

该应用程序似乎永远不会重新连接。即使这是我的数据源配置（它是外部配置）：

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

标签： mysqlhibernategrailsgrails-orm

这可能是由于初始数据源池大小和您的 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
}

mysql - 通信链路故障时 Grails 重新连接到 mysql

问题描述

解决方案

推荐阅读