• gmsv_mysqloo v9 - Rewritten MySQL Module (prepared statements, transactions)
    170 replies, posted
[B]MySQLOO v9.5[/B] A mostly rewritten version of MySQLOO. Made by [URL="https://facepunch.com/member.php?u=3270"]andyvincent[/URL], update by [URL="https://facepunch.com/member.php?u=201608"]Drakehawke[/URL] and [URL="https://facepunch.com/member.php?u=162098"]KingofBeast[/URL]. Link to previous thread: [url]https://facepunch.com/showthread.php?t=1357773[/url] [B]Changes:[/B] [CODE] 05.08.2017: Fixed queries run in coroutines. 31.05.2017: Added db:setCharacterSet() Added db:disconnect() Fixed a crash when starting and waiting for a query in the callback of the database 07.03.2017: fixed query:error() not working in transactions 06.03.2017: Fixed query:error() not returning any results/crashing 28.01.2017: Fixed a memory leak as well as a rare crash that could occur involving prepared statements. 12.01.2017: Queries can now be started multiple times PreparedQueries now cache and reuse the prepared statements allocated on the server PreparedQueries can now return multiple result sets Database:ping() now always attempts to reconnect to the database regardless of autoReconnect status Added automatic update check that checks if an update is available Added mysqloo.MINOR_VERSION Added database:setCachePreparedStatements slightly changed behavior of query:hasMoreResults() Added lua library for ease of use Added tmysql4 wrapper Added connection pool lua library 30.12.2016: If a callback errors the error is now printed to the console instead of being silently ignored. 15.06.2016: Fixed constants to match older versions of mysqloo 13.05.2016: Fixed a crash caused by transactions on shutdown 30.04.2016: Added database:ping() Changed database:wait() to allow swapping the query to the front Fixed setMultiStatements and setAutoReconnect Initial release: Added prepared queries (should be used instead of escaping) Added transactions (Still kind of experimental) Database instances now have autoreconnect enabled by default Queries can now have multiple result sets Added Database:queueSize() Fixed memory leak issues with previous versions Fixed threading issues with previous versions [/CODE] [B]Documentation:[/B] (mostly copied from Drakehawke) [LUA] -- mysqloo table mysqloo.connect( host, username, password [, database, port, socket] ) -- returns [Database] -- Initializes the database object, note that this does not actually connect to the database. mysqloo.VERSION -- [String] Current MySQLOO version (currently "9") mysqloo.MINOR_VERSION -- [String] minor version of this library mysqloo.DATABASE_CONNECTED -- [Number] - Database is connected mysqloo.DATABASE_CONNECTING -- [Number] - Datbase is connecting mysqloo.DATABASE_NOT_CONNECTED -- [Number] - Database is not connected mysqloo.DATABASE_INTERNAL_ERROR -- [Number] - Internal error mysqloo.QUERY_NOT_RUNNING -- [Number] - Query not running mysqloo.QUERY_WAITING -- [Number] - Query is queued/started mysqloo.QUERY_RUNNING -- [Number] - Query is being processed (on the database server) mysqloo.QUERY_COMPLETE -- [Number] - Query is complete mysqloo.QUERY_ABORTED -- [Number] - Query was aborted mysqloo.OPTION_NUMERIC_FIELDS -- [Number] - Instead of rows being key value pairs, this makes them just be an array of values mysqloo.OPTION_NAMED_FIELDS -- [Number] - Not used anymore mysqloo.OPTION_INTERPRET_DATA -- [Number] - Not used anymore mysqloo.OPTION_CACHE -- [Number] - Not used anymore -- Database object -- Functions Database:connect() -- Returns nothing -- Connects to the database (non blocking) -- This function calls the onConnected or onConnectionFailed callback Database:disconnect(shouldWait) -- Returns nothing -- disconnects from the database and waits for all queries to finish if shouldWait is true Database:query( sql ) -- Returns [Query] -- Initializes a query to the database, [String] sql is the SQL query to run. Database:prepare() -- Returns [PreparedQuery] -- Creates a prepared query associated with the database Database:createTransaction() -- Returns [Transaction] -- Creates a transaction that executes multiple statements atomically -- Check [url]https://en.wikipedia.org/wiki/ACID[/url] for more information Database:escape( str ) -- Returns [String] -- Escapes [String] str so that it is safe to use in a query. -- If the characterset of the database is changed after connecting, this might not work properly anymore Database:abortAllQueries() -- Returns the amount of queries aborted successfully -- Aborts all waiting (QUERY_WAITING) queries Database:status() -- Returns [Number] (mysqloo.DATABASE_* enums) -- Checks the connection to the database -- This shouldn't be used to detect timeouts to the server anymore (it's not possible anymore) Database:setAutoReconnect(shouldReconnect) -- Returns nothing -- The autoreconnect feature of mysqloo can be disabled if this function is called with shouldReconnect = false -- This has to be called before Database:connect() to work Database:setMultiStatements(useMultiStatemets) -- Returns nothing -- Multi statements ("SELECT 1; SELECT 2;") can be disabled if this function is called with useMultiStatemets = false -- This has to be called before Database:connect() to work Database:setCachePreparedStatements(cachePreparedStatements) -- Returns nothing -- This will disable all caching of prepared query handles -- which will reduce the performance of prepared queries that are being reused -- Set this to true if you run into the prepared query limit imposed by the server Database:wait() -- Returns nothing -- Forces the server to wait for the connection to finish. (might cause deadlocks) -- This has to be called after Database:connect() Database:serverVersion() -- Returns [Number] -- Gets the MySQL servers version Database:serverInfo() -- Returns [String] -- Fancy string of the MySQL servers version Database:hostInfo() -- Returns [String] -- Gets information about the connection. Database:queueSize() -- Returns [Number] -- Gets the amount of queries waiting to be processed Database:ping() -- Returns [Boolean] -- Actively checks if the database connection is still up and attempts to reconnect if it is down -- This will freeze your server for at least 2 times the pingtime to -- the database server if the connection is down -- returns true if the connection is still up, false otherwise Database:setCharacterSet(charSetName) -- Returns [Boolean, String] -- Attempts to set the connection's character set to the one specified. -- Please note that this does block the main server thread if there is a query currently being ran -- Returns true on success, false and an error message on failure -- Callbacks Database.onConnected( db ) -- Called when the connection to the MySQL server is successful Database.onConnectionFailed( db, err ) -- Called when the connection to the MySQL server fails, [String] err is why. -- Query/PreparedQuery object (transactions also inherit all functions, some have no effect though) -- Functions Query:start() -- Returns nothing -- Starts the query. Query:isRunning() -- Returns [Boolean] -- True if the query is running or waiting, false if it isn't. Query:getData() -- Returns [Table] -- Gets the data the query returned from the server -- Format: { row1, row2, row3, ... } -- Row format: { field_name = field_value } or {first_field, second_field, ...} if OPTION_NUMERIC_FIELDS is enabled Query:abort() -- Returns [Boolean] -- Attempts to abort the query if it is still in the state QUERY_WAITING -- Returns true if aborting was successful, false otherwise Query:lastInsert() -- Returns [Number] -- Gets the autoincrement index of the last inserted row of the current result set Query:status() -- Returns [Number] (mysqloo.QUERY_* enums) -- Gets the status of the query. Query:affectedRows() -- Returns [Number] -- Gets the number of rows the query has affected (of the current result set) Query:setOption( option ) -- Returns nothing -- Changes how the query returns data (mysqloo.OPTION_* enums). Query:wai
yaaaas bitch, yaaaaas
As someone still using tmysql3 for everything, I have to ask the golden question: MySQLOO v9 vs. TMySQL4 Why should I use one or the other, advantages, disadvantages, performance, etc.? (Prepared statements aside)
[QUOTE=Revenge282;50182917]As someone still using tmysql3 for everything, I have to ask the golden question: MySQLOO v9 vs. TMySQL4 Why should I use one or the other, advantages, disadvantages, performance, etc.?[/QUOTE] The major reason to use MySQLOO are prepared statements and transactions. Both of these do not exist in the current version of tmysql. Some of you might know prepared statements from PHP or JDBC. They allow you to reuse one query (so the database server only has to parse and optimize the query once) but more importantly allow you to easily bind arguments to parameters (without having to painfully escape them all). I recommend checking out [URL="https://github.com/syl0r/MySQLOO/blob/master/Examples/multi_parameters.lua"]this[/URL] example. On the other hand, transactions are mostly used to preserve logical consistency in your database model. An example. A player puts a donut from his inventory into his storage. [LUA] db:query("UPDATE inventories SET donuts = donuts - 1 WHERE inventoryid = 5") --very bad db design db:query("UPDATE inventories SET donuts = donuts + 1 WHERE inventoryid = 4") [/LUA] These two statements only make sense to have effect if they [U][B]both[/B][/U] completed successfully, and if one of them fails (for example the database connection goes down), the player might have lost some donuts. Transactions fix this problem by grouping both queries together into one atomic action. To see it in action you can just look at [URL="https://github.com/syl0r/MySQLOO/blob/master/Examples/transactions.lua"]this example[/URL]. Another advantage of this module is that all queries are executed strictly in order. [LUA] db:query("UPDATE users SET bank = 100000 WHERE userid = 5") db:query("UPDATE users SET bank = 10000 WHERE userid = 5") [/LUA] If you use this module and execute those two queries, the user will definitely have $10000 in his bank when they are done. With tmysql4, you can't tell what's going to happen. Tmysql uses 2 threads per database instance, which introduces non-determinism and the result could be either that the user has $100000, or $10000 in their bank. While performance wise tmysql is slightly faster (even though almost equal), performance of the mysql module really isn't a factor. Most of the time of each query is spent communicating with the db server as well as actually executing the query. The time taken to create query objects compared to networking is negligible.
Finally! It's nice to see a new refresh of an old module.
Man, just after I used tmysql for my store. Time to switch over. Good work!
Is it fully backwards compatible?
[QUOTE=FPtje;50185424]Is it fully backwards compatible?[/QUOTE] It will be backwards compatible for almost all scripts. There's just a few incompatibilities: [CODE] Bit fields now return as numbers (0 or 1) OPTION_NAMED_FIELDS and OPTION_CACHE don't have any effect anymore since named fields is the default setting and all data is cached now. OPTION_INTERPRET_DATA is unused as I fail to see any use (if anyone needs it I can easily readd it) [/CODE] Apart from that mysqloo 9 should have all of the functionality that mysqloo 8.1 has.
Looks pretty solid, great release :)
Is there any to tell that we've disconnected from the database? Only way I can see is by checking if the query error is "Lost connection to MySQL server during query"
[QUOTE=bigdogmat;50196720]Is there any to tell that we've disconnected from the database? Only way I can see is by checking if the query error is "Lost connection to MySQL server during query"[/QUOTE] [code]Database:status() == mysqloo.DATABASE_NOT_CONNECTED[/code] [editline]24th April 2016[/editline] Apparently it can't be timed out anymore though.
[QUOTE=man with hat;50196762][code]Database:status() == mysqloo.DATABASE_NOT_CONNECTED[/code][/QUOTE] Even after it disconnects it still reports mysqloo.DATABASE_CONNECTED. Also in the documentation it says under status "This shouldn't be used to detect timeouts to the server anymore (it's not possible anymore)"
[QUOTE=bigdogmat;50196777]Even after it disconnects it still reports mysqloo.DATABASE_CONNECTED. Also in the documentation it says under status "This shouldn't be used to detect timeouts to the server anymore (it's not possible anymore)"[/QUOTE] The main reason this was removed is because mysqloo 9 has autoreconnect built in, which means there's almost no use for the old functionality. Using mysql_ping (which mysqloo 8 used to determine if the connection was still up) could now potentially trigger an auto reconnect. This means that your server will literally freeze for [B]at least[/B] 3 times the ping time to mysql server. If you tell me what you were using it for I might be able to help you find a better solution.
[QUOTE=syl0r;50197090] If you tell me what you were using it for I might be able to help you find a better solution.[/QUOTE] Well I wasn't using it for anything, I'm trying to find a way to run queries that went through while disconnected. I did come up with a solution, and that was if the error in a query was connection failure we start detouring all other queries into a cache table. While doing that we start querying the server with a test query like "SELECT 5+5", and on failure we rerun the query, and on success we start slowly running all cached queries.
Tried using setMultiStatements, but when called before connect it errors with "Tried to reference lua object twice (Query started twice?)" when connect is called
[QUOTE=bigdogmat;50226097]Tried using setMultiStatements, but when called before connect it errors with "Tried to reference lua object twice (Query started twice?)" when connect is called[/QUOTE] I fixed that problem and also added a Database:ping() function that does pretty much what mysqloo 8.1 did before.
[QUOTE=syl0r;50227810]I fixed that problem and also added a Database:ping() function that does pretty much what mysqloo 8.1 did before.[/QUOTE] Any idea on when that'll be pushed to github? Also, any reason error can't be called from within the onError callback? It doesn't print anything or do anything, just acts like error has been detoured to an empty function. Example [code] local Query = Database:query "SELECT * FROM nonexistenttable" function Query:onSuccess(data) PrintTable(data) end -- error "test" function Query:onError(sql, err) print(sql, err) error "failed" end Query:start() [/code] The print will run and print the sql statement and the sql error, though the error just doesn't do anything. I know nothing is overwriting it at least in the file because if I uncomment the error call above it; it works just fine.
[QUOTE=bigdogmat;50228492]Any idea on when that'll be pushed to github? Also, any reason error can't be called from within the onError callback? It doesn't print anything or do anything, just acts like error has been detoured to an empty function. Example [code] local Query = Database:query "SELECT * FROM nonexistenttable" function Query:onSuccess(data) PrintTable(data) end -- error "test" function Query:onError(sql, err) print(sql, err) error "failed" end Query:start() [/code] The print will run and print the sql statement and the sql error, though the error just doesn't do anything. I know nothing is overwriting it at least in the file because if I uncomment the error call above it; it works just fine.[/QUOTE] The latest version is already on the github. The error function doesn't do anything because the callbacks are protected by a pcall (that just ignores errors). This has always been the case with mysqloo, and I'd recommend just using [URL="http://wiki.garrysmod.com/page/Global/Error"]Error[/URL] + return instead.
[QUOTE=syl0r;50228619]The latest version is already on the github.[/QUOTE] I'm using the lastest compiled version for windows and I'm still getting the "Tried to reference lua object twice (Query started twice?)" error
[QUOTE=bigdogmat;50228662]I'm using the lastest compiled version for windows and I'm still getting the "Tried to reference lua object twice (Query started twice?)" error[/QUOTE] Try it now, I forgot to push.
Though setMultiStatements doesn't error now, it doesn't seem to have any effect. Having multiple statements still only executes the first one.
It seems to work on my end: [LUA] local db = CreateDatabaseInstance() db:setMultiStatements(true) db:connect() db:wait() local qu = db:query("SELECT 1; SELECT 2;") function qu:onSuccess() PrintTable(qu:getData()) -- prints 1 PrintTable(qu:getNextResults()) -- prints 2 end qu:start() [/LUA]
Both the links to prebuilt binaries link to the windows version, tiny thread issue but just letting you know, nice to see mysqloo get an update -snip ignore my edit-
This may be a rather dumb question, but do I have to use the same prepared query object each time in order to benefit from the serverside optimization of the queries, or does this module of MySQL somehow remember which queries were prepared automatically? In other words, should I use db:prepare(...) each time I want to start a new query, or should I re-use the same query object each time?
[QUOTE=typedef state;50282182]This may be a rather dumb question, but do I have to use the same prepared query object each time in order to benefit from the serverside optimization of the queries, or does this module of MySQL somehow remember which queries were prepared automatically? In other words, should I use db:prepare(...) each time I want to start a new query, or should I re-use the same query object each time?[/QUOTE] It should definitely be the latter
[QUOTE=Derek_SM;50284645]It should definitely be the latter[/QUOTE] That would seem logical, but trying to use query:start() more than once on the same object throws an error.
[QUOTE=typedef state;50282182]This may be a rather dumb question, but do I have to use the same prepared query object each time in order to benefit from the serverside optimization of the queries, or does this module of MySQL somehow remember which queries were prepared automatically? In other words, should I use db:prepare(...) each time I want to start a new query, or should I re-use the same query object each time?[/QUOTE] You have to call prepare() each time. The only way to benefit from prepared statements (performance wise) currently is executing an [URL="https://github.com/syl0r/MySQLOO/blob/master/Examples/multi_parameters.lua"]entire batch at once[/URL]. The most important aspect of prepared statments for most people are the safety they offer with regards to sql injection and even though there is a (very slight) performance boost it will only be noticeable with very very long and complicated queries. Here's a typical query of mine for comparison: Total execution time ~1 ms seconds. (If you don't have the db on the same machine, even the query execution time becomes insignificant compared to ping) Optimizing ~7 microseconds. [IMG]http://puu.sh/oLs6z/9ad40045c1.png[/IMG] While I do know of a way to always reuse prepared statements through caching, I don't know if it'd be worth it. But if you really need it I can definitely implement it (you would still have to call prepare every time).
[QUOTE=syl0r;50285726]You have to call prepare() each time. The only way to benefit from prepared statements (performance wise) currently is executing an [URL="https://github.com/syl0r/MySQLOO/blob/master/Examples/multi_parameters.lua"]entire batch at once[/URL]. The most important aspect of prepared statments for most people are the safety they offer with regards to sql injection and even though there is a (very slight) performance boost it will only be noticeable with very very long and complicated queries. Here's a typical query of mine for comparison: Total execution time ~1 ms seconds. (If you don't have the db on the same machine, even the query execution time becomes insignificant compared to ping) Optimizing ~7 microseconds. [IMG]http://puu.sh/oLs6z/9ad40045c1.png[/IMG] While I do know of a way to always reuse prepared statements through caching, I don't know if it'd be worth it. But if you really need it I can definitely implement it (you would still have to call prepare every time).[/QUOTE] Ah, I see. Thank you very much for the clarification! I am not in dire need of this, I just wanted to know how they were supposed to be used, and what the performance gains were. Again, thank you!
Running a transaction on shutdown cause a crash on Windows. I made a small script to reproduce it [url]http://pastebin.com/XYs8DBWA[/url]
[QUOTE=Marc0303;50302309]Running a transaction on shutdown cause a crash on Windows. I made a small script to reproduce it [url]http://pastebin.com/XYs8DBWA[/url][/QUOTE] I'm pretty sure I fixed it, in case I didn't just let me know.
Sorry, you need to Log In to post a reply to this thread.