Store user cache in mysql

[MarkusTieger]

Surely it would be better to store the user cache in mysql. But it should be implemented in such a way that it works with the MySQL Connection and it is not simply loaded into RAM, because it should also work for multiple servers. So a shared usercache.

[YouHaveTrouble]

What happens when mysql dies or takes long to respond? There are always tradeoffs for any solution. Sure, using a file loaded in memory only works for a single server, but it's also really safe since it can't really fail in any way. Single server is also by far most common setup and mysql solution doesn't really benefit that and even might be a detriment.

[YouHaveTrouble]

Don't get me wrong, it's an interesting idea, but it's not realistic to implement when all things it affects are considered.

[MarkusTieger]

First of all MySQL Querys are not taking long. If the connections aborts, it will try to reconnect and this will take long.

But i see the problem. Maybe we could replace the save and load functionality with mysql and store it to ram anyway. The cache should only add players and not remove. With a Primary Key we can ensure, that there are not duplicates.

But i am unsure if the function has sense, when it is stored into memory anyway.

[YouHaveTrouble]

First of all MySQL Connections are not taking long.

They are taking long for the time sensitive things like main thread time. There are 50 milliseconds in the tick, literally any remote mysql connection will take at least 5-10ms. Even assuming best case scenario it's too significant.

[MarkusTieger]

And just loading into memory and updating every 5-10 minutes (configurable) for example?

[YouHaveTrouble]

And just loading into memory and updating every 5-10 minutes (configurable) for example?

Would make Bukkit.getOfflinePlayerIfCached() return false values for players that logged in to the server, which is a major api breakage

[MarkusTieger]

I mean, when someone joins which is not in the cache. Then it will be added in the memory and then there will be an async mysql request to add it. The Primary Key will stop that there will be duplicates. It shouldn't break anything.

[YouHaveTrouble]

Even when you make an async request to fill the value, the api call that expects the result instantly will return wrong value. I really considered this feature, really. It seems cool, but it's not implementable without major api breaks

[MarkusTieger]

I don't unterstand how can it return a wrong value.

on startup -> it loads from the database.
-> it has all entries

player join -> (it inserts the entry in the database and directly in the ram too)
-> it has all player cache entries from this server instance, duplicates does not exist because of the primary key in the database

async scheduler -> safely updates the cache entry in an configurable interval with the mysql database.
-> for player entry updates of the cache from other server instances

Bukkit.getOfflinePlayerIfCached() -> value from ram which has all entries from this server instance.

[theomega24]

I think that there are plenty of possible issues with something like this that really aren't up for debate. This is also far, far, out of scope of Purpur's job. In fact, it sounds exactly like what some plugins do for syncing player data between BungeeCord/Velocity instances. The idea of including a MySQL connection built into the API is nice in a perfect world, but unrealistic at best and feels like something that would come out of the 1.6 era of Minecraft.