主页 / 问题 / 947543
Accepted
R1zen
Asked:2020-02-21 18:14:46 +0000 UTC2020-02-21 18:14:46 +0000 UTC

Spring Data JPA 关闭数据库连接

  • 772

有一个 Spring Boot 应用程序可以将通过 API 接收到的大量数据传输到远程数据库（MySQL）。我使用 HikariCP 作为连接池，下面是它在应用启动时的配置：

HikariPool-1 - configuration: 
allowPoolSuspension.............false 
autoCommit......................false 
catalog.........................none 
connectionInitSql...............none 
connectionTestQuery.............none 
connectionTimeout...............30000 
dataSource......................none 
dataSourceClassName.............none 
dataSourceJNDI..................none 
dataSourceProperties............{password=<masked>} 
driverClassName................."com.mysql.cj.jdbc.Driver" 
healthCheckProperties...........{} 
healthCheckRegistry.............none 2019-02-21 13:00:32:351 [main] DEBUG c.z.h.HikariConfig.logConfiguration() - idleTimeout.....................600000 
initializationFailTimeout.......1 
isolateInternalQueries..........false 
jdbcUrl.........................jdbc:mysql://url/db_name?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&failOverReadOnly=false 
leakDetectionThreshold..........0 
maxLifetime.....................1800000 
maximumPoolSize.................10 
metricRegistry..................none 
metricsTrackerFactory...........none 
minimumIdle.....................10 
password........................<masked> 
poolName........................"HikariPool-1" 
readOnly........................false 
registerMbeans..................false 
scheduledExecutor...............none 
schema..........................none 
threadFactory...................internal 
transactionIsolation............default 
username........................"name" 
validationTimeout...............5000

在运行过程中，会出现一个日志：HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)

工作几分钟后，抛出异常：

2019-02-21 12:56:22:323 [HikariPool-1 connection closer] DEBUG c.z.h.p.PoolBase.quietlyCloseConnection() - HikariPool-1 - Closing connection com.mysql.cj.jdbc.ConnectionImpl@4169569c: (connection is dead) 
2019-02-21 12:56:22:327 [HikariPool-1 connection adder] DEBUG c.z.h.p.HikariPool.createPoolEntry() - HikariPool-1 - Cannot acquire connection from data source 
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467)
    at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:706)
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:692)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:152)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:955)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:825)
    ... 14 common frames omitted
Caused by: java.net.ConnectException: В соединении отказано (Connection refused)
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
    at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
    at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
    at java.net.Socket.connect(Socket.java:589)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
    ... 17 common frames omitted

2019-02-21 12:56:28:238 [HikariPool-1 housekeeper] DEBUG c.z.h.p.HikariPool.logPoolState() - HikariPool-1 - Pool stats (total=0, active=0, idle=0, waiting=1) 
2019-02-21 12:56:30:417 [HikariPool-1 connection adder] DEBUG c.z.h.p.HikariPool.createPoolEntry() - HikariPool-1 - Cannot acquire connection from data source

试图安装max-lifetime: 25800, maximum-pool-size: 20, minimum-idle: 0, idle-timeout: 30000- 结果总是一样的。可能是什么问题呢？

升级版：

从数据库输出超时：

connect_timeout 10
deadlock_timeout_long 50000000
deadlock_timeout_short 10000
delayed_insert_timeout 300
innodb_lock_wait_timeout 50
innodb_rollback_on_timeout OFF
interactive_timeout 28800
lock_wait_timeout 31536000
net_read_timeout 30
net_write_timeout 600
slave_net_timeout 3600
thread_pool_idle_timeout 60
wait_timeout 28800

UPD2。

在工作过程中，出现日志：

[yourPoolName connection closer] DEBUG c.z.h.p.PoolBase.quietlyCloseConnection() - yourPoolName - Closing connection com.mysql.cj.jdbc.ConnectionImpl@742e0612: (connection has passed maxLifetime) 
[yourPoolName connection adder] DEBUG c.z.h.p.HikariPool.call() - yourPoolName - Added connection com.mysql.cj.jdbc.ConnectionImpl@16e8fb27

UPD3

原来 MySQL 在远程服务器上崩溃了。提供商说内存不足。什么会阻塞记忆？也许某种 MySQL 缓存？

java

1 个回答

  1. Best Answer

    问题根本不存在Hikari，Hibernate等等。在加载数据到数据库的时候，我也是使用com.jcraft.jsch.JSch库加载图片，没有正确使用服务器连接设置，导致内存溢出。

    • 0

相关问题