Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Asynchronous mysql queries

  1. #1
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts

    Asynchronous mysql queries

    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
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  2. The Following 4 Users Say Thank You to IzNoGoD For This Useful Post:

    kung foo man (25th March 2014),Mitch (26th March 2014),Ni3ls (25th March 2014),RobsoN (26th March 2014)

  3. #2
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    Ok, it's been added and pushed and updated and....

    here is a helper function for you:
    Code:
    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:

    Code:
    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
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  4. The Following 2 Users Say Thank You to IzNoGoD For This Useful Post:

    iBuddie (29th January 2016),RobsoN (9th April 2014)

  5. #3
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    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.
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  6. #4
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    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
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  7. #5
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    Big update: Cleaned up the mysql files and made the stuff make more sense.

    Here goes:
    PHP Code:
    init()
    {
        
    //get your host, user, pass, db, port here
        
    level.JH_mysql mysql_reuse_connection();
        if(!
    isDefined(level.JH_mysql))
            
    level.JH_mysql initMySQL(hostuserpassdbport);
        
    initAsyncMySQL(hostuserpassdbport);
    }

    query(query)
    {
        if(!
    isDefined(level.JH_mysql))
            return 
    undefined;
        
    result mysql_query(level.JH_mysqlquery);
        
    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_mysqlquery);
        
    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(
    0rowcounti++)
        {
            
    row mysql_fetch_row(result);
            
    rows[rows.size] = [];
            for(
    0fields.sizej++)
                
    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(hostuserpassdbport)
    {
        
    mysql_async_initializer(hostuserpassdbport4);
        
    level.JH_mysqlAsync = [];
        
    thread loopAsyncMySQL();
    }

    loopAsyncMySQL()
    {
        while(
    true)
        {
            list = 
    mysql_async_GetDoneList();
            for(
    0< list.sizei++)
            {
                
    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]](rowslevel.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(hostuserpassdbport)
    {
        
    mysql mysql_init();
        
    ret mysql_real_connect(mysqlhostuserpassdbport);
        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_mysqlstring);
        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:

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

    showTime(rowsargs)
    {
       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)
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  8. The Following 5 Users Say Thank You to IzNoGoD For This Useful Post:

    kung foo man (2nd March 2016),Ni3ls (2nd March 2016),suck000 (14th April 2016),voron00 (2nd March 2016),Whiskas (7th March 2016)

  9. #6
    Private Whiskas's Avatar
    Join Date
    Jan 2015
    Posts
    84
    Thanks
    69
    Thanked 20 Times in 17 Posts
    Quote Originally Posted by IzNoGoD View Post
    Big update: Cleaned up the mysql files and made the stuff make more sense.

    Here goes:
    PHP Code:
    init()
    {
        
    //get your host, user, pass, db, port here
        
    level.JH_mysql mysql_reuse_connection();
        if(!
    isDefined(level.JH_mysql))
            
    level.JH_mysql initMySQL(hostuserpassdbport);
        
    initAsyncMySQL(hostuserpassdbport);
    }

    query(query)
    {
        if(!
    isDefined(level.JH_mysql))
            return 
    undefined;
        
    result mysql_query(level.JH_mysqlquery);
        
    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_mysqlquery);
        
    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(
    0rowcounti++)
        {
            
    row mysql_fetch_row(result);
            
    rows[rows.size] = [];
            for(
    0fields.sizej++)
                
    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(hostuserpassdbport)
    {
        
    mysql_async_initializer(hostuserpassdbport4);
        
    level.JH_mysqlAsync = [];
        
    thread loopAsyncMySQL();
    }

    loopAsyncMySQL()
    {
        while(
    true)
        {
            list = 
    mysql_async_GetDoneList();
            for(
    0< list.sizei++)
            {
                
    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]](rowslevel.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(hostuserpassdbport)
    {
        
    mysql mysql_init();
        
    ret mysql_real_connect(mysqlhostuserpassdbport);
        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_mysqlstring);
        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:

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

    showTime(rowsargs)
    {
       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.

  10. #7
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    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
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  11. #8
    Private Whiskas's Avatar
    Join Date
    Jan 2015
    Posts
    84
    Thanks
    69
    Thanked 20 Times in 17 Posts
    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?
    PHP Code:
    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_mysqlquery);
        
    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_mysqlquery);
        
    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(
    0rowcounti++)
        {
            
    row mysql_fetch_row(result);
            
    rows[rows.size] = [];
            for(
    0fields.sizej++)
                
    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(hostuserpassdbport)
    {
        
    mysql_async_initializer(hostuserpassdbport4);
        
    level.JH_mysqlAsync = [];
        
    thread loopAsyncMySQL();
    }

    loopAsyncMySQL()
    {
        while(
    true)
        {
            list = 
    mysql_async_GetDone_List();
            for(
    0< list.sizei++)
            {
                
    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]](rowslevel.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(hostuserpassdbport)
    {
        
    mysql mysql_init();
        
    ret mysql_real_connect(mysqlhostuserpassdbport);
        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_mysqlstring);
        return 
    "";

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

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

  12. #9
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    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.
    "Does not work" is an error report for a bug between keyboard and chair.

    All hail Artie Effem

  13. #10
    Private Whiskas's Avatar
    Join Date
    Jan 2015
    Posts
    84
    Thanks
    69
    Thanked 20 Times in 17 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •