[Release] MySQL Async Library - 3.3.2

MySQL Async Library

You can find it here with all the instructions : https://github.com/brouznouf/fivem-mysql-async

This lib does not replace EssentialMode, it offers however async methods and compatible API for people only needing Essential Mode for MySQL

The current API should be stable, but it may change before a first stable release.

Feel free to raise Issues/Pull request on Github

Current features

  • Async method for mysql by using a callback
  • Sync method also available but not recommended

Download

https://github.com/brouznouf/fivem-mysql-async/archive/3.3.1.zip

If for some reason you still want the C# Version it can be downloaded from https://github.com/brouznouf/fivem-mysql-async/tree/2.0

Changelog

3.3.2

  • Removes whitespaces from a legacy connection string.
  • Migrated client-side from JS -> LUA for reduced overhead.
  • Added a new Server Status display in the UI that gives you hints on optimizing your mysql server (issues should be detected properly, advice might not to be reworked in wording and expanded upon in scope).

3.3.1

  • Added mysql_log_level that lets you control on what is output in console.
  • Added mysql_log_file_format to specify the output log file.
  • Reworked the UI. UI Bundle-size lost ~550kB in size.

3.3.0

  • Uses ghmattimysql now completely, shares almost the complete list of features.
  • Enabled js-syntax, which allows nifty shortcuts:
MySQL.Async.fetchAll('SELECT * FROM ?? WHERE ?', {'users', {['id'] = id}})
-- Renders as: SELECT * FROM `users` WHERE `id` = @id; with parameter id being id
  • The js-syntax also allows for bulk inserts.
  • Switched to typescript.
  • Blob field issue has been fixed and now returns arrays.
  • Store feature has been added, it is useful for replacing reptitive queries, as the entire query string does not need to be resend to mysql-async
local queryId = MySQL.Sync.store('SELECT * FROM ?? WHERE ?')
--...
MySQL.Async.fetchAll(queryId, {'users', {['id'] = id}}, function(result)
  print(json.encode(result))
end)

3.2.3

Changes

  • Fix #129
  • Fix transactions responding with true despite failing.
  • Implemented the command mysql:debug to toggle the debug printing #117. If it only redirects to file, it will only print to file.

3.2.2

  • Fixed a possible timeout when resources were loading too slow.
  • Added a warning against using MySQL 8, since the performance can be abysmal.

3.2.1

  • Switch to mysql 2.18.1
  • Fixed slow query warnings showing up properly, thanks to @Frosty-Ice
  • Added Date + Time for File Logging, from @Neddings
  • Updated all packages, frontend switched to Vuetify v2 (what a huge overhead).
  • Moved to fxmanifest from __resource.lua, should work with redm.
  • Fixed Sync transactions not working properly, thanks to @niekschoemaker

3.2.0

  • Go back to mysql.js 2.15
  • Add transactions
  • Errors are now always red, unless the debug output is only written to files.
  • Use stylus for styling

3.1.1

  • Removed excessive error reporting.
  • Removed MySQL.Ready's reliance on events

3.1.0

  • Breaking: keepAlive queries are now triggered via the connection string.
  • Added a GUI available for admins with the mysql command.
  • Added rerouting debug output to a log file.

3.0.12

  • Added keepAlive queries

3.0.11

  • Fixed some scope errors.
  • Fixed the debug print.

3.0.10

  • I fucked up.

3.0.9

  • Fixed an empty object returned by fetchScalar, thanks to @PichotM and mikroware.
  • Updated the dependencies to the latest versions, thanks to mikroware.

3.0.8

  • Fixes mysql-async not parsing the BIT column, which would return a buffer otherwise.

3.0.7

  • Added a version to the resource.lua so scripts can ask for the version by querying the metadata.
  • Refactor to use Promises in preparation for adding transactions.
  • Make the error on the missing connection string clearer.
  • Added a warning on queries that take more than 500ms. Time can be controlled by setting
set mysql_slow_query_warning <time-in-ms>

e.g.

set mysql_slow_query_warning 50

to print all queries that take more than one server-tick to complete. Setting it to 0 should disable the slow query warnings.

3.0.6

  • Fixes multiple statements partially. See FrBillyD’s , (french) example on how to fix it. e.g.
local result = MySQL.Sync.fetchAll(Query .. Query2, Parameters)
return result[2][1]

when the second query is the select (hence the 2 in the result).

  • Debug print shows now the resource requesting the mysql-query.

