[SkumTomteN]

Hey, i got a problem, i am using mysql on my server for saving levels, but it keeps reseting for some players and some not. When an admin changed the map, they were 4 players, 2 lost their levels, 1 kept them. 1 wasnt even playing and joined later and lost them. it isnt always like this, it is random.

Yes, yes, i have searched both google and the forum, cant find anything.

Mysql have been a pain in the ass for me, ive tried client_authorized, client_putinserver, spawn, etc. nothing seems to work safely. If you have something that is safe, feel free to share your method cause im out of ideas cause ive tried most things and dont know much about mysql, and ive heard that the mysql tutorial is faulty and wrong in many cases which seems true.

PHP Code:

public client_authorized(Id) 
{ 
    get_user_authid(Id, g_Steam[Id], charsmax(g_Steam[])) 
    set_task(5.0,"LoadShit",Id + TASK_SQL) 
} 

public LoadShit(Id)
{    
    Id-= TASK_SQL
    
    Load_PlayerLevel(Id)
} 
public client_disconnect(id)
{
    Save_PlayerLevel(id)
}

public fw_Killed(id) {
    if(is_user_connected(id))
        Save_PlayerLevel(id)
} 


Thanks for helping.

[Bugsy]

You are calling Save_PlayerLevel() at client_disconnect() which could be your problem. Show us your Save_PlayerLevel() function.

[SkumTomteN]

Originally Posted by Bugsy You are calling Save_PlayerLevel() at client_disconnect() which could be your problem. Show us your Save_PlayerLevel() function.

Thats because players usually had problems with reseting when the map changed (Not always), and client_disconnect is called when map changes.

Saving hooks/functions:

PHP Code:

public client_authorized(Id) 
{ 
    get_user_authid(Id, g_Steam[Id], charsmax(g_Steam[])) // Lets get SteamID just once. Safe for client_disconnect saving 
    
    if(g_SQL_Id == Empty_Handle) 
        set_task(4.0,"LoadShit",Id + TASK_SQL) 
    
    else 
        LoadShit(Id + TASK_SQL) 
} 

public LoadShit(Id)
{
    if(g_SQL_Id == Empty_Handle)
    {
        // something is wrong with the connection (maybe offline / wrong ip / user / password)
        return
    }
    
    Id-= TASK_SQL
    
    Clear_PlayerLevel(Id)
    Load_PlayerLevel(Id)
}  

public client_disconnect(id) 
    Save_PlayerLevel(id)


public fw_Killed(id) {
    if(is_user_connected(id))
        Save_PlayerLevel(id)
} 


Mysql Functions:

PHP Code:

public MySQL_Init()
{
    server_print("[MySQL] Initializing...")
    
    g_SQL_Id = SQL_MakeDbTuple(SQL_HOST, SQL_USERNAME, SQL_PASSWORD, SQL_DB)
    
    new ErrorCode, Handle:SqlConnection = SQL_Connect(g_SQL_Id, ErrorCode, g_SQL_Error, charsmax(g_SQL_Error))
    if(SqlConnection == Empty_Handle)
        set_fail_state(g_SQL_Error)
    
    new Handle:Queries
    
    // Level
    Queries = SQL_PrepareQuery(SqlConnection, "CREATE TABLE IF NOT EXISTS Level (Steamid varchar(32), Level INT(8), Exp INT(16))")
    
    if(!SQL_Execute(Queries))
    {
        SQL_QueryError(Queries, g_SQL_Error, charsmax(g_SQL_Error))
        set_fail_state(g_SQL_Error)
    }
    
    
    // Free
    SQL_FreeHandle(Queries)
    SQL_FreeHandle(SqlConnection) 
    
    server_print("[MySQL] Initialize: Completed!")
} 

public Load_PlayerLevel(id)
{
    static szAuthId[33];
    get_user_authid(id, szAuthId, 32); 
    
    static Data[1]; Data[0] = id
    
    format(g_SQL_Query, charsmax(g_SQL_Query),"SELECT * FROM `Level` WHERE (`Level`.`Steamid` = '%s')", szAuthId)
    SQL_ThreadQuery(g_SQL_Id, "SQLHandle_LoadLevel", g_SQL_Query, Data, sizeof(Data))
}

public Save_PlayerLevel(id)
{
    static szAuthId[33];
    get_user_authid(id, szAuthId, 32); 
    
    format(g_SQL_Query, charsmax(g_SQL_Query),"UPDATE `Level` SET `Level` = '%i', `Exp` = '%i' WHERE `Level`.`Steamid` = '%s';", g_PlayerLevel[id], g_PlayerExp[id], szAuthId)
    SQL_ThreadQuery(g_SQL_Id, "SQLHandle_Ignore", g_SQL_Query)
}

