1. Post #1
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Gm_MySQL v7 - OO edition

    I was surprised when I came back to Facepunch & Garry's Mod (after nearly a year off) that people were still using my old MySql bindings. Having fresh inspiration, I set about re-writing it and coming up with a nicer way of doing things.

    Crashing/Problems?
    [list][*]Please give me as much information as possible:
    [list][*]Test scripts / Example code[*]MDMP files[/list][*]Make sure you're running the most up-to-date version[/list]
    Changes
    [list][*]v7
    [list][*]Added calls to mysql_library_init/mysql_library_end[*](Hopefully) fixed crash that could occur randomly[*]Updated to version 6.0.2 of libmysql[*]Fixed problem with on Linux that would stop queries from running[/list][*]v6
    [list][*]Fixed a potential problem with the Linux DLLs[*]Fixed a problem with the Garbage Collection (thanks haza55)[/list][*]v5.1
    [list][*]Linux binaries now available[/list][*]v5
    [list][*]Finally tracked down the crash (it was due to libmysql.dll, so downgraded to 5.0). Thanks to everyone who helped.[*]Added version code (mysqloo.VERSION, mysqloo.MYSQL_VERSION, mysqloo.MYSQL_INFO)[*]Added server version (Database:serverVersion, Database:serverInfo, Database:hostInfo)[/list][*]v4
    [list][*]Attempt to fix the crash problems that people are having...[/list][*]v3
    [list][*]Added function: Database:wait() - blocks until connection has been completed.[*]Added function: Query:wait() - blocks until query has been completed (and all data read back).[/list][*]v2
    [list][*]Bug fix - objects would be garbage collected whilst still running threads. This would show up more often if you were attempting to connect to a server that did not exist.[/list][*]v1
    [list][*]Initial release[/list][/list]
    Example
    require("mysqloo")
    
    local DATABASE_HOST = "localhost"
    local DATABASE_PORT = 3308
    local DATABASE_NAME = "test"
    local DATABASE_USERNAME = "root"
    local DATABASE_PASSWORD = "1234"
    
    function printQuery(query)
      PrintTable(query:getData())
    end
    
    function afterConnected(database)
      local query1 = database:query("SELECT ID, Name, Cost FROM test WHERE Cost > 0.50")
      query1.onData = function(Q,D) print("Q1") PrintTable(D) end
      query1.onSuccess = printQuery
      query1.onError = function(Q,E) print("Q1") print(E) end
      query1:start()
    
      local query2 = database:query("SELECT ID, Name, Cost FROM test")
      query2.onData = function(Q,D) print("Q2") PrintTable(D) end
      query2.onError = function(Q,E) print("Q1") print(E) end
      query2:start()
    end
    
    function connectToDatabase()
      local databaseObject = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
      databaseObject.onConnected = afterConnected
      databaseObject:connect()
    end
    
    connectToDatabase()

    Usage
    Basic functionality:
    [list][*]mysqloo.connect - Create a new database connection
    Parameters: (hostname, username, password, database, port,
    unix socket, client flag)
    Returns: Database object[/list]Database object:
    [list][*]Database:connect - Start connecting to the database
    Parameters: ()
    Returns: Nothing[*]Database:escape - Make text safe for queries
    Parameters: (text)
    Returns: Escaped string[*]Database:abortAllQueries - Aborts all running queries
    Parameters: ()
    Returns: Nothing[*]Database:status - Check database status
    Parameters: ()
    Returns: One of:
    [list][*]mysqloo.DATABASE_CONNECTED - (0) - Connected to the database.[*]mysqloo.DATABASE_CONNECTING - (1) - Current is attempting to connect[*]mysqloo.DATABASE_NOT_CONNECTED - (2) - Connection was not successful or has failed[*]mysqloo.DATABASE_INTERNAL_ERROR - (3) - Some internal error occured[/list][*]Database:query - Query the database
    Parameters: (query)
    Returns: Query object[*]Database:onConnected - Callback when connected successfully
    Parameters: ()
    Returns: Nothing[*]Database:onConnectionFailed - Callback when connection could not be established.
    Parameters: (error_message)
    Returns: Nothing[/list]Query object:
    [list][*]Query:start - Start a query running
    Parameters: ()
    Returns: Nothing[*]Query:abort - Abort a query running
    Parameters: ()
    Returns: Nothing[*]Query:getData - Get all data from a query
    Parameters: ()
    Returns: Table containing all the data that has been read so far[*]Query:setOption - Change query options
    Parameters: (Flag, set)
    Flag is one of:
    [list][*]mysqloo.OPTION_NUMERIC_FIELDS - (1) - Use numeric fields when presenting data [default:off][*]mysqloo.OPTION_NAMED_FIELDS - (2) - Use named fields when presenting data [default:on][*]mysqloo.OPTION_INTERPRET_DATA - (4) - Auto-convert int/float fields to int/float representation [default:on][*]mysqloo.OPTION_CACHE - (8) - Cache the data after reading it [default:on][/list]Returns: Nothing[*]Query:status - Check query status
    Parameters: ()
    Returns: One of:
    [list][*]mysqloo.QUERY_NOT_RUNNING - (0) - Query has not been started yet.[*]mysqloo.QUERY_RUNNING - (1) - The query is executing on the database.[*]mysqloo.QUERY_READING_DATA - (2) - The query has been run, and is now reading the data back.[*]mysqloo.QUERY_COMPLETE - (3) - The query has completed (successfully or with an error).[*]mysqloo.QUERY_ABORTED - (4) - The query has been aborted.[/list][*]Query:onData - Callback when a single row has been received
    Parameters: (data_row)
    Returns: Nothing[*]Query:onSuccess - Callback when all data has been received
    Parameters: ()
    Returns: Nothing[*]Query:onFailure - Callback when an error occured
    Parameters: (error_message)
    Returns: Nothing[*]Query:onAborted - Callback when a query was aborted
    Parameters: ()
    Returns: Nothing[/list]
    Installation
    [list][*]Extract the zip file to your garrysmod\addons directory (ie: c:\steam\steamapps\username\garrysmod\garrysmod\ad dons)[*]Take the libmySQL.dll, and put it in the same directory as HL2.EXE (ie: c:\steam\steamapps\username\garrysmod)[*]Move the files gmsv_mysqloo.dll and gmcl_mysqloo.dll to the garrysmod\lua\includes\modules folder.[/list]
    Downloads
    [list][*]Binaries
    Windows
    Linux[*]Source Code[/list]
    Reply With Quote Edit / Delete Show Events Useful x 34Lua King x 18Winner x 3Lua Helper x 2Friendly x 2Dumb x 2Agree x 1Funny x 1Informative x 1 (list)

  2. Post #2
    Gold Member
    maurits150's Avatar
    February 2007
    1,818 Posts
    I'm sure a lot of people would love this. Thanks a ton.
    Reply With Quote Edit / Delete Netherlands Show Events Agree Agree x 7Disagree Disagree x 2 (list)

  3. Post #3
    Taught by John Lua
    MakeR's Avatar
    May 2007
    2,914 Posts
    Awesome stuff.
    Reply With Quote Edit / Delete United Kingdom Show Events Agree Agree x 2Late Late x 1 (list)

  4. Post #4
    Minecraft Servers
    Jake1305's Avatar
    August 2007
    167 Posts
    Awesome.

    Linux binary's? Because of the GMod Lin Bins today?
    Reply With Quote Edit / Delete United Kingdom Show Events Agree Agree x 3Dumb Dumb x 2 (list)

  5. Post #5
    I'm Better Than You
    Handsome Matt's Avatar
    August 2008
    5,532 Posts
    Ah metatables! Good work!
    Reply With Quote Edit / Delete United Kingdom Show Events

  6. Post #6
    Gold Member

    April 2009
    726 Posts
    Awesome. Gonna update my logs with this!
    Reply With Quote Edit / Delete Denmark Show Events Agree Agree x 1 (list)

  7. Post #7
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Awesome.

    Linux binary's? Because of the GMod Lin Bins today?
    I don't have any means of compiling on Linux at the moment... Plus some code needs to be written to handle the Linux side of things with regard to threading. If anyone wants to contribute, let me know - I suppose you can via GitHub anyway.
    Reply With Quote Edit / Delete Show Events

  8. Post #8
    Minecraft Servers
    Jake1305's Avatar
    August 2007
    167 Posts
    I don't have any means of compiling on Linux at the moment... Plus some code needs to be written to handle the Linux side of things with regard to threading. If anyone wants to contribute, let me know.
    Alright', glad to see you back and things back on track.

    Not sure if I'll switch from tmysql yet, I'll look into it, I'll post back later if I run into problems with it.
    Reply With Quote Edit / Delete United Kingdom Show Events Friendly Friendly x 1Agree Agree x 1 (list)

  9. Post #9
    Mistur Man's Avatar
    July 2009
    74 Posts
    Yay! Thank's alot!
    Reply With Quote Edit / Delete United Kingdom Show Events

  10. Post #10
    Creator of Exsto
    Prefan's Avatar
    January 2009
    1,049 Posts
    There seems to be a bug. Every time I try to connect to a database that is down, Garry's Mod crashes.
    Reply With Quote Edit / Delete United States Show Events

  11. Post #11
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    There seems to be a bug. Every time I try to connect to a database that is down, Garry's Mod crashes.
    Thanks. Updated with version 2, please see the first post!
    Reply With Quote Edit / Delete Show Events

  12. Post #12
    Creator of Exsto
    Prefan's Avatar
    January 2009
    1,049 Posts
    Awesome, thanks.

    Edited:

    Crashed again, after doing some testing while inserting 200 entries into a sql table.

    mdmp if it helps, http://dl.dropbox.com/u/717734/mysqlOO.mdmp.
    Reply With Quote Edit / Delete United States Show Events

  13. Post #13
    Creator of Exsto
    Prefan's Avatar
    January 2009
    1,049 Posts
    Also, if this isn't much, I have a request.

    Would it be possible to create a non-threaded version of this module? I know I might sound crazy, but I designed my code around the non-threaded version of the old module, and it is impossible to move towards a threaded version. I think I'm going to stick with the old module for now, but eventually it would be nice to switch to this.
    Reply With Quote Edit / Delete United States Show Events Lua Helper Lua Helper x 1 (list)

  14. Post #14
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Also, if this isn't much, I have a request.

    Would it be possible to create a non-threaded version of this module? I know I might sound crazy, but I designed my code around the non-threaded version of the old module, and it is impossible to move towards a threaded version. I think I'm going to stick with the old module for now, but eventually it would be nice to switch to this.
    Would it be possible to get a copy of your test code that causes the crash? This would help a lot. Thanks.

    I'm not sure about a non-threaded version, I would like to take a look at your code and see how you would adapt it be thread-capable.
    Reply With Quote Edit / Delete Show Events Late Late x 1Dumb Dumb x 1 (list)

  15. Post #15
    infinitywrai's Avatar
    December 2007
    539 Posts
    Interesting take on callbacks that I'm sure a lot of people would like, very similar to Azu's gm_tmysql2 that I use.
    Reply With Quote Edit / Delete United States Show Events

  16. Post #16
    MegaDeath's Avatar
    August 2007
    93 Posts
    Have a wrench good sir!
    Reply With Quote Edit / Delete United States Show Events Agree Agree x 2Lua Helper Lua Helper x 1Friendly Friendly x 1Late Late x 1 (list)

  17. Post #17
    SchumacherAlt's Avatar
    May 2010
    501 Posts
    Good to see you back, Andy, wonderful release.
    Reply With Quote Edit / Delete United States Show Events Agree Agree x 1 (list)

  18. Post #18
    MegaDeath's Avatar
    August 2007
    93 Posts
    Weird, I set a variable to the DBObject in the connection function, but whenever i go to access it later it has become null? I think the reference counting is glitched...

    Edited:

    I'm gunna switch to the older API till this thing is better tested... Having everything asyncronous is also a bit of a pain when something must be done in order.
    Reply With Quote Edit / Delete United States Show Events

  19. Post #19
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Weird, I set a variable to the DBObject in the connection function, but whenever i go to access it later it has become null? I think the reference counting is glitched...
    Your test code would be helpful!
    Reply With Quote Edit / Delete Show Events

  20. Post #20
    Dennab
    July 2009
    195 Posts
    hl2.exe keeps crashing when i try to connect to the mysql database.
    This is the code i am using
    -- core/sv_mysql.lua
    
    rp.mysql = {}
    
    local host = "localhost"
    local port = 3306
    local name = "rp"
    local user = "root"
    local pass = ""
    
    function rp.mysql.connect()
    
    	databaseObject = mysqloo.connect(host, user, pass, name, port)
    	   databaseObject:connect()                                                                                                                      
    	
    
    end 
    Reply With Quote Edit / Delete Anonymous Proxy Show Events

  21. Post #21
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Are you using the latest version? Is the database setup?

    Edited:

    Version 3 released, see first post!
    Reply With Quote Edit / Delete Show Events Friendly Friendly x 1 (list)

  22. Post #22
    Creator of Exsto
    Prefan's Avatar
    January 2009
    1,049 Posts
    Integrating into all my scripts now, thanks a ton for the wait functions.

    Edited:

    Crashed using the wait functions.

    http://dl.dropbox.com/u/717734/hl2_4...1_31_24C0.mdmp
    Reply With Quote Edit / Delete United States Show Events

  23. Post #23
    Dennab
    July 2009
    195 Posts
    Are you using the latest version? Is the database setup?

    Yes, I am using the latest version, it only crashes when the database is up and running (so the crash is probrably caused by connecting.).
    Reply With Quote Edit / Delete Anonymous Proxy Show Events

  24. Post #24
    Gold Member
    Lexic's Avatar
    March 2009
    6,123 Posts
    Oooh, nice looking module. I might switch to this later.
    Reply With Quote Edit / Delete United Kingdom Show Events

  25. Post #25
    Gold Member
    nicatronTg's Avatar
    July 2009
    4,698 Posts
    I can only imagine the look on your face when you saw the 2006 bindings still being used.

    Thanks a ton, I'll be getting around to MySQL a bit this weekend, I never actually fully integrated the old module.
    Reply With Quote Edit / Delete United States Show Events

  26. Post #26
    Skuch's Avatar
    December 2008
    97 Posts
    My GMod crashes aswell when it connects to the database.

    I don't know whether it's a error in the connection part of the module, or the port handling part. It seems to crash when the port number isn't defined, or it's just plain numbers. But when embedded into a string it borkz out.
    function NS.ConnectSQL()  
    
    	local conn = mysqloo.connect( "127.0.0.1", "root", "", "coke" );
    	conn:connect();
    
    end
    
    NS.ConnectSQL();
    Reply With Quote Edit / Delete Denmark Show Events

  27. Post #27
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Does it actually crash ("HL2.exe has crashed" message), or lock up?

    Edited:

    Whatever I do I cannot make it crash (or lockup)... Please can you verify that you are using the latest version (3) - adding print(mysqloo.VERSION) to your script will tell you the version code (if it prints nil then you are using v1 or v2).

    How is your script started (autorun-client, server or both?)
    Are you using the libmysql.dll that was provided with this download or something else?
    Anything that you can tell me will help :)
    Reply With Quote Edit / Delete Show Events

  28. Post #28
    Dennab
    July 2009
    195 Posts
    Does it actually crash ("HL2.exe has crashed" message), or lock up?

    Edited:

    Whatever I do I cannot make it crash (or lockup)... Please can you verify that you are using the latest version (3) - adding print(mysqloo.VERSION) to your script will tell you the version code (if it prints nil then you are using v1 or v2).

    How is your script started (autorun-client, server or both?)
    Are you using the libmysql.dll that was provided with this download or something else?
    Anything that you can tell me will help :)
    My hl2.exe when I start a listen server,there are also crashes when using the v3, but to be shure that i have v3 I redownloaded it. libmysql is where it shoud be (steam/steamapps/username/garrysmod/).

    The script is in my gamemode and its loaded serverside in its own file.

    I am not using any other mysql modules.
    Reply With Quote Edit / Delete Anonymous Proxy Show Events

  29. Post #29
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Can you re-create the crash with the example code (modify it for your tables and database settings)?
    Reply With Quote Edit / Delete Show Events

  30. Post #30
    Dennab
    July 2009
    195 Posts
    Can you re-create the crash with the example code (modify it for your tables and database settings)?
    yes i can
    Reply With Quote Edit / Delete Anonymous Proxy Show Events

  31. Post #31
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    It should be producing MDMP files in the directory that contains HL2.EXE. Could you send me the one it produces? Thanks.
    Reply With Quote Edit / Delete Show Events

  32. Post #32
    Nemesis036's Avatar
    April 2010
    287 Posts
    thank you

    how could i have it so that on player first joining the server, it stores their name/steamid in a table?
    Reply With Quote Edit / Delete United Kingdom Show Events Dumb Dumb x 1 (list)

  33. Post #33
    quality poster
    Dennab
    August 2009
    12,242 Posts
    Hehe, thanks a lot for this, I was just about to download your old gm_tMySQL and I noticed this. Time to go learn SQL lol

    Edit:

    I'm crashing when connecting to the server as well. I printed my version and it is 3, heres the code I'm using:

    require("mysqloo")
    
    print(mysqloo.VERSION)
    
    local server = mysqloo.connect("MYDATABASE.COM", "USER", "PASS")
    
    function connectSQL(server)
    	server.onConnected = function()
    		print("Connected to SQL host successfully!")
    	end
    	server:connect()
    end
    
    connectSQL(server)
    

    I'm running my script using lua_openscript on a listen server.
    Reply With Quote Edit / Delete United States Show Events Dumb Dumb x 2 (list)

  34. Post #34
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Updated the DLLs to v4 (see the first post). I've not really added anything, but have down-graded to older header files, so this might fix the crashing. If it doesn't then I'll upload some debug DLLs or something.
    Reply With Quote Edit / Delete Show Events

  35. Post #35
    Gold Member
    alexanderk's Avatar
    May 2007
    1,355 Posts
    If it works now(gonna test tommorow or something), I'll freaking love you. I've hoped for some updates to this module, finally they've arrived :D
    Reply With Quote Edit / Delete Norway Show Events

  36. Post #36
    quality poster
    Dennab
    August 2009
    12,242 Posts
    V4 doesnt work, I still crash on connecting. I cleaned my old install, reinstalled the new one, checked my version (printed 4) and ran the same code posted above, crashed.
    Reply With Quote Edit / Delete United States Show Events

  37. Post #37
    Gold Member
    Saint Devil's Avatar
    January 2009
    227 Posts
    Mine's working fine. But the only thing I dislike (though not a big deal), is the fact that I can only connect to one database in a query. For example, if I were to connect to one database and query that, I couldn't query a second database in that same query on the onSuccess function.
    Reply With Quote Edit / Delete United States Show Events Agree Agree x 1 (list)

  38. Post #38
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Mine's working fine. But the only thing I dislike (though not a big deal), is the fact that I can only connect to one database in a query. For example, if I were to connect to one database and query that, I couldn't query a second database in that same query on the onSuccess function.
    I don't think I've added any code to stop you querying other databases... I've not tried it though... This should work:

    local DATABASE1_HOST = "localhost"  
    local DATABASE1_PORT = 3308  
    local DATABASE1_NAME = "test"  
    local DATABASE1_USERNAME = "root"  
    local DATABASE1_PASSWORD = "1234"  
      
    local DATABASE2_HOST = "localhost"  
    local DATABASE2_PORT = 3309
    local DATABASE2_NAME = "test"  
    local DATABASE2_USERNAME = "root"  
    local DATABASE2_PASSWORD = "5678"  
    
    function printQuery(query)  
      PrintTable(query:getData())  
    end  
      
    database1 = nil
    database2 = nil
    connected = 0
    
    function runQuery2(query)
      local query2 = database2:query("SELECT ID, Name, Cost FROM test WHERE Cost > 0.50")  
      query2.onData = function(Q,D) print("Q2") PrintTable(D) end  
      query2.onSuccess = printQuery
      query2.onError = function(Q,E) print("Q2") print(E) end  
      query2:start()  
    end
    
    function afterConnected(database)  
      connected = connected + 1
      if (connected == 2) then
        local query1 = database1:query("SELECT ID, Name, Cost FROM test WHERE Cost > 0.50")  
        query1.onData = function(Q,D) print("Q1") PrintTable(D) end  
        query1.onSuccess = runQuery2  
        query1.onError = function(Q,E) print("Q1") print(E) end  
        query1:start()  
      end 
    end  
    
    function connectToDatabase()  
      database1 = mysqloo.connect(DATABASE1_HOST, DATABASE1_USERNAME, DATABASE1_PASSWORD, DATABASE1_NAME, DATABASE1_PORT)  
      database1.onConnected = afterConnected  
      database1:connect()  
    
      database2 = mysqloo.connect(DATABASE2_HOST, DATABASE2_USERNAME, DATABASE2_PASSWORD, DATABASE2_NAME, DATABASE2_PORT)  
      database2.onConnected = afterConnected  
      database2:connect()  
    end  
      
    connectToDatabase()
    

    In theory you should be able to chain as many database-callbacks as you like.
    Reply With Quote Edit / Delete Show Events

  39. Post #39
    GMod Illuminati
    andyvincent's Avatar
    January 2005
    65 Posts
    Updated: Crash bug fixed, See first post for more information.

    Download
    http://github.com/downloads/andyvinc...MySqlOO-v5.zip
    Reply With Quote Edit / Delete Show Events Friendly Friendly x 1Lua King Lua King x 1 (list)

  40. Post #40
    Gold Member
    alexanderk's Avatar
    May 2007
    1,355 Posts
    Thanks, andy!
    Reply With Quote Edit / Delete Norway Show Events