Raised This Month: $51 Target: $400
 12% 

SQL Stats


Post New Thread Reply   
AlliedModders Forum Index > AMX Mod X > Scripting > Scripting Help
Page 2 of 2 1 2
 
Thread Tools Display Modes
Sylwester
Veteran Member
Sylwester's Avatar
Join Date: Oct 2006
Location: Poland
Old 06-11-2010 , 13:07   Re: SQL Stats
Reply With Quote #11
@portocala: That error log does not match with the code. Maybe you forgot to copy .amxx file to plugins folder before testing it.
Quote:
Originally Posted by ProIcons View Post
If you make the SQL Update while a Player Kills Someone else, if mysql is on another machine from your server the server will be freeze from 1 as to 5 seconds
That will never happen if you use threaded queries.
__________________
Impossible is Nothing
Sylwester is offline
portocala
Member
Join Date: Jun 2010
Old 06-11-2010 , 16:24   Re: SQL Stats
Reply With Quote #12
@unnyquee: the amx_sql_* CVARs are good, because some parts of the plugin works.

I found and fixed the mistake and I optimized the SQL algorithm by saving kills, hs and deaths into 3 increment variables and sending them to SQL Database on client_disconnect. It's a very good idea, thank you ProIcons.

The bad part: I arrived where I started -- [MySQL] Thread worker was unable to start.

This is why I started to use PrepareQuery+Execute, I don't regret that I rewrote the code using SQL_ThreadQuery - I understand that it is better... but how can I fix that problem ?

Should I re-post the script, or other information ?

