Experimal support for this module has been added to MySQLite:
[url]https://github.com/FPtje/MySQLite/commit/05607df41261c43ce78731a0ae79e67a67c1ea51[/url]
And the net change is only 15 added lines :D
MySQLite is an abstraction mechanism I use in FPP and DarkRP. Regardless of whether the end user decides to use SQLite, MySQLOO or tmysql4, the interface of MySQL remains the same. It's very useful.
I haven't merged it with FPP and DarkRP yet because I want people to test it :)
I take it tmysql4 is better than MySQLOO then looking at the comparisons. I've never had a crash with MySQLOO in years of using it though, can't understand why people are having these issues.
Alright, Giraffen93 helped me find a bug: everything is a string.
[code]
] lua_run MySQLite.query("SELECT * FROM darkrp_player LIMIT 1", function(a) PrintTable(a) for k,v in pairs(a[1]) do print(k, type(v)) end end, print)
> MySQLite.query("SELECT * FROM darkrp_player LIMIT 1", function(a) PrintTable(a) for k,v in pairs(a[1]) do print(k, type(v)) end end, print)...
1:
rpname = (FPtje) Falco
salary = 45
uid = 2044737759
wallet = 12540
rpname string
salary string
uid string
wallet string
[/code]
The darkrp_player table looks like this:
[t]http://i.imgur.com/MwLfY15.png[/t]
This is the output when using MySQLOO:
[code]
] lua_run MySQLite.query("SELECT * FROM darkrp_player LIMIT 1", function(a) PrintTable(a) for k,v in pairs(a[1]) do print(k, type(v)) end end, print)
> MySQLite.query("SELECT * FROM darkrp_player LIMIT 1", function(a) PrintTable(a) for k,v in pairs(a[1]) do print(k, type(v)) end end, print)...
1:
rpname = (FPtje) Falco
salary = 45
uid = 2044737759
wallet = 12585
rpname string
salary number
uid string
wallet number
[/code]
Please fix this, I don't want to try to cast everything to int in MySQLite.
It's not really a bug, that's just what tmysql has always done, but I'll see if I can change it.
[editline]18th December 2014[/editline]
I think I got it.
[img]http://i.imgur.com/8zomFsd.png[/img]
[cpp]if (row[i] == NULL)
LUA->PushNil();
else if (field->type == MYSQL_TYPE_DECIMAL ||
field->type == MYSQL_TYPE_NEWDECIMAL ||
field->type == MYSQL_TYPE_FLOAT ||
field->type == MYSQL_TYPE_DOUBLE ||
field->type == MYSQL_TYPE_TINY ||
field->type == MYSQL_TYPE_SHORT ||
field->type == MYSQL_TYPE_INT24 ||
field->type == MYSQL_TYPE_BIT ||
field->type == MYSQL_TYPE_LONG)
LUA->PushNumber(atof(row[i]));
else
LUA->PushString(row[i]);[/cpp]
Am I missing anything? MySQL data types are so weird.
[editline]18th December 2014[/editline]
Well, I updated the downloads to have it convert the value into a number if the field type is one of those types above. The repo has been updated too.
Does this fix sql errors not showing up as well?
Edit: No it does not.
In your Example:
[lua]local function onPlayerCompleted( ply, results, status, error )[/lua]
Should be:
[lua]local function onPlayerCompleted( results, ply, status, error )[/lua]
After my testing.
Dude, I love you x10
miles better than mysqloo, thank you. we've needed a decent mysql module for a while.
[QUOTE=Phoenixf129;46757445]In your Example:
[lua]local function onPlayerCompleted( ply, results, status, error )[/lua]
Should be:
[lua]local function onPlayerCompleted( results, ply, status, error )[/lua]
After my testing.[/QUOTE]
That's a bug then, the object should come first so you can call methods that use self. I fixed this and uploaded new dlls.
[editline]20th December 2014[/editline]
[QUOTE=Teddi Orange;46756427]Does this fix sql errors not showing up as well?
Edit: No it does not.[/QUOTE]
Yes it does.
[img]http://i.imgur.com/nZiJWRb.png[/img]
how would one go to check the status of the connection, like mysqloo with its status()?
edit: also, since this doesn't seem to have something like lastInsert() like mysqloo, does anyone know how I should go by this issue? I'm trying to convert a sourcebans module, but I'm unsure as to how I would find the most recent ban id without something like lastInsert().
nvm, went a different way. discovered it isn't even needed, but having lastInsert() or something like this on tmysql4 would probably be useful.
still wondering though what are some ways I could go by using some type of status() when checking a connection.
great module btw
[QUOTE=DannyCore;46759326]how would one go to check the status of the connection, like mysqloo with its status()?[/QUOTE]
[nevermind, I'm wrong]
[QUOTE=DannyCore;46759326]how would one go to check the status of the connection, like mysqloo with its status()?[/QUOTE]
There's really no need for it. The module will automatically try to reconnect and attempt the query again if the connection fails for some reason. Please see point number 3 in [url=http://facepunch.com/showthread.php?t=1441753&p=46714306&viewfull=1#post46714306]AzuiSleet's post[/url]. Plus, there's actually two connections being made per Database object.
[QUOTE=DannyCore;46759326]edit: also, since this doesn't seem to have something like lastInsert() like mysqloo, does anyone know how I should go by this issue? I'm trying to convert a sourcebans module, but I'm unsure as to how I would find the most recent ban id without something like lastInsert().[/QUOTE]
Use QUERY_FLAG_LASTID
This will cause the error argument in the callback to be the last id inserted.
Example:
[lua]Database:Query("INSERT INTO members VALUES('Something', 23, 'other stuff')", function(results, status, lastid)
print("ID was", lastid)
end, QUERY_FLAG_LASTID)[/lua]
What library does the linux version of tmysql4 require?
[lua]
> require("tmysql4")...
[ERROR] lua_run:1: Couldn't load module library!
[/lua]
The only dependency it should need is libmysqlclient.so
You can run ldd on it to see what you are missing.
[code]ldd gmsv_tmysql4_linux.dll
linux-gate.so.1 (0xb7714000)
libmysqlclient.so.18 => /usr/lib/i386-linux-gnu/libmysqlclient.so.18 (0xb73ba000)
libstdc++.so.6 => /usr/lib/i386-linux-gnu/libstdc++.so.6 (0xb72c8000)
libm.so.6 => /lib/i386-linux-gnu/i686/cmov/libm.so.6 (0xb7281000)
libgcc_s.so.1 => /lib/i386-linux-gnu/libgcc_s.so.1 (0xb7264000)
libc.so.6 => /lib/i386-linux-gnu/i686/cmov/libc.so.6 (0xb70b9000)
/lib/ld-linux.so.2 (0xb7717000)
libpthread.so.0 => /lib/i386-linux-gnu/i686/cmov/libpthread.so.0 (0xb709d000)
libz.so.1 => /lib/i386-linux-gnu/libz.so.1 (0xb7080000)
libdl.so.2 => /lib/i386-linux-gnu/i686/cmov/libdl.so.2 (0xb707a000)[/code]
[QUOTE=BlackAwps;46758271]That's a bug then, the object should come first so you can call methods that use self. I fixed this and uploaded new dlls.
[editline]20th December 2014[/editline]
Yes it does.
[IMG]http://i.imgur.com/nZiJWRb.png[/IMG][/QUOTE]
It doesn't seem to catch errors from stored procedures.
Scratch that, it does error on normal queries, but gives this error instead -
[IMG]http://teddi.eu/ss/2014-12-21_00-47-05.png[/IMG]
(just throwing out there when queries are successful it does return 0 and data is returned)
[QUOTE=Teddi Orange;46760100]
[IMG]http://teddi.eu/ss/2014-12-21_00-47-05.png[/IMG]
(just throwing out there when queries are successful it does return 0 and data is returned)[/QUOTE]
The commands out of sync error means that the client did not handle result sets correctly. It is because the module only expects a single result set, but most mysql CALLs produce more than that. tmysql only handles the first, and leaves the rest there, causing an error. [URL="http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html"]There is even a full chapter about that in the mysql docs[/URL]
[QUOTE=MDave;46762336]The commands out of sync error means that the client did not handle result sets correctly. It is because the module only expects a single result set, but most mysql CALLs produce more than that. tmysql only handles the first, and leaves the rest there, causing an error. [URL="http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html"]There is even a full chapter about that in the mysql docs[/URL][/QUOTE]
Which is problematic as I'm only returning 1 result set :). This only happens if the query has an error - think invalid syntax. It doesn't happen with queries that are actually functioning as intended (or procedures).
(I have read the mySQL docs which is why I'm flagging this as an error and not an issue on my side).
First off, tmysql definitely needed multiple connections, so good job for adding those.
Here are just a bugs/suggestions:
1. NUM_THREADS_DEFAULT should be set to 1, having 2 connections can cause to concurrency issues and unexpected/undefined behaviour when you spawn 2 queries in the same tick.
2. You should use mysql_real_escape_string for escaping strings since mysql_escape_string is deprecated (You also seem to have some new and delete mismatch going on, you must use delete[] for arrays) (gm_tmysql.cpp:77-89)
3. There should be an affected rows option
4. mysql_store_result can also cause an error, you aren't checking for that though (database.cpp:175)
5. You are actually saving a pointer to MYSQL_RES in the query thread, then you are using it later on in the main thread, I am not entirely sure if that is all safe.
6. Why not just use the autoreconnect that comes with mysql?
[CODE]mysql_options(m_sql, MYSQL_OPT_RECONNECT, true);[/CODE]
For the commands out of sync thing, just call [URL="http://dev.mysql.com/doc/refman/5.0/en/mysql-next-result.html"]mysql_next_result()[/URL] and free all additional result sets.
If you fix all these issues it's definitely going to be a good alternative to (the utterly broken) mysqloo.
[QUOTE=syl0r;46764998]
1. NUM_THREADS_DEFAULT should be set to 1, having 2 connections can cause to concurrency issues and unexpected/undefined behaviour when you spawn 2 queries in the same tick.
[/QUOTE]
Concurrency issues? Unexpected behaviour? Are you talking about your own inability to handle it or are you saying the module is buggy?
Just assume the order of results is arbitrary and you'll be absolutely fine. You cannot get race conditions in Lua. Only the module itself can have them, which should be reported as bugs. You can [I]easily[/I] work with this and it should not be a reason to restrain one of the greatest features of this module.
It makes sense, when you run "SELECT * FROM HUGETABLE" and "SELECT age FROM People WHERE id = 1075" after one another, you should expect the latter can give its result before the first one.
[QUOTE=FPtje;46765333]Concurrency issues? Unexpected behaviour? Are you talking about your own inability to handle it or are you saying the module is buggy?
Just assume the order of results is arbitrary and you'll be absolutely fine. You cannot get race conditions in Lua. Only the module itself can have them, which should be reported as bugs. You can [I]easily[/I] work with this and it should not be a reason to restrain one of the greatest features of this module.[/QUOTE]
I will just link to a [URL="http://facepunch.com/showthread.php?t=1426056&p=46040762&viewfull=1#post46040762"]post [/URL]where I explained it.
While lua itself can't produce race conditions (since it is single threaded) the usage of the module can (lua is just one thread, the database is another one). The possible undefined behaviour is caused by the undefined order the queries are executed in.
And just to be clear, I am not talking about the order the results come in, that is only a minor problem.
To be even more clear, NUM_THREADS_DEFAULT sets the number of connections per database instance. So setting it to 1 still means you can have multiple open connections by having multiple database instances.
This is a huge problem. The only way to solve these issues 100% is to implement your own query queue in lua. This will reduce the theoretical ~10000 queries a second that can be executed to 66 queries a second since it has to wait for the next tick.
Alright, so I think this is what I'm going to do..
I think I'm just going to overhaul how most of tmysql works.
Would everyone like if I made the query callback pass a table of results that use a result metatable? For example..
[lua]DATABASE:Qeury("SELECT stuff FROM thing1; SELECT bananas FROM thing2;", function(status, results)
local result = results[1]
if not result:GetStatus() then
print(result:GetError())
else
print(result:GetAffectedRows())
print(result:GetLastID())
PrintTable(result:GetData(QUERY_FLAG_ASSOC))
end
end)[/lua]
[QUOTE=BlackAwps;46765676]Alright, so I think this is what I'm going to do..
I think I'm just going to overhaul how most of tmysql works.
-stuff-
[/QUOTE]
As long as you can keep it as lean as the current version where possible, I'll be happy.
I'll hopefully have this finished tomorrow because I got to go for now. All I need to implement is GetData.
[img]http://i.imgur.com/8f5ivkn.png[/img]
Literally all I did was add a new class to store all the data and stuff and made a metatable to access that data. It should hardly add any bloat. Plus, I'll allow client-flags to be passed to tmysql.initialize so you can enable multi-result sets yourself if you need them.
now the question is, is tmysql4 better than mysqlOO
performence-wise
The previous iterations of tmysql4 were far superior to mysqlOO, bar one or two minor caveats that weren't really so critical.
[QUOTE=syl0r;46764998]1. NUM_THREADS_DEFAULT should be set to 1, having 2 connections can cause to concurrency issues and unexpected/undefined behaviour when you spawn 2 queries in the same tick.[/QUOTE]
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=syl0r;46764998]
2. You should use mysql_real_escape_string for escaping strings since mysql_escape_string is deprecated (You also seem to have some new and delete mismatch going on, you must use delete[] for arrays) (gm_tmysql.cpp:77-89)[/QUOTE]
This is true, but it also requires stealing a connection.
[QUOTE=syl0r;46764998]
4. mysql_store_result can also cause an error, you aren't checking for that though (database.cpp:175)
5. You are actually saving a pointer to MYSQL_RES in the query thread, then you are using it later on in the main thread, I am not entirely sure if that is all safe.[/QUOTE]
It is safe to use the MYSQL_RES that was allocated for you by mysqlclient.
[QUOTE=syl0r;46764998]
6. Why not just use the autoreconnect that comes with mysql?
[CODE]mysql_options(m_sql, MYSQL_OPT_RECONNECT, true);[/CODE]
[/QUOTE]
There are a couple bugs around this option, including it being reset after mysql_real_connect.
While at the topic, could you please fix a long standing issue with pretty much every gmod mysql
[del]module leaking memory[/del] handling BLOB and TEXT fields wrong? 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, so fixing it would be
just passing the correct length to PushString:
[code] PushString( const char* val, unsigned int iLen = 0 ) [/code]
You can determine the length of each field/BLOB with [URL="http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-lengths.html"]unsigned long *mysql_fetch_lengths(MYSQL_RES* res)[/URL]
About one more thing, instead of calling mysql_fetch_field_direct for each row while you convert the values,
you could use [URL="http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-fields.html"]MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res)[/URL] to obtain an array of all fields in the result set.
(As this remains the same while processing a result set)
Also there is an IS_NUM(field->type) macro to determine whether a field is a numeric type.
Don't get be wrong, I am not trying to educate you or anything, it just makes me happy there is finally someone
who listens to the community, and has the knowledge to fix up a module properly.
[editline]22nd December 2014[/editline]
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?
Sorry, you need to Log In to post a reply to this thread.
