[Standalone] OxMySQL - Lightweight MySQL wrapper

[Anclados]

?connectTimeout=30000&acquireTimeout=30000&waitForConnections=true&keepAlive=30

in mysql these parameters worked, now in oxy they no longer work, what would be the parameters for oxy?

[SimReports-Netzwerk]

[script:esx_policejob] SCRIPT ERROR: @esx_policejob/server/main.lua:326: attempt to index a nil value (local ‘store’)
[script:esx_policejob] > ref (@esx_policejob/server/main.lua:326)
[script:esx_policejob] > handler (@esx_datastore/server/main.lua:77)
[script:esx_policejob] > ref (@esx_policejob/server/main.lua:325)
[script:esx_policejob] > TriggerServerCallback (@es_extended/server/functions.lua:33)
[script:esx_policejob] > handler (@es_extended/server/common.lua:88)
[script:esx_datastore] SCRIPT ERROR: error object is not a string
[script:esx_datastore] (nil stack trace)es_extended: TriggerServerCallback => [esx_society:getOnlinePlayers] does not exist

[schwim]

This error has nothing to do with oxmysql.

[schwim]

Not good server admin, script works fine. Install from releases instead of main branch.

[mohammad_shtayeh]

1. guys how can i solve this problem?

[Puntherline]

I’ve already started migrating from mysql-async and so far it’s pretty simple! I have very few insert, fetchAll, update and select x where y queries so I figured I might as well fully switch over to the exports instead of loading the lua file in every single resource, potentially getting better performance.

I’m well versed with SQL syntax , but I am lacking knowledge of the technical aspect behind it, so I have a few questions.

I was wondering when to use prepare and when to use query/insert. From just reading the documentation, it seems as though prepare is always faster and more secure against SQL injection, making it the choice for any query. But is that really the case? I don’t mind more work for better security.

Next up, callbacks vs promises. When I did some testing, both worked perfectly fine. Does it just depend on what you can make work easier in certain situations or are there some obvious advantages to choose one over the other?

The last question I have is about errors. Nothing complex though, I just wonder how (or even if?) I can handle them in such a way that the code won’t throw a tantrum soon as the database is down. Here’s an example of what I mean:

Code

--[[
	Prepare Callback and Query Callback:
	Anything inside the prepare function will stop working immediately,
	the thread created will continue to work without issues though.
]]
Citizen.CreateThread(function()
	exports.oxmysql:prepare("SELECT * FROM test", {""}, function(result)
		if result then print(json.encode(result)) else print("Database down?") end
	end)

	print("I will still print, even if the database is offline.")
end)

--[[
	Prepare Promise and Query Promise:
	The entire thread will stop working, fully unable to continue.
]]
Citizen.CreateThread(function()
	local result = exports.oxmysql:prepare_async("SELECT * FROM test", {""})
	if result then print(json.encode(result)) else print("Database down?") end
	result = nil

	print("If the database is down, you'll never see me.")
end)

I can work around the last problem, but I can’t come up with something reliable that will always work, even if the database is offline. Here’s what I got though:

Code

Citizen.CreateThread(function()
	local _result
	local count = 0

	exports.oxmysql:prepare("SELECT * FROM test", {""}, function(result)
		_result = result
	end)

	while not _result do
		count = count + 1
		if count >= 100 then
			break
		end
		Wait(0)
	end

	if _result then
		print("Result received!")
	else
		print("No result.")
	end
end)

So… Any thoughts, hints, things to google or links to some further documentation?

[Linden]

I might as well fully switch over to the exports instead of loading the lua file in every single resource, potentially getting better performance.

@oxmysql/lib/MySQL.lua is more performant, especially for promises. If you remove mysql-async, you don’t even need to change the import (@mysql-async/lib/MySQL.lua will work).

I was wondering when to use prepare and when to use query/insert. From just reading the documentation, it seems as though prepare is always faster and more secure against SQL injection, making it the choice for any query. But is that really the case? I don’t mind more work for better security.

Prepare is always secure from SQL injection; though, with the multipleStatements flag disabled (default) you should be safe from that.

When writing dynamic queries like filtering for different column indexes you will want to use the standard query methods. You also won’t get much, if any, performance improvements when calling prepare for a statement that doesn’t get reused often or has few if any parameters (at least two).

'SELECT 1 WHERE column = ?' is often used to check if a row exists and you’re better off using scalar over prepare.

