tmysql4 - A multi-connection version of tmysql3 (Now with mysqloo wrapper!)

[BlackAwps]

[QUOTE=icebox3d;49029628]Sure :)

Just for giggles I am building a Debian Jessie server as I tend to use Ubuntu more. Shall see if it loads there

[editline]2nd November 2015[/editline]

Here is what I got from the Jessie server

      5316:     /home/steam/server_1/garrysmod/lua/bin/gmsv_tmysql4_linux.dll: error: symbol lookup error: undefined symbol: _ZNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEE9_M_createERjj (fatal)

ldd garrysmod/lua/bin/gmsv_tmysql4_linux.dll
        linux-gate.so.1 (0xf7737000)
        libstdc++.so.6 => /usr/lib32/libstdc++.so.6 (0xf7239000)
        libm.so.6 => /lib32/libm.so.6 (0xf71f3000)
        libgcc_s.so.1 => /usr/lib32/libgcc_s.so.1 (0xf71d6000)
        libc.so.6 => /lib32/libc.so.6 (0xf702c000)
        /lib/ld-linux.so.2 (0xf773a000)

[/QUOTE]

I honestly can't figure this out or manage to replicate it.

[QUOTE=DylanWilson;49030070]Thanks for the quick support blackawps

out of curiosity, do you have any plans to support prepared statements? I know escape works, but preparing the statement is so much cleaner[/QUOTE]

Why not just make something yourself?

[lua]local datameta = FindMetaTable("Database")

function datameta:QuerySimple(query,...)
local args = {...}
query = query:gsub("{(%d+)}", function(i)
local value = tostring(args[tonumber(i)])
return string.format("\"%s\"", self:Escape(value))
end)

local off = #args+1
for i=1,#args do
if type(args[i]) == "function" then
off = i
break
end
end

local callback = args[off]
local object = args[off+1]
local usenumbers = args[off+2]

self:Query(query, callback, object, usenumbers)
end[/lua]

This is a modified and updated version of what I used to use.

Allows you to do things like this..

[lua]Database:QuerySimple("SELECT * FROM players where name={1} and money>={2}; SELECT {1} as nametest, {2} as moneytest", ply:SteamID(), 200, function(results) PrintTable(results) end)[/lua]

[Mercior]

Ubuntu 12.04.5 LTS

ldd /home/steam/arc1/garrysmod/lua/bin/gmsv_tmysql4_linux.dll
/home/steam/arc1/garrysmod/lua/bin/gmsv_tmysql4_linux.dll: /lib/i386-linux-gnu/libc.so.6: version `GLIBC_2.17' not found (required by /home/steam/arc1/garrysmod/lua/bin/gmsv_tmysql4_linux.dll)
        linux-gate.so.1 =>  (0xf779f000)
        libstdc++.so.6 => /usr/lib/i386-linux-gnu/libstdc++.so.6 (0xf7281000)
        libm.so.6 => /lib/i386-linux-gnu/libm.so.6 (0xf7255000)
        libgcc_s.so.1 => /lib/i386-linux-gnu/libgcc_s.so.1 (0xf7236000)
        libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xf708d000)
        /lib/ld-linux.so.2 (0xf77a0000)

Been googling for hours with no luck :/ Any advice?

[i]edit[/i]

Welcome to Ubuntu 12.04.5 LTS (GNU/Linux 3.14.32-xxxx-std-ipv6-64 x86_64)

sudo apt-cache policy libc6
libc6:
  Installed: 2.15-0ubuntu10.12
  Candidate: 2.15-0ubuntu10.12

sudo apt-get update
sudo apt-get install libc6
Reading package lists... Done
Building dependency tree
Reading state information... Done
libc6 is already the newest version.

I'm confused :/

[nalyD]

[QUOTE=BlackAwps;49030625]
Why not just make something yourself?

[lua]--Huge function that loops through strings like 4 times[/lua]
[/QUOTE]

I know it's one of those "well it's not like you're calling it every frame" type things, but there's at least a few thousand things on my server that don't get called every frame, and I don't want any of them to be even remotely that slow just for a query. and I also realize that at some point that SQL is going to scan through the string, and for that i defer to this: [url]http://benchmarksgame.alioth.debian.org/u64q/lua.html[/url]

Lua is slow

[FPtje]

[QUOTE=DylanWilson;49033026]I know it's one of those "well it's not like you're calling it every frame" type things, but there's at least a few thousand things on my server that don't get called every frame, and I don't want any of them to be even remotely that slow just for a query. and I also realize that at some point that SQL is going to scan through the string, and for that i defer to this: [url]http://benchmarksgame.alioth.debian.org/u64q/lua.html[/url]

Lua is slow[/QUOTE]

LuaJIT is a lot faster than Lua. GMod uses LuaJIT.

Really, it's not that slow, it replaces only as many things in the string as you have values. The worst parts of it might be the call to type and tabling the vararg. But I seriously doubat that whatever is running queries will not form the bottleneck of your server's performance because of this.

Performance claims like that should be made with measurements.

Also, string.gsub appears to be quite efficient. This is LuaJIT 2.0.4's implementation:

LUALIB_API const char *luaL_gsub(lua_State *L, const char *s,
				 const char *p, const char *r)
{
  const char *wild;
  size_t l = strlen(p);
  luaL_Buffer b;
  luaL_buffinit(L, &b);
  while ((wild = strstr(s, p)) != NULL) {
    luaL_addlstring(&b, s, (size_t)(wild - s));  /* push prefix */
    luaL_addstring(&b, r);  /* push replacement in place of pattern */
    s = wild + l;  /* continue after `p' */
  }
  luaL_addstring(&b, s);  /* push last suffix */
  luaL_pushresult(&b);
  return lua_tostring(L, -1);
}

