MySQL connection unrecoverably breaks when inactive for x

[Catzy44]

My MySQL server is set to kill inactive connections after 8 hours. (wait_timeout = 28800). It's pretty common setup. Why? Because it's logical to close an socket when there is no any data transferred through it for x time. 8 hours is really long tbh.

I noticed that when my MC server is inactive for some time (BreweryX is not doing anything for more than 8 hours), and then it's active again BreweryX is crashing. Why? Because it's trying to send data to MySQL server, through the socket which was killed by the server and because of inactivity.

Plugin never recovers from this. Normally I would just include ?autoReconnect=true into MySQL connect string inside plugin configuration. To instruct MySQL driver to automatically reconnect when this kind of situation happens but it's not exposed as a config option.

So idk you should either expose autoReconnect as an config option. ..
Or even better - implement some sort of keepalive packets. As it's done in most plugins.

The config option is generally not needed. You could just execute this MySQL command to read Servers keep_alive:
SHOW VARIABLES LIKE 'wait_timeout';
And implement some sort of mechanism that would send keepalive packets to keep the connection alive according to server's wait_timeout when there's no activity.

EXAMPLES FROM OTHER PLUGINS:

1. In SkinsRestorer plugin there are no additional config options to handle autoReconnect/keepAlive. I suppose that it's keepAlive. And it's value is read from the server automatically as in the above example. Or just hardcoded for few minutes or so. Tbh I would just send keepalives in a loop every few minutes. It's the easy way.

2. In AuthMe plugin there is this config section:

# The maximum lifetime of a connection in the pool, default = 1800 seconds
# You should set this at least 30 seconds less than mysql server wait_timeout
maxLifetime: 1800

I suppose that this plugin is calculating time of inavtivity, distracting maxLifetime and decifing when it's needed to send this kinda of packet.

3. In FunnyGUilds plugin there is additional option inside database section. I suppose that it's working the same way as in AuthMe:

mysql:
  hostname: localhost
  port: 3306
  database: xxx
  user: minecraft
  password: xxx
  poolSize: 5
  connectionTimeout: 30000

Thanks for your time!

