Error spam "[BentoBox] Could not check if key exists in database!" after MariaDB/MySQL version update

MorkaZ commented 4 years ago

Description

Describe the bug

I have updated my mysql servers and after that, bentobox spams console with this error:

Steps to reproduce the behavior

Use latested MariaDB or MySQL server. Tested on both - same result. Only Bentobox has problems, any other plugin works fine.

Expected behavior

Screenshots and videos (Optional)

Environment

BentoBox Version (Mandatory)

1.11.0

Plugins (Optional)

[08:38:31 INFO]: Plugins (48): ActionHealth*, antiRedstoneClock, AuctionHouse, BentoBox, BossShopPro, ChatInjector*, ClearLag, ColoredSigns*, CommandButtons, CraftBook, Essentials, EssentialsChat, EssentialsProtect, EssentialsSpawn, HeadDatabase*, HolographicDisplays, ItemShops*, LiteJoin*, MorkazSk, MoxChatTitles, MoxCore, MoxFables, MoxPerms*, MoxPlayerParticles, MoxPremiumShop, MoxTokensDatabase*, MoxTransmutators, Multiverse-Core*, NoCheatPlus, PlaceholderAPI, PlugMan, ProtocolLib, ProtocolSupport, SAML, SK-NBeeT, SkQuery, skRayFall*, Skript, spark, SQLibrary*, TAB, TuSKe*, Vault, VoidGenerator, WorldBorder, WorldBorderAPI*, WorldEdit, WorldGuard

Additional context (Optional)

tastybento commented 4 years ago

Can you do /bbox version and copy and paste the result?

Also, I assume everything was working fine until you upgraded the database version?

tastybento commented 4 years ago

Also, what specific version of MySQL (or MariaDB) did you upgrade to? 8.0.19?

MorkaZ commented 4 years ago

$ mysql -V
mysql  Ver 15.1 Distrib 10.4.12-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Do not remember exactly what exactly mysql version I had - I chosed 8.0 when I was installing it. I am sure that version was newest because I did apt-get update & upgrade before. I have switched mysql server instantly to mariadb after I saw this error but it did not help. I am actually having database on my second server where I did not update mysql yet.

For now I just exported BentoBox database from not working mariadb server and imported it to old one on other server where mariadb has older version to make everything working correctly.

Here is bbox version output:


[17:44:20] [Server thread/INFO]: Aktualnie: PAPER 1.15.2.
[17:44:20] [Server thread/INFO]: Wersja Bentobox: 1.12.0-SNAPSHOT-b1682
[17:44:20] [Server thread/INFO]: Database: MARIADB
[17:44:20] [Server thread/INFO]: Załadowane światy gry
[17:44:20] [Server thread/INFO]: caveblock-world (MineBlock): Groty, Nether, Kres
[17:44:20] [Server thread/INFO]: Załadowane dodatki
[17:44:20] [Server thread/INFO]: CaveBlock 1.11.0
[17:44:20] [Server thread/INFO]: Challenges 0.8.0-1.6.0
[17:44:20] [Server thread/INFO]: Level 1.9.3
[17:44:20] [Server thread/INFO]: Warps 1.9.0```

tastybento commented 4 years ago

Can you give me a copy of the old database (zipped up and drop here or somewhere)? I can then test. I just did a new install with 8.0 and it all works okay, so I think it's something to do with the upgrading process that the database does.

MorkaZ commented 4 years ago

Here is old database before update Bentobox_Caveblock.zip

tastybento commented 4 years ago

I managed to trigger a similar error when testing with your database. I'll find out why.

tastybento commented 4 years ago

I found the issue: your phpMyAdmin is an old version and does not know how to export JSON databases so the table creation SQL is wrong. For example, here is the table creation SQL from the file you gave me for the Island table:

CREATE TABLE `world.bentobox.bentobox.database.objects.Island` (
  `json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ;

You can see that it does not make the uniqueId column. That's why you are getting the error. There is no uniqueId to look up.

The correct SQLmust be like this:

CREATE TABLE `world.bentobox.bentobox.database.objects.Island` (
  `json` JSON, uniqueId VARCHAR(255) GENERATED ALWAYS AS (json->"$.uniqueId"), UNIQUE INDEX i (uniqueId)
);

The version of phpMyAdmin you are using is 4.6.6deb64. JSON support was added in 4.7.0, so you need to upgrade. Latest version is 4.9.5.. Having said that, I do not know for certain if it supports full exporting of JSON enabled databases because there is a still open bug on it here.

If phpMyAdmin doesn't work, then use the mysqldump command to export the database:

mysqldump -u [username] -p [database-you-want-to-dump] > [path-to-place-data-dump]

for example:

mysqldump -u tastybento -p bentobox > dump.sql

When I did this, the CREATE TABLE sql was correct, for example:

DROP TABLE IF EXISTS `world.bentobox.bentobox.database.objects.Island`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `world.bentobox.bentobox.database.objects.Island` (
  `json` json DEFAULT NULL,
  `uniqueId` varchar(255) GENERATED ALWAYS AS (json_extract(`json`,_utf8mb4'$.uniqueId')) VIRTUAL,
  UNIQUE KEY `i` (`uniqueId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

If you have a running version of the database then I recommend you use the mysqldump command to copy it to the newer database. Just in case, I cleaned up your file and here's the fixed version: Bentobox_Caveblock.sql.zip However, I recommend using a proper dump from your working database.

I hope that fixes the problem for you.

MorkaZ commented 4 years ago

Thanks a lot tastybento. You have explained it very well. People like me will now have answer in the future.

Edit: Thanks a lot. It works! I was thinking that phpmyadmin uses only mysql commands & features to everything including dumping databases. I am sorry I did not give information that I was dumping databases and I was using phpmyadmin. I am hoping this world will have more developers like you :)

BentoBoxWorld / BentoBox