grails在通信链路出现故障时重新连接到mysql

rqqzpn5f  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(527)

我有一个错误,困扰了相当长一段时间,仍然没有解决办法
每当我收到:
2018-07-16 11:21:27815[thread-4]警告spi.sqlexceptionhelper-sql错误:0,sqlstate:08s01 2018-07-16 11:21:27815[thread-4]错误spi.sqlexceptionhelper-通信链路故障
从服务器成功接收的最后一个数据包是197301毫秒前。最后一个成功发送到服务器的数据包是197301毫秒前。
应用程序似乎从未重新连接。即使这是我的数据源配置(它是一个外部配置):

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

ippsafx71#

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

6ljaweal2#

应用程序在另一个数据库上工作正常吗?请检查您的mysql设置并分享以下内容:

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

我想这可能不是grails的问题。

相关问题