Prepare is best used for transaction-like behaviour, but I can’t change it to actually use a transaction because of backwards-compatibility. It also doesn’t follow the same typecasting rules as query, so you may end up having to handle that yourself, i.e.

• bit will return an array, so you need to check the first entry is equal to 1 to determine if it’s truthy
• tinyint(4) always returns a number, so again you need to check if it’s equal to 1

I can’t remember what date was returning as and cbf checking, but I ended up doing DATE_FORMAT(dateofbirth, "%d/%m/%Y") AS dateofbirth when selecting.

Next up, callbacks vs promises. When I did some testing, both worked perfectly fine. Does it just depend on what you can make work easier in certain situations or are there some obvious advantages to choose one over the other?

I use the callback method / MySQL.Async when not returning a value (I won’t even provide a function), otherwise I will only use promises. They’re less confusing to work with when performing multiple queries that rely on the result of the other (callback hell), and it makes debugging simpler. Promises also allow try/catch in JS, and pcall in Lua for a similar experience.

The last question I have is about errors. Nothing complex though, I just wonder how (or even if?) I can handle them in such a way that the code won’t throw a tantrum soon as the database is down.

Your database going down shouldn’t really happen, but you can handle errors with pcall as mentioned above. In the code shown, the second message will print before the callback returns since it executes inside a new thread - that’s also why the function still runs when an error occured.

	while not _result do
		count = count + 1
		if count >= 100 then
			break
		end
		Wait(0)
	end

This is essentially how promises work, however they will be resolved immediately instead of yielding for a server tick (which is ~50ms). mysql-async used this method of waiting for a result rather than promises (which meant a thread could end up looping indefinitely).

CreateThread(function()
    local status, result = pcall(MySQL.query.await, 'SELECT')
    if not status then error(result) end
end)

[Puntherline]

You’re a lifesaver, that pretty much answered all questions I had!

Somehow I forgot pcall existed which will make my life so much easier.

Anyways, thank you for your answer and happy birthday!

[Rocco_RP]

SCRIPT ERROR: @oxmysql/lib/MySQL.lua:95: No such export query in resource oxmysql

how to fix this ?

[Puntherline]

If you already import the MySQL.lua file, you might as well use MySQL.query() or MySQL.query.await(). Here’s the documentation on queries: Query | Overextended

[Beauflexx]

So from what I read, this is pretty much a drag and drop from mysql now? Like I wouldnt have to update a bunch of scripts in order to get this to work? trying to read up more on this but just figured id ask?

[Mia_Ava]

How to use semicolon-separated format for the password?

[Beauflexx]

the transfer from mysql to oxmysql, was pretty easy going.
However have a few scripts still messing up.
Tried looking at the documentation but unsure how to re code this so it works with ox, as i didnt see a fetchAll thing on the .com
Thanks in advance.

[Linden]

Has nothing to do with mysql, beyond the fact that a query was performed.
That’s an error occurring in core_multijob, because it is… concatenating a nil value… as it says.

If the error didn’t occur with mysql-async, it’s more likely a result of uncaught errors due to yielding in a function reference. With mysql-async many errors will not throw an error, but you may see a random empty line in the console.

[Beauflexx]

okay thank you for reply!

[S1xsense]

Could not find dependency /server:5104 for resource oxmysql.

[Linden]

Important policy update for server owners

When you’ve been told to go here for ‘more info’: If you’re seeing the message below saying that your platform version is out of support or has reached end-of-life, you can fix this by updating your FiveM/RedM server artifacts by following the basic installation steps in our guide: >>> Go to the installation/update guide <<< [eos] Still need help? Post a topic (in English only) in #server-development:server-discussion or ask in the Cfx.re Discord server-talk channel. For more details, se…

[Dakalysta]

Hello,

I imagine this question has been asked 1000 times, but I need to be sure.

As I understand it, oxmysql provides mysql-async

provide ‘mysql-async’

So I just have to launch the oxmysql resource and stop mysql-async, without modifying the fxmanifest.lua of my old scripts?
Is it backwards compatible?

[Luke]

Is it backwards compatible?

It is.

Remove your mysql-async folder, drop in oxmysql and it will work.

[Dat_Boi_Roasta]

Can someone help please I don’t know what happen everything was working fine and I flew in for some test and now when I open the players inventory nothing comes up and can’t move have to restart qb-inventory just to walk around again this is the new error I’ve noticed