Managing MySQL Database on a Multiplayer System

Hi, I was working with a third party blueprint plugin to enable connection to a MySQL database on UE 4.27 to connect and run database queries.
(Here is a link in case anyone is wondering: GitHub - darkgoogle/MysqlDBUnrealengine4.26 )
I have a multiplayer environment with a dedicated server. I want to make it so that the server is the only one to connect to the database and the client is able to send query requests to the server whenever needed.
However, I am unable to think about a way of doing so over blueprints. I had used the GameMode earlier (since it only resides on the server) to have the Database Connection object but realized later that clients would not be able to cast to it and call query functions.

I need help on it as soon as possible.

Just route your queries/responses via PlayerController or PlayerState, with server RPC and owning client RPC.

It is very unsafe to receive the SQL query directly from client though. Instead of sending the query itself you probably rather want to send the appropriate values via the RPC, and build the query on server side with proper SQL escaping (which your plugin/api should provide).

1 Like

Thanks for the response! I have a doubt though:
If I route through PlayerController or PlayerState, doesn’t that mean that the program will have to connect to the database (though executed server side) multiple times (once per client)?

The player controller can in turn call some object that is a singleton on the server. That could be a global connection pool, or something you put on the GameState object, or some global variable you expose through a blueprint library.

Alright, so could I just have the connection done through the GameState and also house the query functions there?
That would mean that the PlayerController would have to cast to the GameState (Server) to run a query, which is exactly what I want.
Thank you!

The PlayerState on the server can get ahold of the game state, but the client does not have a copy of the GameState. Thus, to send a message from a client, the PlayerController needs to run a server-RPC, and when receiving it on the server, call through to the GameState.

Make sure you don’t block the game within those calls – you have to structure your API as “make this request, and when done, call this event back,” or perhaps “start this request,” and then every tick, ask the database API whether it’s done or not.

Doing blocking I/O in the game thread is a possible cause of laggy network games, de-syncs, or even random disconnects if it gets bad enough.

Alright. I got it.
For preventing blocking of I/O, I have been using an Async Function Caller which runs the blueprints in another thread.
Thank you!