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

[DannyCore]

[QUOTE=BlackAwps;46780179]Are you sure the mdmp is useless? I don't have the means to replicate this right now..[/QUOTE]

[url]http://dumps.metastruct.uk.to/dumper.py?dump=exec_cggmod7test1_1678491_crash_2014_12_23T19_10_58C0.mdmp[/url]

[url]http://dumps.metastruct.uk.to/dumper.py?dump=exec_cggmod7test1_1678491_crash_2014_12_23T19_18_39C0.mdmp[/url]

[editline]23rd December 2014[/editline]

ok, did some more testing, and it looks like just not having anything connected at all on a map, and then map changing, will results in a brief moment of cpu hog and then it proceeds to change the map. but there is still that crash issue above that I experienced when a big query was spammed every few seconds.

also it looks like when it attempts to connect to a database, it hogs the cpu for a second or two, then receives the normal error message saying unable to connect, etc. you can notice a difference in load times when switching maps while the databases are offline, and while they're online.

this defiantly does not seem like an issue on my side.

[AzuiSleet]

I've submitted two bug fixes:

[url]https://bitbucket.org/breakpointservers/bkacjios-glua-modules/pull-request/1/assign-a-value-to-query-status/diff[/url]
[url]https://bitbucket.org/breakpointservers/bkacjios-glua-modules/pull-request/2/clean-up-the-escape-connection-on-release/diff[/url]

Here is the "sanity" script I use to check for shutdown issues:
[url]https://gist.github.com/azuisleet/42796408334950cf3c67[/url]

I was not able to reproduce any hanging or crashes on shutdown.

[MDave]

One more idea about the API: Maybe you should not populate the .error field of the
result if there was not an error. Currently if the query passed the .error field contains an empty string.

[BlackAwps]

[QUOTE=AzuiSleet;46780895]I've submitted two bug fixes:

[url]https://bitbucket.org/breakpointservers/bkacjios-glua-modules/pull-request/1/assign-a-value-to-query-status/diff[/url]
[url]https://bitbucket.org/breakpointservers/bkacjios-glua-modules/pull-request/2/clean-up-the-escape-connection-on-release/diff[/url]

Here is the "sanity" script I use to check for shutdown issues:
[url]https://gist.github.com/azuisleet/42796408334950cf3c67[/url]

I was not able to reproduce any hanging or crashes on shutdown.[/QUOTE]

Whoops, thanks for that!

[QUOTE=MDave;46781111]One more idea about the API: Maybe you should not populate the .error field of the
result if there was not an error. Currently if the query passed the .error field contains an empty string.[/QUOTE]

I'll do that now.

[editline]23rd December 2014[/editline]

Ok, the error string will no longer be passed to the result table if the status is true. I updated the downloads with new builds that has AzuiSleets changes.

DannyCore, you should see if you can replicate your issue with this new build.

