Using MySQL without callbacks

Update: please read the posts below this one to note potential dangers of using MySQL in a blocking manner.




If you're like me I found myself frustrated with the existing MySQL plugin examples code that used callbacks for queries, as this made my code flow messed up. So I got to poking around and it appears the Oxide MySQL extension taps into the mysql connector found at MySQL :: Download Connector/Net which means we can use it directly, and without downloading any extra files! it's located at RustDedicated_Data/Managed/MySql.Data.dll

Here's a simply query example that I put together from looking at stackoverflow that doesn't use callbacks

First put this at the top

Code:

using MySql.Data;
using MySql.Data.MySqlClient;

And inside your plugin somewhere, here's some test code, you can issue the console command sql via rusty. It
uses host = 127.0.01, database = rust, user = root, pass = abc123 and table = farmstats

Code:

[ConsoleCommand("sql")]
        void MySQLTest()
        {
            var DB = new MySqlConnection("Server=127.0.0.1; database=rust; UID=root; password=abc123");
            DB.Open();
       
            var cmd = new MySqlCommand("SELECT * FROM farmstats", DB);
       
            var reader = cmd.ExecuteReader();
            while(reader.Read())
            {
               /* this reads from the wood column, but could be anything */
                string woodcolumn = reader.GetString(reader.GetOrdinal("wood"));
               string stonecolumn = reader.GetString(reader.GetOrdinal("stone"));
                Puts(woodcolumn);
            }
              DB.Close();
        }

enjoy! Also this now means you can use prepared statements for the extra speed and security, just google some examples!

 

Please add that if you use this, you have to take extra measures in your plugin to protect against DDoS attacks and that it may have a huge negative impact on server performance (especially with MySQL!).
Not using the Oxide API opens up a bunch of security problems, allowing players to DDoS your server by abusing the fact that the entire server (not just the plugin!) halts until the query is finished, allowing them to cause massive rubberbanding (for instance by spamming commands that send queries or by flooding your likely weaker MySQL db server).
I just want to make sure that plugin devs know this, otherwise they'll always use non concurrent queries instead of concurrent querries for ease of use (an illusory one, I might add, because instead of worrying about simple callbacks you now have to worry about protecting against DDoS attacks, which is a bunch more complicated) and create plugins that allow attackers and angry players to easily make servers unplayable.

 

I'm going to put to the test your "the entire server (not just the plugin!) halts until the query is finished" statement as that doesn't sound like it would be accurate.

 

Just look at the Oxide source code and the source of the Rust server (testing instead of reading the source is inappropriate in this case).
The Rust server runs on the main thread. The Rust server calls plugins and blocks until plugin calls are done and uses their return value (if it didn't block, it wouldn't be able to use the return value, also this makes a lot of things easier). This means that hook calls also execute on the main thread. If the hook calls are also executed on the main thread, then that means that your blocking query blocks the main thread.
Plugins cannot run concurrently with the main thread, because plugins and the Rust server concurrently read and modify game state, meaning race conditions would otherwise occur.

 

The old oxide mysql/sqlite libs worked without the callbacks actually. The reason it changes is because of the fact that everything is executed on the main thread. And because of that huge queries or queries to an external mysql server literally stall/freeze the server for multiple seconds to minutes depending on the queey/server. I actually made a plugin back in those days for someone that made it to use callbacks before the extension was changed.

 

Put simply, Oxide just injects Interface.CallHook calls into the DLL via IL patching.
Interface.CallHook calls Oxide/PluginManager.cs at eb1ba03bc0a76e08c638b93999c4e32ebbc7935c · OxideMod/Oxide · GitHub indirectly, which calls Oxide/Plugin.cs at develop · OxideMod/Oxide · GitHub and is implemented by Oxide/CSPlugin.cs at 1919a148d3f5374638c327d4de418f281a8bd3e4 · OxideMod/Oxide · GitHub. You can see that it is also run on the main thread, because the main thread executes Interface.CallHook.

 

Yeah I know Node.js being javascript is all about the callbacks, since it's mostly single threaded and just has a big loop internally to check the state on everything. In terms of databases if we're talking resource heavy queries sqlite is usually a poor choice in-general given its concurrency issues seeing as it's dealing with a simple file, and locks the db file on writes. Granted using WAL seems to help a lot. In any case I personally am going to use the blocking MySQL code I posted in sensible spots with sensible queries but i'll update my initial post indicating it's potentially dangerous to others. Another developer in a thread expressed his frustrations with the callback implementation so I kindly ask you consider allowing this thread to stay up, as this may benefit him and others. Thanks!

 

Using it in sensible spots is probably completly fine, yeah.

In my opinion, for MySQL, a sensible spot would be a spot not directly called by user input (Universal hooks, timers, that kind of stuff) where the SQL query isn't too large and the code is only run rarely (e.g.: every 30s, when the plugin initializes, stuff like that).

Also, in regards to SQLite, I think blocking queries can be used a lot more liberally with SQLite, because the latency to a local database is mostly better than to a remote database. That being said, one should probably still refrain from using them in spots that are called very frequently (e.g. OnTick) or in spots where queries are huge.