• gmsv_mysqloo v9 - Rewritten MySQL Module (prepared statements, transactions)
    157 replies, posted
  • Avatar of Fredy
  • [QUOTE=Willox;51985268]I'd guess this is because Lua's C errors are going to jump straight past the destructor of any C++ object. That'd mean the lock guard [url=https://github.com/syl0r/MySQLOO/blob/92aa5bc91566b42c05cad357e84f658dceac7506/MySQLOO/source/Database.cpp#L154]here[/url] never unlocks. It's annoying and also means there's probably many other bugs in the module, but that's how Lua's C API works.[/QUOTE] Yup, that's what's causing it. Unfortunately that is what you will get when you are mixing two languages that have vastly different language constructs (and both of which suck). I was aware of the RAII issue with LUA and I did go through most of the code to find any issues, but I somehow missed that one. The sad thing is that some scenarios (such as stack overflow or out of memory) a crash is unavoidable. Even without RAII, a longjump will always fuck up your code. As a sidenote, there's only very few scenarios where you should use db:escape(). A prepared query (if possible) should always be preferred.
  • Avatar of BlackCetha
  • I am currently running into issues. It seems to me like the callback just doesnt fire. This is how my function works: [code] local queryDoesExist = db:prepare( [[ SELECT id FROM items WHERE name = ? ]] ) function doesExist ( name, callback ) print "0" queryDoesExist:setString( 1, name ) function queryDoesExist:onSuccess ( rows ) print "1" return callback( #rows ~= 0 ) end function queryDoesExist:onError ( err ) print( "Error: " .. err ) end print "2" queryDoesExist:start() end [/code] In this example, it works if it is instantly run after the database connected. Calling it with lua_run or a proxy function doesn't work. It would print "0" and "2".
  • [QUOTE=BlackCetha;52020147]I am currently running into issues. It seems to me like the callback just doesnt fire. This is how my function works: In this example, it works if it is instantly run after the database connected. Calling it with lua_run or a proxy function doesn't work. It would print "0" and "2".[/QUOTE] I just tested your code and it works fine for me. I suggest you clean it up a little bit though. [code] function doesExist(name, callback) local q = [[SELECT id FROM items WHERE name = ?;]] local query = db:prepare(q) query:setString(1, name) function query:onError(err) print("Error "..err) end function query:onSuccess(data) callback(data[1] and true or false) end query:start() end [/code]
  • Avatar of BlackCetha
  • [QUOTE]I suggest you clean it up a little bit though.[/QUOTE] I'm not going to argue about coding style. The weird thing is that it is just this specific function signature [CODE]function ( ..., callback )[/CODE] that doesn't seem to work. Every other function I've written seems to work fine. After some testing it does work everytime once I :wait() for the query to finish. I rewrote the function and query from scratch, and it still doesn't work even if I put a syncronous function in the :onSuccess handler. Here's my current code: [CODE] queries = { ..., groupExists = [[ SELECT group_id FROM groups WHERE name = ? ]], ... } ucl.groupExists = function ( name, callback ) ULib.checkArg( 1, "ULib.ucl.addGroup", "string", name ) local groupExists = db:prepare( queries.groupExists ) groupExists:setString( 1, name ) function groupExists:onSuccess ( rows ) callback( #rows == 1 ) end function groupExists:onError ( errstring ) db.LogError( "ULib.ucl.groupExists", name, errstring ) end groupExists:start() groupExists:wait( true ) end [/CODE] I really want to get rid of the :wait() though. Maybe there's some LUA-specific mistake in here that I don't have the knowledge to see, after all I'm coming from JS/Node where something like this style works fine.
  • Avatar of bigdogmat
  • [QUOTE=BlackCetha;52037641]I'm not going to argue about coding style. The weird thing is that it is just this specific function signature :snip: that doesn't seem to work. Every other function I've written seems to work fine. After some testing it does work everytime once I :wait() for the query to finish. I rewrote the function and query from scratch, and it still doesn't work even if I put a syncronous function in the :onSuccess handler. Here's my current code: :snip: I really want to get rid of the :wait() though. Maybe there's some LUA-specific mistake in here that I don't have the knowledge to see, after all I'm coming from JS/Node where something like this style works fine.[/QUOTE] That function looks fine, the only reason I could see it needing wait would if be if you've made the callback synchronous. If you could post your whatever your callback function is. [code] print(1) local t = db:prepare "SELECT 5 + ?" function kkkk() print(2) t:setNumber(1, 5) function t:onSuccess(tab) print("Success:", 3) PrintTable(tab) end function t:onError(err) print("Error:", 3) print(err) end t:start() end [/code] Also if you could, run the code above anytime after you've run db:connect(), then call the kkkk() function through something such as lua_run. Just to make sure this is user mistake and not an issue on your installation.
  • Avatar of BlackCetha
  • It seems like it doesnt even get to the :onSuccess handler. Output from your debug code: [CODE] [DB] Connected 1 2 Success: 3 1: 5 + ? = 10 lua_run kkkk() > kkkk() ... 2 [/CODE] The first bit where it works is called directly from the script file. If it makes any difference I'm running MariaDB on a Ubuntu Server 16.04 VM together with srcds.
  • Avatar of Fredy
  • [QUOTE=BlackCetha;52041963]It seems like it doesnt even get to the :onSuccess handler. Output from your debug code The first bit where it works is called directly from the script file. If it makes any difference I'm running MariaDB on a Ubuntu Server 16.04 VM together with srcds.[/QUOTE] For me it prints [LUA] lua_run kkkk() > kkkk()... 2 Success: 3 1: 5 + ? = 10 [/LUA] regardless of whether or not I call it from a script or lua_run. MariaDB should not make any difference since it is mostly compatible with mysql.
  • Avatar of BlackCetha
  • Here is the relevant code If you don't mind reading through it: [URL="https://gist.github.com/BlackCetha/f5eb75563ba0b7124b6bd9684f53a573"]https://gist.github.com/BlackCetha/f5eb75563ba0b7124b6bd9684f53a573[/URL] It has some design flaws, but the only thing I can't fix myself is the queries.
  • Avatar of Fredy
  • I ran your code with some dummy db entries and it printed: [LUA] [DB] Connecting Connected to database as server #1 'fagsad' [/LUA] So I think that works on my end too.
  • Avatar of bigdogmat
  • [QUOTE=BlackCetha;52042158]Here is the relevant code If you don't mind reading through it: [URL="https://gist.github.com/BlackCetha/f5eb75563ba0b7124b6bd9684f53a573"]https://gist.github.com/BlackCetha/f5eb75563ba0b7124b6bd9684f53a573[/URL] It has some design flaws, but the only thing I can't fix myself is the queries.[/QUOTE] Just to make sure, you have downloaded the latest release from [URL="https://github.com/syl0r/MySQLOO/releases"]this[/URL] page right?
  • Avatar of BlackCetha
  • Yes, I'm running the latest version. MariaDB, libmysql and the srcds itself should also be the latest versions. There are no addons installed on the server except my script that I published, ULib and ULX. [CODE] print(mysqloo.VERSION) 9 print(mysqloo.MINOR_VERSION) 3 [/CODE]
  • Avatar of BlackCetha
  • After some testing it seems like my installation is broken. I'll just wipe the VM and try again. Thanks for helping.
  • Heads up: MySQLOO seems to cause a crash when used after a changelevel on the dev branch. Tested using DarkRP with Windows SRCDS. It could be a regression with the changes made to binary modules. (You'll need to add a bot or join the server or something to kick the server out of hibernation before changing level to trigger the crash.)
  • Avatar of Willox
  • [QUOTE=Bo98;52091024]Heads up: MySQLOO seems to cause a crash when used after a changelevel on the dev branch. Tested using DarkRP with Windows SRCDS. It could be a regression with the changes made to binary modules. (You'll need to add a bot or join the server or something to kick the server out of hibernation before changing level to trigger the crash.)[/QUOTE] no dump?
  • [QUOTE=Willox;52091073]no dump?[/QUOTE] Sorry, here it is: [url]https://cl.ly/2c1h1H1t1T1l/download/srcds_2117156_crash_2017_4_11T21_37_6C0.mdmp[/url]
  • Avatar of Willox
  • [QUOTE=Bo98;52091084]Sorry, here it is: [url]https://cl.ly/2c1h1H1t1T1l/download/srcds_2117156_crash_2017_4_11T21_37_6C0.mdmp[/url][/QUOTE] It looks like a normal C function call is failing from a query callback which calls in to http.Fetch or HTTP. Sounds stupid, but could you verify the server's content before trying again?
  • [QUOTE=Willox;52091159]It looks like a normal C function call is failing from a query callback which calls in to http.Fetch or HTTP. Sounds stupid, but could you verify the server's content before trying again?[/QUOTE] Seems to happen if sv_lan is changed. i.e. [sv_lan 0 is set by default] 1. Startup server 2. Run http.Fetch 3. sv_lan 1 4. changelevel 5. Run http.Fetch again 6. Crash The same also happens in reverse (1 -> 0). The reason for the change in sv_lan was because I had it in server.cfg and not autoexec.cfg. Setting sv_lan in server.cfg does not work until changelevel.
  • Avatar of Willox
  • [QUOTE=Bo98;52091348]Seems to happen if sv_lan is changed. i.e. [sv_lan 0 is set by default] 1. Startup server 2. Run http.Fetch 3. sv_lan 1 4. changelevel 5. Run http.Fetch again 6. Crash The same also happens in reverse (1 -> 0). The reason for the change in sv_lan was because I had it in server.cfg and not autoexec.cfg. Setting sv_lan in server.cfg does not work until changelevel.[/QUOTE] So this issue is specific to HTTP and not MySQLoo or any 3rd party binary module stuff?
  • [QUOTE=Willox;52093457]So this issue is specific to HTTP and not MySQLoo or any 3rd party binary module stuff?[/QUOTE] Yeah, I didn't initially look into the root cause and just saw that it happened when MySQLOO was installed, which is why I reported it here. It seems to affect all uses of HTTP, which MySQLOO happens to use for its update checks. That said, it's still a regression in GMod (it doesn't happen on the main branch) but I'll report it over on garrysmod-issues if you want.
  • Avatar of Rubat
  • [QUOTE=Bo98;52093848]Yeah, I didn't initially look into the root cause and just saw that it happened when MySQLOO was installed, which is why I reported it here. It seems to affect all uses of HTTP, which MySQLOO happens to use for its update checks. That said, it's still a regression in GMod (it doesn't happen on the main branch) but I'll report it over on garrysmod-issues if you want.[/QUOTE] Looking into it atm Edit: Should be fixed in Dev and Pre-Release, sorry about that
  • Avatar of bigdogmat
  • [code] local co = coroutine.create(function() --local q = database:query "SELECT 5 + 5" --q:start() end) coroutine.resume(co) [/code] I'm not sure if this has anything to do with the module, but for whatever reason uncommenting the above 2 lines cause the `coroutine.resume` call to error with [quote] bad argument #1 to 'resume' (table expected, got thread) [/quote] [editline]13th April 2017[/editline] I've heard coroutines have issues with modules, so I'm assuming that's the case here.
  • Avatar of bigdogmat
  • Last update added ILuaBase->SetState, so coroutine issue can be fixed. Base module changes [URL="https://github.com/Facepunch/gmod-module-base/tree/development"]here[/URL]
  • Avatar of Fredy
  • I don't see how I am supposed to use that function to solve the coroutine issue. I really don't want to save the lua_state in a variable since that seems incredibly bad to me.
  • Avatar of bigdogmat
  • As far as I understood, the reason it didn't work was because the ILuaBase was using the wrong Lua state. Last update added ILuaBase->SetState which allowed the state to be corrected as seen in the LUA_FUNCTION define.
  • Avatar of Fredy
  • [QUOTE=bigdogmat;52129553]As far as I understood, the reason it didn't work was because the ILuaBase was using the wrong Lua state. Last update added ILuaBase->SetState which allowed the state to be corrected as seen in the LUA_FUNCTION define.[/QUOTE] The LUA_FUNCTION macro seems to not work with static class functions. Unfortunately pretty much all of the lua functions in my module are static class functions, so I can't use the macro without having to rewrite [B][U]a lot[/U][/B] of code. I refuse to restructure my code just because the macro was only intended for C style programming (even though it's for C++....). I might come up with a better (own) solution at some point, but I don't see it as a high priority right now.
  • Avatar of Bings
  • Im fresh outta the box when it comes to MYSQLOO (I know basic SQL though im not that hopeless :v:) but im trying to make a prepared query but ive ran into a problem with dates Far as I can tell setDate doesn't exist and can't find an easy alternative - wondering if anyone knows what I can do as an alternative? or is there nothing that can be done? Example of what on earth im on about [CODE] local preparedQuery = db:prepare("INSERT INTO table (`date`) VALUES(?)") preparedQuery:setDate(1, '2017-04-27') -- (not sure if how ive formatted the date - but it doesn't get that far anyway) preparedQuery:start() [/CODE] And the error just in case you want a peaky [CODE] [ERROR] addons/folder/lua/autorun/server/sql.lua:46: attempt to call method 'setDate' (a nil value) 1. unknown - addons/folder/lua/autorun/server/sql.lua:46 [/CODE]
  • Avatar of Bings
  • [QUOTE=bigdogmat;52156910]Do [code] preparedQuery:setString(1, "2017-04-27") [/code][/QUOTE] Thanks for that :)
  • Avatar of bigdogmat
  • Seems making a query wait inside the `onConnected` callback causes the `onConnected` callback itself to be called. If the code below is used you'll see it print out 1 continually. [code] function database:onConnected() print(1) local query = database:query "SELECT 5" query:start() query:wait() end [/code]
  • Avatar of Fredy
  • [QUOTE=bigdogmat;52157077]Seems making a query wait inside the `onConnected` callback causes the `onConnected` callback itself to be called. If the code below is used you'll see it print out 1 continually. [code] function database:onConnected() print(1) local query = database:query "SELECT 5" query:start() query:wait() end [/code][/QUOTE] Yeah I found the cause. Thanks for pointing that out. I will release a fixed version soonish.