[url=http://breakpoint.me/gmsv_tmysql4_win32.dll]Windows[/url]
[url=http://breakpoint.me/gmsv_tmysql4_linux.dll]Linux[/url]

[DannyCore]

[QUOTE=BlackAwps;46781501]Whoops, thanks for that!

I'll do that now.

[editline]23rd December 2014[/editline]

Ok, the error string will no longer be passed to the result table if the status is true. I updated the downloads with new builds that has AzuiSleets changes.

DannyCore, you should see if you can replicate your issue with this new build.

[url=http://breakpoint.me/gmsv_tmysql4_win32.dll]Windows[/url]
[url=http://breakpoint.me/gmsv_tmysql4_linux.dll]Linux[/url][/QUOTE]

hey thanks, it seems to have prevented that cpu hog on changelevel when it was disconnected from the databases. but this build has issues.

nothing is loading like it used to? what changes did you make, because no data is loading, escape is returning a nil value (attempt to call field 'escape' (a nil value)). I didn't change anything but update the build.

also, when connecting to the database, there is still that 1-3 second wait before marking it as unable to connect, which can still be noticed.

[BlackAwps]

tmysql.escape was removed in favor of Database:Escape

It seems to be returning data fine for me and the timeout on a connection can't really be fixed as tmysql.initialize isn't threaded.

[img]http://i.imgur.com/H6Yz0DP.png[/img]

[DannyCore]

[QUOTE=BlackAwps;46781990]tmysql.escape was removed in favor of Database:Escape

It seems to be returning data fine for me and the timeout on a connection can't really be fixed as tmysql.initialize isn't threaded.

[img]http://i.imgur.com/H6Yz0DP.png[/img][/QUOTE]

older version works, this one doesn't.

local row = results[1]
local points = row[2] or 0

what exactly do I need to change there? it seems you updated a lot and i'm lost. nothing is being retrieved nor updated correctly, so I guess i need to fix how I find things in results

and okay, I mean that little 1-3 wait isn't a problem, but can be one. thanks for letting me know.

[BlackAwps]

Oh, I see why you're confused. This is a new test version where it support multiple result sets.

If you just want a single result set, do this..

[lua]DATABASE:Query("SELECT SOMETHING HERE", function(results, status, error)
local result = results[1].data
PrintTable(result)
end)[/lua]

Pretty much I overhauled how it returns data.. There's no more QUERY_FLAG_*'s and everything you need is passed to the new results table. It also defaults to using QUERY_FLAG_ASSOC.

The structure of the results table is like so..

[lua]Results {
[1] = {
status = true/false,
error = the error string,
affected = number of rows affected by the query,
lastid = the index of the last row inserted by the query,
data = {
{
somefield = "some shit",
}
},
},
}[/lua]

[Teddi]

So far I've got it loading data and storing data no problem. Just in the process of testing multiple results sets.

[editline]24th December 2014[/editline]

Did errors break again? I can't seem to get them to load at all now.

Edit: Nevermind, got it.

Yep, Multiple data sets are working from what I can see.

[MDave]

Found a quite serious bug. Turns out I was not right with BLOBs working correctly. Previously my
tests only returned a single row, and in that case the code works right.

mysql_fetch_lengths(result) required to be called at each row! Now the module returns the first row
correct, and the rest gets cut/extended to the first rows lengths

[editline]24th December 2014[/editline]

Also if you execute multiple queries, and one of them fails, the error won't show, and further result sets will be omitted.

The problem is here:

-- database.cpp 165-166
    status = mysql_next_result(pMYSQL);
} while (status == 0);

mysql will return -1 when there are no more result sets, and >0 if an error occured.

[BlackAwps]

Fixed!

[editline]24th December 2014[/editline]

So like I'm pretty sure we ironed out most of the issues, so I think I'm going to release this as it stands now.

The latest build has a few minor changes to make it all consistent. The callback function only passes the results table now. There's no more status, error arguments because the each result set can have its own error.

I'll update the OP to reflect all the changes I've made..

[Mitsudigi]

Everything is working great so far. Keep up the good work.

[DannyCore]

the results change got me confused at first..

but now I understand, and I feel it's better and more organized than before.

time to convert everything again :c

good work btw, much better than mysqloo. crashes have reduced significantly.

[Teddi]

[QUOTE=DannyCore;46793835]the results change got me confused at first..

but now I understand, and I feel it's better and more organized than before.

time to convert everything again :c

good work btw, much better than mysqloo. crashes have reduced significantly.[/QUOTE]

You're probably better off writing your own wrapper for ease of life and to accommodate any (unlikely) future changes that may happen to this module or should you choose a different module.

[BomBom]

Is there any point giving the lastid for a result which doesn't insert data?
Just wondering since it is currently being returned with a value of 0 for every result.

[Teddi]

I seem to have come across a bug, at least with stored procedures. If I use conditionals within the procedure (standard IF check), to say SELECT something, tmysql recognises two sets of results being returned, not one. The first returned results will be what I'm after, however the second set of results is the standard response, but with an empty set of data.

Running it via cli or other applications that incorporate mysql don't have this issue.

[editline]4th January 2015[/editline]

Edit - also client_flags is set to 0, so multiple return sets shouldn't be happening in the first place.

[BlackAwps]

Can you give me an example or something to work with that I can use to test with? I'm really not that all familiar with stored procedures.

[Teddi]

It's a bit dumbed down, but oriented around loading a player's data from the database. If it doesn't exist, then we create a new row and select that.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- replace root with whatever username you use.
-- likewise, do the same with loadExample if you want a different name.
-- Call with CALL loadExample('Steamidhere')
-- --------------------------------------------------------------------------------
DELIMITER $$


CREATE DEFINER=`root`@`%` PROCEDURE `loadExample`(SteamID VARCHAR(25))
BEGIN
	IF EXISTS (SELECT * FROM table WHERE steamid = SteamID) THEN
		SELECT * FROM table WHERE steamid = SteamID;
	ELSE
		INSERT INTO table (steamid) VALUES (SteamID);
		SELECT * FROM table WHERE steamid = SteamID;
	END IF;
END

[BlackAwps]

[quote]To write C programs that use the CALL SQL statement to execute stored procedures that produce result sets, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. CLIENT_MULTI_RESULTS must also be enabled if CALL is used to execute any stored procedure that contains prepared statements. It cannot be determined when such a procedure is loaded whether those statements will produce result sets, so it is necessary to assume that they will.[/quote]

This is probably why you're getting weird results then, seeing how you said client flags were set to 0, so I don't think this is a problem on the modules end.

[Teddi]

[QUOTE=BlackAwps;46869824]This is probably why you're getting weird results then, seeing how you said client flags were set to 0, so I don't think this is a problem on the modules end.[/QUOTE]

That's interesting actually, I never knew that was directly the case. It's just funny as some of their own stuff purposely ignores the extra returned status.

Oh well, I'm handling it anyhow now, thanks!

[Santamon]

Just a minor problem, the auto-number casting causes accuracy loss with 64bit Steam ID's (MySQL BITINTS) when you're fetching them from your SQL DB as they get auto-converted to numbers in lua. Would it be possible to get BIGINT's be auto-converted into strings instead.

[BlackAwps]

Updated it to ignore bigint's so it should be a string now. Updated the downloads/repo.

[Badutski]

Can someone with some experience with this, or just a brain, which I clearly don't have..
I'm looking to have 5 servers connected with 1 MySQL connection, Is that possible? and if it is, can I have some help setting it up?

[Donkie]

After being plagued by table locks for weeks I've come to the conclusion that it's my usage of BEGIN...COMMIT along with the multi-threaded nature of tMySQL that makes it all get stuck on the BEGIN command, then executing the COMMIT command on a different thread, locking the whole shit up.

The way I do it now is basically

Query BEGIN
for all players do
   Query Insert someshit
End
Query COMMIT

What is the proper way to do this using tMySQL?

[BlackAwps]

All queries should be sent to a single active connection. There's no way a query would be split between two threads, it must be something else.

So long as you are doing this...

[lua]DATABASE:Query([[Query BEGIN
for all players do
Query Insert someshit
End
Query COMMIT]])[/lua]

It will execute on one of 4 threads that is currently not being used.

[Donkie]

The way I have been doing it is 1 query for each statement, but I guess I should bake it all into one string, right?

[BlackAwps]

Worth a shot.

[charozoid]

Does anyone have a OS X version of tmysql4?

[Gravious]

Could a timeout for a connection (MYSQL_OPT_CONNECT_TIMEOUT or mysql->options.connect_timeout = 3;) be a thing?
Is this even necessary or does it automatically throw a safe error when the destination server isn't responding?
With mysqloo there's an issue where the server just crashes if the destination server is unreachable.

[Giraffen93]

Can i keep track of semicolon separated queries (multiple) somehow?
I want to get the last insert id but also know which row it belongs to (identified by a lua variable)..