public Clear_PlayerLevel(id)
{
    g_PlayerLevel[id] = 1
    g_PlayerExp[id] = 0
}

public SQLHandle_LoadLevel(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
        log_amx("[MySQL] Error (Load): Could not connect to SQL Database. [%d] %s", Errcode, Error)
    else if(FailState == TQUERY_QUERY_FAILED)
        log_amx("[MySQL] Error (Load): Query failed. [%d] %s", Errcode, Error)
    
    static id; id = Data[0]
    if(SQL_NumResults(Query) < 1) 
    { // if there are no results found
        static szAuthId[33];
        get_user_authid(id, szAuthId, 32); 
        
        format(g_SQL_Query, charsmax(g_SQL_Query), "INSERT INTO `Level` ( `Steamid` , `Level` , `Exp`)VALUES ('%s' , '1' , '0');", szAuthId)
        SQL_ThreadQuery(g_SQL_Id, "SQLHandle_Ignore", g_SQL_Query)
        } else {
        g_PlayerLevel[id] = SQL_ReadResult(Query, 1)
        g_PlayerExp[id] = SQL_ReadResult(Query, 2)
    }
    
    return PLUGIN_HANDLED
}  

public SQLHandle_Ignore(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
    SQL_FreeHandle(Query)
    return PLUGIN_HANDLED
} 


[Jhob94]

Ok let me try again \o/
You should NOT touch mysql on disconnect. You are making some sort of point system where people get points by killing. So, you only need to save points on ham_killed function. Don't use disconnect because it isn't needed and isn't safe either.
That's the only thing that may be causing problems here, because your load seems to be ok.

[SkumTomteN]

Originally Posted by Jhob94 Ok let me try again \o/ You should NOT touch mysql on disconnect. You are making some sort of point system where people get points by killing. So, you only need to save points on ham_killed function. Don't use disconnect because it isn't needed and isn't safe either. That's the only thing that may be causing problems here, because your load seems to be ok.

Only called disconnect cause it was were the fault often was. (In change map).

but thanks for the information. i will try this.

[Clauu]

Originally Posted by Jhob94 Ok let me try again \o/ You should NOT touch mysql on disconnect. Don't use disconnect because it isn't needed and isn't safe either.

And why is that?

[Nextra]

Originally Posted by Jhob94 Ok let me try again \o/ You should NOT touch mysql on disconnect. You are making some sort of point system where people get points by killing. So, you only need to save points on ham_killed function. Don't use disconnect because it isn't needed and isn't safe either. That's the only thing that may be causing problems here, because your load seems to be ok.

Why would that be? Besides saving every time data is touched, saving on disconnect is pretty much the only way to reliably save data for clients. Everything else is just additional safety nets.

[Jhob94]

If you change the map manually, it will be too fast and may not be able to save everyone's data. Plus it is not good to send so many queries at the same time. If he has the chance to save it on ham_killed, it's pointless to save it again on disconnect.

[Bugsy]

client_disconnect() is useful, I just try to avoid calling functions on players there. If it's data that is stored in variables then it's fine to do the actual save procedure at disconnect. In your case, I would store each players steam id in a global array of strings (retrieve at client_authorized), this will save you a native call @ disconnect as well. I just noticed that you are already retrieving authid into a global array at authorized and you even have a comment saying it will be used at disconnect for safe saving. I'm guessing you copied the code from somewhere?

PHP Code:

public client_authorized(Id) 
{ 
    get_user_authid(Id, g_Steam[Id], charsmax(g_Steam[])) // Lets get SteamID just once. Safe for client_disconnect saving  

//...

public Save_PlayerLevel(id)
{
    static szAuthId[33];
    get_user_authid(id, szAuthId, 32); 


I would get an application that can view your tables to make sure all of the data looks ok.

For your SQL, I would name the table something different than one of the fields. You are using a table named Level while having a field named Level. Maybe name your table 'tblLevels'?

[Clauu]

I don't think so, many queries will be if you;re updating sql data after every kill
Let's assume for a 32 players full server and a 20min for ex maptime, so in this 20 mins you will have 32*nr of kills queries to run.. kind a huge ammount of jobs for the mysql server but if you're saving at the.moment when player leaves the server then the ammount of queries will be much lower since in 20mins how much players can you have that leaves or joins the server? For ex in 5 min with a round time of 2 min can be made ~100kills and this for a non csdm server(nr of kills will be much greater on this), as an oposite will you have 100 connections/ disconnections in 5min? Much likely no
Also in threaded queries you don't have to free the query handle