Witam.
Chciałbym prosić o pomoc w optymalizacji części pluginu odpowiedzialnego za zapis/odczyt statystyk graczy. Problemem jest fakt, iż po rozegraniu kilku map (kilkudziesięcu rund) otrzymuję taki błąd: "User xxx already has more than 'max_user_connections' active connections".Oczywiście wiem na czym on polega, jednakże jego limit jest ustawiony odgórnie przez hosting na 50 i nie może ulec zmianie.
Ze względu na swoją podstawową znajomość dot. MySql, moje pytanie teraz, co należy poprawić w kodzie, aby uniknąć występowania sporej ilości połączeń z bazą danych w krótkim czasie i aby zachować ciągłość połączenia (by zapis i odczyt danych nie potrzebował notorycznego łączenia się z bazą MySql)?
Pozdrawiam.
public plugin_cfg() { new szHostname[30], szUsername[30], szPassword[30], szDatabase[30] new szError[512], szErr get_pcvar_string(g_Cvar[1], szHostname, charsmax( szHostname )) get_pcvar_string(g_Cvar[2], szUsername, charsmax( szUsername )) get_pcvar_string(g_Cvar[3], szPassword, charsmax( szPassword )) get_pcvar_string(g_Cvar[4], szDatabase, charsmax( szDatabase )) g_SqlTuple = SQL_MakeDbTuple(szHostname, szUsername, szPassword, szDatabase) new Handle:SqlConnection = SQL_Connect(g_SqlTuple, szErr, szError, charsmax( szError )) if(SqlConnection == Empty_Handle) set_fail_state( szError ) new Handle:Queries = SQL_PrepareQuery(SqlConnection, "CREATE TABLE IF NOT EXISTS `stats`(`id` INTEGER NOT NULL PRIMARY KEY, `name` VARCHAR(32) NOT NULL, `points` INTEGER NOT NULL, `goals` INTEGER NOT NULL, `assists` INTEGER NOT NULL, `steals` INTEGER NOT NULL, `stealsrv` INTEGER NOT NULL, `disarm` INTEGER NOT NULL, `disarmrv` INTEGER NOT NULL, `bkills` INTEGER NOT NULL, `bkillsrv` INTEGER NOT NULL, `offside` INTEGER NOT NULL, `foul` INTEGER NOT NULL, `warns` INTEGER NOT NULL);") if(!SQL_Execute(Queries)) { SQL_QueryError(Queries,szError,charsmax(szError)) set_fail_state(szError); } SQL_FreeHandle(Queries) SQL_FreeHandle(SqlConnection); }
public load_data(id) { new szError[512], ErrorCode new szName[32] get_user_name(id, szName, charsmax( szName )) new Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,szError,511) SQL_QuoteString(SqlConnection, szName, 32, szName); new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT `id` FROM `stats` WHERE `name`='%s';", szName) if(!SQL_Execute(Query)) { SQL_QueryError(Query,szError,511); log_amx(szError); return ; } if(SQL_NumResults(Query) > 0 ) { next_load_data(id, "", 0); } else { register_player(id,""); } SQL_FreeHandle(Query); SQL_FreeHandle(SqlConnection); } public next_load_data(id,data[],len) { new szName[33]; get_user_name(id, szName, charsmax( szName )); new szError[512], ErrorCode; new Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,szError,511) SQL_QuoteString(SqlConnection, szName, 32, szName); if(SqlConnection == Empty_Handle) { log_amx(szError); return ; } new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM `stats` WHERE `name`='%s';", szName) if(!SQL_Execute(Query)) { SQL_QueryError(Query,szError,511) log_amx(szError) } if( SQL_NumResults(Query) > 0) { UserData[id][gPoints]= SQL_ReadResult(Query, 2); UserData[id][gGoals]= SQL_ReadResult(Query, 3); UserData[id][gAssists]= SQL_ReadResult(Query, 4); UserData[id][gSteals]= SQL_ReadResult(Query, 5); UserData[id][gStealsRv]= SQL_ReadResult(Query, 6); UserData[id][gDisarm]= SQL_ReadResult(Query, 7); UserData[id][gDisarmRv]= SQL_ReadResult(Query, 8); UserData[id][gBKills]= SQL_ReadResult(Query, 9); UserData[id][gBKillsRv]= SQL_ReadResult(Query, 10); UserData[id][gOffside]= SQL_ReadResult(Query, 11); UserData[id][gFoul]= SQL_ReadResult(Query, 12); UserData[id][gWarns]= SQL_ReadResult(Query, 13); } SQL_FreeHandle(Query); SQL_FreeHandle(SqlConnection); } public register_player(id, data[]) { new szName[33]; get_user_name(id, szName, charsmax( szName )); new szError[512], ErrorCode; new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError,511) SQL_QuoteString(SqlConnection, szName, 32, szName); if(SqlConnection == Empty_Handle) { log_amx(szError); return ; } new Handle:Query = SQL_PrepareQuery(SqlConnection, "INSERT INTO `stats` (`name`, `points`, `goals`, `assists`, `steals`, `stealsrv`, `disarm`, `disarmrv`, `bkills`, `bkillsrv`, `offside`, `foul`, `warns`) VALUES ('%s', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');", szName); if(!SQL_Execute(Query)) { SQL_QueryError(Query,szError,511) log_amx(szError) } SQL_FreeHandle(Query); SQL_FreeHandle(SqlConnection); UserData[id][gPoints]= 0; UserData[id][gGoals]= 0; UserData[id][gSteals]= 0; UserData[id][gStealsRv]= 0; UserData[id][gAssists]= 0; UserData[id][gDisarm]= 0; UserData[id][gDisarmRv]= 0; UserData[id][gBKills]= 0; UserData[id][gBKillsRv]= 0; UserData[id][gOffside]= 0; UserData[id][gFoul]= 0; UserData[id][gWarns]= 0; } public save_usr(id) { new szName[33]; get_user_name(id, szName, charsmax( szName )); new szError[512], ErrorCode; new Handle:szUpdate; new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError, 511) SQL_QuoteString(SqlConnection, szName, 32, szName) if(SqlConnection == Empty_Handle) { log_amx(szError); return ; } szUpdate = SQL_PrepareQuery(SqlConnection, "UPDATE `stats` SET `points`='%d', `goals`='%d', `assists`='%d', `steals`='%d', `stealsrv`='%d', `disarm`='%d', `disarmrv`='%d', `bkills`='%d', `bkillsrv`='%d', `offside`='%d', `foul`='%d', `warns`='%d' WHERE `stats`.`name`='%s';", UserData[id][gPoints], UserData[id][gGoals], UserData[id][gAssists], UserData[id][gSteals], UserData[id][gStealsRv], UserData[id][gDisarm], UserData[id][gDisarmRv], UserData[id][gBKills], UserData[id][gBKillsRv], UserData[id][gOffside], UserData[id][gFoul], UserData[id][gWarns], szName) if(!SQL_Execute(szUpdate)) { SQL_QueryError(szUpdate, szError, charsmax( szError )) set_fail_state( szError ) } } public top_players(id){ new szError[512], ErrorCode; new num = 0; new name[10][32], points[10][11], goals[10][11], assists[10][11], steals[10][11], stealsrv[10][11], disarm[10][11], disarmrv[10][11], bkills[10][11], bkillsrv[10][11], offside[10][11], foul[10][11]; new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError, 511) new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM `stats` ORDER BY `points` DESC, `goals` DESC") SQL_Execute(Query) while(SQL_MoreResults(Query) && num <= 9){ SQL_ReadResult(Query, 1, name[num], 31); SQL_ReadResult(Query, 2, points[num], 10); SQL_ReadResult(Query, 3, goals[num], 10); SQL_ReadResult(Query, 4, assists[num], 10); SQL_ReadResult(Query, 5, steals[num], 10); SQL_ReadResult(Query, 6, stealsrv[num], 10); SQL_ReadResult(Query, 7, disarm[num], 10); SQL_ReadResult(Query, 8, disarmrv[num], 10); SQL_ReadResult(Query, 9, bkills[num], 10); SQL_ReadResult(Query, 10, bkillsrv[num], 10); SQL_ReadResult(Query, 11, offside[num], 10); SQL_ReadResult(Query, 12, foul[num], 10); num++ SQL_NextRow(Query) } new motd[2001], iLen; iLen = format(motd, sizeof motd - 1, "<body bgcolor=#000000><font color=#98f5ff><pre>"); iLen += format(motd[iLen], (sizeof motd - 1) - iLen, "<center><h2>---- SoccerJam Top 10 ----</h2></center>^n^n"); iLen += format(motd[iLen], (sizeof motd - 1) - iLen, "<b><U>%2s %-22.22s %8s %6s %6s %6s %6s %6s %6s %6s %6s %6s %6s</U></b>^n", "#", "Nick", "Points", "Goals", "Assists", "Steals", "StealsRv", "Disarm", "DisarmRv", "BKills", "BKillsRv", "Offsides", "Fouls"); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"1 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[0], str_to_num(points[0]), str_to_num(goals[0]), str_to_num(assists[0]), str_to_num(steals[0]), str_to_num(stealsrv[0]), str_to_num(disarm[0]), str_to_num(disarmrv[0]), str_to_num(bkills[0]), str_to_num(bkillsrv[0]), str_to_num(offside[0]), str_to_num(foul[0])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"2 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[1], str_to_num(points[1]), str_to_num(goals[1]), str_to_num(assists[1]), str_to_num(steals[1]), str_to_num(stealsrv[1]), str_to_num(disarm[1]), str_to_num(disarmrv[1]), str_to_num(bkills[1]), str_to_num(bkillsrv[1]), str_to_num(offside[1]), str_to_num(foul[1])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"3 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[2], str_to_num(points[2]), str_to_num(goals[2]), str_to_num(assists[2]), str_to_num(steals[2]), str_to_num(stealsrv[2]), str_to_num(disarm[2]), str_to_num(disarmrv[2]), str_to_num(bkills[2]), str_to_num(bkillsrv[2]), str_to_num(offside[2]), str_to_num(foul[2])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"4 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[3], str_to_num(points[3]), str_to_num(goals[3]), str_to_num(assists[3]), str_to_num(steals[3]), str_to_num(stealsrv[3]), str_to_num(disarm[3]), str_to_num(disarmrv[3]), str_to_num(bkills[3]), str_to_num(bkillsrv[3]), str_to_num(offside[3]), str_to_num(foul[3])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"5 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[4], str_to_num(points[4]), str_to_num(goals[4]), str_to_num(assists[4]), str_to_num(steals[4]), str_to_num(stealsrv[4]), str_to_num(disarm[4]), str_to_num(disarmrv[4]), str_to_num(bkills[4]), str_to_num(bkillsrv[4]), str_to_num(offside[4]), str_to_num(foul[4])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"6 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[5], str_to_num(points[5]), str_to_num(goals[5]), str_to_num(assists[5]), str_to_num(steals[5]), str_to_num(stealsrv[5]), str_to_num(disarm[5]), str_to_num(disarmrv[5]), str_to_num(bkills[5]), str_to_num(bkillsrv[5]), str_to_num(offside[5]), str_to_num(foul[5])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"7 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[6], str_to_num(points[6]), str_to_num(goals[6]), str_to_num(assists[6]), str_to_num(steals[6]), str_to_num(stealsrv[6]), str_to_num(disarm[6]), str_to_num(disarmrv[6]), str_to_num(bkills[6]), str_to_num(bkillsrv[6]), str_to_num(offside[6]), str_to_num(foul[6])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"8 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[7], str_to_num(points[7]), str_to_num(goals[7]), str_to_num(assists[7]), str_to_num(steals[7]), str_to_num(stealsrv[7]), str_to_num(disarm[7]), str_to_num(disarmrv[7]), str_to_num(bkills[7]), str_to_num(bkillsrv[7]), str_to_num(offside[7]), str_to_num(foul[7])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"9 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[8], str_to_num(points[8]), str_to_num(goals[8]), str_to_num(assists[8]), str_to_num(steals[8]), str_to_num(stealsrv[8]), str_to_num(disarm[8]), str_to_num(disarmrv[8]), str_to_num(bkills[8]), str_to_num(bkillsrv[8]), str_to_num(offside[8]), str_to_num(foul[8])) iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"10 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[9], str_to_num(points[9]), str_to_num(goals[9]), str_to_num(assists[9]), str_to_num(steals[9]), str_to_num(stealsrv[9]), str_to_num(disarm[9]), str_to_num(disarmrv[9]), str_to_num(bkills[9]), str_to_num(bkillsrv[9]), str_to_num(offside[9]), str_to_num(foul[9])) show_motd(id, motd, "Top 10"); } public rank_players(id){ new szError[512], ErrorCode; new num = 1; new name[32], points[11], notes; new szName[32]; get_user_name(id, szName, 31); new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError, 511); new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM `stats` ORDER BY `points` DESC, `goals` DESC"); SQL_Execute(Query) notes = SQL_NumResults(Query) while(SQL_MoreResults(Query)) { SQL_ReadResult(Query, 1, name, 31) if(equal(name, szName)) { SQL_ReadResult(Query, 2, points, 10) break }else{ num++ SQL_NextRow(Query) } } PrintChatColor(id, _, "%L", LANG_PLAYER, "SJ_RANK", num, notes, str_to_num(points)) } public rankstats_players(id){ new motd[1501],iLen; new szName[33]; get_user_name(id,szName,32); iLen = format(motd, sizeof motd - 1,"<body bgcolor=#000000><font color=#98f5ff><pre>"); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<center><h2>---- SoccerJam Stats ----</h2></center>^n^n"); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<center><b>%s</b></center>^n", szName); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<font color=#fff000><b>%-22.22s</b>: %10i^n", "Points", UserData[id][gPoints]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Goals", UserData[id][gGoals]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Assists", UserData[id][gAssists]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Steals", UserData[id][gSteals]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "StealsRv", UserData[id][gStealsRv]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Disarm", UserData[id][gDisarm]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "DisarmRv", UserData[id][gDisarmRv]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "BKills", UserData[id][gBKills]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "BKillsRv", UserData[id][gBKillsRv]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Offsides", UserData[id][gOffside]); iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n^n", "Fouls", UserData[id][gFoul]); show_motd(id, motd, "Stats"); return PLUGIN_HANDLED } public plugin_end() { if(g_SqlTuple != Empty_Handle) SQL_FreeHandle(g_SqlTuple); } public fw_ServerDown() { for(new i = 0; i <= MaxPlayers; i++) { if(is_user_connected(i)) save_usr(i) } } public StatsEnable() { new suma_player = get_playersnum() if(suma_player >= 6) { stats_enable = 1 } else { stats_enable = 0 } }