PDA

View Full Version : Asynchronous mysql queries



IzNoGoD
25th March 2014, 22:53
Hey all
Im currently working on asynchronous mysql queries for libcod. This means you can have multiple mysql queries in parallel, and they wont lagg your game if they take a little longer than the frametime.

The downside is that they are not guaranteed to end within the frame, the game will keep running, so you shouldnt use them for really time-critical things.

It's working as of now, but still proof-of-concept code. I will optimize it and it will be in libcod soon :)

IzNoGoD
9th April 2014, 20:39
Ok, it's been added and pushed and updated and....

here is a helper function for you:


init_async_mysql()
{
host = getcvar("mysql_host");
user = getcvar("mysql_user");
port = getcvarint("mysql_port");
pass = getcvar("mysql_password");
db = getcvar("mysql_database");
mysql_async_initializer(host, user, pass, db, port, 4);
level.mysql_async = [];
while(true)
{
list = mysql_async_getdone_list();
for(i = 0; i < list.size; i++)
{
//iprintln("Query done");
result = mysql_async_getresult_and_free(list[i]);
if(!isdefined(result))
continue;
if(result == 0)
{
result = undefined;
}
f = level.mysql_async["" + list[i]];
if(isdefined(f))
{
if(isdefined(f.function))
{
thread [[f.function]](result, f.args);
}
else if(isdefined(result))
{
mysql_free_result(result);
}
f = undefined;
}
level.mysql_async["" + list[i]] = undefined;
}
wait .05;
}
}

add_async_query_nosave(q, function, args)
{
if(getcvarint("show_mysql") == 1)
printf("mysql_query async nosave:" + q + "\n");
id = mysql_async_create_query_nosave(q);
f = spawnstruct();
f.function = function;
f.args = args;
level.mysql_async["" + id] = f;
}

add_async_query(q, function, args)
{
if(getcvarint("show_mysql") == 1)
printf("mysql_query async:" + q + "\n");
id = mysql_async_create_query(q);
f = spawnstruct();
f.function = function;
f.args = args;
level.mysql_async["" + id] = f;
}
Usage is like this then:


some_function()
{
args = [];
args[0] = self;
add_async_query("SELECT * FROM table ORDER BY foo LIMIT 1000", ::bar, args);
}

bar(result, args)
{
player = args[0];
if(!isdefined(player))
{
if(isdefined(result))
mysql_free_result(result);
return;
}
if(isdefined(result))
{
rowcount = mysql_num_rows(result);
for(i = 0; i < rowcount; i++)
player iprintln(mysql_fetch_row(result)[0]);
mysql_free_result(result);
}
}

Good luck with it :)

IzNoGoD
23rd October 2014, 14:29
Just a heads-up: I found a critical bug in a version of which I'm unsure I released. Updates to this are handed in to kung foo man and awaiting release.

Also, this update will remove the need for c++ threads (which had some lib errors at my new host) and use pthread instead (c-based only)

tl;dr: once new update is out, please use it.

IzNoGoD
1st November 2014, 13:43
async mysql is now used by the kingbot mod. Using a mysql server in europe, the australian server and the US server both get access to exactly the same data - without any lagg due to mysql pings :)

IzNoGoD
2nd March 2016, 04:41
Big update: Cleaned up the mysql files and made the stuff make more sense.

Here goes:


init()
{
//get your host, user, pass, db, port here
level.JH_mysql = mysql_reuse_connection();
if(!isDefined(level.JH_mysql))
level.JH_mysql = initMySQL(host, user, pass, db, port);
initAsyncMySQL(host, user, pass, db, port);
}

query(query)
{
if(!isDefined(level.JH_mysql))
return undefined;
result = mysql_query(level.JH_mysql, query);
resettimeout();
if(result)
{
printf("Error in " + query + "\n");
return undefined;
}
result = mysql_store_result(level.JH_mysql);
rows = getRows(result);
return rows;
}

queryNosave(query)
{
if(!isDefined(level.JH_mysql))
return undefined;
result = mysql_query(level.JH_mysql, query);
resettimeout();
if(result)
{
printf("Error in " + query + "\n");
return undefined;
}
return [];
}

