SQL Stats

[portocala]

Hello,

I'm working on a plugin that records live statistics of players on my CS 1.6 Server (kills, deaths, hs, online time, etc) and I'm wondering if SQL is able to handle this without lag. I intend to display the results on a webpage.

I worry about DeathMsg event, because there will be more data traffic (think about a 22 players Deathmatch Server). Generaly speaking it's not much, but it is for SQL ?

Every kill / hs / death is send to MySQL Database with SQL_PrepareQuery and SQL_Execute.

I respectfully request your opinions about that.

Excuse me if I made mistakes of expression.

[Brreaker]

I think MySQL can handle it, also, is your server performant enough to handle this ?

[unnyquee]

Use threaded queries.

[fang]

Try psychostats? :o

[portocala]

I think MySQL can handle it, also, is your server performant enough to handle this ?

Yes. No problems about the server's configurations (CPU, RAM, etc).

Use threaded queries.

Do you mean SQL_ThreadQuery ? I had problems using this. Every map end / start I got errors ([MySQL] Thread worker was unable to start.), so I started code using SQL_PrepareQuery and SQL_Execute which by now works perfectly.

Can you explain the difference between SQL_ThreadQuery and SQL_PrepareQuery+SQL_Execute ? Honestly, I have searched but not found anything about it.

Try psychostats? :o

I used Psychostats, but now I haven't an external server to perform the log processing, so I thought to build my own way to get these information on my website.



Thanks for the replies!


Edited:

If I have this:


new Handle:Query;

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)

Query = SQL_PrepareQuery(g_SqlConnection, "SELECT kills FROM %s WHERE name='%s';", table, name_killer)
SQL_Execute(Query);

if(SQL_NumResults(Query)) {
new kills = SQL_ReadResult(Query, 0);
SQL_FreeHandle(Query);
Query = SQL_PrepareQuery(g_SqlConnection, "UPDATE %s SET kills='%d' WHERE name='%s';",table, kills + 1, name_killer)
SQL_Execute(Query);
}

SQL_FreeHandle(Query);
}

What happens when 'double/triple-kill' ? Is it OK for SQL ?

[Brreaker]

It should be, as far as I know you may enter up to 150 data/second :)

[Sylwester]

Do you mean SQL_ThreadQuery ? I had problems using this. Every map end / start I got errors ([MySQL] Thread worker was unable to start.), so I started code using SQL_PrepareQuery and SQL_Execute which by now works perfectly.

Can you explain the difference between SQL_ThreadQuery and SQL_PrepareQuery+SQL_Execute ? Honestly, I have searched but not found anything about it.
It's all explained in sqlx tutorial:
The true power lies in threaded querying. Threaded querying means nothing short of a rewrite of an entire plugin that's written with DBI, but has huge advantages, especially on slow connections. The idea of a threaded query is essentially that a new thread (a sub-process) with which its only goal is to send, monitor, and inform of the things that happen to a query. Because it is on a seperate thread, a query that would normally take 1 second (and 1 second of total connection loss for all clients in the server) still takes the same amount of time, but does not interrupt gameplay


So, forget about prepare/execute query and go back to using threaded queries. I recently posted sqlx example that uses sqlite (file on localhost):
http://forums.alliedmods.net/showthread.php?p=1204466#post1204466
Maybe it will help you fix your problems with threaded queries.

[portocala]

OK, Thank you!

I rewrote the code using SQL_ThreadQuery but it is not working at all.

I get:


L 06/11/2010 - 12:41:57: Function "event_death" was not found
L 06/11/2010 - 12:41:57: [AMXX] Displaying debug trace (plugin "stats_gg.amxx")
L 06/11/2010 - 12:41:57: [AMXX] Run time error 19: function not found
L 06/11/2010 - 12:41:57: [AMXX] [0] stats_gg.sma::plugin_init (line 23)
L 06/11/2010 - 12:42:02: [MySQL] Invalid database handle: 0
L 06/11/2010 - 12:42:02: [AMXX] Displaying debug trace (plugin "stats_gg.amxx")
L 06/11/2010 - 12:42:02: [AMXX] Run time error 10: native error (native "SQL_PrepareQuery")
L 06/11/2010 - 12:42:02: [AMXX] [0] stats_gg.sma::authorizedx (line 76)
L 06/11/2010 - 12:42:02: [AMXX] [1] stats_gg.sma::client_putinserver (line 57)
.
.
.
And there is my entire plugin:


