tmysql4 - A multi-connection version of tmysql3 (Now with mysqloo wrapper!) - Garry's Mod

[QUOTE=MDave;46769339] One last request, there might be a slight disagreement here tough. I personally use a BIT field to store booleans in my databases, could you make that BIT(1) fields are interpreted as booleans please?[/QUOTE] Leave that down to the person writing the lua portion of the script.

[QUOTE=MDave;46769339]While at the topic, could you please fix a long standing issue with pretty much every gmod mysql module leaking memory with binary BLOB or TEXT fields? The problem is caused because the PushString in the gmod module header defaults the length to 0, and uses strlen internally to figure it out[/QUOTE] How does this leak memory? Did you mean to say strdup or what?

[QUOTE=AzuiSleet;46767948]If you require guaranteed ordering (especially transactional logic that you described in your post where you can create race conditions by having multiple money updates post at the same time) you are required to handle ordering on your end regardless. Dispatching two queries and expecting a particular execution or result order is insane. [/QUOTE] Would you please point out how it is insane? I think I have already argued that is quite possible and needed. I have also already mentioned that the lua approach reduces queries per second to 66, that is insane to me. [QUOTE=AzuiSleet;46767948]This is true, but it also requires stealing a connection.[/QUOTE] Escape should be a database method. [QUOTE=AzuiSleet;46767948]There are a couple bugs around this option, including it being reset after mysql_real_connect.[/QUOTE] According to [URL="http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html"]here [/URL]the only real problem is that it is a new connection. What tmysql does is exactly that, it just drops the current connection and creates a new one.

[QUOTE=AzuiSleet;46770394]How does this leak memory? Did you mean to say strdup or what[/QUOTE] Whoops, I was wrong about that, it does not leak memory as lua copies the string. It still cuts all binary text off at the first \0 character. Corrected my original post.

[QUOTE=syl0r;46770421]Would you please point out how it is insane? I think I have already argued that is quite possible and needed. I have also already mentioned that the lua approach reduces queries per second to 66, that is insane to me.[/QUOTE] Transactional logic is going to be required to handle most edge cases. You aren't limited to "66 queries per second" because you can issue as many queries per tick as you like, and the more connections in the connection pool the more results you will get per tick. If you limit yourself to one connection then yes you will be limited to one query per connection. The insane part about trying to serialize into one connection is the fact that you give up aggregate performance to poorly solve edge cases by not waiting for the result of a query (eg: what if your record got updated in MySQL by another process, what if your connection was interrupted before it could be processed)

[QUOTE=AzuiSleet;46770540]Transactional logic is going to be required to handle most edge cases.[/QUOTE] Transactional logic is going to be required where transactional logic is required. On a gameserver you don't know when and in what order people do things, therefore you can't have static transactions for all of it (unless you store all queries and wait for the tick to finish and then execute them). [QUOTE=AzuiSleet;46770540]You aren't limited to "66 queries per second"[/QUOTE] You are if you want to solve it. [QUOTE=AzuiSleet;46770540]The insane part about trying to serialize into one connection is the fact that you give up aggregate performance to poorly solve edge cases by not waiting for the result of a query (eg: what if your record got updated in MySQL by another process, what if your connection was interrupted before it could be processed)[/QUOTE] No, you don't have to give up performance. You can still create multiple database instances and create your own connection pool in lua if you want to (even though it is stupid). And just because you call them edge cases doesn't make them edge cases (whatever that means.....). While my example can be easily solved by using UPDATE .... SET money = money - 450 instead of setting it to a new value every time, there are more complicated cases that can't be solved like that. A real example: In my database I have a table for stacks and items. Items have a foreign key to the stacks they belong to. A user drops an item which sets the stack reference to null. Another user picks the item up in the next tick which sets the stack reference to a stack in his inventory. Using tmysql (or the current mysqloo) I do not know if the stack reference is correct in the database, it could very well be null. You can't have transactions for that since you can't predict that the other user is going to pick up the item in the next tick. The only thing you can do is wait for the query to finish before the user can drop the item. That is completely unecessary though if the order of execution is enforced.