getRows(result)
{
if(!isDefined(result))
return [];
rowcount = mysql_num_rows(result);
fields = [];
field = mysql_fetch_field(result);
while(isDefined(field))
{
fields[fields.size] = field;
field = mysql_fetch_field(result);
}
rows = [];
for(i = 0; i < rowcount; i++)
{
row = mysql_fetch_row(result);
rows[rows.size] = [];
for(j = 0; j < fields.size; j++)
rows[rows.size - 1][fields[j]] = row[j];
}
mysql_free_result(result);
return rows;
}

asyncQuery(query, function, args)
{
id = mysql_async_createQuery(query);
task = spawnstruct();
task.query = query;
task.invoker = self;
task.function = function;
task.args = args;
level.JH_mysqlAsync["" + id] = task;
}

asyncQueryNosave(query, function, args)
{
id = mysql_async_createQueryNosave(query);
task = spawnstruct();
task.query = query;
task.invoker = self;
task.function = function;
task.args = args;
level.JH_mysqlAsync["" + id] = task;
}

initAsyncMySQL(host, user, pass, db, port)
{
mysql_async_initializer(host, user, pass, db, port, 4);
level.JH_mysqlAsync = [];
thread loopAsyncMySQL();
}

loopAsyncMySQL()
{
while(true)
{
list = mysql_async_GetDoneList();
for(i = 0; i < list.size; i++)
{
result = mysql_async_getResultAndFree(list[i]);
if(!isdefined(result))
continue;
if(result == 0)
result = undefined;
task = "" + list[i];
if(isDefined(level.JH_mysqlAsync[task]))
{
if(isDefined(level.JH_mysqlAsync[task].function))
{
if(isDefined(level.JH_mysqlAsync[task].invoker))
{
rows = getRows(result);
level.JH_mysqlAsync[task].invoker thread [[level.JH_mysqlAsync[task].function]](rows, level.JH_mysqlAsync[task].args);
}
else if(isDefined(result))
mysql_free_result(result);
}
else if(isDefined(result))
mysql_free_result(result);
}
else if(isDefined(result))
mysql_free_result(result);
level.JH_mysqlAsync[task] = undefined;
}
wait .05;
}
}

initMySQL(host, user, pass, db, port)
{
mysql = mysql_init();
ret = mysql_real_connect(mysql, host, user, pass, db, port);
if(!ret)
{
printf("errno=" + mysql_errno(mysql) + " error= " + mysql_error(mysql) + "\n");
mysql_close(mysql);
return undefined;
}
return mysql;
}
stripString(string)
{
if(isDefined(level.JH_mysql))
return mysql_real_escape_string(level.JH_mysql, string);
return "";
}


How to use:
Call init() at game start.

Call query() and queryNosave() for stuff at gamestart or non-latency queries (not recommended though)

Call asyncQuery() and asyncQueryNosave() for your runtime queries.

Example:



foo()
{
asyncQuery("SELECT NOW() AS `time`", ::showTime);
}

showTime(rows, args)
{
if(isDefined(rows) && isDefined(rows[0]) && isDefined(rows[0]["time"]))
iprintlnbold("It is " + rows[0]["time"]);
}


Additional args will be passed to the secondary function as well, as long as it is a single var or a single array. The helper function will be called on the original invoker, and auto-free-s and auto-quits if the invoker is gone (useful for disconnecting players etc)

Whiskas
7th March 2016, 13:39
Big update: Cleaned up the mysql files and made the stuff make more sense.

Here goes:


init()
{
//get your host, user, pass, db, port here
level.JH_mysql = mysql_reuse_connection();
if(!isDefined(level.JH_mysql))
level.JH_mysql = initMySQL(host, user, pass, db, port);
initAsyncMySQL(host, user, pass, db, port);
}

query(query)
{
if(!isDefined(level.JH_mysql))
return undefined;
result = mysql_query(level.JH_mysql, query);
resettimeout();
if(result)
{
printf("Error in " + query + "\n");
return undefined;
}
result = mysql_store_result(level.JH_mysql);
rows = getRows(result);
return rows;
}

queryNosave(query)
{
if(!isDefined(level.JH_mysql))
return undefined;
result = mysql_query(level.JH_mysql, query);
resettimeout();
if(result)
{
printf("Error in " + query + "\n");
return undefined;
}
return [];
}

