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

[darkjacky]

[QUOTE=P4sca1;49302613]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.[/QUOTE]

Take a look at [URL="https://facepunch.com/showthread.php?t=1357773&p=48922624&viewfull=1#post48922624"]this[/URL]. I posted an example of how to achieve this. Even though I do not recommend this.
The reason to not want to use my example is that it can cause an infinite loop. It also uses 100% cpu from 1 core as long as it is going.

Usually what you would want to do in such a case is:

/* the script above the query */
db:Query("Select * from dbx", function( q )
    /* The script below the query */ -- now in the query
end )

This is much better but can not always be done. But then my argument would be to make it so it can be done.

[P4sca1]

[QUOTE=darkjacky;49303059]Take a look at [URL="https://facepunch.com/showthread.php?t=1357773&p=48922624&viewfull=1#post48922624"]this[/URL]. I posted an example of how to achieve this. Even though I do not recommend this.
The reason to not want to use my example is that it can cause an infinite loop. It also uses 100% cpu from 1 core as long as it is going.

Usually what you would want to do in such a case is:

/* the script above the query */
db:Query("Select * from dbx", function( q )
    /* The script below the query */ -- now in the query
end )

This is much better but can not always be done. But then my argument would be to make it so it can be done.[/QUOTE]

I solved my problem, now I don't need to wait.
Thanks anyway!

[Main Fighter]

Hello,

The host I am running my server with is running CentOS 6 and the tmysql4 module appears not to work on that system. Doesn't work on a local test I did either but works fine on Ubuntu 14.04.

I put the error that I'm getting on CentOS 6 and some of the other stuff in a pastebin here: [url]http://pastebin.com/j2wbPNcG[/url]

I don't really know if there is much that can be done to fix it but I really don't want to have to rely on mysqloo (which is what I am doing currently). Any help would be appreciated.

Thanks in advanced,
Main Fighter.

[darkjacky]

I just found by accident that calling

tmysql.initialize()

with 0-3 arguments will crash the server. It will however not produce a mdmp.
When using 4 arguments it will not crash. (Unless one or more of them is nil.) Numbers seem to be fine. Functions will also cause a crash. Same for entities.

I think its funny. It took me so long to discover this.

That is crashes is weird though. Here is a tiny part of the LuaBase.h:

//
// Like Get* but throws errors and returns if they're not of the expected type
//
virtual const char* CheckString( int iStackPos = -1 ) = 0;

Tmysql4 uses CheckString meaning it does check. So why does it crash?

Of course you can fix it by doing LUA->CheckType... but really? CheckString should do that.

[andreblue]

[QUOTE=P4sca1;49302613]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.[/QUOTE]

You could always use the call back to set a variable and allow the script to continue when the variable is the correct one.

[P4sca1]

[QUOTE=andreblue;49331160]You could always use the call back to set a variable and allow the script to continue when the variable is the correct one.[/QUOTE]

I tried that, but it was not working like I did. Can you give an example?

[darkjacky]

[QUOTE=P4sca1;49354985]I tried that, but it was not working like I did. Can you give an example?[/QUOTE]

local Position = Vector()
DB:Query("SELECT Position FROM tbl WHERE Candy = 'chocolade'", function( tbl )
    if tbl[1] and tbl[1].data and isvector( tbl[1].data.Position ) then
        Position = tbl[1].data.Position
        return
    end
end)

Something like this I guess.

[P4sca1]

[QUOTE=darkjacky;49355055]

local Position = Vector()
DB:Query("SELECT Position FROM tbl WHERE Candy = 'chocolade'", function( tbl )
    if tbl[1] and tbl[1].data and isvector( tbl[1].data.Position ) then
        Position = tbl[1].data.Position
        return
    end
end)

Something like this I guess.[/QUOTE]

If you use the Position Vector after the callback function, it will always use Vector() because the code below won't wait until Position is updated.

[darkjacky]

[QUOTE=P4sca1;49355419]If you use the Position Vector after the callback function, it will always use Vector() because the code below won't wait until Position is updated.[/QUOTE]

That is correct. It will be 0, 0, 0 until updated. So this code will be for things that do not need the Position at that time. Like spawning an Entity on a certain event.

So in that case this: "You could always use the call back to set a variable and allow the script to continue when the variable is the correct one."
Would make me use this script. The one I said earlier.

