springboot+hikaricp 管理mysql连接池出现以下错误:
2023-08-04 10:43:16.569 [xhx3-pool connection adder] ERROR c.z.h.p.HikariPool 498 createPoolEntry - xhx3-pool - Error thrown while acquiring connection from data source
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 5,008 milliseconds ago. The last packet sent successfully to the server was 5,008 milliseconds ago.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1419) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1404) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:408) ~[HikariCP-4.0.3.jar!/:?]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369) ~[HikariCP-4.0.3.jar!/:?]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar!/:?]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar!/:?]
at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71) ~[HikariCP-4.0.3.jar!/:?]
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:726) ~[HikariCP-4.0.3.jar!/:?]
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:712) ~[HikariCP-4.0.3.jar!/:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_201]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_201]
at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_201]
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet successfully received from the server was 5,008 milliseconds ago. The last packet sent successfully to the server was 5,008 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_201]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_201]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_201]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_201]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:546) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:710) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:649) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:136) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:311) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.NativeSession.queryServerVariable(NativeSession.java:1008) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1411) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
... 12 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.net.SocketInputStream.socketRead0(Native Method) ~[?:1.8.0_201]
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[?:1.8.0_201]
at java.net.SocketInputStream.read(SocketInputStream.java:171) ~[?:1.8.0_201]
at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[?:1.8.0_201]
at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at java.io.FilterInputStream.read(FilterInputStream.java:133) ~[?:1.8.0_201]
at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:540) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:710) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:649) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:136) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:311) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.NativeSession.queryServerVariable(NativeSession.java:1008) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1411) ~[mysql-connector-java-8.0.25.jar!/:8.0.25]
... 12 more
我的JAR包版本:
JDK:1.8
springboot:2.5.13
hikaricp:4.0.3
mysql-connector-java:8.0.25
mysql:8.0.25-15
我的springboot服务相关配置如下:
spring:
application:
name: XXXX
datasource:
xhx3:
url: jdbc:mysql://XXX:XXXX/ledger3_lns?zeroDateTimeBehavior=convertToNull&useSSL=false&autoReconnect=true&characterEncoding=utf8
username: XXXX
password: XXXX
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
hikari:
pool-name: xhx3-pool
minimum-idle: 30
maximum-pool-size: 30
idle-timeout: 360000
keepalive-time: 60000
max-lifetime: 540000
connection-timeout: 30000
auto-commit: true
read-only: true
mysql服务的wait_timeout配置如下:
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 10 |
| innodb_print_lock_wait_timeout_info | OFF |
| innodb_rollback_on_timeout | ON |
| interactive_timeout | 600 |
| lock_wait_timeout | 3600 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 600 |
+-------------------------------------+----------+
以上就是我的相关问题,目前异常是不定时就会出现一次,程序每天凌晨会有跑批任务查询数据库,数据库的QPS在2000左右,跑批SQL都是简单的查询没有写入操作,而且查询虽然有join查询但是使用的都是索引且join一张表;请各位帮忙看一下这个问题是我的配置有问题还是我的SQL有问题?感谢