getRows(result)
{
if(!isDefined(result))
return [];
rowcount = mysql_num_rows(result);
fields = [];
field = mysql_fetch_field(result);
while(isDefined(field))
{
fields[fields.size] = field;
field = mysql_fetch_field(result);
}
rows = [];
for(i = 0; i < rowcount; i++)
{
row = mysql_fetch_row(result);
rows[rows.size] = [];
for(j = 0; j < fields.size; j++)
rows[rows.size - 1][fields[j]] = row[j];
}
mysql_free_result(result);
return rows;
}

asyncQuery(query, function, args)
{
id = mysql_async_createQuery(query);
task = spawnstruct();
task.query = query;
task.invoker = self;
task.function = function;
task.args = args;
level.JH_mysqlAsync["" + id] = task;
}

asyncQueryNosave(query, function, args)
{
id = mysql_async_createQueryNosave(query);
task = spawnstruct();
task.query = query;
task.invoker = self;
task.function = function;
task.args = args;
level.JH_mysqlAsync["" + id] = task;
}

initAsyncMySQL(host, user, pass, db, port)
{
mysql_async_initializer(host, user, pass, db, port, 4);
level.JH_mysqlAsync = [];
thread loopAsyncMySQL();
}

loopAsyncMySQL()
{
while(true)
{
list = mysql_async_GetDoneList();
for(i = 0; i < list.size; i++)
{
result = mysql_async_getResultAndFree(list[i]);
if(!isdefined(result))
continue;
if(result == 0)
result = undefined;
task = "" + list[i];
if(isDefined(level.JH_mysqlAsync[task]))
{
if(isDefined(level.JH_mysqlAsync[task].function))
{
if(isDefined(level.JH_mysqlAsync[task].invoker))
{
rows = getRows(result);
level.JH_mysqlAsync[task].invoker thread [[level.JH_mysqlAsync[task].function]](rows, level.JH_mysqlAsync[task].args);
}
else if(isDefined(result))
mysql_free_result(result);
}
else if(isDefined(result))
mysql_free_result(result);
}
else if(isDefined(result))
mysql_free_result(result);
level.JH_mysqlAsync[task] = undefined;
}
wait .05;
}
}

initMySQL(host, user, pass, db, port)
{
mysql = mysql_init();
ret = mysql_real_connect(mysql, host, user, pass, db, port);
if(!ret)
{
printf("errno=" + mysql_errno(mysql) + " error= " + mysql_error(mysql) + "\n");
mysql_close(mysql);
return undefined;
}
return mysql;
}
stripString(string)
{
if(isDefined(level.JH_mysql))
return mysql_real_escape_string(level.JH_mysql, string);
return "";
}


How to use:
Call init() at game start.

Call query() and queryNosave() for stuff at gamestart or non-latency queries (not recommended though)

Call asyncQuery() and asyncQueryNosave() for your runtime queries.

Example:



foo()
{
asyncQuery("SELECT NOW() AS `time`", ::showTime);
}

showTime(rows, args)
{
if(isDefined(rows) && isDefined(rows[0]) && isDefined(rows[0]["time"]))
iprintlnbold("It is " + rows[0]["time"]);
}


Additional args will be passed to the secondary function as well, as long as it is a single var or a single array. The helper function will be called on the original invoker, and auto-free-s and auto-quits if the invoker is gone (useful for disconnecting players etc)

Hey, is this script suppose to work on 1.0? Cause I've got some segmentation fault when initing and I'm not sure if it's 1.2/1.3 only.

Sorry for bothering and thanks in advance.

IzNoGoD
7th March 2016, 14:13
It should work if you have the latest libcod installed.

However, if you use bad mysql queries, it can surely backfire.

Show the rest of your script

Whiskas
7th March 2016, 17:22
I am using libcod from 29.01.15 -> http://killtube.org/downloads/libcod/2015_01_29/ To get newer libcod I should compile it myself?

init()
{
//get your host, user, pass, db, port here
level.JH_mysql = mysql_reuse_connection();
if(!isDefined(level.JH_mysql))
level.JH_mysql = initMySQL("ip", "user", "password", "db", 3306);
initAsyncMySQL("ip", "user", "password", "db", 3306);
}