local ent = ents.Create( "weapon_crowbar" )
ent:SetColor( Color( 255, 255, 255, 0 ) )

DB.Query( "SELECT SpawnPos FROM Weapontbl WHERE weapon = 'weapon_crowbar'", function( result )
   if result[1] and result[1].data and isvector(result[1].data.SpawnPos) then
      ent:SetPos( result[1].data.SpawnPos )
      ent:Spawn() -- spawn
   else
      ent:Remove() --remove
   end
end )

You can do more things to the entity below that. And it will spawn (or get removed) after the query is done.

[TomatoSoup]

ULX GlobanBanModule has a problem using the wrapper. A timer checks if

(not ULX_DB or ULX_DB:status() ~= mysqloo.DATABASE_CONNECTED) 

is true and, if it is, goes to reconnect to the database.

There it calls

ULX_DB:connect()

which then goes into your wrapper, passes the IsConnected() check, and promptly throws an error about trying to connect to a database that is already connected.

I'm working on rewriting the addon to use straight tmysql4 without going through mysqloo, but it's slow and I thought I should bring this bug to someone's attention.

Another thing I wanted to bring up was your omission of support for :wait() from mysqloo.

I have lots of constructs in my code that look like

    function q:onError(err, sql)
        if db:status() ~= mysqloo.DATABASE_CONNECTED then
            db:connect()
            db:wait()
            if db:status() ~= mysqloo.DATABASE_CONNECTED then
                ErrorNoHalt("Re-connection to database server failed.")
                callback(nil)
                return
            end
        end
        MsgN('TomatoReferrer MySQL: Query Failed: ' .. err .. ' (' .. sql .. ')')
        q:start()
    end

where q is a query, and I was curious what the recommended alternative to using wait is.

[BlackAwps]

Nevermind, I found the issue!

[cpp]
int DBIsConnected(lua_State* state)
{
LUA->CheckType(1, DATABASE_ID);

UserData* userdata = (UserData*)LUA->GetUserdata(1);
Database* mysqldb = (Database*)userdata->data;

if (!mysqldb) {
LUA->ThrowError("Attempted to call IsConnected on a shutdown database");
return 0;
}

LUA->PushBool(mysqldb->IsConnected());
return 0;
}[/cpp]

[cpp]
return 0;
[/cpp]

The problem seems to be IsConnected will ALWAYS return nil since I'm pushing a bool, but not telling the stack there is a return value. WHOOPS. I'll fix this asap.

[TomatoSoup]

Holy shit, I reported a problem and it wasn't just me being a moron! I'll alert the press! Thank you very much for your swift solution!

[BlackAwps]

Haha, no problem.

[url]https://github.com/blackawps/gm_tmysql4/releases[/url]

Here's some builds with a fix in place. IsConnected should return a bool properly now. Let me know if there's any issues with these builds.

[TomatoSoup]

It works! At the very least, the problem I reported isn't a problem anymore.

[Viltsuuu]

Could you include the :IsConnected fix in the newest DLL?

I'm doing this (using mysqloo wrapper)

require('mysqloo')

local db = mysqloo.connect(mysql_hostname, mysql_username, mysql_password, mysql_database, mysql_port)

hook.Add("Think", "lol", function()
	print(db:GetIsConnected())
end)

And this bit of code in the mysqloo wrapper

function gofuckurself:GetIsConnected()
	return self.db:IsConnected()
end

The print doesn't print anything else thank blank spaces.
Otherwise it wouldn't be too big of a problem, but ULX's messing with hook somehow causes pointshop to call db:Connect() after the initial connect :>

edit: Windows

[BlackAwps]

Both of the dll's, linux and windows, are updated with the fix...

[url]https://github.com/blackawps/gm_tmysql4/releases[/url]

I think your problem is you're not calling db:connect()

[lua]require('mysqloo')

local db = mysqloo.connect(mysql_hostname, mysql_username, mysql_password, mysql_database, mysql_port)
db:connect() -- You need this

hook.Add("Think", "lol", function()
print(db:status() == mysqloo.DATABASE_CONNECTED)
end)[/lua]

[Viltsuuu]

[QUOTE=BlackAwps;49400075]Both of the dll's, linux and windows, are updated with the fix...

[url]https://github.com/blackawps/gm_tmysql4/releases[/url]

I think your problem is you're not calling db:connect()

[lua]require('mysqloo')

