AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   SQL Money saving optimization help (https://forums.alliedmods.net/showthread.php?t=224356)

aron9forever 08-23-2013 09:31

SQL Money saving optimization help
 
Okay, so here's the deal: I got a server running which uses "credits" as currency instead of money. You get one credit for each minute you play, but that's pretty irrelevant. What I care about is saving those credits, and I use SQL for that, but since I barely know how to use it, it proved to be a challenge. People are sometimes losing their credits, there's no real evidence of how and why but they do, and it's not just some dumbasses lying to get some free cash. There's almost no latency between the sql host and the server(same machine) and by what I figured out it has something to do with the credits getting saved after they are set to 0. Please help me figure it out.
Since the plugin is not public and many would do lots of things to get it, I can't post the full source(also it's huge, 5k lines), but I will post everything relevant to the cause.
Code:

Okay so the SQL connection and all that stuff is taken from a SQL tutorial off this forum and it's working fine, won't post that.

#define ID_GIVE_CREDIT (taskid - TASK_GIVE_CREDITS)

public Load_MySql(id)
{
    new nume[32], szTemp[512]
    get_user_name(id, nume, 31)

    new Data[1]
    Data[0] = id

    format(szTemp,charsmax(szTemp),"SELECT * FROM `credit` WHERE (`credit`.`nume` = '%s')", nume)
    SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
}

public register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
    {
        log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
    }
    else if(FailState == TQUERY_QUERY_FAILED)
    {
        log_amx("Load Query failed. [%d] %s", Errcode, Error)
    }

    new id
    id = Data[0]

    if(SQL_NumResults(Query) < 1)
    {
        new nume[32]
        get_user_name(id, nume, 31)

        if(equal(nume,"Player"))
            return PLUGIN_HANDLED

        new szTemp[512]

        format(szTemp,charsmax(szTemp),"INSERT INTO `credit` ( `nume` , `credite`) VALUES ('%s','0');",nume)
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    }
    else
    {
        iCredit[id] = SQL_ReadResult(Query, 1)
    }

    return PLUGIN_HANDLED
}

public Save_MySql(id)
{
    new nume[32], szTemp[512]
    get_user_name(id, nume, 31)

    format(szTemp,charsmax(szTemp),"UPDATE `credit` SET `credite` = '%i' WHERE `credit`.`nume` = '%s';", iCredit[id], nume)
    SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}


public client_putinserver(id)
{
        if(!is_user_bot(id))
        {
                set_task(2.0, "setnull", id)
                set_task(4.0,"loadcredits", id)
        }
}
public loadcredits(id)
{
        Load_MySql(id)
        set_task(60.0, "Give1Points", id+TASK_GIVE_CREDITS, _, _, "b")
}

public forward_client_userinfochanged(id, buffer) {
        if (!is_user_connected(id))
                return FMRES_IGNORED

        static oldname[32], newname[32]
        get_user_name(id, oldname, sizeof oldname - 1)
        engfunc(EngFunc_InfoKeyValue, buffer, g_name, newname, sizeof newname - 1)
        if (equal(newname, oldname))
                return FMRES_IGNORED
               
        msg_name_change(id, oldname, newname)
       
        return FMRES_SUPERCEDE
}

msg_name_change(id, /* const */ oldname[], /* const */ newname[]) {
        message_begin(MSG_BROADCAST, g_msgid_saytext)
        write_byte(id)
        write_string(g_name_change)
        write_string(oldname)
        write_string(newname)
        message_end()
        set_task(1.5, "setnull", id)
        remove_task(id+TASK_GIVE_CREDITS)
        set_task(3.0,"loadnewcredits", id)
}

public loadnewcredits(id)
{
        Load_MySql(id)
        set_task(60.0, "Give1Points", id+TASK_GIVE_CREDITS, _, _, "b")
       
}

public client_disconnect(id)
{
        remove_task(id+TASK_GIVE_CREDITS)
        Save_MySql(id)
        set_task(3.0, "setnull", id)
}

public setnull(id)
{
        iCredit[id] = 0
}

public Give1Points(taskid)
{
        static id
        id = ID_GIVE_CREDIT;
        if(!is_user_connected(id) || cs_get_user_team(id) == CS_TEAM_SPECTATOR || cs_get_user_team(id) == CS_TEAM_UNASSIGNED)
        {
                return
        }
        iCredit[id] = iCredit[id] + 1
        Save_MySql(id)
}

This is everything that has save_mysql in it, let me know if I missed something or if you want another piece of code and I'll be sure to post it.
Thanks for reading.

jimaway 08-23-2013 10:44

Re: SQL Money saving optimization help
 
instead of using tasks, create the save_mysql function with 2 parameters and pass players credits as the second parameter, that way you can set the 0 in the array instantly
Code:

public client_disconnect(id)
{
        remove_task(id+TASK_GIVE_CREDITS)
        Save_MySql(id)
        set_task(3.0, "setnull", id)
}


aron9forever 08-25-2013 16:48

Re: SQL Money saving optimization help
 
Quote:

Originally Posted by jimaway (Post 2020087)
instead of using tasks, create the save_mysql function with 2 parameters and pass players credits as the second parameter, that way you can set the 0 in the array instantly
Code:

public client_disconnect(id)
{
        remove_task(id+TASK_GIVE_CREDITS)
        Save_MySql(id)
        set_task(3.0, "setnull", id)
}


That's not how it works, save_mysql takes the value of iCredit[] and saves it on the player's name, it's never supposed to save as 0 in the sql, the iCredit[] becomes 0 for the next player that joins the server and gets that id

I had to remove the saving on disconnect due to many users losing money after disconnecting. Right now my server is exploitable due to this because once an user gets one credit and his balance is saved he can keep dropping credits on the ground until the next minute and if he disconnects he duplicates the money

aron9forever 08-27-2013 16:00

Re: SQL Money saving optimization help
 
bumping this, anyone have a solution please?

Clauu 08-28-2013 03:23

Re: SQL Money saving optimization help
 
You have some glitches in your code, 'register_client' should stop at that failstates and there are so many tasks. Start making some debugs, is data saved or loaded properly? From what i see most probably your sql code part must be rewritten.

aron9forever 08-28-2013 07:46

Re: SQL Money saving optimization help
 
Quote:

Originally Posted by Clauu (Post 2023132)
You have some glitches in your code, 'register_client' should stop at that failstates and there are so many tasks. Start making some debugs, is data saved or loaded properly? From what i see most probably your sql code part must be rewritten.

After I've removed money saving on disconnect, this part
Code:

        Save_MySql(id)
        set_task(3.0, "setnull", id)

People stopped losing their money. The problem is this opens a gateway to exploiting and allows people to infinitely duplicate money. The problem is somewhere here and I just can't figure out how it's possible for the credits to be set to 0 before they are saved, is it possible for the sql to hang(for example at a mapchange when all the players disconnect) and save them after it's set to null?
I thought SQL is way too efficient for something like this to happen. If it's the case I'll remove the setnull task and just save them and leave the credits on that id, and only reset them when someone connects on it, because that doesn't seem to cause a problem

If anyone here is a sql guru I'd like a confirmation on whether such kind of delay is possible with sql(2~3 seconds)


All times are GMT -4. The time now is 19:09.

Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.