search

PyvesB / advanced-achievements

:fireworks: Popular plugin that adds unique and challenging achievements to Minecraft servers.
https://www.spigotmc.org/resources/83466
GNU General Public License v3.0
199 stars 215 forks source link

Database write error (achievements has 4 columns but 3 were supplied) #1043

Closed GenSecrets closed 3 years ago

GenSecrets commented 3 years ago

:information_source: Basic information

:question: Help request For some reason, we're getting a database write error suddenly after updating plugin versions. Happens for a lot of achievements.

For some reason, we're getting a database write error suddenly after updating plugin versions. Happens for a lot of achievements. Here's an example of the error below; how do we fix this? We have a very large server and achievements are a huge part for player retention. Been about 3 weeks now since it started, restarts are automated nightly. image

:wrench: Configuration n/a, ask if needed for specific parts, our customs section is massive

PyvesB commented 3 years ago

Hello @GenSecrets ! 👋🏻

Which version of the plugin did you update from? The achievements table should have 3 columns nowadays, the upgrade should have automatically removed one of them, but that doesn't seem to be the case for you. Did you see any Removing descriptions from database storage, please wait... message in the logs when you first launched the 7.1.0 version, or on any subsequent restarts of the server?

GenSecrets commented 3 years ago

Heya! I do not know which version we came from sadly, however I was able to look through some of our log archives and found out that it appears when we updated, our server crashed after the first time it ever said the Removing descriptions from database storage, please wait... message. After the server restarted, the plugin attempted again and appears that this error was thrown: image

Looks like the tempTable was created when it first attempted to fix the database, then it failed, then when the server restarted it failed to convert due to tempTable already existing.

I did look and seems like the version that we had bumped to at that time was 7.0.5 (we were on an earlier version obviously). Since then, we have upgraded to latest version mentioned above, however after looking through logs it appears that the Removing descriptions from database storage, please wait... message has not happened since we moved from 7.0.5 to current. It only happened while the server was still on 7.0.5 only.

PyvesB commented 3 years ago

our server crashed after the first time it ever said the Removing descriptions from database storage, please wait... message.

Do you have information on that very first crash? Was it related to Advanced Achievements at all?

It only happened while the server was still on 7.0.5 only.

If you switch back to 7.0.5 temporarily, does it happen again?

GenSecrets commented 3 years ago

I can get the info on the crash shortly^^

For sake of swapping back to 7.0.5, I just did and this is the immediate result however it doesn't appear the database has changed any size at all:


