Ghmattimysql: My MySQL Implementation for FiveM [1.0.0]

dev
mysql
resource
serverside
database

#123

I have never had problems with being to slow, I was just wondering, is it worth to replace it, what else is better with it? does it have any bad parts with it that MySQL-Async does better? as it is right now i use lua, would u say its better to use this in the end?


#124

It actually uses prepared statements. Instead of escaping it manually. Imho it is not worth it to replace if you have the mysql-async node variant going. Which is pretty smooth on windows.


#125

That freeze seem to be gone, but it still does not free any RAM and it keeps climbing. If I knew how to use perf properly, I could run one to crash and hand it someone with actual expertise towards the linux builds.

The script I ran is in the deleted message.


#126

Released 1.0.0

Also sorry Linux servers. You won’t be able to run this and have to stick with the C# version, which is for everything BUT reads on par with this version. The read is somewhere between two to three times faster, due to buffer issues of the underlying MysqlConnector with fivem.

If you then include the possiblity of using ssl and other niceties you can easily see why I am going forward with developing node.js version of ghmattimysql and mysql-async, instead of continuing the C# development.

For the next version I will do something about the error reporting probably, which is still a bit too verbose. And hopefully finish the docs.


#127

Do you plan to implement it for linux or do you already discard that idea?


#128

It works on linux partly, the issues for using the 1.0.0 version on linux are mostly depending on the server version. I think you need at least 760 to get it somewhat working, but then you face the issue that the linux is not garbage collecting properly for v8.

It is not something I can change for the resource, so I will have to work with the v8 implementation of fivem. or something like that.


#129

For some Reason neither config neither using set command works with my vrpex base.


#130

I think vrp uses the old C# one. I am not certain, but I think you should use the C# connector, instead of the newer js connector (which only runs properly on windows servers).

I have also found recently a new bug which needs some really extended stress testing to see if I fixed it.


#131

Hello!
I’m currently doing the transition from the 0.6.1 version to the current one and I’m facing a error on a query.
Everything was working well with the 0.6.1 version. The function that uses the query was like that (from gcphone resource):

function _internalAddMessage(transmitter, receiver, message, owner)
    local Query = "INSERT INTO phone_messages (`transmitter`, `receiver`,`message`, `isRead`,`owner`) VALUES(@transmitter, @receiver, @message, @isRead, @owner);"
    local Query2 = "SELECT * from phone_messages WHERE `id` = (SELECT LAST_INSERT_ID());"
	local Parameters = {
        ['@transmitter'] = transmitter,
        ['@receiver'] = receiver,
        ['@message'] = message,
        ['@isRead'] = owner,
        ['@owner'] = owner
    }
	return exports["GHMattiMySQL"]:QueryResult(Query .. Query2, Parameters)[1]
end

I modified it in order to make it works with the new version, which is:

function _internalAddMessage(transmitter, receiver, message, owner)
    local Query = "INSERT INTO phone_messages (`transmitter`, `receiver`,`message`, `isRead`,`owner`) VALUES (@transmitter, @receiver, @message, @isRead, @owner);"
    local Query2 = "SELECT * FROM phone_messages WHERE `id` = (SELECT LAST_INSERT_ID());"
	local Parameters = {
        ['transmitter'] = transmitter,
        ['receiver'] = receiver,
        ['message'] = message,
        ['isRead'] = owner,
        ['owner'] = owner
    }
	return exports.ghmattimysql:executeSync(Query .. Query2, Parameters)[1]
end

And this is the error:

[MySQL] INSERT INTO phone_messages (`transmitter`, `receiver`,`message`, `isRead`,`owner`) VALUES (?, ?, ?, ?, ?);SELECT * FROM phone_messages WHERE `id` = (SELECT LAST_INSERT_ID()); : ["202-2002","202-2002","=)",0,0]
Error: (node:8472) UnhandledPromiseRejectionWarning: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * from phone_messages WHERE `id` = (SELECT LAST_INSERT_ID())' at line 1
    at Packet.asError (ghmattimysql.js:91:11763)
    at Prepare.Command.execute (ghmattimysql.js:115:488)
    at PoolConnection.Connection.handlePacket (ghmattimysql.js:200:9784)
    at PacketParser.onPacket (ghmattimysql.js:200:1602)
    at PacketParser.executeStart (ghmattimysql.js:263:1699)
    at Socket.<anonymous> (ghmattimysql.js:200:1791)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
Error: (node:8472) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
Error: (node:8472) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

If you have any ideas to fix it, it would be awesome, as awesome as your work to this mysql middleware for FiveM :heart_eyes:
Thanks in advance!


#132

Use multipleStatements: true as a parameter, though if people can inject on that, if there is a successful injection attempt, the damage that can be done will increase in scope.

It only allows a single statement at the moment per query by default.


#133

Alright. Thanks a lot, I’ll try that as soon as possible :slight_smile:


#134

I did some more testing. And it seems that mysql2 is running into some issues on fivem, unlike the normal mysql in node, which the current mysql-async version is using, which are on very short duration tests (sub hours) not noticeable at all.

Thus before I am fully certain about it or how to circumvent the problems on extended use which I am having. I recommend using the C# version instead.

The issues are 3 small hitches and one big hitch. The big one repeated approximately hourly - the small ones didn’t, but I was playing other games meanwhile, although it should not have interfered to get a 3-4s hitch.


#135

Hey, i have huge problem with GHMattiMySQL, when new player without entry in users database connects to server. it basically doesnt create database entry…

[GHMattiMySQL Debug] Connection: 12ms; Interaction: 16ms; Total 28ms for Query: SELECT * FROM users WHERE `steam:110000101d5eca6`=@steam:110000101d5eca6;
[GHMattiMySQL ERROR] [ERROR] Parameter '@license' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string.
  at MySqlConnector.Core.StatementPreparer+ParameterSqlParser.OnNamedParameter (System.Int32 index, System.Int32 length) [0x0005b] in <3c0d3c7928b341c89f199d843f17af8c>:0 
  at MySqlConnector.Core.SqlParser.Parse (System.String sql) [0x001b8] in <3c0d3c7928b341c89f199d843f17af8c>:0 
  at MySqlConnector.Core.StatementPreparer.ParseAndBindParameters () [0x0003a] in <3c0d3c7928b341c89f199d843f17af8c>:0 
  at MySqlConnector.Core.TextCommandExecutor.CreateQueryPayload (System.String commandText, MySql.Data.MySqlClient.MySqlParameterCollection parameterCollection) [0x00056] in <3c0d3c7928b341c89f199d843f17af8c>:0 
  at MySqlConnector.Core.TextCommandExecutor+<ExecuteReaderAsync>d__3.MoveNext () [0x0007a] in <3c0d3c7928b341c89f199d843f17af8c>:0

#136

If you knew your code you’d find that you somewhere set @license, but you did not pass it as a parameter.


#137

problem is that @license is Rockstar License in ESX, not my code.


#138

it is in your code somewhere. you pass @license to mysql via query, but you do not define it. So mysql errors. This is exactly this error.


#139

Hey. Can u help me setup MySQL? My discord is KING#1121 I would greatly appreciate it. Im lost.


#140

Hey guys, is it possible to use boolean with Linux C# version?
I think thats my problem.


#141

Yes you can it uses the mysql-connector.


#142

Its not working using strings, can you please give me advice? Do i have to put that setting in config file?