#include <amxmodx>
#include <amxmisc>
#include <cstrike>
#include <sqlx>
#include <fakemeta>

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

#define table "gg_statsx13"
#define table2 "gg_stats"

new Handle:g_SqlX;
new Handle:g_SqlConnection;
new g_error[512];
new clientcheck[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;
}

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 cache[1001];

format(cache, 1000, "SELECT * FROM %s WHERE name='%s';", table, name);
SQL_ThreadQuery(g_SqlX, "handle_firstcheck", cache, name, 31);

format(cache, 1000, "SELECT wins FROM %s WHERE name='%s';", table2, name);
SQL_ThreadQuery(g_SqlX, "handle_wins", cache, name, 31);

format(cache, 1000, "SELECT points FROM %s WHERE name='%s';", table2, name);
SQL_ThreadQuery(g_SqlX, "handle_points", cache, name, 31);

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, name, 31);
}
else
{
format(cache, 1000, "SELECT nonsteam FROM %s WHERE name='%s';", table, name);
SQL_ThreadQuery(g_SqlX, "handle_nonsteam", cache, name, 31);
}

}

public handle_firstcheck(FailState,Handle:Query,Erro r[],Errcode,Data[],DataSize)
{
if(!SQL_NumResults(Query))
{
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, Data, currentTime, currentTime);
SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
}
}

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

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

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

public handle_nonsteam(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
if(SQL_NumResults(Query))
{
new nonsteam = SQL_ReadResult(Query, 0);
new cache[1000];
format(cache, 1000, "UPDATE %s SET nonsteam='%d' WHERE name='%s';",table, nonsteam + 1, Data) ;
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);

new cache[1001];

if(read_data(3))
{
format(cache, 1000, "SELECT hs FROM %s WHERE name='%s';", table, name_killer);
SQL_ThreadQuery(g_SqlX, "handle_hs", cache, name_killer, 31);
}
else
{
format(cache, 1000, "SELECT kills FROM %s WHERE name='%s';", table, name_killer);
SQL_ThreadQuery(g_SqlX, "handle_kills", cache, name_killer, 31);
}

format(cache, 1000, "SELECT deaths FROM %s WHERE name='%s';", table, name_victim);
SQL_ThreadQuery(g_SqlX, "handle_deaths", cache, name_victim, 31);
}

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

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

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

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 cache[1001];

format(cache, 1000, "SELECT time FROM %s WHERE name='%s';", table, name);
SQL_ThreadQuery(g_SqlX, "handle_time", cache, name, 31);

format(cache, 1000, "UPDATE %s SET lastseen='%s' WHERE name='%s';",table, currentTime, name);
SQL_ThreadQuery(g_SqlX, "DefaultHandle", cache);
}

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

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

}

public plugin_end()
{
SQL_FreeHandle(g_SqlX);
}

There must be some scripting mistakes, but I can't see where.
I don't expect you to do all the work for me, I just need some tips.

Thanks again!

[ProIcons]

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, I Suggest you to save players's stats on variables and when the Map finish or when you want with a command to update all the kills,

[unnyquee]

L 06/11/2010 - 12:42:02: [MySQL] Invalid database handle: 0


Write:

server_cmd( "exec sql.cfg" );
server_exec( );


Before catching the amx_sql_* CVARs.
That should fix this.

[Sylwester]

@portocala: That error log does not match with the code. Maybe you forgot to copy .amxx file to plugins folder before testing it.
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.

[portocala]

@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'.

#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,Erro r[],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);
}
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

[portocala]

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:


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:


[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.

[Sylwester]

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)

[portocala]

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]

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.