query(query)
{
if(!isDefined(level.JH_mysql))
return undefined;
result = mysql_query(level.JH_mysql, query);
resettimeout();
if(result)
{
printf("Error in " + query + "\n");
return undefined;
}
result = mysql_store_result(level.JH_mysql);
rows = getRows(result);
return rows;
}

queryNosave(query)
{
if(!isDefined(level.JH_mysql))
return undefined;
result = mysql_query(level.JH_mysql, query);
resettimeout();
if(result)
{
printf("Error in " + query + "\n");
return undefined;
}
return [];
}

getRows(result)
{
if(!isDefined(result))
return [];
rowcount = mysql_num_rows(result);
fields = [];
field = mysql_fetch_field(result);
while(isDefined(field))
{
fields[fields.size] = field;
field = mysql_fetch_field(result);
}
rows = [];
for(i = 0; i < rowcount; i++)
{
row = mysql_fetch_row(result);
rows[rows.size] = [];
for(j = 0; j < fields.size; j++)
rows[rows.size - 1][fields[j]] = row[j];
}
mysql_free_result(result);
return rows;
}

asyncQuery(query, function, args)
{
id = mysql_async_create_Query(query);
task = spawnstruct();
task.query = query;
task.invoker = self;
task.function = function;
task.args = args;
level.JH_mysqlAsync["" + id] = task;
}

asyncQueryNosave(query, function, args)
{
id = mysql_async_create_Query_Nosave(query);
task = spawnstruct();
task.query = query;
task.invoker = self;
task.function = function;
task.args = args;
level.JH_mysqlAsync["" + id] = task;
}

initAsyncMySQL(host, user, pass, db, port)
{
mysql_async_initializer(host, user, pass, db, port, 4);
level.JH_mysqlAsync = [];
thread loopAsyncMySQL();
}

loopAsyncMySQL()
{
while(true)
{
list = mysql_async_GetDone_List();
for(i = 0; i < list.size; i++)
{
result = mysql_async_getresult_and_free(list[i]);
if(!isdefined(result))
continue;
if(result == 0)
result = undefined;
task = "" + list[i];
if(isDefined(level.JH_mysqlAsync[task]))
{
if(isDefined(level.JH_mysqlAsync[task].function))
{
if(isDefined(level.JH_mysqlAsync[task].invoker))
{
rows = getRows(result);
level.JH_mysqlAsync[task].invoker thread [[level.JH_mysqlAsync[task].function]](rows, level.JH_mysqlAsync[task].args);
}
else if(isDefined(result))
mysql_free_result(result);
}
else if(isDefined(result))
mysql_free_result(result);
}
else if(isDefined(result))
mysql_free_result(result);
level.JH_mysqlAsync[task] = undefined;
}
wait .05;
}
}

initMySQL(host, user, pass, db, port)
{
mysql = mysql_init();
ret = mysql_real_connect(mysql, host, user, pass, db, port);
if(!ret)
{
printf("errno=" + mysql_errno(mysql) + " error= " + mysql_error(mysql) + "\n");
mysql_close(mysql);
return undefined;
}
return mysql;
}
stripString(string)
{
if(isDefined(level.JH_mysql))
return mysql_real_escape_string(level.JH_mysql, string);
return "";
}

Saved this as _mysql.gsc then in tdm.gsc added
thread maps\mp\gametypes\_mysql::init(); in Callback_StartGameType().

Had problems with few undeclared functions :
id = mysql_async_createQueryNosave(query);
id = mysql_async_createQuery(query);
result = mysql_async_getResultAndFree So I used these from documentation https://znation.nl/cod4script/ .

IzNoGoD
7th March 2016, 17:36
Looks ok, when does it crash? right after a query, an async query or after handling an async query?

Keep in mind that this updated code does the mysql_free_result for you.

Whiskas
7th March 2016, 17:50
I'm not using any query, just initiating on game start. Couldn't find problem cause I don't really know how to debug segmentation fault.

IzNoGoD
7th March 2016, 18:00
ulimit -c unlimited
start ur cod2 serv here
gdb /path/to/cod2_lnxded /home/user/core
bt

Whiskas
7th March 2016, 19:42
Got output like this, something wrong with mysql_real_connect()?