It iterates over the source string once, building up a buffer. When a match is made, the replacement is pushed to the buffer instead of the match. In the end the buffer is returned as a string.
Algorithmic complexity is O(source string length + length of replacements + O(replace function)), so basically O(n), which is pretty cheap.

[nalyD]

Right, but then it uses string:format, which runs through the string again
and then string:escape, which runs through the string a 3rd time

all the while, providing none of the security improvements of actual prepared statements implemented through SQL

[FPtje]

[QUOTE=DylanWilson;49033906]Right, but then it uses string:format, which runs through the string again
and then string:escape, which runs through the string a 3rd time

all the while, providing none of the security improvements of actual prepared statements implemented through SQL[/QUOTE]

string.format And db:escape only run through the [I]value[/I] string, not the query string. You can make your own unsafe query function that doesn't format and that doesn't escape the input values if you really think that's going to freeze your server.

I can understand that you want actual prepared statements, and I understand that they're safer than escaping, but your the performance argument is very weak.

[nalyD]

I was going to do some fancy lua to benchmark preparing vs escaping, except normally I'd use my server but there was a fiber cut this morning and the network's been shaky all day.

So I decided to use my raspberry pi over my home network because why not.

and then I decided to just let it do each one 30k queries because fuck real world analogs.
then after running it twice I noticed that they were spilling in ~50/s where my raspberry pi was screaming bloody murder

and then I killed the SQL service over SSH, which crashed my gmod server on my desktop

is this a bug or a feature?

on a more serious note, there's no discernible difference between the two, I'll concede my point to you sir, since tmysql uses a queue I guess(?) the queries get piled up, and there was no difference between these two in terms of execution time:
[lua]for i = 1,5000 do
db:Query("PREPARE testq FROM 'INSERT INTO `testtable` (`username`, `password`) VALUES (?, ?)'; EXECUTE testq USING 'user"..i.."', 'HABBERDASH';")
end[/lua]
[lua]for i = 1,5000 do
QuerySimple("INSERT INTO testtable (username, password) VALUES ({1}, {2})", "user"..i, "HABBERDASH")
end[/lua]

[BlackAwps]

If it crashed when stopping the sql service that most certainly is a bug. Do you have a crash dump I can look at?

And yeah, all queries get queue'd up and then ran in a separate thread. All the results are then pushed to another queue that get popped off during a Tick of the server.

[nalyD]

[QUOTE=BlackAwps;49035640]If it crashed when stopping the sql service that most certainly is a bug. Do you have a crash dump I can look at?

And yeah, all queries get queue'd up and then ran in a separate thread. All the results are then pushed to another queue that get popped off during a Tick of the server.[/QUOTE]
This should be it
[url]http://nalyd.net/srcds_2117156_crash_2015_11_2T21_14_14C0.7z[/url]