3.0.5

  • DateTime, DateTime2, Timestamp, Timestamp2, Date, Year should now behave properly. Is cast to ms, not to seconds.
  • Time, Time2 are currently not parsed.
  • It should be STABLE now on Linux without any leakage. Many thanks to @Doacola for running tests showing stable ram.

3.0.4

  • Fixes the issue above (datetime, datetime2, timestamp2 is now cast to seconds)

3.0.3

  • Fixed that dates were not returned as in 2.x. Timestamps are now returned in seconds. DateTime is tableified.
  • For this fix to work you must not have dateStrings=true on your connection string.
  • I probably introduced some bugs with this.
  • Some rough progress was made with getting 3.x to run stable on linux.

3.0.2

  • Allow multiple statements per query string on the legacy connection string
  • Support bignumbers by default on the legacy connection string

3.0.1

  • Parse both types of connection strings into options. No need to change it anymore.
  • Fixed an issue that MySQL.ready might not trigger on resource restart. onMySQLReady will still trigger only once, but MySQL.ready should work now properly.

3.0.0

  • Release

3.0.0-rc.0

  • Migrated entire library to node.js
  • Breaking : The console variable mysql_connection_string needs to be formated now like mysql://username:password@host/database?dateStrings=true additional options can be chained like in urls, first with ? then with &. The options can be gotten from https://github.com/mysqljs/mysql#connection-options
  • Breaking : Transactions are not included in this release candidate.
  • Threaded options are no longer needed and will be removed as of 3.1.0

Previous Versions

Change Log Version 2.X

2.1.2

  • Added default options for connection strings and mysql_debug, so it should not error anymore, hopefully.
  • SslMode is set to none by default contributed by @Syntasu

2.1.1

  • Updated MySqlConnector to a Custom 0.38.0 Release; This fixes the sessions leaking on heavy contention.

2.1.0

  • Updated MySqlConnector to a Custom 0.37.1 Release. I am not sure if I lost performance on Async queries somewhere along the way from 0.26/2.0.2; can someone compare?
  • Added Threaded operations. They work the same way as sync calls, but are slower as they are not executed on the server thread. You call them by using MySQL.Threaded.execute/insert/fetchAll/fetchScalar . If you are experiencing timeouts (replace some async queries with them). If you are experiencing hitches, replace some sync queries with them.
  • Added Transactions; they do not have a Threaded option yet.

2.0.4

  • Updated MySqlConnector to a Custom 0.36.1 Release (Differences will be on my MySqlConnector branch once I reach the up to date version 0.37.0). This should improve performance, reduce CPU load, and contains tons of bugfixes.

2.0.3

  • Updated MySqlConnector to 0.28.2 (the last version to actually work with mysql-async) Thanks to @IceHax testing it once.

2.0.2

  • Fix receiveing null result on fetch scalar

2.0.1

  • Remove native call on exception to avoid fatal error

2.0.0

  • Better logging, split time between connection / query / reading
  • Add mysql_debug config for activation / deactiving log of queries
  • Fix bug when returning null on fetchScalar
  • Add “MySQL.ready” function, use this function insted of the onMySQLReady event
  • Update underlying lib

2.0.0-beta6

  • Fix file name for linux

2.0.0-beta5

  • Readd pooling

2.0.0-beta4

  • Fix hard bug with fields and value being null cause of using IsDbNullAsync
  • Better exception message
  • Better error detection
  • Now show parameters in the log

Previous not for Beta 3:

It is now FULLY Async, before only the query execution was async, now it’s everything when using Async functions (Opening connecting, Query Execution and Result reading are async)

2.0.0-beta3 - Don’t use it it’s broken !

  • Complete rewrite, should be more stable and faster for async version
  • Add mysql_debug convar (default to false) true = print query statements
  • Add insert methods which allow to execute a query and return the last insert id from the database
  • Add a vstudio project, to give more trust about what is done and allow better contribution
  • Update to last version of the underlying lib

2.0.0-beta2

  • Fix a memory leak
  • Upgrade underlying mysql lib to fix some bugs (0.19.5 to 0.23.0)

2.0.0-beta1

  • Small but existing BC Break on the new API (read the upgrade guide)
  • FXServer Only
  • No more support for essential mode API (you should update to the new API before upgrading to 2.0)
Change Log Version 1.X

1.0.0

  • No changes since 0.2.2, it just stable enough to release it under a stable version

0.2.2

  • Close connection before calling user callback, so in case of error connection is still properly closed
  • Fix bad transformations of fields, .NET object were present in result which used in TriggerEvent would faild as MessagePack don’t know how to transform it
  • Add a optional script to clear connections every 10sec

