BentoBoxWorld
MySQL Connection breaks after a while
Expected behavior
Plugin should automatically reconnect if the connection to the database closes.
Observed/Actual behavior
[04:55:26 ERROR]: [BentoBox] Could not load objects The last packet successfully received from the server was 17,653,517 milliseconds ago. The last packet sent successfully to the server was 17,653,518 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.
Steps/models to reproduce
Leave BentoBox with MySQL running for a while.
BentoBox version
[04:58:21 INFO]: Running PAPER 1.17.1. [04:58:21 INFO]: BentoBox version: 1.20.0 [04:58:21 INFO]: Database: MARIADB [04:58:21 INFO]: Loaded Game Worlds: [04:58:21 INFO]: oneblock_world (OneBlock): Overworld, Nether [04:58:21 INFO]: Loaded Addons: [04:58:21 INFO]: AOneBlock 1.9.0 (ENABLED) [04:58:21 INFO]: Bank 1.4.0 (ENABLED) [04:58:21 INFO]: Border 3.2.0 (ENABLED) [04:58:21 INFO]: Chat 1.1.4 (ENABLED) [04:58:21 INFO]: Level 2.8.1 (ENABLED) [04:58:21 INFO]: Likes 2.1.1 (ENABLED) [04:58:21 INFO]: Limits 1.17.3-SNAPSHOT-LOCAL (ENABLED) [04:58:21 INFO]: Upgrades 0.2.0-SNAPSHOT-LOCAL (ENABLED) [04:58:21 INFO]: Warps 1.11.2 (ENABLED)
Plugin list
BentoBox, Multiverse-Core, ViaVersion, CMI, Votifier (Actually a custom implementation; but its not using MySQL at all), ProtocolLib, spark, PlaceholderAPI (+Bunch of other custom plugins. but they do not use SQL or interact with BentoBox in any way).
Other
Appending the autoReconnect flag to the mysql connection url usually fixes this from my experience.
https://github.com/BentoBoxWorld/BentoBox/blob/ac8458fd3c67de4c155af404d03cb79a21af9593/src/main/java/world/bentobox/bentobox/database/sql/mariadb/MariaDBDatabaseConnector.java#L18
It looks like the autoReconnect flag is already present at this time ( and has been for a while ). But still, the connection breaks and bentobox is not able to reconnect.
2022-03-04 12:55:23 17 [Warning] Aborted connection 17 to db: 'bentobox' user: '' host: '.**.0.1' (Got an error reading communication packets)
Looks like something is a bit broken with the connection. Doesnt happen for all the other plugins using SQL
Yeah, I'm sorry but this is way out of my technical expertise. As you wrote, the connection is in theory set to autoReconnect. I did some googling on that error and it appears to be one of those hard to diagnose ones. I'm not sure if you ever use the BentoBox reload, or server reload function, but maybe that is causing the database connection not to be closed properly. I do know that other servers run fine for days or weeks without issue, so I'm not sure why you are seeing this. I could do with some help from someone who knows more than I do about this!
Thanks for the quick response. Feel free to assign this issue to me if other people are experiencing same errors. I'll gladly investigate :)
Yeah, I'm sorry but this is way out of my technical expertise. As you wrote, the connection is in theory set to autoReconnect. I did some googling on that error and it appears to be one of those hard to diagnose ones. I'm not sure if you ever use the BentoBox reload, or server reload function, but maybe that is causing the database connection not to be closed properly. I do know that other servers run fine for days or weeks without issue, so I'm not sure why you are seeing this. I could do with some help from someone who knows more than I do about this!
Even with AutoReconnect, disconnection issues can still arise. There's a testConnection method with SQL, you can use it async on a scheduler and reconnect when an exception is thrown.
