Raised This Month: $ Target: $400
 0% 

SQL Money saving optimization help


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
aron9forever
Veteran Member
Join Date: Feb 2013
Location: Rromania
Old 08-23-2013 , 09:31   SQL Money saving optimization help
Reply With Quote #1

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.
aron9forever is offline
jimaway
Heeeere's Jimmy!
Join Date: Jan 2009
Location: Estonia
Old 08-23-2013 , 10:44   Re: SQL Money saving optimization help
Reply With Quote #2

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)
}
jimaway is offline
aron9forever
Veteran Member
Join Date: Feb 2013
Location: Rromania
Old 08-25-2013 , 16:48   Re: SQL Money saving optimization help
Reply With Quote #3

Quote:
Originally Posted by jimaway View Post
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 is offline
aron9forever
Veteran Member
Join Date: Feb 2013
Location: Rromania
Old 08-27-2013 , 16:00   Re: SQL Money saving optimization help
Reply With Quote #4

bumping this, anyone have a solution please?
aron9forever is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 08-28-2013 , 03:23   Re: SQL Money saving optimization help
Reply With Quote #5

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.
Clauu is offline
aron9forever
Veteran Member
Join Date: Feb 2013
Location: Rromania
Old 08-28-2013 , 07:46   Re: SQL Money saving optimization help
Reply With Quote #6

Quote:
Originally Posted by Clauu View Post
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)

Last edited by aron9forever; 08-28-2013 at 07:48.
aron9forever is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


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


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