[QUOTE=syl0r;46770661]In my database I have a table for stacks and items. Items have a foreign key to the stacks they belong to. A user drops an item which sets the stack reference to null. Another user picks the item up in the next tick which sets the stack reference to a stack in his inventory. Using tmysql (or the current mysqloo) I do not know if the stack reference is correct in the database, it could very well be null.[/QUOTE] It's unlikely that even MySQLOO with a single connection even enforces order execution. What if you were to for example run the commands to drop the item and pick up the item? There's a possibility that the second thread (pickup query) was run before the first thread (drop query), resulting in the final stack reference to be null. Another example is moving the item in your inventory at the same time that you drop it, how do you guarantee the ordering of those queries? I would think the solution here is optimistic concurrency where you check the affected rows.

[QUOTE=AzuiSleet;46770853]It's unlikely that even MySQLOO with a single connection even enforces order execution. What if you were to for example run the commands to drop the item and pick up the item? There's a possibility that the second thread (pickup query) was run before the first thread (drop query), resulting in the final stack reference to be null.[/QUOTE] MySQLOO doesn't do that at all, it is even more random than in tmysql (every single query has its own thread). I "fixed" MySQLOO for my server since it was causing exactly the issues I mentioned. When each database instance has exactly one thread and that one thread has its own connection to the mysql server, all queries issued to that database instance are executed in order. Setting NUM_THREADS_DEFAULT to 1 in tmysql has the same effect. Edit: I think a solution that makes everyone happy is to allow the user to specify how many threads a database instance should have ;)

Would either windows or Linux versions also work on osx?

[QUOTE=Banhfunbags;46771345]Would either windows or Linux versions also work on osx?[/QUOTE] No, I would have to specifically compile it for OSX and I don't have any means to do so. Anyway, would you guys mind testing this for me and seeing if you like all these changes I made? Temporary downloads [url=http://breakpoint.me/gmsv_tmysql4_win32.dll]Windows[/url] [url=http://breakpoint.me/gmsv_tmysql4_linux.dll]Linux[/url] The changes I made.. [code]Data conversion now uses IS_NUM to determine if the field should be converted to a number or not Field strings now uses the field length (mainly for BLOB/TEXT fields) Can now handle multiple result sets (callback now has a table of results instead of a single table of a single result set) Removed query flags (QUERY_FLAG_ASSOC and QUERY_FLAG_LASTID) lastid is now part of the result table tmysql.initialize now takes a clientflag argument Added all the mysqlclient CLIENT_ flags as globals Changed DATABASE:Escape to use mysql_real_escape_string and removed tmysql.escape Databases now use MYSQL_OPT_RECONNECT (if this causes problems I'll revert it back)[/code] The results table is setup 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]

So there is now no way to have my callback call once for each result without some sort of Lua wrapper?

If everyone wants it to be called more than once, rather than it having it called once with each result set, I can change it.

I've always had my library supply two separate methods that do either. It really simplifies the process when I only want to act upon each result and do nothing if there are none. On the other hand having a method to return all results is important to be able to check if there are none without the need for using COUNT.

