I made a player ranking system for soccerjam, but the problem is that sometimes on player connect (or disconnect) all player rank values are set to zero in mysql database. And it's totaly random - random players, random timing. Sometimes everything is fine for days (though I can't be sure, there are 1k+ players in the database, I just monitor the top20). And I think that the players row isn't deleted, just the values set to zero (not sure though).
Can't find the problem. This is what I tryed to do to fix the problem:
changed SQL_PrepareQuery and SQL_Execute method to SQL_ThreadQuery method;
changed tables engine from MyISAM to InnoDB;
changed mysql servers;
Did many corrections in the code using Hawks sqlx tutorial.
Nothing helped.
Here is the code, that loads and saves the rankstats:
PHP Code:
public client_putinserver(id)
{
if(!is_user_bot(id))
{
new authid[36], query[101]
get_user_authid(id, authid, 35)
new Data[2]
Data[0] = id
formatex(query,100,"SELECT * FROM SjStats WHERE Authid='%s'", authid)
SQL_ThreadQuery(g_SqlTuple,"LoadData",query,Data,2)
}
}
public LoadData(FailState,Handle:Query,Error[],Errcode,Data[],DataSize) {
if(FailState == TQUERY_CONNECT_FAILED) {
return log_amx("Could not connect to SQL database.")
}
else if(FailState == TQUERY_QUERY_FAILED) {
return log_amx("Error in query LoadData.")
}
if(Errcode) log_amx("Error in query: %s",Error)
new id
id = Data[0]
if(!SQL_MoreResults(Query))
{
new Info[3][36]
get_user_ip(id,Info[1],32,1)
get_user_name(id,Info[0],32)
get_user_authid(id, Info[2], 35)
formatex(query,512,"INSERT INTO SjStats(Ip,Name,Goal_saves,Goals,Assists,Steals,Ball_kills,Points,Date,Authid) VALUES('%s','%s',0,0,0,0,0,0,CURRENT_TIMESTAMP,'%s')",Info[1],Info[0],Info[2])
SQL_ThreadQuery(g_SqlTuple, "AddRec", query)
return PLUGIN_CONTINUE
}
//I reset these on client_connect(id)
g_PlayerBestData[id][pBestData:gsaves] = SQL_ReadResult(Query, 2)
g_PlayerBestData[id][pBestData:goals] = SQL_ReadResult(Query, 3)
g_PlayerBestData[id][pBestData:assists] = SQL_ReadResult(Query, 4)
g_PlayerBestData[id][pBestData:steals] = SQL_ReadResult(Query, 5)
g_PlayerBestData[id][pBestData:bkills] = SQL_ReadResult(Query, 6)
g_PlayerBestData[id][pBestData:points] = SQL_ReadResult(Query, 7)
return PLUGIN_CONTINUE
}
public AddRec(FailState, Handle:Query, Error[], Errcode)
{
if(FailState == TQUERY_CONNECT_FAILED)
return log_amx("Could not connect to SQL database.")
else if(FailState == TQUERY_QUERY_FAILED)
return log_amx("Error in query AddREC.")
if(Errcode)
log_amx("Error in query: %s", Error)
return PLUGIN_CONTINUE
}
// on client_disconnect(id). The same function updates online players stats regulary, but no it is not the cause, because I tryed to use seperate function to update stats on player disconnect.
public UpdatePlayerStats(id)
{
static pIp[33], nick[33]
get_user_ip(id,pIp,32,1)
get_user_name(id,nick,32)
new authid[36], query[513], query2[513]
get_user_authid(id, authid, 35)
formatex(query,512,"UPDATE SjStats SET Ip='%s', Name='%s', Goal_saves=%i, Goals=%i, Assists=%i, Steals=%i, Ball_kills=%i, Points=%i, Date=CURRENT_TIMESTAMP WHERE Authid='%s'",
pIp,
nick,
g_PlayerBestData[id][pBestData:gsaves],
g_PlayerBestData[id][pBestData:goals],
g_PlayerBestData[id][pBestData:assists],
g_PlayerBestData[id][pBestData:steals],
g_PlayerBestData[id][pBestData:bkills],
g_PlayerBestData[id][pBestData:points],
authid)
SQL_ThreadQuery(g_SqlTuple, "UpdatePlayerStatsQ", query)
}
public UpdatePlayerStatsQ(FailState, Handle:Query, Error[], Errcode)
{
if(FailState == TQUERY_CONNECT_FAILED)
return log_amx("Could not connect to SQL database.")
else if(FailState == TQUERY_QUERY_FAILED)
return log_amx("Error in query.")
if(Errcode)
log_amx("Error in query: %s", Error)
return PLUGIN_CONTINUE
}
Maybe sometimes when player connects to server, for some reason, his values aren't loaded from the db (though I don't get any query errors) and when he is in the game and a regular update of all online players stats happen, he gets new values saved. I'll try to do a recheck right after players connects if his points == 0.
Also it seems that I have the same problem with my
killstreak records plugin
Halp!