[Bug Report]: Unknown collation: 'utf8mb4_0900_ai_ci'

[ChristopherPS96]

LibertyBans Version

1.1.0_RC2

I have confirmed that ...

• [X] LibertyBans is up to date
• [X] No similar issue has been reported

Platform

Spigot/Paper

Description

[14:38:33 INFO]: [com.zaxxer.hikari.pool.HikariPool] LibertyBansPool-MySQL - Added connection org.mariadb.jdbc.Connection@5b729414
[14:38:34 WARN]: java.sql.SQLException: Unknown collation: 'utf8mb4_0900_ai_ci'
[14:38:34 WARN]: at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)

This warning do I get on my g-portal minecraft server (Paper 1.20.1). It happens on startup and prevents the creation of a table in my database.

[A248]

This would suggest your database doesn't support utf8mb4, which is strange. Can you tell me the MySQL version you're using? I need more information to figure this out.

[ChristopherPS96]

I'm not quite sure. A g-portal server gives me access to phpmyadmin , where I can look at the databases (with limited rights tho).

Maybe the information on the right side helps you there.

Bukkit Plugins: AutoWhitelistRemove, BagOfGold, CoreProtect, CustomItemsLib, DecentHolograms, Domain, Essentials, EssentialsChat, EssentialsSpawn, GSit, HoloMobHealth, ImageFrame, InteractionVisualizer, InteractiveChat, InvSeePlusPlus, InvSeePlusPlus_Give, LibertyBans, LuckPerms, Maintenance, MobHunting, Multiverse-Core, PlaceholderAPI, ProtocolLib, Shopkeepers, spark, TAB, Terra, ValhallaKits, ValhallaMMO, ValhallaRaces, ValhallaTrinkets, vane-admin, vane-bedtime, vane-core, vane-enchantments, vane-portals, vane-trifles, Vault, VoidGen, WitherSound

Just wanted to report it, since apart from Domain and LibertyBans, every other plugin was able to create a table. But maybe g-portal doesn't update the database version, then of course I'm sorry for wasting your time.

[ChristopherPS96]

https://dba.stackexchange.com/questions/248904/mysql-to-mariadb-unknown-collation-utf8mb4-0900-ai-ci

Seems like this is an issue with MariaDB not supporting 9.0.0 Unicode

[A248]

What is strange about this bug is that it probably shouldn't be happening, despite your MariaDB version. We support MariaDB 10.4 (although at least 10.6 is recommended), and we have an automated testing framework for LibertyBans which tests every change against multiple database types. According to all of the tests, LibertyBans works on MariaDB 10.3, 10.6, and 10.10.

Also, there are no changes documented on the MariaDB release notes which suggest CHARACTER SET utf8mb4 COLLATION utf8mb4_bin (the character set and collation we use) were ever invalidated. Moreover, no one else using MariaDB has ever reported this issue. Besides, we're not even using the utf8mb4_0900_ai_ci mentioned in the error log.

I strongly suspect this is somehow related to your database or system configuration. We're not doing anything out of the ordinary -- just setting the character set and collation for good practice. Many plugins I've observed don't set the character set, which can lead to silent inconsistent behavior across deployments of the same plugin. I will keep working to try and see if I can reproduce this bug, or if there is a solution we're both overlooking. Until then, I suggest using the local database (HSQLDB), and you can switch to MariaDB when you get it working later.

[ChristopherPS96]

Thanks for the answer.

Yeah, It's really strange, and I too suspect it's something that is specific to my database. Because I have a second minecraft server, that is also on g-portal, that works perfectly fine with libertybans.

For me personally, this isn't anything drastic, and I can perfectly fine use a local database. But I just wanted to bring attention to it.

What is strange. The only plugin, that had I the same issue with, was Domain. They have given me a new jar file, where they have removed all collates out of their "create table" queries. And now it works fine also. Not saying, that what you should be doing, just wanna share the information I have.

[A248]

I'm going to close this issue since LibertyBans works fine on your other g-portal database, and I strongly suspect a database misconfiguration. Maybe resetting or redownloading your database's character sets may help. Not sure.