--edit--
Also, I just put the mysqloo wrapper on my server again to attempt this transition, and I'm getting crashes every 20 minutes or so again, even with the newer build I just downloaded.

It's Windows Server 2008 if that helps at all

--doubleedit--
that's helpful maybe, there's some code in one of the addons I use that tries to reconnect if the SQL connection dies because I guess mysqloo doesn't manage it's connection very well?
either way, I don't think it's intentional to outright crash the server when this happens, this was the log right around the time of the last crash:
[lua]
[ERROR] lua/includes/modules/mysqloo.lua:45: Attempted to call Connect on an already connected database
1. Connect - [C]:-1
2. connect - lua/includes/modules/mysqloo.lua:45
3. Retry - lua/includes/modules/sourcebans.lua:1393
4. unknown - lua/includes/modules/sourcebans.lua:1431
[/lua]

yip yip yip, just happened again and the same thing in the logs, defiantly the cause, I'll remove the reconnect stuff from the old mysqloo addons, but, I imagine you'd like to fix this too

[BlackAwps]

I can't load your crash log because the symbols are different. You sure you have the latest version? I would like to check this out but I need a crash dump from the latest dll.

[nalyD]

[QUOTE=BlackAwps;49035837]I can't load your crash log because the symbols are different. You sure you have the latest version? I would like to check this out but I need a crash dump from the latest dll.[/QUOTE]

ehhhhhh it should be, I'll triple check but I downloaded it before I started my benchmarking just a few hours ago

--edit--

yah, downloaded it again, and the file sizes are the same down to the byte

[BlackAwps]

Well... I don't know then. Your crash dump refuses to load the symbols for me.

I also just setup a sql server to test to make sure everything works.

Ran a query with sql server up.
Shutdown sql server.
Ran another query with sql server down.
Everything worked as expected.

ATTEMPTING QUERY ON CONNECTED DATABASE
1:
                affected        =       1
                data:
                                1:
                                                bigint  =       3204982309482309809
                                                decimal =       298473
                                                double  =       1234.56789
                                                float   =       123.456
                                                number  =       123
                                                string  =       this is a test
                lastid  =       0
                status  =       true
                time    =       0.0010792978156873
ATTEMPTING QUERY ON SHUTDOWN MYSQL SERVER
1:
                error   =       MySQL server has gone away
                errorid =       2006
                status  =       false
                time    =       2.2624217991827

[editline]2nd November 2015[/editline]

I did fix a small issue where the database would always fail to reconnect if it ever lost connection. I also added a check to the mysqloo wrapper to prevent it from connecting if it's already connected.

[nalyD]

I'm also unable to reproduce actually, booted my local server back up and ran the exact same script and killed the SQL server halfway through and nothing

[Mercior]

[QUOTE=BlackAwps;49036093]Well...[/QUOTE]

Hey BlackAwps I worry you missed my post while speaking with this other guy. You have any advice for me?

