Ghmattimysql: My MySQL Implementation for FiveM [1.3.2]

ghmattimysql

A mysql middleware for fivem

A brief overview, why and how?

First there was mysql-async (which I am maintaining now too), written in C#. Due to the current mono-limitations several issues arose:

  • Everything was executed on the main server threadpool, meaning that the server might start to hitch on longrunning queries that were not async.
  • Async continuitation after awaiting was only possible at the next server tick, leading to slow reads.
  • Which became even slower because the buffer used was affected by the underlying mono issue.

This lead to me creating GHMattiMySQL in C#, which executed queries in synchronous mode on different threads from the server thread pool, which in turn to tackle these issues as properly as possible. And it worked.

But then node.js picked up on FiveM, and the mysql protocol middleware for node, was suffering none of these issues. So I decided to move both projects: mysql-async and ghmattimysql to node.js.

If you are looking for the old C# version to see how it tackled the problem then look below (or if you are using vRP):

C# Version

https://github.com/GHMatti/FiveM-MySQL/releases

Why are still two implementations needed? Well since mysql-async targets more towards the a large amount of people using it and needs to be fully backwards compatible, this cripples the syntax of the mysql.js implementation and many of its functions and is solely thought for the usage in lua.

ghmattimysql instead still aims to be mostly backwards compatible, but will include some breaking changes every now and then to aim for the easier use, better performance.

Download

To download the latest version, follow the link to the releases of the github repository.

Documentation

To read up on how to install, configure and use this resource follow this link to the Documentation.

The node.js version breaks with the naming of exports due to the preferral of using the node.js mysql names

26 Likes

Nice! :smile:

True story. He almost beat me.

1 Like

Nice work I’ll be testing this out on my server soon.

1 Like

Yea!!!
20 characters

1 Like

Nice!

1 Like

Updated

Changes:

  • Added parameter support, which is backwards compatible, so you do not need to change your lua function calls.
  • Parameters are tested if they are in the right shape. e.g. in Lua: {["@id"] = 1}, for the query: SELECT username FROM users WHERE id = @id;
  • Added Async exports for Lua that do not wait for the C# response to finish, thus speeding up INSERT, DELETE and UPDATE calls considerably.
1 Like

A quick question, do functions have same parameter structure compared to MySQL-Async?

( Asking because I’ll do a quick search-change all for my resources in notepad++ if this is the case )

Yes. You can pretty much just replace the functions.

Should i disable mysql-async or should i have them both running?

Your choice really.

I had some crashes when running two instances of mine, but if I reflect, it was probably because I deleted the table where the other was inserting.

So it should be fine.

Hm okay, when i have changed settings.xml and disable mysql-async it wont even read from DB.
Have i missed something?

No it should work.

It probably does not even connect, so check if all lua files sending commands to the database have:
dependency 'GHMattiMySQL' [if that is the proper resourcename in your resource folder] in their respective __resource.lua files. Also just to be obvious again, you should have the resource in your resources folder and start the resource in your server.cfg

It does not really display the errors clearly yet, but you should have one, right? If it is just a connection issue, then the settings.xml might be wrongly set.

There is an example resource doing the work in the github repository:
https://github.com/GHMatti/FiveM-MySQL/tree/master/MySQLTest/resource

Ohh okay, so just to make sure im thinking right, example i use ESX framework, i need to add “dependency ‘GHMattiMySQL’” to every __resource.lua? policejob, ambulance and so on?

every single one that sends commands yes.

1 Like

Updated

Changes:

  • Removed the issuing of warnings for bad / empty parameters for async interactions if Debug is set to false
  • Made the callback on async interactions optional. If you do not want to know what it does, you don’t.
  • Added a Multi-Row Insert Wrapper. Which should in theory also work for single row inserts.
  • Does not die anymore on faulty query / command syntax, just issues an error.

Uhm Feature Requests?

ToDo is now down to: Multithreading, Possibly waking up the server thread for the execution of returning the value now.

I’m so gonna use this in a script cuz c# rocks for coding

Updated

Changes:

  • Added Multithreading. This speeds up using many database querys quite a bit.
  • Fixed the missing handling of Parameters by QueryScalar[Async].
  • Added support for using Convars instead of definiting the server connection by the settings.xml
  • Added an optional Callback to :Insert

Installed this on my servers to compare performance with mysql-async and i’ll probably keep your resource, it works great! nice job.

3 Likes

Updated

Changes:

  • Insert now has another optional parameter, to indicate if it should return the last inserted id by the statement. The id needs to be an auto incrementing value.
  • Added an optional console variable / convar mysql_thread_limit, which can be set in the server.cfg if you want to limit or control multithreading better.
  • Fixed an bug where QueryScalar[Async] would crash the resource when it returned null, as the server tries to unwrap nothing. Do not use dynamic and send null with it folks.
  • Added Transactions.
  • Changed so Debug mode would show querys stringified. Not correctly, but probably enough to debug your queries.
  • Jumped the Version, because I am almost out of ideas to add.
2 Likes

Then how do you use it? You only show the exports?

2 Likes