local db = mysqloo.connect(mysql_hostname, mysql_username, mysql_password, mysql_database, mysql_port)
db:connect() -- You need this

hook.Add("Think", "lol", function()
print(db:status() == mysqloo.DATABASE_CONNECTED)
end)[/lua][/QUOTE]

db:Connect is being called. I just placed that snippet of code in pointshop's mysql.lua. Also, as I was uploading the .dll to the server it was the same size as the old version?

[B]EDIT: [/B]Never mind, issue was on my end. Filezilla didn't overwrite the old .dll

[MrManiak]

Why have result.error and result.status if result.status is just a bool? You can know that the query failed if result.error isn't nil.

Thanks for the release by the way!

[TomatoSoup]

Because that's a more natural paradigm from the module-side, not the Lua side.

[MrManiak]

The way I see it, there should be only one status or error per query result, not one per query returned object.

[B]EDIT[/B]: Found out that sometimes the query result has an error member. My bad.

[darkjacky]

	Database* mysqldb = makeDatabase(state);
	
	std::string error;
	if (!mysqldb->Initialize( error ))
	{
		LUA->PushBool( false );
		LUA->PushString(error.c_str());
		delete mysqldb;
		return 2;
	}

If makeDatabase(state) errors mysqldb will be NULL. It will still try to call mysqldb->Initialize and that causes a crash.

[Spencer Sharkey]

tmysql4 on centos 7 isn't working.
srcds hangs(/runs extremely slowly to the point where it takes 2-3 minutes for tmysql.initialize to execute, then returns nil) after trying to initialize a connection

I'll try compiling it on this system...

Running it with
[U]LD_LIBRARY_PATH=/home/steam/steamapp/gmod/garrysmod/bin:bin:. LD_DEBUG=libs [/U]
Didnt' provide much output, other than at first it did reach libdns to resolve the hostname I was providing the initialize function. I changed it to an IP, and now there's no output and it's hung.

[andreblue]

[QUOTE=Spencer Sharkey;49450336]tmysql4 on centos 7 isn't working.
srcds hangs(/runs extremely slowly to the point where it takes 2-3 minutes for tmysql.initialize to execute, then returns nil) after trying to initialize a connection

I'll try compiling it on this system...

Running it with
[U]LD_LIBRARY_PATH=/home/steam/steamapp/gmod/garrysmod/bin:bin:. LD_DEBUG=libs [/U]
Didnt' provide much output, other than at first it did reach libdns to resolve the hostname I was providing the initialize function. I changed it to an IP, and now there's no output and it's hung.[/QUOTE]

is there a bot on the server or anyone connected to it?

[Spencer Sharkey]

Yes, don't think this is a lua issue. Code works fine with windows module.