Remember that I get the error only when map starts (or ends, I'm not sure), and only with SQL_ThreadQuery.


Edited:

I decided to re-post the entire script for a 'better vision'.

PHP Code:
#include <amxmodx>
#include <amxmisc>
#include <cstrike>
#include <sqlx>
#include <fakemeta>

#define PLUGINNAME    "gg stats"
#define VERSION        "1.0"
#define AUTHOR        "me"

#define table           "gg_statsx15"
#define table2          "gg_stats"

new Handle:g_SqlX;
new Handle:g_SqlConnection;
new g_error[512];
new clientcheck[32];

new kills_buffer[32];
new hs_buffer[32];
new deaths_buffer[32];

public plugin_init()
{
    register_plugin(PLUGINNAME, VERSION, AUTHOR);
    register_event("DeathMsg", "event_death", "a");
    sql_init();
}

public sql_init()
{
    new host[64], user[64], pass[64], db[64], errorcode;

    get_cvar_string("amx_sql_host", host, 63);
    get_cvar_string("amx_sql_user", user, 63);
    get_cvar_string("amx_sql_pass", pass, 63);
    get_cvar_string("amx_sql_db", db, 63);

    g_SqlX = SQL_MakeDbTuple(host, user, pass, db);
    g_SqlConnection = SQL_Connect(g_SqlX,errorcode,g_error,511);
    
    if (g_SqlConnection == Empty_Handle) set_fail_state(g_error);
    else 
    {
        new query1[1001]; format(query1, 1000, "CREATE TABLE IF NOT EXISTS `%s`(`name` varchar(100) NOT NULL default '', `kills` int(10) NOT NULL default '0',`hs` int(10) NOT NULL default '0',`deaths` int(10) NOT NULL default '0', `time` int(10) NOT NULL default '0',`firstseen` varchar(100) NOT NULL default '',", table);
        new query2[1001]; format(query2, 1000, "`lastseen` varchar(100) NOT NULL default '',`steam` int(10) NOT NULL default '0',`nonsteam` int(10) NOT NULL default '0',`lastip` varchar(100) NOT NULL default '',`wins` int(10) NOT NULL default '0',`points` int(10) NOT NULL default '0');");
        new cache[1001]; format(cache, 1000, "%s%s", query1, query2);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public client_connect(id) 
{
    clientcheck[id] = 0;
    
    kills_buffer[id] = 0;
    hs_buffer[id] = 0;
    deaths_buffer[id] = 0;
}

public client_putinserver(id) 
{
    if(clientcheck[id] == 1) authorizedx(id);
    else clientcheck[id] = 1;
}

public client_authorized(id) 
{
    if(clientcheck[id] == 1) authorizedx(id);
    else clientcheck[id] = 1;
}

public authorizedx(id) 
{
    new name[32]; get_user_name(id, name, 31);
    new authid[32]; get_user_authid(id, authid, 31);
    new ip[32]; get_user_ip(id, ip, 31, 1);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
    
    new data[1];
    data[0] = id;
    
    new cache[1001];
    
    format(cache, 1000, "SELECT * FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_firstcheck", cache, data, 1);
    
    format(cache, 1000, "SELECT wins FROM %s WHERE name='%s';", table2, name);
    SQL_ThreadQuery(g_SqlX, "handle_wins", cache, data, 1);
    
    format(cache, 1000, "SELECT points FROM %s WHERE name='%s';", table2, name);
    SQL_ThreadQuery(g_SqlX, "handle_points", cache, data, 1);
    
    format(cache, 1000, "UPDATE %s SET lastip='%s' WHERE name='%s';", table, ip, name);
    SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    
    if(contain(authid, "STEAM_0") != -1) 
    {
        format(cache, 1000, "SELECT steam FROM %s WHERE name='%s';", table, name);
        SQL_ThreadQuery(g_SqlX, "handle_steam", cache, data, 1);
    } 
    else 
    {
        format(cache, 1000, "SELECT nonsteam FROM %s WHERE name='%s';", table, name);
        SQL_ThreadQuery(g_SqlX, "handle_nonsteam", cache, data, 1);
    }
        
}

public handle_firstcheck(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(!SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
    
        new currentTime[32]; get_time("%d %b %Y - %H:%M", currentTime, 31);
        new cache[1000];
        format(cache, 1000, "INSERT INTO %s (name, kills, hs, deaths, time, firstseen, lastseen, steam, nonsteam, lastip, wins, points) values ('%s', '0', '0', '0', '0', '%s', '%s', '0', '0', '', 0, 0);",table, name, currentTime, currentTime);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_wins(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new wins = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET wins='%d' WHERE name='%s';",table, wins, name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_points(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new points = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET points='%d' WHERE name='%s';",table, points, name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_steam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new steam = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET steam='%d' WHERE name='%s';",table, steam + 1, name) ;
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_nonsteam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new nonsteam = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET nonsteam='%d' WHERE name='%s';",table, nonsteam + 1, name) ;
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public event_death() 
{
    new killer = read_data(1);
    new victim = read_data(2);
    new name_killer[32]; get_user_name(killer, name_killer, 31);
    new name_victim[32]; get_user_name(victim, name_victim, 31);
    SQL_QuoteString(g_SqlConnection, name_killer, 31, name_killer);
    SQL_QuoteString(g_SqlConnection, name_victim, 31, name_victim);
    
    if(read_data(3))  hs_buffer[killer]++;
    kills_buffer[killer]++;
    deaths_buffer[victim]++;
}

public client_disconnect(id) 
{
    clientcheck[id] = 0;

    new name[32]; get_user_name(id, name, 31);
    new currentTime[32]; get_time("%d %b %Y - %H:%M", currentTime, 31);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
    
    new data[1];
    data[0] = id;
    
    new cache[1001];
    
    format(cache, 1000, "SELECT hs FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_hs", cache, data, 1);
    
    format(cache, 1000, "SELECT kills FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_kills", cache, data, 1);
    
    format(cache, 1000, "SELECT deaths FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_deaths", cache, data, 1);
    
    format(cache, 1000, "SELECT time FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_time", cache, data, 1);
    
    format(cache, 1000, "UPDATE %s SET lastseen='%s' WHERE name='%s';",table, currentTime, name);
    SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
}

public handle_hs(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query))
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new hs = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET hs='%d' WHERE name='%s';",table, hs + hs_buffer[id], name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_kills(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new kills = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET kills='%d' WHERE name='%s';",table, kills + kills_buffer[id], name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_deaths(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new deaths = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET deaths='%d' WHERE name='%s';",table, deaths + deaths_buffer[id], name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
    }
}

public handle_time(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new timex = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET time='%d' WHERE name='%s';",table, timex + get_user_time(id, 1), name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);    
    }
}

public DefaultHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    
}

public plugin_end() 
{ 
    SQL_FreeHandle(g_SqlX);
} 
Code:
L 06/11/2010 - 23:13:46: [MySQL] Thread worker was unable to start.
L 06/11/2010 - 23:13:46: [AMXX] Displaying debug trace (plugin  "new_gg.amxx")
L 06/11/2010 - 23:13:46: [AMXX] Run time error 10: native error (native  "SQL_ThreadQuery")
L 06/11/2010 - 23:13:46: [AMXX]    [0] new_gg.sma::handle_time (line  284)
L 06/11/2010 - 23:13:46: [MySQL] Thread worker was unable to start.
L 06/11/2010 - 23:13:46: [AMXX] Displaying debug trace (plugin  "new_gg.amxx")
L 06/11/2010 - 23:13:46: [AMXX] Run time error 10: native error (native  "SQL_ThreadQuery")
L 06/11/2010 - 23:13:46: [AMXX]    [0] new_gg.sma::handle_deaths (line  269)
L 06/11/2010 - 23:13:46: [MySQL] Thread worker was unable to start.
L 06/11/2010 - 23:13:46: [AMXX] Displaying debug trace (plugin  "new_gg.amxx")
L 06/11/2010 - 23:13:46: [AMXX] Run time error 10: native error (native  "SQL_ThreadQuery")
L 06/11/2010 - 23:13:46: [AMXX]    [0] new_gg.sma::handle_kills (line  254)
L 06/11/2010 - 23:13:46: [MySQL] Thread worker was unable to start.
L 06/11/2010 - 23:13:46: [AMXX] Displaying debug trace (plugin  "new_gg.amxx")
L 06/11/2010 - 23:13:46: [AMXX] Run time error 10: native error (native  "SQL_ThreadQuery")
L 06/11/2010 - 23:13:46: [AMXX]    [0] new_gg.sma::handle_hs (line 239)
L 06/11/2010 - 23:13:46: [MySQL] Thread worker was unable to start.
L 06/11/2010 - 23:13:46: [AMXX] Displaying debug trace (plugin  "new_gg.amxx")
L 06/11/2010 - 23:13:46: [AMXX] Run time error 10: native error (native  "SQL_ThreadQuery")
L 06/11/2010 - 23:13:46: [AMXX]    [0] new_gg.sma::handle_time (line  284)
L 06/11/2010 - 23:13:46: [MySQL] Thread worker was unable to start.
L 06/11/2010 - 23:13:46: [AMXX] Displaying debug trace (plugin  "new_gg.amxx")
L 06/11/2010 - 23:13:46: [AMXX] Run time error 10: native error (native  "SQL_ThreadQuery")
...............................................
etc.etc.
AMX Mod X: v1.8.2-d
MySQL: 1.8.2-dev
Last edited by portocala; 06-12-2010 at 11:02.
portocala is offline
portocala
Member
Join Date: Jun 2010
Old 06-12-2010 , 11:57   Re: SQL Stats
Reply With Quote #13
The error [MySQL] Thread worker was unable to start. appears on map end, when trying to do SQL_ThreadQuery on client_disconnect.
If the player is disconnected during game-play, it works, but if he is disconnected due to mapchange, appears the error.

Does anyone know what's wrong ? Or even an opinion.


And more specifically:

PHP Code:
public client_disconnect(id) 
{
    new name[32]; get_user_name(id, name, 31);
    SQL_QuoteString(g_SqlConnection, name, 31, name);
    
    new data[1];
    data[0] = id;
    
    new cache[1001];
    
    format(cache, 1000, "SELECT time FROM %s WHERE name='%s';", table, name);
    SQL_ThreadQuery(g_SqlX, "handle_this", cache, data, 1);
}

public handle_this(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(SQL_NumResults(Query)) 
    {
        new id = Data[0];
        new name[32]; get_user_name(id, name, 31);
        SQL_QuoteString(g_SqlConnection, name, 31, name);
        
        new timex = SQL_ReadResult(Query, 0);
        new cache[1000];
        format(cache, 1000, "UPDATE %s SET time='%d' WHERE name='%s';",table, timex + get_user_time(id, 1), name);
        SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);    
    }
    
    return PLUGIN_CONTINUE;
}

public DefaultHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    return PLUGIN_CONTINUE;
} 
Result:

