Best ways to network and save data with MySQL?

Hello,

I am working on some RP content and I am thinking about the way I network and save the data and I see some exploits, so I am asking for help.
I thinked about two ways, the first one that I abandonned was to save the data on the MySQL database and then when the query is completed it network the new value (SetNW).
BUT: if the query takes too many time like seconds, it can be exploited since my Get function will work thanks to GetNW.

So I was thinking of an other way: it network the value before saving it, so it can’t be exploited.

But the thing is, for exemple if the MySQL server is down, it would be a bit buggy since the query would not run but it would network the value.

So i’m not really sure of both methods. If someone has better idea, feel free to tell me. Thank you.

Try to think of the database as a separate thing than the network.

Data gets sent to the server by a client, then when it is time to save that data somewhere, escape it properly and use SQL to save it at that moment.

Most of the time you don’t want to be saving and loading things from the database. It’s just a long-term-memory for anything you want to keep around between server restarts, accessible from different places (such as the web, or other game servers). Don’t save anything to the database until you need to. If a player changes a setting, use the net library to send the new setting over to the server. Check that the player who is sending the message is allowed to, then store their change in the database.

For everything else, you can store it in normal Lua tables.

Sorry if that sounds a bit simplistic or doesn’t answer the question - I just want to make sure you understand why to use the relational database in the first place, first.

Yes I totally understand what is a database, but I presume that the queries can take some seconds if there is problems, so I try to add more security.

Since I need to save, and network the money (for exemple) I use SetNW and GetNW to get the values on the server side, but I can’t directly get it from the database because it’s just stupid to make more queries than necessary. So I get the value a first time, OnPlayerAuthed and I restore it thanks to SetNWInt and the Database.

But the thing is, I would like “add” a security is the SQL server is down. Since I seperated the two functions (SetNW and the query) even if the SQL server is down, it will give them money from the salary (for exemple) and they will be able to use it, even if they can do much with it since it’s not really saved. But the biggest problem I think is if the SQL server crash not before the server has started but during, it would be great to notice players, and/or stop them from receiving money. So I presume that I have to put some security in my SetMoney function, but I don’t want to call :status() on my database each calls.

You can leverage PData as temporary and persistent data storage for players if your database goes down.

Player:SetPData

Player:GetPData

Player:RemovePData

If the database is down, use PData. When you’re connected to the database again, update players’ info with their PData and remove the PData.

You should assume the SQL server is always working and base it on that. SQL servers doesn’t just crash or stop working, they’re predictable and reliable.

This is correct. In theory, any part of the system could have a problem and you can’t check for everything. I would suggest that the gmod server is more likely to go down before the DB is.

You should have some sort of redundancy such as queuing queries when the server dies because although it shouldn’t it can happen.

Of course - however, should the server still be playable with no database behind it?

Let’s say a network message fails to write to the database and the client is notified that it didn’t work. Let’s say that this was a player to player transaction. Since the database wasn’t updated, the money isn’t transferred. People will notice this and fix the DB, then bring the server back up.

If this is about remembering where props are, you could maybe update the positions of the props in the DB every 500ms per group of 30 props (for example).

That way, things won’t have moved far when the server comes back up.

Never assume the SQL server has 100% uptime.
[sp]You can probably assume 99% uptime, but do you ever want to NOT KNOW?[/sp]

I’ve done, and have helped people with, SQL setups in GMod with local or remote SQL servers and things go wrong. Servers DO in fact go down in one way or another whether that is a table lock running rampant or (god forbid) the SQL server is not on the same box as the game server and there is network loss.

Always have some kind of fallback (at the very least storing failed queries in a queue until reconnected). If you are worried about a reconnect never happening before the game server goes down for any reason store your data to a file and when you need it back reconcile the file data with the SQL server using timestamps. I usually do not bother going that far.

Yeah of course it’s not always up but it will most likely be. The only thing you really have to code to get around this is some small reconnection code, not base your entire gamemode off the idea that it might not be up. The worst thing that can happen is that player X didn’t lose $50 for buying that AK during the small window that the server is down.

…and the most likely reason it’s down is because you used a flaky cloud provider to host it.

Honestly, for most uses, if you have a single server and don’t want real-time-stats displayed somewhere else such as on your forum - SQLite is fine - and then you don’t have to worry about it going down at all.