Raised This Month: $ Target: $400
 0% 

MYSQL Load Fail


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Schwabba
Senior Member
Join Date: Apr 2008
Old 10-24-2010 , 03:17   MYSQL Load Fail
Reply With Quote #1

Sometimes on my server mysql does'nt load the stats of the player and reset them.

PHP Code:
L 10/24/2010 09:03:50Start of error session.
L 10/24/2010 09:03:50Info (map "deathrun_flugsmellav2-b2") (file "addons/amxmodx/logs/error_20101024.log")
L 10/24/2010 09:03:50: [MySQLInvalid info tuple handle0
L 10
/24/2010 09:03:50: [AMXXDisplaying debug trace (plugin "drmanager.amxx")
L 10/24/2010 09:03:50: [AMXXRun time error 10native error (native "SQL_Connect")
L 10/24/2010 09:03:50: [AMXX]    [0drmanager.sma::Load_MySql (line 2540)
L 10/24/2010 09:03:50: [AMXX]    [1drmanager.sma::client_putinserver (line 2623
PHP Code:
public client_putinserver(id)
{
    
Load_MySql(id// This is line 2623
}

public 
Load_MySql(id)
{
    new 
ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error)) // This is line 2540

    
if(g_SqlTuple == Empty_Handle)
    {
        
set_fail_state(g_Error)
    }

    new 
szSteamId[32], szTemp[512]
    
get_user_authid(idszSteamIdcharsmax(szSteamId))

    new 
Data[1]
    
Data[0] = id

    
//we will now select from the table `deathrun` where the steamid match
    
format(szTemp,charsmax(szTemp),"SELECT * FROM `deathrun` WHERE (`deathrun`.`steamid` = '%s')"szSteamId)
    
SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
    
SQL_FreeHandle(SqlConnection)
}

public 
register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(
FailState == TQUERY_CONNECT_FAILED)
    {
        
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
    }
    else if(
FailState == TQUERY_QUERY_FAILED)
    {
        
log_amx("Load Query failed. [%d] %s"ErrcodeError)
    }
    new 
id
    id 
Data[0]
    if(
SQL_NumResults(Query) < 1
    {
        
//.if there are no results found

        
new szSteamId[32], szName[32]
        
get_user_authid(idszSteamIdcharsmax(szSteamId)) // get user's steamid
        
get_user_name(idszName31// get user's name

        //  if its still pending we can't do anything with it
        
if (equal(szSteamId,"ID_PENDING"))
        {
            return 
PLUGIN_HANDLED
        
}
            
        new 
szTemp[512]

        
// now we will insert the values into our table.
        
format(szTemp,charsmax(szTemp),"INSERT INTO `deathrun` ( `steamid` , `name` , `points` , `lives` , `frags` , `model`)VALUES ('%s','%s','0','0','0','0');",szSteamIdszName)
        
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    } 
    else 
    {
    
// if there are results found
        
iPoints[id]         = SQL_ReadResult(Query2)
        
iLives[id]         = SQL_ReadResult(Query3)
        
iFrags[id]         = SQL_ReadResult(Query4)
        
iModel[id]         = SQL_ReadResult(Query5)
    }
    
Update_Rank(id)
    return 
PLUGIN_HANDLED
}

public 
Update_Rank(id// register cmd to this function
{
    for(new 
i<= maxplayersi++)
    {
        if(
is_user_connected(i))
        {
            
Save_MySql(i// Save all stats to get the correct rank
        
}
    }

    new 
Data[1]
    
Data[0] = id

    
new szTemp[512]
    
format(szTemp,charsmax(szTemp),"SELECT COUNT(*) FROM `deathrun` WHERE `points` >= %d"iPoints[id])
    
// Select the count where the points is matching or higher (Incase of equal points)
    
SQL_ThreadQuery(g_SqlTuple,"Sql_Rank",szTemp,Data,1)

    return 
PLUGIN_CONTINUE
}

public 
Sql_Rank(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(
FailState == TQUERY_CONNECT_FAILED)
    {
        
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
    }
    else if(
FailState == TQUERY_QUERY_FAILED)
    {
        
log_amx("Load Query failed. [%d] %s"ErrcodeError)
    }

    new 
count 0
    count 
SQL_ReadResult(Query,0)
    if(
count == 0)
    
count 1

    
new id
    id 
Data[0]

    
iRank[id] = count

    
return PLUGIN_HANDLED


Last edited by Schwabba; 10-24-2010 at 03:19.
Schwabba is offline
YamiKaitou
Has a lovely bunch of coconuts
Join Date: Apr 2006
Location: Texas
Old 10-24-2010 , 03:28   Re: MYSQL Load Fail
Reply With Quote #2

Show where you are defining g_SqlTuple
__________________
ProjectYami Laboratories

I do not browse the forums regularly anymore. If you need me for anything (asking questions or anything else), then PM me (be descriptive in your PM, message containing only a link to a thread will be ignored).
YamiKaitou is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 10-24-2010 , 03:45   Re: MYSQL Load Fail
Reply With Quote #3

PHP Code:
new Handle:g_SqlTuple
new g_Error[512]

public 
MySql_Init()
{
    
// we tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    
g_SqlTuple SQL_MakeDbTuple(Host,User,Pass,Db)

    
// ok, we're ready to connect
    
new ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(
SqlConnection == Empty_Handle)
    {
        
// stop the plugin with an error message
        
set_fail_state(g_Error)
    }

    new 
Handle:Queries
    
// we must now prepare some random queries
    
Queries SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS deathrun (steamid varchar(32), name varchar(40), points INT(11), lives INT(11), frags INT(11), model INT(11))")

    if(!
SQL_Execute(Queries))
    {
        
// if there were any problems
        
SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        
set_fail_state(g_Error)
    }

    
// close the handle
    
SQL_FreeHandle(Queries)

    
// you free everything with SQL_FreeHandle
    
SQL_FreeHandle(SqlConnection)   

Schwabba is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 10-24-2010 , 05:49   Re: MYSQL Load Fail
Reply With Quote #4

1. You don't need SqlConnection in Load_MySql.

2. About the main problem I can only guess because you didn't post whole code:
You used something like this (from this bad tutorial):
PHP Code:
public plugin_init(){
    
set_task(1.0"MySql_Init")
}
public 
client_putinserver(id){
    
Load_MySql(id)

Result: after mapchange the server is trying to use Load_MySql on some players before calling MySql_Init (so Load_MySql fails).

Solution:
PHP Code:
new g_authed[33]
new 
g_sql_ready false
new g_max_players
public plugin_init(){
    
g_max_players get_maxplayers()
    
set_task(0.1"MySql_Init"//you don't need to use time == 1.0
}
public 
MySql_Init()
    
//your code
    
g_sql_ready true
    
for(new i=1i<=g_max_playersi++)
        if(
g_authed[i])
            
Load_MySql(i)
}

public 
Load_MySql(id){
    if(!
g_sql_ready)
        return
    
//your code
}
public 
client_authorized(id){ //use Load_MySql in this function, not client_putinserver
    
g_authed[id] = true
    
//your code
}
public 
client_disconnect(id){
    
//your code
    
g_authed[id] = false

__________________
Impossible is Nothing

Last edited by Sylwester; 10-24-2010 at 05:51.
Sylwester is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 10-25-2010 , 03:38   Re: MYSQL Load Fail
Reply With Quote #5

Okay, i think that works, but then i have to change

PHP Code:
public Save_MySql(id)
{
    new 
szSteamId[32], szTemp[512], szName[32]
    
get_user_authid(idszSteamIdcharsmax(szSteamId))
    
get_user_name(idszName31)

    
// Here we will update the user hes information in the database where the steamid matches.
    
format(szTemp,charsmax(szTemp),"UPDATE `deathrun` SET `name` = '%s', `points` = '%i', `lives` = '%i', `frags` = '%i', `model` = '%i' WHERE `deathrun`.`steamid` = '%s';",szName,iPoints[id],iLives[id],iFrags[id],iModel[id], szSteamId)
    
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)

to

PHP Code:
public Save_MySql(id)
{
    if(
g_authed[id])
    {
        new 
szSteamId[32], szTemp[512], szName[32]
        
get_user_authid(idszSteamIdcharsmax(szSteamId))
        
get_user_name(idszName31)

        
// Here we will update the user hes information in the database where the steamid matches.
        
format(szTemp,charsmax(szTemp),"UPDATE `deathrun` SET `name` = '%s', `points` = '%i', `lives` = '%i', `frags` = '%i', `model` = '%i' WHERE `deathrun`.`steamid` = '%s';",szName,iPoints[id],iLives[id],iFrags[id],iModel[id], szSteamId)
        
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    }

right?
Schwabba is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 10-25-2010 , 05:38   Re: MYSQL Load Fail
Reply With Quote #6

No, because it is possible that Save_MySql will be called after authorization, but before getting reply from database and in that case player will lose his points.
PHP Code:
new bool:g_loaded[33]

public 
Load_MySql(id)
{
    if(!
g_sql_ready)
        return
    
//...

    
SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
}

public 
register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(
FailState)
    {
        
log_amx("SQL Error: %s (%d)"ErrorErrcode)
        return 
PLUGIN_HANDLED
    
}

    new 
id
    id 
Data[0]

    
//...

    
g_loaded[id] = true
    
return PLUGIN_HANDLED
}

public 
client_disconnect(id)
{
    
//...
    
    
iPoints[id] = 0
    iLives
[id] = 0
    iFrags
[id] = 0
    iModel
[id] = 0

    g_authed
[id] = false
    g_loaded
[id] = false
}

public 
Save_MySql(id)
{
    if(!
g_loaded[id])
        return
    
//...

__________________
Impossible is Nothing

Last edited by Sylwester; 10-25-2010 at 05:41.
Sylwester is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 10-26-2010 , 08:54   Re: MYSQL Load Fail
Reply With Quote #7

Ah ok, i understand.. thanks.
Schwabba is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 10-27-2010 , 10:07   Re: MYSQL Load Fail
Reply With Quote #8

Mhh, i found a new big bug on that plugin.

Every time when this player plays on our server:

PHP Code:
# 6 "[Buraak]-44'# B J K ;)" 104 STEAM_0:0:2335520   1 04:38   43    0 
Then this bug happens:

http://www.pingstars-gaming.de/deathrun/toplist.php


Maybe it's because the ' or the #, i don't know. It changes all names in the database to his one, the bug does'nt happens on displaying the database, it's changed on all playernames, the rest of the data is correct.

Last edited by Schwabba; 10-27-2010 at 10:09.
Schwabba is offline
issen1
Member
Join Date: Jan 2010
Old 10-27-2010 , 11:03   Re: MYSQL Load Fail
Reply With Quote #9

It's probably because of the '. You have to escape MySQL special characters in your queries otherwise an attacker might even get control over your server where MySQL ist running.

Since I can't see any escape function in the includes, you will have to do this yourself. Replace characters like ' " etc. (I don't know all of them myself, but just google for MySQL injection) through something different. Maybe their respective hex codes or something.
__________________
greets (:
issen1 is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 10-27-2010 , 11:53   Re: MYSQL Load Fail
Reply With Quote #10

It seems that I forgot to mention that before. Just as issen1 said, you need to back-quote names and other strings that may contain invalid characters.
You can do it using SQL_QuoteString like this:
PHP Code:
new Handle:g_SqlConnection // you will need global connection handle

public MySql_Init()
{
    
g_SqlTuple SQL_MakeDbTuple(Host,User,Pass,Db)
    new 
ErrorCode
    g_SqlConnection 
SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(
g_SqlConnection == Empty_Handle)
    {
        
set_fail_state(g_Error)
    }
    
    
//,,,
    //SQL_FreeHandle(SqlConnection) // do not free connection handle here

    
g_sql_ready true
    
for(new i=1i<=g_max_playersi++)
        if(
g_authed[i])
            
Load_MySql(i)
}

public 
Save_MySql(id)
{
    if(!
g_loaded[id])
        return
    new 
szSteamId[32], szTemp[512], szName[32], szQuotedName[64]
    
get_user_authid(idszSteamIdcharsmax(szSteamId))
    
get_user_name(idszName31)
    
SQL_QuoteString(g_SqlConnectionszQuotedName63szName//back-quote player name

    //also name in your sql table must be varchar(64), not 32:
    //CREATE TABLE ... (steamid varchar(32), name varchar(64)
    
format(szTemp,charsmax(szTemp),"UPDATE ... SET ... `name` = '%s' ...;",..., szQuotedName, ...)
    
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}

public 
plugin_end(){
    if(
g_SqlConnection != Empty_Handle)
    {
        
SQL_FreeHandle(g_SqlConnection//free connection handle here
    
}

__________________
Impossible is Nothing
Sylwester 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 10:27.


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