MySQL parallel executes order

FiveM Resource Development Discussion
1

Hello all,

I’am inserting data into a table using the MySQL Async lua library. The problem I have is that I insert data into my database using the following code:

for i=1, #checkpoints, 1 do
	MySQL.Async.insert('INSERT INTO RaceCheckpoints (RaceID, CheckpointX, CheckpointY, CheckpointZ) VALUES (@RaceID, @X, @Y, @Z)',
	{ ['@RaceID'] = insertId, ['@X'] = checkpoints[i].coords.x, ['@Y'] = checkpoints[i].coords.y, ['@Z'] = checkpoints[i].coords.z})
end

I expected the queries performed in the loop to be executed in order. However in practice the insert of iteration 3 could be done before the query of iteration 2. I’am however expecting the order to be right once I reload the data. Is there a way to force the queries to be performed in the order of the iteration?

Thanks in advance.

2

The way you are doing this is not very optimised at all not trying to make you feel bad or anything but you should not be calling a query for each checkpoint like that, thats how you fry your database and overload it so everything become super slow to retrieve data because of the backlog of events, You probably should just json.encode and json.decode a table instead of for looping each checkpoint, feel free to ask for more help if you dont understand what I mean

3

@ixHal , I totally get your point, I was looking for more efficient ways but couldn’t directly find any. Could you help me with an example? It would be great to be able to insert arrays/json strings into the table in a single query.

4

so lets say your checkpoints is saved as a table like so…

checkpoints = {
    [1] = {x = 0.0, y = 0.0, z = 0.0},
    [2] = {x = 1.0, y = 1.0, z = 1.0},
}

You can pass that table with the TriggerServerEvent I guess you are already doing?
All you would need to do then is create a unique ID/name to the track you are saving in the database I guess RaceID/insertId is something you already setup todo this?
then on the mysql insert and fetch it would look something like this:

MySQL.Async.insert('INSERT INTO RaceCheckpoints (RaceID, raceData) VALUES (@RaceID, @raceData)',
	{ ['@RaceID'] = insertId, ['@raceData'] = json.encode(checkpoints)})
end

so for example it would look like this in the database and LUA would not recognise it as a table:

[{"1":{"x":"0.0", "y":"0.0", "z":"0.0"} "2":{"x":"1.0", "y":"1.0", "z":"1.0"}]

To get the databack into a format lua would recognise you would use json.decode and it would look something like this:

MySQL.Async.fetchAll('SELECT * FROM RaceCheckpoints WHERE RaceID=@RaceID',{['@RaceID'] = what ever race id you want to fetch..)}, function(RecievedData)
    if RecievedData[1] ~= nil then
        checkpoints = json.decode(RecievedData[1].raceData)
        --Put a callback here to the client so they recieve the data.
    end
end)

It would then be back into the lua table format like so:

checkpoints = {
    [1] = {x = 0.0, y = 0.0, z = 0.0},
    [2] = {x = 1.0, y = 1.0, z = 1.0},
}
1 Like
5

feel free to ask for more help if you need anything else explained

1 Like
6

Thanks alot for the explanation and example. I will give this a try soon but it already makes alot of sense :slight_smile: