Raised This Month: $ Target: $400
 0% 

MYSQL - weird bug


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Schwabba
Senior Member
Join Date: Apr 2008
Old 06-16-2011 , 07:25   MYSQL - weird bug
Reply With Quote #1

Hi, since i use MySql i have a bug on every plugin i use with MySql, i never found the problem.. till now!

The more players are on the server, the more often it happens. Currently there are 18/18 players on the server from ~12am till 1am and it happens like 2-3 times every day, so it's not much, but still annoying.

I store the money in the database and give it to them when they rejoin the server.

The database-structure looks like:

steamid - name - money - more but not important

But sometimes it happens that there are more entries for the same steamid and the money for the steamid is 0 or just the half.

Example correct:

STEAM_1:123 - Schwabba - 1000000

Examples wrong:

STEAM_1:123 - Schwabba - 500000
STEAM_1:123 - Schwabba - -500000

or:

STEAM_1:123 - Schwabba - 500000
STEAM_1:123 - Schwabba - 500000

or:

STEAM_1:123 - Schwabba - 500000
STEAM_1:123 - Schwabba - 0

or:

STEAM_1:123 - Schwabba - 0
STEAM_1:123 - Schwabba - 0

Sometimes there are 10 entries for the same player.

PHP Code:
public Load_MySql(id)
{
    if(
g_sql_ready)
    {
        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

        format
(szTemp,charsmax(szTemp),"SELECT * FROM `furienmoney` WHERE (`furienmoney`.`steamid` = '%s')"szSteamId)
        
SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
    }
}

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// The bug have to be here
    
{
        new 
szSteamId[32], szName[32], szQuotedName[64]
        
get_user_authid(idszSteamIdcharsmax(szSteamId)) // get user's steamid
        
get_user_name(idszName31// get user's name
        
SQL_QuoteString(g_SqlConnectionszQuotedName63szName)

        
//  if its still pending we can't do anything with it
        
if (equal(szSteamId,"ID_PENDING"))
        {
            return 
PLUGIN_HANDLED
        
}
            
        new 
szTemp[512]
        new 
time get_systime()
        
// now we will insert the values into our table.
        
format(szTemp,charsmax(szTemp),"INSERT INTO `furienmoney` ( `steamid` , `name` , `money`) VALUES ('%s','%s','0');",
        
szSteamIdszQuotedNametimetime)
        
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)

        
Save_MySql(id)
    } 
    else 
    {
    
// if there are results found
        
iMoney[id]        = SQL_ReadResult(Query2)
        
iMoney2[id]        = SQL_ReadResult(Query2)
        new 
szLogname[32], szLogauth[32];
        
get_user_name(idszLognamecharsmax(szLogname));
        
get_user_authid(idszLogauthcharsmax(szLogauth));
        
log_to_file("Moneylog.txt""Loaded %i$ of %s (%s)."iMoney[id], szLognameszLogauth)
        
set_task(0.2,"give_money"id)
    }
    
g_loaded[id] = true
    
return PLUGIN_HANDLED
}