Recompiled module on system, still experiencing same problem. Module takes about 3 minutes then eventually returns tmysql.initialize with error 110 (Can't connect to MySQL server on <ip address> (110))
Going to dive deeper into this, possible issue with the supplied mysql lib? Downside of statically linking things...

[BlackAwps]

Can you try release 1 instead of release 1.01?
[url]https://github.com/blackawps/gm_tmysql4/releases[/url]

It really just sounds like an issue with your mysql server though, especially since it is returning an error.

You can change the timeout time by doing this..

[lua]local db, err = tmysql.Create(String hostname, String username, String password, String database, Number port, String unixSocketPath, Number ClientFlags)
local success, err = db:Option(MYSQL_OPT_CONNECT_TIMEOUT, 10) -- Change connection timeout to 10 seconds
local success, err = db:Connect()
[/lua]

Would recommend using asserts to make sure everything works

[lua]local db = assert(tmysql.Create(String hostname, String username, String password, String database, Number port, String unixSocketPath, Number ClientFlags))
assert(db:Option(MYSQL_OPT_CONNECT_TIMEOUT, 10)) -- Change connection timeout to 10 seconds
assert(db:Connect())[/lua]

[nulls]

Hey, stupid question here. I'm new to LUA/GMod development but not to PHP/MySQL. What's the benefit of using this module rather than sending an http.Post request to my server that has files setup to pass data to my database? Speed? Simplicity? I can use prepared statements to make 100% sure that nothing bad like SQL injections are getting passed to my server that way. The way queries are setup and executed with this module just seems like bad practice (in my opinion). Am I wrong? Can someone explain this to me (not trying to be rude, genuinely curious)? Thanks.

[Spencer Sharkey]

[QUOTE=BlackAwps;49457664]Can you try release 1 instead of release 1.01?
[URL]https://github.com/blackawps/gm_tmysql4/releases[/URL]

It really just sounds like an issue with your mysql server though, especially since it is returning an error.

You can change the timeout time by doing this..

local db, err = tmysql.Create(String hostname, String username, String password, String database, Number port, String unixSocketPath, Number ClientFlags)local success, err = db:Option(MYSQL_OPT_CONNECT_TIMEOUT, 10) -- Change connection timeout to 10 secondslocal success, err = db:Connect()

Would recommend using asserts to make sure everything works

local db = assert(tmysql.Create(String hostname, String username, String password, String database, Number port, String unixSocketPath, Number ClientFlags))assert(db:Option(MYSQL_OPT_CONNECT_TIMEOUT, 10)) -- Change connection timeout to 10 secondsassert(db:Connect())
[/QUOTE]

I've been using tmysql.initialize(host, user, password, database, port)
I'll try switching to tmysql.Create and :Connect() to see if that has anything to do with it.

[DEL]Definitely doesn't have anything to do with the server, as it works fine on Windows :)[/DEL]
Edit: It was definitely a server configuration error. I'm a tobbs.

[Awesome123]

Alright so I decided to get tmysql4. Due to all the reasons listed above. So I deleted the default mysql module: gmsv_mysqloo_win32.dll. I also had to install the wrapper. I am having an issue with ulx now not remembering people's user groups. I kept libmysql.dll in the folder with srcds. I am using the normal way to save ulx in the data folder. Does anyone have any ideas why this change made it have issues forgetting ulx groups. I put it back to the mysqloo module and everything works again.

[BlackAwps]

[QUOTE=Marooca;49461304]Hey, stupid question here. I'm new to LUA/GMod development but not to PHP/MySQL. What's the benefit of using this module rather than sending an http.Post request to my server that has files setup to pass data to my database? Speed? Simplicity? I can use prepared statements to make 100% sure that nothing bad like SQL injections are getting passed to my server that way. The way queries are setup and executed with this module just seems like bad practice (in my opinion). Am I wrong? Can someone explain this to me (not trying to be rude, genuinely curious)? Thanks.[/QUOTE]

Doing that would be terrible, slow, and inefficient. This module allows you to communicate with a mysql server directly, allowing you to fetch and update data directly without a middleman. If you wan't prepared statements, do something like in this post.

[url]https://facepunch.com/showthread.php?t=1442438&p=49030625&viewfull=1#post49030625[/url]

[QUOTE=Spencer Sharkey;49462172]Definitely doesn't have anything to do with the server, as it works fine on Windows :)[/QUOTE]

That means nothing to me. You could still have your server misconfigured to not allow your Linux server to connect to it. But seeing how you said the error was that it couldn't connect to the ip, it sounds like a firewall/permissions problem on your end. I know the module works fine on CentOS, having helped someone fix an error with it. I can 100% assure you the issue is with you.

[QUOTE=Awesome123;49463018]Alright so I decided to get tmysql4. Due to all the reasons listed above. So I deleted the default mysql module: gmsv_mysqloo_win32.dll. I also had to install the wrapper. I am having an issue with ulx now not remembering people's user groups. I kept libmysql.dll in the folder with srcds. I am using the normal way to save ulx in the data folder. Does anyone have any ideas why this change made it have issues forgetting ulx groups. I put it back to the mysqloo module and everything works again.[/QUOTE]

ULX must be using something that the wrapper doesn't support, like Database:wait()

[Awesome123]

[QUOTE=BlackAwps;49463254]Doing that would be terrible, slow, and inefficient. This module allows you to communicate with a mysql server directly, allowing you to fetch and update data directly without a middleman.

That means nothing to me. You could still have your server misconfigured to not allow your Linux server to connect to it. But seeing how you said the error was that it couldn't connect to the ip, it sounds like a firewall/permissions problem on your end. I know the module works fine on CentOS, having helped someone fix an error with it. I can 100% assure you the issue is with you.

ULX must be using something that the wrapper doesn't support, like Database:wait()[/QUOTE]

Even if I am not using mysql with ulx? It is a normal data saving. Shouldn't have anything to do with the addon

Edit: Some ranks go through and some don't. Also I kept the tmysql in the server with the mysqloo and it doesnt happen. I even have the wrapper in too.