gmsv_mysqloo v9 - Rewritten MySQL Module (prepared statements, transactions)

MySQLOO v9.5
A mostly rewritten version of MySQLOO. Made by andyvincent, update by Drakehawke and KingofBeast.

Link to previous thread: http://forum.facepunch.com/showthread.php?t=1357773

Changes:



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


Documentation: (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 https://en.wikipedia.org/wiki/ACID 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:wait(shouldSwap)
– Returns nothing
– Forces the server to wait for the query to finish.
– This should only ever be used if it is really necessary, since it can cause lag and
– If shouldSwap is true, the query is being swapped to the front of the queue
– making it the next query to be executed

Query:error()
– Returns [String]
– Gets the error caused by the query (if any).

Query:hasMoreResults()
– Returns [Boolean]
– Returns true if the query still has more data associated with it (which means getNextResults() can be called)

Query:getNextResults()
– Returns [Table]
– Pops the current result set, chaning the results of lastInsert() and affectedRows() and getData()
– to those of the next result set. Returns the rows of the next result set in the same format as getData()
– Throws an error if attempted to be called when there is no result set left to be popped

– Callbacks
– ALWAYS set these callbacks before you start the query or you might run into issues

Query.onAborted( q )
– Called when the query is aborted.

Query.onError( q, err, sql )
– Called when the query errors, [String] err is the error and [String] sql is the SQL query that caused it.

Query.onSuccess( q, data )
– Called when the query is successful, [Table] data is the data the query returned.

Query.onData( q, data )
– Called when the query retrieves a row of data, [Table] data is the row.

– PreparedQuery object
– A prepared query uses a prepared statement that, instead of having the actual values of
– new rows, etc. in the query itself, it uses parameters, that can be set using the appropriate methods
– PreparedStatements make sql injections pretty much impossible since the data is sent seperately from the parameterized query
– Prepared queries also make it easy to reuse one query several times to insert/update/delete multiple rows
– An example of a parameterized query would be “INSERT INTO users (steamid, nick) VALUES(?,?)”
– A few types of queries might not work with prepared statements
– For more information about prepared statements you can read here: https://en.wikipedia.org/wiki/Prepared_statement

PreparedQuery:setNumber(index, number)
– Returns nothing
– Sets the parameter at index (1-based) to be of type double with value number

PreparedQuery:setString(index, str)
– Returns nothing
– Sets the parameter at index (1-based) to be of type string with value str
– Note: str should not!! be escaped

PreparedQuery:setBoolean(index, bool)
– Returns nothing
– Sets the parameter at index (1-based) to be of type boolean with value bool

PreparedQuery:setNull(index)
– Returns nothing
– Sets the parameter at index (1-based) to be NULL

PreparedQuery:putNewParameters()
– Returns nothing
– This shouldn’t be used anymore, just start the same prepared multiple times with different parameters

– Transaction object
– Transactions are used to group several seperate queries or prepared queries together.
– Either all queries in the transaction are executed successfully or none of them are.
– Please note that single queries are already transactions by themselves so it usually only makes sense to have transactions
– with at least two queries.
– Since mysqloo works async, much of the power of transactions (such as manually rolling back a transaction) cannot be used properly, but
– there’s still many areas they can be useful.

Transaction:addQuery(query)
– Adds a query to the transaction. The callbacks of the added queries will not be called.
– query can be either a PreparedQuery or a regular Query

Transaction:getQueries()
– Returns all queries that have been added to this transaction.

– Callbacks

Transaction.onError(tr, err)
– Called when any of the queries caused an error, no queries will have had any effect

Transaction.onSuccess()
– Called when all queries in the transaction have been executed successfully

[/LUA]

tmysql4 Wrapper
Since tmysql4 is not actively maintained anymore I have included a tmysql4 wrapper which can be found here

I also wrote a library that makes using mysqloo easier and less verbose.

Some important changes between mysqloo9 and mysqloo8 that might cause incompatibilities:
Bigint fields now return as numbers. This might be problematic if you want to use a bigint as the primary key and select it from the server (such as steamid64).
In these cases make sure to cast the bigint to a string in your SQL query like this: SELECT (CAST steamid64 as ‘CHAR’) as steamid64 FROM …
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)

Examples on how to use old and new features can be found in the github repository.

Download:
There are prebuilt binaries available for Linux and Windows.
They have to be placed within your garrysmod/lua/bin/ folder (create the bin folder if it does not exist)

You are also going to need either libmysql on Windows or libmysqlclient on Linux.
They have to be placed within the root folder of your server (the one that contains srcds.exe or srcds_run)

Bugs:
As this release contains some experimental features there might be some bugs.
If you find any bugs or you think your server crashes because of this module, please let me know in this thread.
Additionally, feel free to contribute on GitHub.

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)

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 this 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 both 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 this example.

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?

It will be backwards compatible for almost all scripts.
There’s just a few incompatibilities:



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)


Apart from that mysqloo 9 should have all of the functionality that mysqloo 8.1 has.

Looks pretty solid, great release :slight_smile:

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”


Database:status() == mysqloo.DATABASE_NOT_CONNECTED

[editline]24th April 2016[/editline]

Apparently it can’t be timed out anymore though.

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)”

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 at least 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.

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

I fixed that problem and also added a Database:ping() function that does pretty much what mysqloo 8.1 did before.

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



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()


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.

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 Error + return instead.

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

Try it now, I forgot to push.