[QUOTE=BlackAwps;46771467]Changed DATABASE:Escape to use mysql_real_escape_string and removed tmysql.escape Databases now use MYSQL_OPT_RECONNECT (if this causes problems I'll revert it back)[/QUOTE] When I have a chance, I will take a closer look at these. I suspect escape() will fail under load as there will not be an available connection with 2 connections / 2 threads. You probably need N+1 connections. I will try and reproduce the MYSQL_OPT_RECONNECT behavior I saw if possible. My other concern is setcharset requires that you not have any active queries, but to be safe it may be worth looking into shutting down the threadpool and restarting it afterwards.

Could always just have a connection dedicated to escaping I guess. Yeah, stopping the threadpool when changing charset is probably a good idea, but I don't see any reason to use setcharset at any other time except right after initialize. If it becomes a problem for someone I'll change it.

Wait, does that mean that escape cannot return the escaped string immediately? That would be a bummer.

No. What he means is that there are currently 2 connections being made, and if both of them are under load via queries or whatever, Database:Escape will fail since it needs to use a connection. If we had a dedicated connection alongside the other two, this wouldn't be an issue. I just committed a fix for this.

[QUOTE=AzuiSleet;46772197]When I have a chance, I will take a closer look at these. I suspect escape() will fail under load as there will not be an available connection with 2 connections / 2 threads. You probably need N+1 connections. I will try and reproduce the MYSQL_OPT_RECONNECT behavior I saw if possible. My other concern is setcharset requires that you not have any active queries, but to be safe it may be worth looking into shutting down the threadpool and restarting it afterwards.[/QUOTE] AFAIK you don't actually need the connection not to be used. mysql_real_escape_string doesn't use the connection itself at all, it just uses its characterset for escaping, the characterset is set during mysql_real_connect, so all you have to do is wait for the connection to finish.

could you possibly make a changelog in the OP instead of posting them on the thread only? this thread is eventually going to get big and cluttered and it's going to get hard to track down what got changed/added. ...also for the easy access to see what exactly was touched.

It's not in the OP because I want people to test this new build out first. The downloads in the OP still match the documentation in it too.

Not sure if this is correlated or not but since switching over to tmysql from mysqloo, my server hasn't crashed every few hours as it normally does and it averages ~80 player concurrently for the entire duration.

How is loss of database connection handled?

[QUOTE=erie1555;46774312]Not sure if this is correlated or not but since switching over to tmysql from mysqloo, my server hasn't crashed every few hours as it normally does and it averages ~80 player concurrently for the entire duration.[/QUOTE] I found a few of my crashes also seemed to reduce around the time of my switch a long time ago from mysqloo to tmysql4 to the extent their uptime was so good you would get desync of CurTime() and other similar things :v:

[QUOTE=syl0r;46773240]AFAIK you don't actually need the connection not to be used. mysql_real_escape_string doesn't use the connection itself at all, it just uses its characterset for escaping, the characterset is set during mysql_real_connect, so all you have to do is wait for the connection to finish.[/QUOTE] Yeah, I wanted to confirm this as well. If it doesn't touch any of the state on the connection object, we could get away with re-using a connection that is live. [QUOTE=StonedPenguin;46775138]How is loss of database connection handled?[/QUOTE] You'll want to resubmit queries that return with an error (assuming it wasn't a syntax error). It will retry the connection once for each query. There's no real reason to poll connected status, as simply submitting the query will tell you if it recovered or if you still have a connection issue.

mysql_real_escape_string accesses connection->server_status and connection->charset. I believe you could get problems if a connection is being re-established in one of your worker threads (or if a thread happens to be changing the character set).

[QUOTE=Willox;46777408]mysql_real_escape_string accesses connection->server_status and connection->charset. I believe you could get problems if a connection is being re-established in one of your worker threads (or if a thread happens to be changing the character set).[/QUOTE] The charset is initially set during mysql_real_connect. It can be changed after every query if the client used SET NAMES ..., so that is a problem. I don't really think the reconnect thing is a problem though because why would the charset change after reconnect (unless the user changed it before)? So I guess mysql_real_escape_string is safe to use after the connection was being established, unless the user manually changes the charset.

SET NAMES doesn't modify the connection structure's charset property. You should avoid from using it as it'll leave mysql_real_escape_string giving 'wrong' results.

ok, so it seems that my server experiences a crash right when it's about to map change when it's unable to make a connection to the databases. so I have found out that when a query is repeatedly made, e.g. spammed every few seconds to a disconnected database, returning an error, and the map experiences a map change, then the server will crash and auto restart with a useless mdmp file created which doesn't provide enough info in it. if no queries are being spammed near the map change, then the server will simply hog cpu for a few seconds, and then process the map change. video of no queries being spammed near map change (cpu hog): [url]http://daaaaan.com/storage/videos/tmysql_serverhogvid-12.23.14.mp4[/url] video of queries being spammed near map change (crash): [url]http://daaaaan.com/storage/videos/tmysql_servercrashvid-12.23.14.mp4[/url] i'm running around 4 things that use tmysql on the test server. i'm using latest version of tmysql. i'm simply doing service mysql stop via putty on the server to stop the mysql server, and then watching the databases detect that they couldnt make the query, waiting a few minutes, and changing map > crash or cpu hog and then it proceeds. is this just me or has anyone else experienced this? am I doing something wrong here? also sorry for circling my mouse so much.

Just wanted to confirm that BLOBs now work correctly. Downloading images from an SQL database works like a charm!

[QUOTE=DannyCore;46779638]ok, so it seems that my server experiences a crash right when it's about to map change when it's unable to make a connection to the databases.[/QUOTE] Are you sure the mdmp is useless? I don't have the means to replicate this right now..