Program terminated with signal 11, Segmentation fault.
#0 0xb71b3b4b in mysql_options ()
from /usr/lib/i386-linux-gnu/libmysqlclient.so.18
(gdb) bt
#0 0xb71b3b4b in mysql_options ()
from /usr/lib/i386-linux-gnu/libmysqlclient.so.18
#1 0xb7785cb1 in gsc_mysql_real_connect () from ./libcod2_1_0.so
#2 0x080806a0 in ?? ()
#3 0x080838ca in ?? ()
#4 0x080839eb in ?? ()
#5 0x08115eaf in ?? ()
#6 0x0810700e in ?? ()
#7 0x08090d12 in ?? ()
#8 0x08090dbf in ?? ()
#9 0x08091f94 in ?? ()
#10 0x0808ad5f in ?? ()
#11 0x08060724 in ?? ()
#12 0x0805fc79 in ?? ()
#13 0x0808b228 in ?? ()
#14 0x08060724 in ?? ()
#15 0x0805fdf5 in ?? ()
#16 0x0805fe07 in ?? ()
#17 0x08061ff1 in ?? ()
#18 0x08062038 in ?? ()
#19 0x080d2ace in ?? ()
#20 0xb74c14d3 in __libc_start_main () from /lib/i386-linux-gnu/libc.so.6
#21 0x0804a4d1 in ?? ()

Mitch
7th March 2016, 19:48
What is the output this command?

ldd libcod2_1_0.so

Whiskas
7th March 2016, 19:55
What is the output this command?

ldd libcod2_1_0.so

./libcod2_1_0.so: /usr/lib/i386-linux-gnu/libmysqlclient.so.18: no version information available (required by ./libcod2_1_0.so)
linux-gate.so.1 => (0xb7787000)
libdl.so.2 => /lib/i386-linux-gnu/libdl.so.2 (0xb7749000)
libmysqlclient.so.18 => /usr/lib/i386-linux-gnu/libmysqlclient.so.18 (0xb7431000)
libpthread.so.0 => /lib/i386-linux-gnu/libpthread.so.0 (0xb7415000)
libz.so.1 => /lib/i386-linux-gnu/libz.so.1 (0xb73ff000)
libm.so.6 => /lib/i386-linux-gnu/libm.so.6 (0xb73d3000)
librt.so.1 => /lib/i386-linux-gnu/librt.so.1 (0xb73ca000)
libgcc_s.so.1 => /lib/i386-linux-gnu/libgcc_s.so.1 (0xb73ac000)
libc.so.6 => /lib/i386-linux-gnu/libc.so.6 (0xb7202000)
/lib/ld-linux.so.2 (0xb7788000)

IzNoGoD
7th March 2016, 20:08
Can you debug wether the problem is in initMysql() or in initAsyncMysql?

Whiskas
7th March 2016, 20:25
Sure I can!

Of course:
level.JH_mysql = initMySQL();
and
initAsyncMySQL();
had login params inside them like in my first post.

Solution 1.

init()
{
//get your host, user, pass, db, port here
level.JH_mysql = mysql_reuse_connection();
if(!isDefined(level.JH_mysql))
level.JH_mysql = initMySQL();
//initAsyncMySQL();
}

Solution 2.

init()
{
//get your host, user, pass, db, port here
level.JH_mysql = mysql_reuse_connection();
//if(!isDefined(level.JH_mysql))
// level.JH_mysql = initMySQL();
initAsyncMySQL();
}

Both gives erorr.

Debugging 1. solution:
Program terminated with signal 11, Segmentation fault.
#0 0xb71b3b4b in mysql_options ()
from /usr/lib/i386-linux-gnu/libmysqlclient.so.18
(gdb) bt
#0 0xb71b3b4b in mysql_options ()
from /usr/lib/i386-linux-gnu/libmysqlclient.so.18
#1 0xb7785cb1 in gsc_mysql_real_connect () from ./libcod2_1_0.so
#2 0x080806a0 in ?? ()
#3 0x080838ca in ?? ()
#4 0x080839eb in ?? ()
#5 0x08115eaf in ?? ()
#6 0x0810700e in ?? ()
#7 0x08090d12 in ?? ()
#8 0x08090dbf in ?? ()
#9 0x08091f94 in ?? ()
#10 0x0808ad5f in ?? ()
#11 0x08060724 in ?? ()
#12 0x0805fc79 in ?? ()
#13 0x0808b228 in ?? ()
#14 0x08060724 in ?? ()
#15 0x0805fdf5 in ?? ()
#16 0x0805fe07 in ?? ()
#17 0x08061ff1 in ?? ()
#18 0x08062038 in ?? ()
#19 0x080d2ace in ?? ()
#20 0xb74c14d3 in __libc_start_main () from /lib/i386-linux-gnu/libc.so.6
#21 0x0804a4d1 in ?? ()