( [url]https://facepunch.com/showthread.php?t=1442438&p=49032103&viewfull=1#post49032103[/url] )

[BlackAwps]

I compile on Debian Jessie now, if it doesn't work, you might just want to look into compiling it yourself.

[nalyD]

[QUOTE=Mercior;49037718]Hey BlackAwps I worry you missed my post while speaking with this other guy. You have any advice for me?

( [url]https://facepunch.com/showthread.php?t=1442438&p=49032103&viewfull=1#post49032103[/url] )[/QUOTE]

Ubuntu 12.04 is almost 4 years old now, so you're using the "oldstable" repositories which doesn't ahve newer versions of libc6 available. As you can see on the right bar in the file names the latest version for your distro version is 2.15
[url]http://packages.ubuntu.com/precise/libc6[/url]

you need to update your OS to get newer versions

this process can take a few hours, I'd do some research because I'm by no means an expert on the subject, but in theory you can just do "sudo apt-get dist-upgrade" and then update/upgrade once or twice to make sure everything is settled, then reboot.

[Mercior]

[QUOTE=DylanWilson;49039343]Ubuntu 12.04 is almost 4 years old now, so you're using the "oldstable" repositories which doesn't ahve newer versions of libc6 available. As you can see on the right bar in the file names the latest version for your distro version is 2.15
[/QUOTE]

Ok so today I've reinstalled my server on Debian Jessie.

Now I have:

[ERROR] addons/gw/lua/isql.lua:23: Couldn't load module library!
  1. require - [C]:-1

Line 23 being:

require ( "tmysql4" )

supreme00
And this is ldd:

steam@ns301xxxx:~/arc1/garrysmod/lua$ ldd bin/gmsv_tmysql4_linux.dll
        linux-gate.so.1 (0xe46d2000)
        libstdc++.so.6 => /usr/lib32/libstdc++.so.6 (0xe41d3000)
        libm.so.6 => /lib32/libm.so.6 (0xe418d000)
        libgcc_s.so.1 => /usr/lib32/libgcc_s.so.1 (0xe4170000)
        libc.so.6 => /lib32/libc.so.6 (0xe3fc6000)
        /lib/ld-linux.so.2 (0xe46d3000)

[BomBom]

Same OS, Debian Jessie.
Latest Module.

> require("tmysql4")...
 
[ERROR] lua_run:1: Couldn't load module library!
  1. require - [C]:-1
   2. unknown - lua_run:1

ldd gmsv_tmysql4_linux.dll
        not a dynamic executable

[andreblue]

Got a bit more error detail then above for ya,

lua_run require"tmysql4"
> require"tmysql4"...
      7966:     /home/gmod_sandbox/serverfiles/garrysmod/lua/bin/gmsv_tmysql4_linux.dll: error: symbol lookup error: undefined symbol: _ZNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEE9_M_createERjj (fatal)

root@ns504810:~# cat /proc/version
Linux version 3.14.32-xxxx-grs-ipv6-64 ([e-mail]) (gcc version 4.7.2 (Debian 4.7.2-5) ) #5 SMP Wed Sep 9 17:24:34 CEST 2015

root@ns504810:~# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 8 (jessie)"
NAME="Debian GNU/Linux"
VERSION_ID="8"
VERSION="8 (jessie)"
ID=debian

~/serverfiles/garrysmod/lua/bin$ md5sum *
1b226024ff0b587953e764bf4d8fafc3  gmsv_tmysql4_linux.dll

Also if someone has some info on how to compile the dll then please post it. I tried to do it but i think i failed hard...

[Snowywolf]

Using the mysqloo wrapper in the OP i discovered that when a gamemode or addon that uses mysqloo attempts to connect to a mysql server that isn't responding or if using the wrong password (etc) srcds will crash upon startup.

Using latest tmysql dll

Minidump: [url]https://www.dropbox.com/s/b54kaw9f0nft2k3/srcds_2117156_crash_2015_11_4T1_20_6C0.mdmp[/url]

[BlackAwps]

[QUOTE]

lua_run require"tmysql4"
> require"tmysql4"...
      7966:     /home/gmod_sandbox/serverfiles/garrysmod/lua/bin/gmsv_tmysql4_linux.dll: error: symbol lookup error: undefined symbol: _ZNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEE9_M_createERjj (fatal)

[/QUOTE]

FINNALLY FIXED THIS SHIT FOR GOOD. That took me like 4 hours to figure it out, but I swear it's fixed now.
[QUOTE=icefox;49043488]Using the mysqloo wrapper in the OP i discovered that when a gamemode or addon that uses mysqloo attempts to connect to a mysql server that isn't responding or if using the wrong password (etc) srcds will crash upon startup.

Using latest tmysql dll

Minidump: [url]https://www.dropbox.com/s/b54kaw9f0nft2k3/srcds_2117156_crash_2015_11_4T1_20_6C0.mdmp[/url][/QUOTE]

I'll take a look at this now.

[andreblue]

[QUOTE=BlackAwps;49044598]FINNALLY FIXED THIS SHIT FOR GOOD. That took me like 4 hours to figure it out, but I swear it's fixed now.

I'll take a look at this now.[/QUOTE]

Cool, seems to be working. no errors.

[Viltsuuu]

I'm in the process of creating a custom administration mod and using this module for the MySQL part of it with it, but I'm not getting any callbacks on queries. Is it a problem with my code, or a bug?
Tested the callbacks with this kind of code but I can't get it working:

require("tmysql4")
if !ADMIN.UseMySQL then return end

MsgN("[ADMIN] Loaded MySQL")

local db, err = tmysql.Create(ADMIN.MySQL_Host, ADMIN.MySQL_Username, ADMIN.MySQL_Password, ADMIN.MySQL_Database, ADMIN.MySQL_Port)
if err then
	MsgN("[ADMIN] MySQL Connection error: " .. err)
end

local function onCompleted(results)
	print("test1")
	PrintTable(results)
end

function ADMIN.InitData()
	print("Test")
	local status, err = db:Connect()
	if err then
		MsgN("[ADMIN] MySQL Connection error: " .. err)
	end
	db:Query("CREATE TABLE IF NOT EXISTS admin_groups (Name VARCHAR(30) PRIMARY KEY, DisplayName VARCHAR(30) NOT NULL, Immunity TINYINT UNSIGNED NOT NULL, Commands VARCHAR(1000))", onCompleted)
end

My console is outputting only "Test". The query seems to be running fine, as it's creating the table in my database.

Also tried code like this with no success:

require("tmysql4")
if !ADMIN.UseMySQL then return end

MsgN("[ADMIN] Loaded MySQL")

local db, err = tmysql.Create(ADMIN.MySQL_Host, ADMIN.MySQL_Username, ADMIN.MySQL_Password, ADMIN.MySQL_Database, ADMIN.MySQL_Port)
if err then
	MsgN("[ADMIN] MySQL Connection error: " .. err)
end

function ADMIN.InitData()
	print("Test")
	local status, err = db:Connect()
	if err then
		MsgN("[ADMIN] MySQL Connection error: " .. err)
	end
	db:Query("CREATE TABLE IF NOT EXISTS admin_groups (Name VARCHAR(30) PRIMARY KEY, DisplayName VARCHAR(30) NOT NULL, Immunity TINYINT UNSIGNED NOT NULL, Commands VARCHAR(1000))", function(results)
		print("test1")
		PrintTable(results)
	end)
end

Both produce the same result.

[BlackAwps]

Is your server empty? If so the Tick hook wont get called and all the query callbacks won't get called too. Add a bot and all the callbacks should be called.

[Viltsuuu]

[QUOTE=BlackAwps;49085931]Is your server empty? If so the Tick hook wont get called and all the query callbacks won't get called too. Add a bot and all the callbacks should be called.[/QUOTE]

That explains. Thanks!

[kpjVideo]

How can I start a new connection without closing the previous one in a gamemode?

[P4sca1]

I started my first project, using tmysql4 today and I have some questions:
-Is this automatically reconnecting to the mysql server and are querys queued if they fail?
-I don't understand the use of the 3rd and 4th parameter of the Database:Query function. Can somebody explain?
-tmysql.initialize is the same as creating the db with tmysql.create and using Database:Connect afterwards, right?
Thanks in advance, Pascal

[editline]9th December 2015[/editline]

If I put require("tmysql4") above my Code, what will happen, if it is not installed? So what's the use of this function?

[BlackAwps]

[QUOTE=P4sca1;49281375]-Is this automatically reconnecting to the mysql server and are querys queued if they fail?[/quote]

Yes. It will automatically attempt to reconnect if the connection goes down. It will attempt to re-run the query one more time after a reconnect. If it fails again the query will error with a result saying the connection is down.

[QUOTE=P4sca1;49281375]-I don't understand the use of the 3rd and 4th parameter of the Database:Query function. Can somebody explain?[/quote]

Third parameter is an optional object to be passed to the callback function. If you look in the OP there's a small example of it. The 4th parameter is to change the data table structure to use numbers instead of column names as the table keys.

[QUOTE=P4sca1;49281375]-tmysql.initialize is the same as creating the db with tmysql.create and using Database:Connect afterwards, right?[/quote]

Yep, exactly the same. The only point of using create is if you wanted to set a connection option.

[QUOTE=P4sca1;49281375]If I put require("tmysql4") above my Code, what will happen, if it is not installed? So what's the use of this function?[/QUOTE]

If the module is not installed, the require will error. The use of the function is to load the module.

[matiasg90]

I'm using, works great!

[P4sca1]

Is there any function like query:Wait() from mysqloo?

Because I have a query and I need the code below to wait for the query to be finished.