当通信链路出现故障时，Grails会重新连接到mysql

Question

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

每当我收到：

2018-07-16 11:21:27815[线程4]警告spi。SqlExceptionHelper-SQL错误：0，SQL状态：08S01 2018-07-16 11:21:27815[线程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            |                                                              |                                                      

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
}

Answer 2

应用程序在另一个数据库上运行良好吗？请检查您的mysql设置并分享以下行中的内容：

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

我认为这可能不是一个圣杯问题。