[06:20:51 INFO]: [BreweryX] java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) [06:20:51 INFO]: [BreweryX] java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) [06:20:51 INFO]: [BreweryX] java.base/java.lang.Thread.run(Thread.java:1583) [06:20:51 INFO]: [BreweryX] ERROR: Failed to manage transaction for saving objects to: wakeups due to MySQL exception! [06:20:51 INFO]: [BreweryX] ERROR: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 215,580,230 milliseconds ago. The last packet sent successfully to the server was 215,580,230 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. [06:20:51 INFO]: [BreweryX] ERROR: com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:165) [06:20:51 INFO]: [BreweryX] ERROR: com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:55) [06:20:51 INFO]: [BreweryX] ERROR: com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2217) [06:20:51 INFO]: [BreweryX] ERROR: com.dre.brewery.storage.impls.MySQLStorage.saveAllGeneric(MySQLStorage.java:183) [06:20:51 INFO]: [BreweryX] ERROR: com.dre.brewery.storage.impls.MySQLStorage.saveAllGeneric(MySQLStorage.java:170) [06:20:51 INFO]: [BreweryX] ERROR: com.dre.brewery.storage.impls.MySQLStorage.saveAllWakeups(MySQLStorage.java:369) [06:20:51 INFO]: [BreweryX] ERROR: com.dre.brewery.storage.DataManager.doSave(DataManager.java:200) [06:20:51 INFO]: [BreweryX] ERROR: com.dre.brewery.storage.DataManager.lambda$saveAll$1(DataManager.java:182) [06:20:51 INFO]: [BreweryX] ERROR: com.dre.brewery.depend.universalScheduler.foliaScheduler.FoliaScheduler.lambda$runTaskAsynchronously$12(FoliaScheduler.java:138) [06:20:51 INFO]: [BreweryX] ERROR: io.papermc.paper.threadedregions.scheduler.FoliaAsyncScheduler$AsyncScheduledTask.run(FoliaAsyncScheduler.java:216) [06:20:51 INFO]: [BreweryX] ERROR: java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) [06:20:51 INFO]: [BreweryX] ERROR: java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) [06:20:51 INFO]: [BreweryX] ERROR: java.base/java.lang.Thread.run(Thread.java:1583) [06:20:51 INFO]: [BreweryX] Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 215,580,230 milliseconds ago. The last packet sent successfully to the server was 215,580,230 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. [06:20:51 INFO]: [BreweryX] java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62) [06:20:51 INFO]: [BreweryX] java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502) [06:20:51 INFO]: [BreweryX] java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486) [06:20:51 INFO]: [BreweryX] com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:52) [06:20:51 INFO]: [BreweryX] com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:95) [06:20:51 INFO]: [BreweryX] com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:140) [06:20:51 INFO]: [BreweryX] com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:156) [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.a.NativeProtocol.clearInputStream(NativeProtocol.java:876) [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:689) [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:940) [06:20:51 INFO]: [BreweryX] com.mysql.cj.NativeSession.execSQL(NativeSession.java:817) [06:20:51 INFO]: [BreweryX] com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2192) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.impls.MySQLStorage.saveAllGeneric(MySQLStorage.java:183) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.impls.MySQLStorage.saveAllGeneric(MySQLStorage.java:170) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.impls.MySQLStorage.saveAllWakeups(MySQLStorage.java:369) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.DataManager.doSave(DataManager.java:200) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.DataManager.lambda$saveAll$1(DataManager.java:182) [06:20:51 INFO]: [BreweryX] com.dre.brewery.depend.universalScheduler.foliaScheduler.FoliaScheduler.lambda$runTaskAsynchronously$12(FoliaScheduler.java:138) [06:20:51 INFO]: [BreweryX] io.papermc.paper.threadedregions.scheduler.FoliaAsyncScheduler$AsyncScheduledTask.run(FoliaAsyncScheduler.java:216) [06:20:51 INFO]: [BreweryX] java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) [06:20:51 INFO]: [BreweryX] java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) [06:20:51 INFO]: [BreweryX] java.base/java.lang.Thread.run(Thread.java:1583) [06:20:51 INFO]: [BreweryX] Caused by: java.io.IOException: Socket is closed. [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.AbstractSocketConnection.getMysqlInput(AbstractSocketConnection.java:68) [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.a.NativeProtocol.clearInputStream(NativeProtocol.java:872) [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:689) [06:20:51 INFO]: [BreweryX] com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:940) [06:20:51 INFO]: [BreweryX] com.mysql.cj.NativeSession.execSQL(NativeSession.java:817) [06:20:51 INFO]: [BreweryX] com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2192) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.impls.MySQLStorage.saveAllGeneric(MySQLStorage.java:183) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.impls.MySQLStorage.saveAllGeneric(MySQLStorage.java:170) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.impls.MySQLStorage.saveAllWakeups(MySQLStorage.java:369) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.DataManager.doSave(DataManager.java:200) [06:20:51 INFO]: [BreweryX] com.dre.brewery.storage.DataManager.lambda$saveAll$1(DataManager.java:182) [06:20:51 INFO]: [BreweryX] com.dre.brewery.depend.universalScheduler.foliaScheduler.FoliaScheduler.lambda$runTaskAsynchronously$12(FoliaScheduler.java:138) [06:20:51 INFO]: [BreweryX] io.papermc.paper.threadedregions.scheduler.FoliaAsyncScheduler$AsyncScheduledTask.run(FoliaAsyncScheduler.java:216) [06:20:51 INFO]: [BreweryX] java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) [06:20:51 INFO]: [BreweryX] java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) [06:20:51 INFO]: [BreweryX] java.base/java.lang.Thread.run(Thread.java:1583)

[GillesSchippers]

I have the exact same issue on Paper, does anyone know how to force the autoreconnect functionality for Connector/J?