[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Remaining requests sent to the database, plugin successfully disabled.
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Backing up and loading configuration files...
[13:23:27] [Server thread/WARN]: [AdvancedAchievements] Overriding configuration: disabling PetMasterGive and PetMasterReceive categories.
[13:23:27] [Server thread/WARN]: [AdvancedAchievements] Ensure you have placed Pet Master in your plugins folder or add PetMasterGive and PetMasterReceive to the DisabledCategories list in config.yml.
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] 5 disabled categories: [AdvancementsCompleted, PetMasterGive, JobsReborn, DistanceSneaking, PetMasterReceive]
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Registering event listeners...
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Initialising sqlite database...
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Removing descriptions from database storage, please wait...
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Setting up command executor and custom tab completers...
[13:23:27] [Server thread/INFO]: [AdvancedAchievements] Registering permissions...
[13:23:27] [Thread-7069/INFO]: [AdvancedAchievements] Checking for plugin update...
[13:23:28] [Server thread/INFO]: [PlaceholderAPI] Successfully registered expansion: aach
[13:23:33] [Server thread/INFO]: GenSecrets issued server command: /aach
[13:23:35] [Server thread/INFO]: GenSecrets issued server command: /aach list```
PyvesB commented 3 years ago

By the way, whilst you're gathering extra info from the crash, could you please also share the database file so that I can take a look?

GenSecrets commented 3 years ago

Looked through our logs archives and found the following:

[07:57:37] [Server thread/INFO]: [AuctionHouse] Loaded 5313 listings
[07:57:37] [Server thread/INFO]: [AdvancedModreq] Enabling AdvancedModreq v6.0.2
[07:57:37] [Server thread/INFO]: [PlaceholderAPI] Successfully registered expansion: advancedmodreq
[07:57:37] [Server thread/WARN]: [AdvancedModreq] Discord setting is enabled, but the dependency `DiscordSRV` was not found! Disabled Discord setting!
[07:57:37] [Server thread/INFO]: [BungeeTabListPlus] Enabling BungeeTabListPlus v3.4.3
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] Enabling AdvancedAchievements v7.0.5
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] Backing up and loading configuration files...
[07:57:37] [Server thread/WARN]: [AdvancedAchievements] Overriding configuration: disabling PetMasterGive and PetMasterReceive categories.
[07:57:37] [Server thread/WARN]: [AdvancedAchievements] Ensure you have placed Pet Master in your plugins folder or add PetMasterGive and PetMasterReceive to the DisabledCategories list in config.yml.
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] Loaded 186 achievements in 48 categories.
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] 5 disabled categories: [AdvancementsCompleted, PetMasterGive, PetMasterReceive, DistanceSneaking, JobsReborn]
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] Registering event listeners...
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] Initialising sqlite database...
[07:57:37] [Server thread/INFO]: [AdvancedAchievements] Removing descriptions from database storage, please wait...
[07:57:46] [Thread-27/INFO]: Could not connect to the server.
[07:57:57] [Thread-27/INFO]: Could not connect to the server.
[07:58:08] [Thread-27/INFO]: Could not connect to the server.
[07:58:19] [Thread-27/INFO]: Could not connect to the server.
[07:58:30] [Thread-27/INFO]: Could not connect to the server.
[07:58:41] [Thread-27/INFO]: Could not connect to the server.
[07:58:52] [Thread-27/INFO]: Could not connect to the server.
[07:59:03] [Thread-27/INFO]: Could not connect to the server.
[07:59:14] [Thread-27/INFO]: Could not connect to the server.
[07:59:25] [Thread-27/INFO]: Could not connect to the server.
[07:59:36] [Thread-27/INFO]: Could not connect to the server.
[07:59:47] [Thread-27/INFO]: Could not connect to the server.
[07:59:58] [Thread-27/INFO]: Could not connect to the server.
[08:00:09] [Thread-27/INFO]: Could not connect to the server.
[08:00:20] [Thread-27/INFO]: Could not connect to the server.
[08:00:31] [Thread-27/INFO]: Could not connect to the server.
[08:00:42] [Thread-27/INFO]: Could not connect to the server.
[08:00:53] [Thread-27/INFO]: Could not connect to the server.
[08:01:04] [Thread-27/INFO]: Could not connect to the server.
[08:01:15] [Thread-27/INFO]: Could not connect to the server.
[08:01:26] [Thread-27/INFO]: Could not connect to the server.
[08:01:37] [Thread-27/INFO]: Could not connect to the server.
[08:01:48] [Thread-27/INFO]: Could not connect to the server.
[08:01:59] [Thread-27/INFO]: Could not connect to the server.
[08:02:10] [Thread-27/INFO]: Could not connect to the server.
[08:02:21] [Thread-27/INFO]: Could not connect to the server.
[08:02:32] [Thread-27/INFO]: Could not connect to the server.
[08:02:43] [Thread-27/INFO]: Could not connect to the server.
[08:02:54] [Thread-27/INFO]: Could not connect to the server.
[08:03:05] [Thread-27/INFO]: Could not connect to the server.
[08:03:16] [Thread-27/INFO]: Could not connect to the server.
[08:03:27] [Thread-27/INFO]: Could not connect to the server.
[08:03:38] [Thread-27/INFO]: Could not connect to the server.
[08:03:49] [Thread-27/INFO]: Could not connect to the server.
[08:04:00] [Thread-27/INFO]: Could not connect to the server.
[08:04:11] [Thread-27/INFO]: Could not connect to the server.
[08:04:22] [Thread-27/INFO]: Could not connect to the server.
[08:04:33] [Thread-27/INFO]: Could not connect to the server.
[08:04:33] [Thread-2/INFO]: Stopping main thread (Ignore any thread death message you see! - DO NOT REPORT THREAD DEATH TO PAPER)
[08:04:33] [Server thread/ERROR]: Main thread terminated by WatchDog due to hard crash
java.lang.ThreadDeath: null
    at java.lang.Thread.stop(Thread.java:850) [?:1.8.0_121]
    at net.minecraft.server.v1_16_R3.MinecraftServer.stop(MinecraftServer.java:754) ~[patched_1.16.5.jar:git-Paper-457]
    at net.minecraft.server.v1_16_R3.DedicatedServer.stop(DedicatedServer.java:704) ~[patched_1.16.5.jar:git-Paper-457]
    at net.minecraft.server.v1_16_R3.MinecraftServer.close(MinecraftServer.java:728) ~[patched_1.16.5.jar:git-Paper-457]
    at org.bukkit.craftbukkit.v1_16_R3.util.ServerShutdownThread.run(ServerShutdownThread.java:28) ~[patched_1.16.5.jar:git-Paper-457]
[08:04:33] [Thread-2/INFO]: Stopping server
[08:04:33] [Thread-2/INFO]: [EpicCraftingsPlus] Has been disabled! Version: 6.4.4
[08:04:33] [Thread-2/INFO]: [com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Shutdown initiated...
[08:04:33] [Thread-2/INFO]: [com.zaxxer.hikari.HikariDataSource] HikariPool-1 - Shutdown completed.
[08:04:34] [Jobs-DatabaseSaveTask/INFO]: [Jobs] Database save task shutdown!
[08:04:34] [Jobs-BufferedPaymentThread/INFO]: [Jobs] Buffered payment thread shutdown.
[08:04:34] [Thread-2/INFO]: [Jobs] Plugin has been disabled successfully.
[08:04:34] [Thread-2/INFO]: [CMI] Closed db connections
[08:04:34] [Thread-2/INFO]: ==============================================================
[08:04:34] [Thread-2/INFO]: [Towny] Finishing File IO Tasks...
[08:04:34] [Thread-2/INFO]: [Towny] Version: 0.96.5.6 - Plugin Disabled
[08:04:34] [Thread-2/INFO]: =============================================================
[08:04:34] [Thread-2/INFO]: [Bank] Bank Disabled
[08:04:34] [Bank - Loader Thread/INFO]: [Bank] Loader thread stopped
[08:04:34] [Thread-2/INFO]: ajParkour v2.11.1 by ajgeiss0702 has been disabled!
GenSecrets commented 3 years ago

Here is the database file, I have zipped it since .db isn't a supported upload type by GitHub.

achievements.zip

PyvesB commented 3 years ago

Here's your fixed database, let me know if things work as expected again: achievements.zip

I'm also working on a code improvement to reduce the risk of your problem happening with other users in the future.

GenSecrets commented 3 years ago

Alright I will upload and test the database ^^ thank you for the help. Our server network has 2 more servers which utilize AdvancedAchievements as well (each server is an independent copy of the same survival based server, different communities of players).

Can you look at the databases here as well and see if they are correct? I do believe I remember seeing a report from one of the other servers as well, however I'm not certain on this one. I've not seen the error message in the past few days of logs for either of these servers so I'm not sure if it's just luck, no one hitting achievements, or if the other 2 servers converted fine. If you are able to that would help tremendously, if not what would i need to do in order to manipulate the database?

achievements-Gold.zip achievements-Diamond.zip

GenSecrets commented 3 years ago

After a quick test it appears on first glance that the database fix worked perfect for that one ^^

PyvesB commented 3 years ago

Gold seemed incorrect as well, here it is: achievements-Gold.zip

I use this tool to manipulate databases: https://github.com/dbeaver/dbeaver

It's really handy and user friendly, I would highly recommend it. :)

PyvesB commented 3 years ago

Worth noting that I'll be away for the next 4 weeks, I'll not be able to provide any further support during that period. Do feel free to close the issue if you've not got any further database queries!

GenSecrets commented 3 years ago

Thanks for the info! Can do can do then, were you able to check the Diamond one as well to see if it was? However this looks good, will be uploading it as well. Thanks a bunch! If I don't hear back from you in a day or so I'll close the issue out since you're gonna be away. Thanks again^^

GenSecrets commented 3 years ago

appears we should be good, thanks a lot for the assistance :)