AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   LAG caused by SQL (https://forums.alliedmods.net/showthread.php?t=99719)

abubhz 08-08-2009 16:43

LAG caused by SQL
 
2 Attachment(s)
i do apologize about my english.


I'm using the "Forum Mod" plugin you could see it at http://forums.alliedmods.net/showthread.php?t=5913 .

The plugin is working and checking registrations but everytime when a player joins the server
it freezes several times, briefly(lag). My sql server is a external server on the internet and my game server is at my home pc.

I need your help because i dont know pawn and C++. But i'm also glad if you give me some tips, so i 'll try to fix it myself.

Thank's

abubhz 08-11-2009 20:41

Re: LAG caused by SQL
 
Code:

public bool:FMAPI_GetReggedInformation(const STEAMID[], const bool:LOOKFORGROUPS, &forum_account_id, forumname[32], groups[], const MAX_GROUPS) {
    // Each FtPplugin implements this part
    // Paramaters: byref, set the data to be returned back to the FM plugin...
    // $return: bool, regged or not
 
    new Sql:sql
    if (!fm_db_connect(sql)) {
        return false
    }
 
    const SIZE = 512
    new query[SIZE + 1]
 
    format(query, SIZE, "SELECT `%s`, `%s` FROM `%s` WHERE `%s` = ^"%%%s%%^" LIMIT 1", g_FIELD_USERS_NAME, g_FIELD_USERS_USERID, g_TABLE_USERS, g_FIELD_USERS_STEAMID, STEAMID)
    server_print("query: %s", query)
 
    //server_print("[%s] Query: ^"%s^"", PLUGINNAME, query)
    new Result:result = dbi_query(sql, query)
 
    if (result < RESULT_NONE) {
        new sqlerror[256]
        dbi_error(sql, sqlerror, 255)
        log_amx("Error: %s", sqlerror)
        dbi_close(sql)
        return false
    }
    else if (result == RESULT_NONE) {
        dbi_close(sql)
        return false
    }
    // else...
 
    if (!dbi_nextrow(result)) {
        //log_amx("%s just doesn't exist in forum database (dbi_nextrow() failed).", authid)
        //server_print("[%s] %s just doesn't exist in phpBB database (dbi_nextrow() failed) Number of rows: %d", PLUGINNAME, authid, dbi_num_rows(result))
        dbi_free_result(result)
        dbi_close(sql)
        return false // should have been a result, but nextrow somehow failed (because the set succeeded, but is empty?)
    }
 
    // Get name
    dbi_field(result, 1, forumname, 31)
 
    // Get userid
    new celldata[64]
    dbi_field(result, 2, celldata, 63)
    forum_account_id = str_to_num(celldata)
 
    dbi_free_result(result)
 
    // Get groups
    groups[0] = 0
 
    // Say goodbye if we shouldn't look for groups...
    if (!LOOKFORGROUPS) {
        dbi_close(sql)
        return true
    }
 
    // PHPBB has its own reference table of the sort many<->many (groups<->users)
    format(query, SIZE, "SELECT `group_id` FROM `phpbb_user_group` WHERE `user_id` = %d && `user_pending` = 0", forum_account_id)
 
    result = dbi_query(sql, query)
    if (result < RESULT_NONE) {
        new error[256]
        dbi_error(sql, error, 255)
        log_amx("[%s] ERROR - while quering SQL server for %s's group memberships: %s", PLUGINNAME, forumname, error)
        dbi_close(sql)
        return true
    }
    else if (result == RESULT_NONE) {
        // No memberships at all... which should be odd?
        log_amx("[%s] ERROR - while quering SQL server for %s's group memberships! Membership count was ZERO while at least a few was expected...", PLUGINNAME, forumname)
        dbi_close(sql)
        return true
    }
    // else...
 
    new i = 0
    while (i < MAXGROUPS && dbi_nextrow(result))
        groups[i++] = dbi_field(result, 1)
 
    dbi_free_result(result)
 
    dbi_close(sql)
 
    return true
}


abubhz 08-15-2009 19:45

Re: LAG caused by SQL
 
Well i was reading a lot cause i'm noob at scripting.. i think i've found the problem:

the problem seems "DBI".
the solution? "SQLx"

Can you help me to learn sqlx or show me the syntax? i 'll try to rewrite this plugin to use sqlx but i cant found any GOOD documentation.


thank's

AntiBots 08-15-2009 20:30

Re: LAG caused by SQL
 
sqlx == php :D

abubhz 08-15-2009 21:51

Re: LAG caused by SQL
 
Well.... LOL....

i'll post here the old codes and my codes ok? i'm not a lazy boy.....:mrgreen:!!

abubhz 08-15-2009 21:53

Re: LAG caused by SQL
 
OLD CODE

PHP Code:

bool:fm_db_connect(&Sql:sql) {
 const 
LEN 128
 
new host[LEN], user[LEN], pass[LEN], db[LEN], error_msg[LEN]
 
get_cvar_string(CVAR_HOSThostLEN 1)
 
get_cvar_string(CVAR_USERuserLEN 1)
 
get_cvar_string(CVAR_PASSpassLEN 1)
 
get_cvar_string(CVAR_DBdbLEN 1)
 
sql dbi_connect(hostuserpassdberror_msgLEN 1)
 
//server_print("Passed this to dbi_connect(%s, %s, %s, %s, %d) sql handle result: %d", host, user, pass, db, LEN - 1, sql)
 //if (dbi_check_error(sql)) {
 
if (sql SQL_OK) {
  
dbi_error(sqlerror_msgLEN)
  
log_amx("[%s] ERROR - Can't connect to SQL db: %s"PLUGINNAMEerror_msg)
  return 
false
 
}
 
//log_amx("[%s] Successfully connected to SQL db.", PLUGINNAME)
 //server_print("[%s] Successfully connected to SQL db.", PLUGINNAME)
 
return true



abubhz 08-15-2009 21:55

Re: LAG caused by SQL
 
MY CODE

PHP Code:

bool:fm_db_connect(&Sql:sql) {
 new 
Handle:g_SqlTuple
 
const LEN 128
 
new host[LEN], user[LEN], pass[LEN], db[LEN], error_msg[LEN]
 
get_cvar_string(CVAR_HOSThostLEN 1)
 
get_cvar_string(CVAR_USERuserLEN 1)
 
get_cvar_string(CVAR_PASSpassLEN 1)
 
get_cvar_string(CVAR_DBdbLEN 1)
 
 
g_SqlTuple SQL_MakeDbTuple(host,user,pass,db)
 
 new 
ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,error_msg,511)
 
 if(
SqlConnection == Empty_Handle){
  
// stop the plugin with an error message
  
set_fail_state(error_msg)
  
log_amx("[%s] ERROR - Can't connect to SQL db: %s"PLUGINNAMEerror_msg)
  
server_print("[%s] ERROR - Can't connect to SQL db: %s"PLUGINNAMEerror_msg)
  return 
false
 
  
else{
   
server_print("[%s] Successfully connected to SQL db."PLUGINNAME)
   return 
true
  
}
 }



abubhz 08-15-2009 21:55

Re: LAG caused by SQL
 
Is it ok? LOL i'm trying!

joaquimandrade 08-15-2009 21:59

Re: LAG caused by SQL
 
What you need are threaded queries (Available with sqlx). Opposed to non-thread queries, they occur in a process parallel to the halflife one so their execution don't interrupt its flux. That makes their coding process harder and tedious. So, or you wait that someone recode the full plugin for you (what I doubt) or you pay for it.

abubhz 08-15-2009 22:02

Re: LAG caused by SQL
 
Do you speak portuguese Joaquim?


Can you explain me how to do it?


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

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