1. Post #81
    syl0r's Avatar
    January 2012
    423 Posts
    Yeah. I did use wget though and chmod +x on it. Do you think that may be why?
    When using wget you have to use the direct link (https://github.com/syl0r/MySQLOO/raw...qloo_linux.dll)
    Otherwise it just downloads an html file which is obviously why it doesn't work.

  2. Post #82

    January 2012
    22 Posts
    Nevermind me! I fixed it. The issue was when I initially downloaded the files through wget, I didn't specify the raw URL. It downloaded html instead.

    I retried it with the so.18.3.0, but it only worked with the so.18, so I used that one.

    Linux install for those who also might want it, where server_1 is your root folder with srcds_linux:

    Code:
    cd ~/server_1
    wget https://raw.githubusercontent.com/syl0r/MySQLOO/master/MySQL/lib/linux/libmysqlclient.so.18
    chmod +x ./libmysqlclient.so.18
    cd ./garrysmod/lua
    mkdir -p bin
    cd ./bin
    wget https://raw.githubusercontent.com/syl0r/MySQLOO/master/out/linux/gmsv_mysqloo_linux.dll
    chmod +x ./gmsv_mysqloo_linux.dll
    Thanks sly0r

    Edited:

    When using wget you have to use the direct link (https://github.com/syl0r/MySQLOO/raw...qloo_linux.dll)
    Otherwise it just downloads an html file which is obviously why it doesn't work.
    Beat me to it. I'm slow.

  3. Post #83
    YukiTheater.org
    WinterPhoenix's Avatar
    January 2013
    154 Posts
    A few years ago, I used to use MySQLOO 8 for my server (circa 2013), but switched over to tmysql4 after experiencing the utterly depressing performance and lack of stability MySQLOO had at the time.

    I understand that MySQLOO has been greatly rewritten for this version update, so seeing as tmysql4 is no longer actively developed, nor does it have prepared statements or ensured query execution order, in what ways/in what scenarios is MySQLOO 9 slower than tmysql4? Furthermore, how stable is it as it stands right now?

  4. Post #84
    syl0r's Avatar
    January 2012
    423 Posts
    You should never notice any speed difference between mysqloo and tmysql except for the fact that tmysql used two connections per database instance (which is a bad thing).

    The current mysqloo9 version should be perfectly stable. I run it on my own server since ~1 year and I haven't noticed any crashes caused by it. I will probably release an update to the module today or tomorrow that is going to change a lot of things (while maintaining full backwards compatibility) but even that should be mostly stable.
    If you do however notice any crashes that you think might be related to this module feel free to post about it in this thread.
    Reply With Quote Edit / Delete Reply Windows 10 Chrome Germany Show Events Useful Useful x 2Informative Informative x 1Disagree Disagree x 1 (list)

  5. Post #85

    January 2017
    7 Posts
    Hey, anyone here who could help me with a little problem with this version of mysqloo?
    I'm not sure if I'm using the database:ping() correctly.. currently I don't see any way to reconnect to a database after the connection is lost while server is on.

    I posted a thread here, please see for further information:
    https://facepunch.com/showthread.php?t=1548244


    Thanks!

  6. Post #86
    syl0r's Avatar
    January 2012
    423 Posts
    Hey, anyone here who could help me with a little problem with this version of mysqloo?
    The problem is that mysqloo does not attempt to automatically reconnect to the database if the database connection failed initially. This is because mysqloo doesn't know that the connection can even be established successfully (i.e. wrong credentials) so it would be a problem to try to automatically reconnect even in those cases.
    To fix your problem you have to recreate the db using mysqloo.connect() if the connection fails.


    I also just released a new update that changes many things, the most important being that you can reuse query objects and start them multiple times:
    local query = db:query("UPDATE counter_tbl SET counter = counter + 1")
    query:start()
    query:start()
    

    This is especially useful for prepared queries as they can reuse the statement handle allocated on the database server:
    local query = db:prepare("INSERT INTO logs (`message`) VALUES(?)")
    query:setString(1, "hello")
    query:start()
    query:setString(2, "world")
    query:start()
    

    It even calls the callbacks you'd expect every time as long as you always set the callbacks before you start the query.
    local ply1 = player.GetAll()[1]
    local query = db:prepare("SELECT rank FROM users WHERE steamid = ?")
    query:setString(1, ply1:SteamID())
    function query:onSuccess(data)
    	//This will set the rank of ply1
    	ply1:SetRank(data[1].rank)
    end
    query:start()
    local ply2 = player.GetAll()[2]
    query:setString(1, ply2:SteamID())
    function query:onSuccess(data)
    	//This will set the rank of ply2
    	ply2:SetRank(data[1].rank)
    end
    query:start()
    
    Reply With Quote Edit / Delete Reply Windows 10 Chrome Germany Show Events Winner Winner x 6 (list)

  7. Post #87
    bigdogmat's Avatar
    May 2014
    694 Posts
    The update notes said that prepared queries can now return multiple result sets, however when trying to do something such as

    Code:
    local a = database:prepare "SELECT 1, 2; SELECT 3, 4"
    
    function a:onError(err, sql)
      MsgN(err, "\n\n", sql)
    end
    
    function a:onSuccess(data)
    
      while self:hasMoreResults() do
        PrintTable(data)
        data = self:getNextResults()
      end
    end
    
    a:start()
    I get

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 3, 4' at line 1

    SELECT 1, 2; SELECT 3, 4

  8. Post #88
    syl0r's Avatar
    January 2012
    423 Posts
    Prepared statements can only ever represent one statement (hence the name).
    You can still get multiple result sets without having multiple statements by calling a stored procedure.
    So I would recommend either creating a stored procedure that does what you want or to use two prepared queries in a transaction.

  9. Post #89
    bigdogmat's Avatar
    May 2014
    694 Posts
    Prepared statements can only ever represent one statement (hence the name).
    You can still get multiple result sets without having multiple statements by calling a stored procedure.
    Ah, thanks for the information, also, probably worth noting ErrorNoHalt needs a newline on the end for callback errors.

  10. Post #90

    January 2017
    7 Posts
    The problem is that mysqloo does not attempt to automatically reconnect to the database if the database connection failed initially. This is because mysqloo doesn't know that the connection can even be established successfully (i.e. wrong credentials) so it would be a problem to try to automatically reconnect even in those cases.
    To fix your problem you have to recreate the db using mysqloo.connect() if the connection fails.
    I tried adding these two lines of code to my db:onConnectionFailed timer:

    db = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
    db:connect()
    
    It still keeps printing "false" with the db:ping() all the time while my databse is online and I can access it fine on phpMyAdmin. (And so can the server if it's started while database is already online)

  11. Post #91
    syl0r's Avatar
    January 2012
    423 Posts
    Then you must have wrong credentials. Try printing the error message.

  12. Post #92

    January 2017
    7 Posts
    Then you must have wrong credentials. Try printing the error message.
    This is my unchanged code. Credentials are correct because it connects successfully if I start the server while database is already online.

    The error message from db:onConnectionFailed is always the same, whether my databse is online or offline.
    "Can't connect to MySQL server on 'localhost'"

    Also I'll just clarify again, this only occurs if I start server while database is offline. Otherwise it works.

    require ("mysqloo")
    
    local DATABASE_HOST = "localhost"
    local DATABASE_PORT = 3306
    local DATABASE_NAME = "bleur"
    local DATABASE_USERNAME = "root"
    local DATABASE_PASSWORD = "pass"
    
    
    local db = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
    
    function db:onConnected( )
    		if timer.Exists("pingdb") then
    			timer.Remove("pingdb")
    		end
    		MsgC( Color( 255, 255, 0 ), "Successfully connected to the MySQL Database\n" )
    end
    
    function db:onConnectionFailed( err )
    		MsgC( Color( 255, 255, 0 ), "Connection to the MySQL Database failed\nError: ", err, "\n")
    		timer.Create("pingdb", 5, 0, function()
    			print("Retrying connection to database...")
    			db = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
    			db:connect()
    			db:ping()
    			print (db:ping(),"\n")
    		end)
    end
    
    db:connect()
    

  13. Post #93
    syl0r's Avatar
    January 2012
    423 Posts
    The problem is that you are creating a new database instance but you aren't setting the callbacks again. This will mean onConnected will not be called and your timer will just retry every 5 seconds.
    Additionally db:ping() does only work _after_ the connection has been established. Since db:connect() doesn't wait for the connection to finish db:ping() will probably run before that and return false every time.

  14. Post #94

    January 2017
    7 Posts
    The problem is that you are creating a new database instance but you aren't setting the callbacks again. This will mean onConnected will not be called and your timer will just retry every 5 seconds.
    Additionally db:ping() does only work _after_ the connection has been established. Since db:connect() doesn't wait for the connection to finish db:ping() will probably run before that and return false every time.
    Thank you!

  15. Post #95
    Known to all as "ARitz Cracker"
    LegoGuy's Avatar
    March 2009
    2,308 Posts
    So I'm working on the next update for my addon. I set up a MySQL database on my home server, and this query is erroring.
    Code:
    CREATE TABLE IF NOT EXISTS arcbank_log
    (
    transaction_id UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    account1 varchar(255) NOT NULL,
    account2 varchar(255) NOT NULL,
    user1 varchar(255) NOT NULL,
    user2 varchar(255) NOT NULL,
    moneydiff INT NOT NULL,
    money BIGINT,
    transaction_type UNSIGNED SMALLINT NOT NULL,
    comment varchar(255) NOT NULL
    );
    The error is
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    acc' at line 3
    So, for some reason my query is getting truncated. Why would that be?

  16. Post #96

    December 2012
    942 Posts
    So I'm working on the next update for my addon. I set up a MySQL database on my home server, and this query is erroring.
    Code:
    CREATE TABLE IF NOT EXISTS arcbank_log
    (
    transaction_id UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    account1 varchar(255) NOT NULL,
    account2 varchar(255) NOT NULL,
    user1 varchar(255) NOT NULL,
    user2 varchar(255) NOT NULL,
    moneydiff INT NOT NULL,
    money BIGINT,
    transaction_type UNSIGNED SMALLINT NOT NULL,
    comment varchar(255) NOT NULL
    );
    The error is
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    acc' at line 3
    So, for some reason my query is getting truncated. Why would that be?
    I am pretty sure its an error from the MySQL server, not from the module, so you better go to a mysql support forum rather than here.

  17. Post #97
    Mista Tea's Avatar
    May 2012
    586 Posts
    The error is
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    acc' at line 3
    So, for some reason my query is getting truncated. Why would that be?
    Have you tried putting the UNSIGNED attribute after the data type, rather than before?

    That's how the syntax looks like it's supposed to be based on sections like https://dev.mysql.com/doc/refman/5.7...-overview.html
    Reply With Quote Edit / Delete Reply Windows 10 Chrome United States Show Events Friendly Friendly x 1Agree Agree x 1 (list)

  18. Post #98
    So I'm working on the next update for my addon. I set up a MySQL database on my home server, and this query is erroring.
    Code:
    CREATE TABLE IF NOT EXISTS arcbank_log
    (
    transaction_id UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    account1 varchar(255) NOT NULL,
    account2 varchar(255) NOT NULL,
    user1 varchar(255) NOT NULL,
    user2 varchar(255) NOT NULL,
    moneydiff INT NOT NULL,
    money BIGINT,
    transaction_type UNSIGNED SMALLINT NOT NULL,
    comment varchar(255) NOT NULL
    );
    The error is
    Code:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNSIGNED BIGINT NOT NULL AUTO_INCREMENT,
    timestamp UNSIGNED BIGINT NOT NULL,
    acc' at line 3
    So, for some reason my query is getting truncated. Why would that be?
    UNSIGNED needs to be after your data types, also I believe you'll need to set a primary key (transaction_id)

    Edit: I'm not sure how I didn't realize you answered before me Thomas, I swear I didn't see it
    Reply With Quote Edit / Delete Reply Windows 10 Chrome United States Show Events Friendly Friendly x 2 (list)

  19. Post #99
    Known to all as "ARitz Cracker"
    LegoGuy's Avatar
    March 2009
    2,308 Posts
    You are all correct! :)
    And yes, I needed to set the primary key too. While I was at it I set the charset to utf8.

  20. Post #100
    Luni's Avatar
    July 2012
    1,112 Posts


    Fuck me, somehow I overwrite the v9 binary with v8

  21. Post #101
    syl0r's Avatar
    January 2012
    423 Posts
    That seems to be a problem with all binary modules (at least the ones I tested).
    Besides that I don't see any point using mysqloo in a coroutine since all of its functions are async anyways (except for query:wait()).
    Reply With Quote Edit / Delete Reply Windows 10 Chrome Germany Show Events Informative Informative x 1 (list)

  22. Post #102
    Luni's Avatar
    July 2012
    1,112 Posts


    Fuck me, somehow I overwrite the v9 binary with v8

  23. Post #103
    syl0r's Avatar
    January 2012
    423 Posts
    To easily chain queries you should use transactions.
    Even in your example I don't see how the code is made any nicer by coroutines.

  24. Post #104
    Luni's Avatar
    July 2012
    1,112 Posts
    To easily chain queries you should use transactions.
    Even in your example I don't see how the code is made any nicer by coroutines.
    Fair enough, I've been working with code that was originally designed for v7 or v8. Those don't seem to work as well if you have to do any work in Lua between queries, but I need to spend an afternoon doing code overhauls before I can really say anything.

    The dbal code itself doesn't get any nicer, I'll agree, but the intent was to make the gamemode that uses it cleaner -- run it in a coroutine and call db:Query() as much as you like instead of creating a whole bunch of local callbacks and chaining them together. Looks like I'll have to spend some time tinkering.

    edit: turns out I accidentally overwrote v9 with v8 at some point, fuck my life

  25. Post #105
    Gold Member
    TomatoSoup's Avatar
    December 2006
    287 Posts
    I'm inexperienced in the details of how a program communicates with its database. Would I be correct to assume that this cannot communicate with postgres? Would I be correct in assuming this *can* communicate with MariaDB? I'm looking to reorganize my system and move my MySQL database and I'd like to go to postgres but I recognize it's very possible that won't work, so I'm prepared to move to MariaDB.

  26. Post #106
    syl0r's Avatar
    January 2012
    423 Posts
    You'd be correct. MariaDB is mostly compatibly with mysql (as it is a fork of mysql). Postgresql on the other hand is very different than mysql and they do not use the same protocol.

  27. Post #107
    Gold Member
    TomatoSoup's Avatar
    December 2006
    287 Posts
    Right. On one hand I was thinking "Why not? It's the same SQL query and it's not likely anybody in gmod is using any fancy features" but then I remembered the dependency on libmysql and I figured there was some deeper shenanigans going on.

  28. Post #108
    FrankPetrov's Avatar
    May 2010
    1,721 Posts
    I'm probably going to be laughed at, but how difficult would it be to convert and compile the source to 64bit? Before people start on about source not being 64bit, I'm not using it for gmod.

  29. Post #109
    syl0r's Avatar
    January 2012
    423 Posts
    It should be quite easy since I don't believe I am using anything specific to 32 bits. Of course you need to change all libraries to be 64 bit, but that should be doable.
    If you aren't using it with gmod, then you'd also have to replace the gmod lua headers with something that emulates gmod's behaviour. If you don't want to do that yourself feel free to pm me, I can provide you with one that should work.

  30. Post #110

    February 2017
    1 Posts
    Hi im having errors with mysqloo. Even though i installed both the binary file and the lib file, im still having errors with darkrp not recognising the module. here is the error message i received. Any help will be appriciated

    ERROR] gamemodes/darkrp/gamemode/libraries/mysqlite/mysqlite.lua:131: Could not find a suitable MySQL module. Supported modules are MySQLOO and tmysql4.

    1. error - [C]:-1

    2. loadMySQLModule - gamemodes/darkrp/gamemode/libraries/mysqlite/mysqlite.lua:131

    3. unknown - gamemodes/darkrp/gamemode/libraries/mysqlite/mysqlite.lua:144

    4. include - [C]:-1

    5. unknown - gamemodes/darkrp/gamemode/init.lua:42

    6. DeriveGamemode - [C]:-1

  31. Post #111
    syl0r's Avatar
    January 2012
    423 Posts
    That means you didn't place the file in the correct location, it has to go in the lua/bin folder.

  32. Post #112
    bigdogmat's Avatar
    May 2014
    694 Posts
    Seems Query:error() returns no value even when the query has errored
    Reply With Quote Edit / Delete Reply Windows 10 Chrome United States Show Events Agree Agree x 1 (list)

  33. Post #113
    syl0r's Avatar
    January 2012
    423 Posts
    Seems Query:error() returns no value even when the query has errored
    I fixed it, it could've even crashed the server.
    Thanks for pointing it out.
    Reply With Quote Edit / Delete Reply Windows 10 Chrome Germany Show Events Winner Winner x 1 (list)

  34. Post #114
    bigdogmat's Avatar
    May 2014
    694 Posts
    Now it works when used inside of the onError called for queries, however it seems when the query is apart of a transaction it doesn't.

    e.g.
    Code:
    local t = database:createTransaction()
    
    local q = database:query "yes"
    t:addQuery(q)
    
    function t:onSuccess()
      print "ok"
    end
    
    function t:onError(err)
      print(self:getQueries()[1]:error())
    end
    
    t:start()
    Not really needed considering we already have the error from the callback, though it'd be useful in figuring out which query caused the error.

  35. Post #115
    syl0r's Avatar
    January 2012
    423 Posts
    Now it works when used inside of the onError called for queries, however it seems when the query is apart of a transaction it doesn't.
    Should be fixed.
    Reply With Quote Edit / Delete Reply Windows 10 Chrome Germany Show Events Winner Winner x 1 (list)

  36. Post #116
    Gold Member
    Revenge282's Avatar
    July 2007
    2,304 Posts
    Say I have a function to take money from a player. The function returns true or false to indicate whether the money was actually taken.

    local moneyquery = db:prepare("UPDATE users SET money = ? WHERE steamid = ?")
    
    function PLAYER:TakeMoney(amt)
    	if self.Money < amt then return false end
    	local x = self.Money - amt
    
    	moneyquery:setNumber(1,x)
    	moneyquery:setString(2,self:SteamID64())
    	moneyquery:start()
    
    	local status = false
    
    	function moneyquery:onSuccess() --TakeMoney should return true now because the money operation was successful
    		self.Money = x
    		status = true
    	end
    
    	function moneyquery:onError(e) --TakeMoney should return false now because the money operation failed at the database
    		status = false
    	end
    
    	return status
    end

    This is just an example I thought of while I'm at work, but is there a better way to accomplish what I am trying to do?

  37. Post #117
    -snip-
    code_gs's Avatar
    March 2013
    16,757 Posts
    Say I have a function to take money from a player. The function returns true or false to indicate whether the money was actually taken.

    local moneyquery = db:prepare("UPDATE users SET money = ? WHERE steamid = ?")
    
    function PLAYER:TakeMoney(amt)
    	if self.Money < amt then return false end
    	local x = self.Money - amt
    
    	moneyquery:setNumber(1,x)
    	moneyquery:setString(2,self:SteamID64())
    	moneyquery:start()
    
    	local status = false
    
    	function moneyquery:onSuccess() --TakeMoney should return true now because the money operation was successful
    		self.Money = x
    		status = true
    	end
    
    	function moneyquery:onError(e) --TakeMoney should return false now because the money operation failed at the database
    		status = false
    	end
    
    	return status
    end

    This is just an example I thought of while I'm at work, but is there a better way to accomplish what I am trying to do?
    Queries take time. You can't have a simple return like that unless you want to halt the entire server until it's done.

  38. Post #118
    syl0r's Avatar
    January 2012
    423 Posts
    The function you wrote will always return false because mysqloo is asynchronous.
    This means that the moneyquery:start() call returns immediately and the callbacks are called at some point later (but definitely after you return the status).

    You have three options:
    1. Use query:wait(): This will pause the gmod server until the query completed, and the callbacks have been called. This is only really a possibility if the database is hosted on the same machine because otherwise it is going to cause some major lag and will leave you vulnerable to DOS attacks.

    2. Design your code in a way that works with the queries being asynchronous:
    An example of this would be adding a callback to your TakeMoney function that is called from one of the query callbacks:
    function PLAYER:TakeMoney(amt, callback)
    	if self.Money < amt then return false end
    	local x = self.Money - amt
    	moneyquery:setNumber(1,x)
    	moneyquery:setString(2,self:SteamID64())
    	function moneyquery:onSuccess() 
    		self.Money = x
    		callback(true)
    	end
    
    	function moneyquery:onError(e)
    		callback(false)
    	end
    	moneyquery:start()
    end
    
    Unfortunatley this might make your code a bit more verbose and is generally not as easy to do, but it's the safer option.

    3. You could ignore the possibility of anything going wrong because in almost all cases nothing will go wrong (unless the db goes down). This is of course not safe at all but might unclutter your code by a lot.

    Personally I am using a mix of approach 2. and 3. but ideally you should always go for 2.

    On a sidenote: you should always assign the callbacks to the query before you start the query, especially when you are reusing the query. Otherwise mysqloo might call the wrong callback.
    Reply With Quote Edit / Delete Reply Windows 10 Chrome Germany Show Events Informative Informative x 2Agree Agree x 1 (list)

  39. Post #119
    Mista Tea's Avatar
    May 2012
    586 Posts
    On Windows, trying to escape seemingly anything other than a string will crash the server (i.e. numbers, tables, entities, and particularly nil). I'm not exactly sure where the exception is getting thrown, since it seems like the escape method is handling it, but SRCDS crashes immediately afterward:

    Code:
    > db:escape()...
    
    [ERROR] lua_run:1: bad argument #1 to 'escape' (string expected, got no value)
      1. unknown - [C]:-1
       2. escape - [C]:-1
        3. unknown - lua_run:1
    In most cases this isn't much of an issue, but accidentally trying to escape nil (as might occur in certain scenarios) will lead to a lot of unexplainable crashes to the end-user. Having it only error in the Lua state would be much more preferable.

  40. Post #120
    Willox said I can put anything in here, so I've put
    *meow*
    Willox's Avatar
    December 2009
    2,912 Posts
    On Windows, trying to escape seemingly anything other than a string will crash the server (i.e. numbers, tables, entities, and particularly nil). I'm not exactly sure where the exception is getting thrown, since it seems like the escape method is handling it, but SRCDS crashes immediately afterward:

    Code:
    > db:escape()...
    
    [ERROR] lua_run:1: bad argument #1 to 'escape' (string expected, got no value)
      1. unknown - [C]:-1
       2. escape - [C]:-1
        3. unknown - lua_run:1
    In most cases this isn't much of an issue, but accidentally trying to escape nil (as might occur in certain scenarios) will lead to a lot of unexplainable crashes to the end-user. Having it only error in the Lua state would be much more preferable.
    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 here 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.
    Reply With Quote Edit / Delete Reply Windows 10 Chrome United Kingdom Show Events Informative Informative x 2 (list)