There is a Spring boot application that transfers a large amount of data received by the API to a remote database (MySQL). I use HikariCP as a connection pool, here is its configuration at the moment of launching the application:

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

During the work log appears: HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)

And after a few minutes of work, an exception falls:

 2019-02-21 12:56:22:323 [HikariPool-1 connection closer] DEBUG czhpPoolBase.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 czhpHikariPool.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 czhpHikariPool.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 czhpHikariPool.createPoolEntry() - HikariPool-1 - Cannot acquire connection from data source 

Tried to set the max-lifetime: 25800, maximum-pool-size: 20, minimum-idle: 0, idle-timeout: 30000 - the result is always the same. What could be the problem?

UPD:

Conclusion timeouts from the database.

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.

During the work out the log:

 [yourPoolName connection closer] DEBUG czhpPoolBase.quietlyCloseConnection() - yourPoolName - Closing connection com.mysql.cj.jdbc.ConnectionImpl@742e0612: (connection has passed maxLifetime) [yourPoolName connection adder] DEBUG czhpHikariPool.call() - yourPoolName - Added connection com.mysql.cj.jdbc.ConnectionImpl@16e8fb27 

UPD3

It turned out that MySQL crashes on the remote server. The provider says lack of memory. What can memory memory? Maybe some kind of MySQL cache?

  • "The driver has not received any packets from the server." Indicates that the problem is on the server side. The driver does not receive packages from it. - talex
  • @talex 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 - R1zen
  • And if you open a connection to mysql from the command line or SQLDeveloper or MySQL Workbench, does the connection hold normally? - Alex Yu
  • @AlexYu launched in an infinite loop, sampling by the piece using java.sql.* , There are no errors. In this Connection I initialize only once and do not close in the loop. - R1zen
  • Hm So the suspicion of HikaryCP. You wrote: "I tried to install maximum-pool-size: 20" - was there any difference in behavior noticed? Exceptions occur sooner-later-equally? And what happens if you reduce the size of the pool to a minimum? Generally try successively: 1, 2, 4, 8 - will there be any exceptions and how soon? - Alex Yu

1 answer 1

The problem was not at all in Hikari , Hibernate , etc. At the time of loading data into the database, I also used the com.jcraft.jsch.JSch library to load images and did not correctly use the settings for connecting to the server, which caused memory overflow.