0.2.1

  • Fix bug of not closing connections, due to this pooling has been desactivated, will reactivate once i have proper time to debug this.

0.2.0

  • Remove coroutine support as it was leaking thread due to the way of using it
  • Stabilize connections on mysql (correctly open / close)
  • Fix many bugs about specific parameters and null value on a Select queries
  • Add query log (with execution time of the query)

0.1.0

  • Release

Installation and Usage

44 Likes

Awesome. I too was doing the same thing as you have using the same MySqlConnector connector as you.
Cool to see someone else doing this too.
Might want to give some credit to @Kanersps as I see, like I was, you’ve used some of his code in your Lua script.

3 Likes

Right, will add a note in the credits,

In fact i used some of this code to understand some things in the beginning and also because i will certainly give a BC layer for people still using the old essential mode so if they want to stick with mysql (like me) they can with this lib.

Don’t hesite to raise an Issue on how you have done things in your lib so we can share knowledge.

1 Like

hey bud you have any good scripts for fo the old sql

1 Like

GG bro :slight_smile: that’s good script :slight_smile:

1 Like

Nice Work.

Good Script.

1 Like

The MySQL.escape function doesn’t work properly. Whenever MySQL.fetchAllAsync is used with parameters, it returns an error saying Can not call nil value.
This is caused by self.mysql.MySqlHelper.EscapeString
I just removed this call from the string.gsub in MySQL.fetchAllAsync and it fixed it, until this gets fixed properly.

1 Like

Seen it, I just push a new version with this corrected, and also make the code cleaner and easier to understand, by separating code into small modules.

I also add a BC layer fully compatible with essential mode < 3.0

I will rewrite the documentation tonight so that everyone can understand how to use this.

1 Like

I just updated the library, it now provides a BC Api for the MySQL methods of EssentialMode and also an Upgrade Guide.

ATM this library is still in Alpha, but the API should not change unless there is very bad feedback.

1 Like

Beta released

See the main post for features

Need testers and feedback to make a stable version

1 Like

look good. :slight_smile:

1 Like

Updated to v0.2.0 (see changelog in the first thread)

Stability has been hugely improved.

Thanks for all people who have helped me testing this.

4 Likes

Does an async library actually help speed things up? Everything is pretty much running in its own thread, seems like this complicates things way beyond what’s actually needed?

Obviously this is cool, great to have a mysql option, I’m already playing with it, might even use it, but I’m wondering what kind of advantages people are seeing on the servers? Of all the mysql scripts I’ve seen, I’d be shocked if anyone actually noticed a different between the basic mysql package people have and this. And of all the tables I’ve seen used in scripts, I’m not sure any queries or bulk queries would go any faster, and maybe slower.

Sync functions block what main thread? the server or mysql? pretty much all db stuff is batched out on its own thread and doesn’t block anything? as for the main mysql thread, uh no.

1 Like

The old implementation, who was using the official MySQL Connector, was having lock thread issue on slow queries. I don’t know the details on how the thread manager of C# works and how it is implemented in the FiveM server, but apparently not having a async lib for MySQL was a problem and that’s one of the reason the author decided to switch to CouchDB (as there is an async api for making http requests)

My guess is that the event handler is not async and run on the main thread, but not sure about that.

1 Like

Ahh makes sense. I’m glad to see more MySQL options regardless. :grinning:

1 Like

im getting these errors. help me please

1 Like

We have seen weird behavior with imbriqued TriggerEvent like the following :

	TriggerEvent('es:getPlayerFromId', source, function(user)
		if user ~= nil then
			TriggerEvent('es:getPlayerFromIdentifier', user.identifier, function(user)
...

It may come from here, i’m trying to narrow down this use case to a minimum reproducible bug, anyway i have update the library to 0.2.1 since 0.2.0 was having too many connections error, it seems better now

2 Likes

Many bugs have been corrected in 0.2.2, consider upgrade for better stability

1 Like

Problem I think is that people who made mods with mysql added selects and updates right there…
Since everything is working in memory why not use classes and populate them once when the player connect and do 1 update when the player quit ?
I have done this for saving the position of the player and there were no more locks.
But yeah only problem is when the player’s client crashes or doesn’t disconnect normally.

Anyways, this is a great release :slight_smile:

1 Like

I did everything said in upgrade.md and readme.md. When i trying to join server says “Default ban reason error” !!! Please help me i want to use this script !!!

1 Like