Raised This Month: $ Target: $400
 0% 

LAG caused by SQL


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
abubhz
Junior Member
Join Date: Aug 2009
Old 08-08-2009 , 16:43   LAG caused by SQL
Reply With Quote #1

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
Attached Files
File Type: sma Get Plugin or Get Source (forummod_fixed.sma - 557 views - 54.5 KB)
File Type: zip forummod_0_7_2.zip (23.5 KB, 68 views)

Last edited by abubhz; 08-08-2009 at 17:46.
abubhz is offline
abubhz
Junior Member
Join Date: Aug 2009
Old 08-11-2009 , 20:41   Re: LAG caused by SQL
Reply With Quote #2

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 
}

Last edited by abubhz; 08-11-2009 at 20:52.
abubhz is offline
abubhz
Junior Member
Join Date: Aug 2009
Old 08-15-2009 , 19:45   Re: LAG caused by SQL
Reply With Quote #3

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
abubhz is offline
AntiBots
Veteran Member
Join Date: May 2008
Location: Brazil
Old 08-15-2009 , 20:30   Re: LAG caused by SQL
Reply With Quote #4

sqlx == php
__________________
AntiBots is offline
Send a message via ICQ to AntiBots Send a message via MSN to AntiBots Send a message via Skype™ to AntiBots
abubhz
Junior Member
Join Date: Aug 2009
Old 08-15-2009 , 21:51   Re: LAG caused by SQL
Reply With Quote #5

Well.... LOL....

i'll post here the old codes and my codes ok? i'm not a lazy boy.....!!
abubhz is offline
abubhz
Junior Member
Join Date: Aug 2009
Old 08-15-2009 , 21:53   Re: LAG caused by SQL
Reply With Quote #6

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 is offline
abubhz
Junior Member
Join Date: Aug 2009
Old 08-15-2009 , 21:55   Re: LAG caused by SQL
Reply With Quote #7

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
  
}
 }


Last edited by abubhz; 08-15-2009 at 22:43.
abubhz is offline
abubhz
Junior Member
Join Date: Aug 2009
Old 08-15-2009 , 21:55   Re: LAG caused by SQL
Reply With Quote #8

Is it ok? LOL i'm trying!
abubhz is offline
joaquimandrade
Veteran Member
Join Date: Dec 2008
Location: Portugal
Old 08-15-2009 , 21:59   Re: LAG caused by SQL
Reply With Quote #9

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.
__________________
joaquimandrade is offline
abubhz
Junior Member
Join Date: Aug 2009
Old 08-15-2009 , 22:02   Re: LAG caused by SQL
Reply With Quote #10

Do you speak portuguese Joaquim?


Can you explain me how to do it?

Last edited by abubhz; 08-15-2009 at 22:07.
abubhz is offline
Reply



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 18:18.


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