[Free] oxmysql - asynchronous MySQL wrapper

FiveM Releases
, , , ,
44
  • Added support for mysql_debug true/false convar - this will log both query and parameters with execution time into console.

You can swap MySQL.Ready() right away with Citizen.CreateThread(), mysql connection will be ready on-demand after resource startup / first query.

2 Likes
45

Not sure if it’s just me, but using MySQL Procedures with oxmysql:execute seems to just kill my server with no error logs or trace, tested with this procedure which works perfectly with ghmattimysql:

exports.oxmysql:execute("CALL `TropixRP_CreatePlayerData` (?, ?, ?, ?, ?, ?, ?)", {ID, SteamID, Discord, FirstName, LastName})

vs

exports.ghmattimysql:execute("CALL `TropixRP_CreatePlayerData` (@ID, @SteamID, @Discord, @FirstName, @LastName)",
    {
        ["@ID"] = ID,
        ["@SteamID"] = SteamID,
        ["@Discord"] = Discord,
        ["@FirstName"] = FirstName,
        ["@LastName"] = LastName,
    })
46

I may know cause of this. Create issue on github.

Maybe because exports are exporting execute method of mysql2, which prepares queries and for procedures we need to call mysql2 method query

47

thanks but it should be awsome if we can config it wrtie debug on one file and clean up that on every startup
like

set mysql_debug "File"
set mysql_log_file_format "debug-mysql.log"
48

A tutorial how to switch easily from mysql-async to OxMySQL would be dope. Since im not that good with SQL stuff… Thx

49

I can look onto that. Could you please create an issue on github about this feature request? Thank you.

50

It´s not easy as easy :smiley:

Technically you would need to:

  • Use exports.oxmysql:fetch() instead of MySQL.Async.fetchAll() for example.
  • Remove mysql-async lib from fxmanifest.lua to reduce memory overhead.
  • Edit some queries if they need to be. I have prepared experimental backward compatibility with mysql-async parameters written in @param format, but it would be better to use unnamed ? or named :param placeholders.
  • Test everything before going to production.

Also there are some known issues or rather incompabilities between old node-mysql and newer used node-mysql2. I have written some on github page under issues but from head I remember that:

  • You can´t pass nil as parameter value,
  • TIMESTAMP is returning table value / date string instead of unix timestamp,
  • TINYINT(1) is not converting to boolean but stays as number,
  • You cannot and shouldn´t concatenate strings in query
    Oxmysql is preparing and storing every query into cache to get faster results. This cache is default limited to approx. 16000 queries. Instead of concatenating you should use placeholders (:param or ?) and pass required values as parameters.

Example:

-- bad query, unsanitized, possible SQL injection and fills up prepared statement cache!
exports.oxmysql:fetch('SELECT * FROM `users` WHERE `identifier` = '..license)

-- using parameters, sanitized, query is saved only once into cache
exports.oxmysql:fetch('SELECT * FROM `users` WHERE `identifier` = ?', { xPlayer.identifier })
exports.oxmysql:fetch('SELECT * FROM `users` WHERE `identifier` = :license', { license = xPlayer.identifier })

-- if you want to pass parameter as column use ?? (haven´t tested properly yet)
exports.oxmysql:fetch('SELECT ?? FROM `users` WHERE `identifier` = ?', {'group', xPlayer.identifier })
3 Likes
51

DBcore for VRPEX

52

a question from noob. would you have to change mysql-async to oxmsql or just be running oxmsql together with msql-async

1 Like
53

oxmysql is a replacement for mysql-async, so you have to replace it, and then replace all the queries.

54

Update 1.2.0

  • Fixed typecasting (TINYINT/BIT → BOOLEAN | DATE → TIMESTAMP)
  • Fixed error when calling procedures (use execute(...))
  • Execute and insert is no more storing query into cache ^
  • Refactored query backward compatibility
  • Fixed some minor errors
2 Likes
55

I might be an idiot…but how can you use passwords with "@" in them, using this format? For now, I just made a different account in the database, but knowing how to use @ would be nice too.

56

its better always check github issues and make issue in there :smiley:

1 Like
57

Could the default convar mysql_connection_string be changed so we can used mysql-async and oxmysql simultaneously. This allows to start converting from one to the other progressively.

Also what would be the equivalent to MySQL.ready

58 
function MySQL.ready(cb)
    Citizen.CreateThread(function()
        while GetResourceState('oxmysql') ~= 'started' do
            Citizen.Wait(0)
        end
        cb()
    end)
end
1 Like
59

Nice Job. Thanks!

60

It’ll come, but what’s the point of overly complicated passwords? Prevent access from outside localhost and require ssh if you want to connect via heidi or whatever.

If your database is hosted separately from the server there is a way to setup a connection via ssh but you’d need to take a look at the node package.

61

This style of connection string already works with mysql-async/ghmattimysql.

Why do you need it? Unless your SQL server itself isn’t running or you have connection/database issues then it’s ready from the moment oxmysql has started.

Just replace those functions and wrap the entire code block in an async export

exports.oxmysql.fetch(query, params, function(result)
    -- now use your results
62

image
image1156×94 12.1 KB

Idexing directly errors out when there are 0 rows returned

63 
exports.oxmysql:fetch('SELECT * FROM users WHERE lastname = ?', {'Linden'}, function(async)
    print(async[1].firstname)
    local sync = exports.oxmysql:fetchSync('SELECT * FROM users WHERE lastname = ?', {'Linden'})[1]
    print(sync.firstname)
end)

Both of these give the same result for me, but if you’re looking to retrieve a single row you should use single rather than fetch.

exports.oxmysql:single('SELECT * FROM users WHERE lastname = ?', {'Linden'}, function(async)
    print(async.firstname)
    local sync = exports.oxmysql:singleSync('SELECT * FROM users WHERE lastname = ?', {'Linden'})
    print(sync.firstname)
end)