Code:
[MySQL] Thread worker was unable to start.
[AMXX] Displaying debug trace (plugin "new_gg.amxx")
[AMXX] Run time error 10: native error (native "SQL_ThreadQuery")
[AMXX]    [0] new_gg.sma::handle_this (line xxx)
* Sorry for double-post.
portocala is offline
Sylwester
Veteran Member
Sylwester's Avatar
Join Date: Oct 2006
Location: Poland
Old 06-12-2010 , 13:53   Re: SQL Stats
Reply With Quote #14
I'm not sure why, but it seems that it will not work if you try to run another ThreadedQuery in a handler.

Right now you do it like this:
on client_disconnect: get timex
query_handler: set timex + get_user_time(player_id, 1)

You can fix your problem this way:
on client_authorized: get timex (store in global array)
on client_disconnect: set timex[player_id] + get_user_time(player_id, 1)
__________________
Impossible is Nothing
Sylwester is offline
portocala
Member
Join Date: Jun 2010
Old 06-12-2010 , 15:40   Re: SQL Stats
Reply With Quote #15
It's a good idea, but I'm tired...

Thinking about sockets and PHP.

I will try what you recommended and if it is not working I will use sockets.

Thanks!
portocala is offline
portocala
Member
Join Date: Jun 2010
Old 06-13-2010 , 06:17   Re: SQL Stats
Reply With Quote #16
I fixed the error by doing many SQL code optimizations. I rethought the algorithm and I tried to do less queries (the same number of statements, but in less threads).

Now I'm using SQL_ThreadQuery and works perfectly.

Thank you all for your help!

Thank you Sylwester.
portocala is offline
Reply
AlliedModders Forum Index > AMX Mod X > Scripting > Scripting Help
Page 2 of 2 1 2

« Previous Thread | Next Thread »


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

DMCA - Archive - Top

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