public 
Save_MySql(id)
{
    if(
g_loaded[id] && iStart[id])
    {
        new 
szSteamId[32], szTemp[512], szName[32], szQuotedName[64], money
        get_user_authid
(idszSteamIdcharsmax(szSteamId))
        
get_user_name(idszName31)
        
SQL_QuoteString(g_SqlConnectionszQuotedName63szName)
        
money iMoney[id] - iMoney2[id]
        
format(szTemp,charsmax(szTemp),"UPDATE `furienmoney` SET `name` = '%s', `money` = `money` +%i  WHERE `furienmoney`.`steamid` = '%s';",szQuotedName,money,szSteamId)
        
iMoney2[id] = iMoney[id]
        new 
szLogname[32], szLogauth[32];
        
get_user_name(idszLognamecharsmax(szLogname));
        
get_user_authid(idszLogauthcharsmax(szLogauth));
        
log_to_file("Moneylog.txt""Changed money of %s (%s) to %i$."szLognameszLogauthiMoney[id])
    }


Someone know how that happens?

Last edited by Schwabba; 06-16-2011 at 07:29.
Schwabba is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-16-2011 , 16:52   Re: MYSQL - weird bug
Reply With Quote #2

Because you are selecting everything from that table and not those specific vars, the match isn't done so
PHP Code:
if (SQL_NumResults(Query) < 1
will not work, try to select only what you need from that table and btw the most used format is where x = '%s' AND y = '%s' so try to use it in this way..
Clauu is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 06-16-2011 , 19:54   Re: MYSQL - weird bug
Reply With Quote #3

I got that from this tutorial: http://forums.alliedmods.net/showthr...&highlight=sql

But i dont have to use x = '%s' AND y = '%s' because i only wanna search the steamid.

And you mean it's better to use
PHP Code:
SQL_ReadResult(Query0
instead of
PHP Code:
SQL_NumResults(Query) < 
?
Schwabba is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-17-2011 , 00:33   Re: MYSQL - weird bug
Reply With Quote #4

No, i was said that your if condition will return true everytime and that's why you have duplicate entries.
PHP Code:
SQL_ReadResult(Query0
is reading the first value selected from your table, has nothing to do with the other if statement, and if you search only by steamid then use this -
Code:
SELECT money FROM furienmoney WHERE steamid = '%s'

And this it must to be executed in a proper function with the correct steamid to work.

Last edited by Clauu; 06-17-2011 at 00:36.
Clauu is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 06-17-2011 , 03:03   Re: MYSQL - weird bug
Reply With Quote #5

But then i can only get the money, not the other things in the database. It saves the money, played time, achievements etc. There are 83 fields in this database.

Last edited by Schwabba; 06-17-2011 at 03:15.
Schwabba is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 06-17-2011 , 03:44   Re: MYSQL - weird bug
Reply With Quote #6

Don't listen to Clauu. His claims are wrong.

Your code doesn't look good. There is no need to insert data if noone was found while loading. You could just use "replace into ..." in Save_MySQL.

Where do you execute Load_MySQL?
__________________
Impossible is Nothing
Sylwester is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-17-2011 , 04:23   Re: MYSQL - weird bug
Reply With Quote #7

Why i'm wrong?
PHP Code:
if(SQL_NumResults(Query) < 1// The bug have to be here 
the duplicate entry is beeing created here because this is true everytime in this code.
Clauu is offline
Sylwester
Veteran Member
Join Date: Oct 2006
Location: Poland
Old 06-17-2011 , 07:05   Re: MYSQL - weird bug
Reply With Quote #8

I suspect that the problem is caused by invalid steamid sent in the SELECT query (there is some checking in register_client to see if the steamid is correct, but there is none in Load_MySQL).

1. SQL_NumResults returns number of rows retrieved from database. If you execute SELECT ... WHERE steamid = '%s'; it will be greater than 0 for all steamids that are present in database so "if" condition will be false.

2. There is nothing wrong with SELECT * FROM ...

3. Using SQL_ReadResult to check if there were any rows retrieved from database is incorrect.
__________________
Impossible is Nothing
Sylwester is offline
Schwabba
Senior Member
Join Date: Apr 2008
Old 06-17-2011 , 08:16   Re: MYSQL - weird bug
Reply With Quote #9

Quote:
Originally Posted by Sylwester
I suspect that the problem is caused by invalid steamid sent in the SELECT query (there is some checking in register_client to see if the steamid is correct, but there is none in Load_MySQL).
That could it be.

Quote:
Originally Posted by Sylwester
1. SQL_NumResults returns number of rows retrieved from database. If you execute SELECT ... WHERE steamid = '%s'; it will be greater than 0 for all steamids that are present in database so "if" condition will be false.
That's what i want, check if the steamid is in the database, if yes, load the entries, when not, insert the steamid to the database with money 0.

Quote:
Originally Posted by Sylwester
3. Using SQL_ReadResult to check if there were any rows retrieved from database is incorrect.
Why?

Last edited by Schwabba; 06-17-2011 at 11:14.
Schwabba is offline
Clauu
Senior Member
Join Date: Feb 2008
Location: RO
Old 06-17-2011 , 08:16   Re: MYSQL - weird bug
Reply With Quote #10

1 - in his code that if statement will be true everytime.
2 - it isn't but is much better to select only what you need.
3 - where in this topic did you see the use/encouragement of that?
Clauu is offline
Reply


Thread Tools
Display Modes

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 23:30.


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