2. solution

Program terminated with signal 11, Segmentation fault.
#0 0xb71b3b4b in mysql_options ()
from /usr/lib/i386-linux-gnu/libmysqlclient.so.18
(gdb) bt
#0 0xb71b3b4b in mysql_options ()
from /usr/lib/i386-linux-gnu/libmysqlclient.so.18
#1 0xb7785cb1 in gsc_mysql_real_connect () from ./libcod2_1_0.so
#2 0x080806a0 in ?? ()
#3 0x080838ca in ?? ()
#4 0x080839eb in ?? ()
#5 0x08115eaf in ?? ()
#6 0x0810700e in ?? ()
#7 0x08090d12 in ?? ()
#8 0x08090dbf in ?? ()
#9 0x08091f94 in ?? ()
#10 0x0808ad5f in ?? ()
#11 0x08060724 in ?? ()
#12 0x0805fc79 in ?? ()
#13 0x0808b228 in ?? ()
#14 0x08060724 in ?? ()
#15 0x0805fdf5 in ?? ()
#16 0x0805fe07 in ?? ()
#17 0x08061ff1 in ?? ()
#18 0x08062038 in ?? ()
#19 0x080d2ace in ?? ()
#20 0xb74c14d3 in __libc_start_main () from /lib/i386-linux-gnu/libc.so.6
#21 0x0804a4d1 in ?? ()


Edit:
If both are disabled, server works. :P

IzNoGoD
7th March 2016, 21:46
Looks almost like your connection is failing hard and/or your mysqlclient is buggy.

Can you try compiling libcod from source (mitch's git) and doublecheck your mysql parameters?

Whiskas
7th March 2016, 22:12
God I'm the most dumbest in the world!

I'm running mysql on same machine as my codserver. Tried with "localhost" instead of my machine's ip and it worked! (I thought i did this before ._.).

Sorry for loosing your time and big THANK YOU for you guys!

IzNoGoD
7th March 2016, 22:25
You should set your listen ip in my.conf

Whiskas
7th March 2016, 22:30
You should set your listen ip in my.conf

Never heard of it before, thanks!

IzNoGoD
7th March 2016, 23:34
Comment the bind-address out in /etc/mysql/my.cnf using a # at the start of the line, then you can connect from all ips.

Make sure to just whitelist the ips you want in your firewall though.

serthy
5th September 2016, 12:45
What are the NoSafe queries for? I've looked into the source, and if you have NoSafe, you just do not call mysql_store_result() on it, but I'm not that firm with it.
Is it for queries, that do only reading (not writing) the database?

Also, it might be useful for others:

the mas string length that can be sent from CoD2 to the mysql server seems to be 512 chars
the libcod-functions in cod2 seem to have been renamed, the correct names can be found here: https://znation.nl/cod4script/

IzNoGoD
5th September 2016, 14:09
nosave is to not save the result. (nosafe is not something in the code though, it's nosave)

Stringlength is limited by the engine and should be 1024

Yes, I renamed a few functions for my next mod, forgot to change them back in the code

serthy
12th September 2016, 09:45
nosave is to not save the result. (nosafe is not something in the code though, it's nosave) wow, I feel stupid now..

Okay, when I call mysql_async_create_query_nosave() once, all other queries (even normal async ones) fail to execute, I have to shut the server down (close the terminal/screen), a normal /killserver doesn't help

But its just a note, I don't get it why one would use nosave when you do not get any result back? Maybe its just some mysql internals with this store

IzNoGoD
12th September 2016, 13:09
wow, I feel stupid now..

Okay, when I call mysql_async_create_query_nosave() once, all other queries (even normal async ones) fail to execute, I have to shut the server down (close the terminal/screen), a normal /killserver doesn't help

But its just a note, I don't get it why one would use nosave when you do not get any result back? Maybe its just some mysql internals with this store

Try the same query with normal async create query, probably your query is fcked