PDA

View Full Version : Unknown MySQL Problem


Surferdude
02/10/2011, 01:02 AM
Well, Hello.

I have this problem! My server keeps restarting every once and a while due to an error unknown to me. I have checked logs and nothing was a problem there BUT when I had a look at the Debug folder for MySQL I found this everytime the server restarted.

[01:25:29] CMySQLHandler::FreeResult() - The result is already empty.
[01:25:29] >> mysql_store_result( Connection handle: 1 )
[01:25:29] CMySQLHandler::StoreResult() - No data to store.
[01:25:29] >> mysql_free_result( Connection handle: 1 )
[01:25:29] CMySQLHandler::FreeResult() - The result is already empty.
[01:38:52]
[01:38:52] ---------------------------
[01:38:52] MySQL Debugging activated (09/23/11) <<<<<<<Where it Restarts


And also before some of the crashes I found long values like this


[08:36:38] CMySQLHandler::EscapeString(dasdasdasdasesdadadads adadadAsdasdasdasdasdasdasdadad); - Escaped 53 characters to dasdasdasdasesdadadadsadadadAsdasdasdasdasdasdasda dad.
[08:36:38] >> mysql_query( Connection handle: 1 )
[08:36:38] CMySQLHandler::Query(SELECT * FROM `playerinfo` WHERE `user` = '' AND `password` = md5('dasdasdasdasesdadadadsadadadAsdasdasdasdasdas dasdadad')) - Successfully executed.
[08:36:38] >> mysql_store_result( Connection handle: 1 )
[08:36:38] CMySQLHandler::StoreResult() - Result was stored.
[08:36:38] >> mysql_num_rows( Connection handle: 1 )
[08:36:38] CMySQLHandler::NumRows() - Returned 0 row(s)
[08:36:45]
[08:36:45] ---------------------------
[08:36:45] MySQL Debugging activated (10/01/11) <<<<<<Restarts here


Im using the R6 MySQL Plugin by Gystyleez (Or however you spell it)

HELP!! Please!!

[HiC]TheKiller
02/10/2011, 02:14 AM
Could we see some code?

Surferdude
02/10/2011, 04:31 AM
This is my Code in my script which saves and loads playerinfo.

stock SavePInfo(playerid)
{
if(GetPVarInt(playerid, "LoggedIN") == 1)
{
new Query[600];

format(Query, sizeof(Query), "UPDATE `playerinfo` SET `kills` = %d, `deaths` = %d, `money` = %d, `Level` = %d, `Score` = %d, `VIP` = %d, `Ban` = %d, `Connect` = %d WHERE `user` = '%s'", // Also remember to update this...

Account[playerid][pKills],
Account[playerid][pDeaths],
GetPlayerMoney(playerid),
Account[playerid][pLevel],
GetPlayerScore(playerid),
Account[playerid][pVIP],
Account[playerid][pBanned],
Account[playerid][pConnect],
pName(playerid));

mysql_query(Query);
mysql_free_result();
AvoidOOSync();
return 1;
}
else return 0;
}

stock LoadPlayerInfo(iPlayer)
{
new Query[700];

if(mysql_fetch_row(Query))
{
sscanf(Query, "p<|>e<s[24]s[35]dddddddd>",Account[iPlayer]);
mysql_free_result();
}
return 1;
}

Do you want more of the debug?

Kar
02/10/2011, 05:13 AM
where are you calling mysql_debug.. your surpose to call it before the first mysql_connect

[HiC]TheKiller
02/10/2011, 05:25 AM
Firstly, you're freeing a result that you haven't stored in the first place. Secondly, you're probably starting debug when the script is already debugging. Make sure that you only have mysql_debug(1) once in your script on OnGameModeInit / OnFilterScriptInit.

Surferdude
02/10/2011, 05:42 AM
I have only called mysql_debug(1) once which is under OnGameModeInit.

TheKiller;1437394']Firstly, you're freeing a result that you haven't stored in the first place.

The AvoidOOSync(); has this stock

stock AvoidOOSync()
{
mysql_store_result();
mysql_free_result();
return 1;
}

So should I just remove the mysql_free_result from LoadPlayerInfo(iPlayer) or where?


Also where mysql_debug(1); was put, It was put after mysql_connect. Could this have been the cause?

iggy1
02/10/2011, 07:24 AM
stock AvoidOOSync()
{
mysql_store_result();
mysql_free_result();
return 1;
}

I'm not sure but i think if you call that function and you have a result already stored, you will be causing a memory leak. That's probably causing the sql errors too (storing an empty result/trying to free empty result.).

Surferdude
05/10/2011, 10:44 AM
This has been fixed, How?

AvoidOOSync was stuffing up everything so I have put:

mysql_store_result();
mysql_free_result();

Where Avoid00Sync was.

There was also a problem in the LOGIN area, The mysql_store_result was there but no mysql_free_result.

Thanks for all your help!

Scenario
05/10/2011, 11:32 AM
md5('dasdasdasdasesdadadadsadadadAsdasdasdasdasdas dasdadad'))

I don't think that is the password either. It seems you are storing the value above into the password variable, when instead you should be hashing the password and saving the HASH into the password variable. :)

Surferdude
05/10/2011, 11:47 AM
md5('dasdasdasdasesdadadadsadadadAsdasdasdasdasdas dasdadad'))

I don't think that is the password either. It seems you are storing the value above into the password variable, when instead you should be hashing the password and saving the HASH into the password variable. :)

Ohh, Really? If it were wrong would it still be working?

kirk
05/10/2011, 12:48 PM
Make sure you store the password on a string not on an integer, aswell make sure to save it on the database as a string not as an integer, using '%s' or if you use mysql_format(); '%e'.

Aswell show us the code used for the log in of a player.

Surferdude
06/10/2011, 12:34 AM
Make sure you store the password on a string not on an integer, aswell make sure to save it on the database as a string not as an integer, using '%s' or if you use mysql_format(); '%e'.

Aswell show us the code used for the log in of a player.


case LOGIN:
{
if(!response)
{
format(Msg, sizeof(Msg), ""#CBLUE"Player: "#CORANGE"%s(%d) "#CBLUE"has been kicked - Reason: Not loggin in!", pName(playerid), playerid);
SendClientMessageToAll(COLOR_GREY, Msg);
Kick(playerid);
}
else
{
if(!strlen(inputtext)) DialogInput(playerid, LOGIN, ""#CCADET"Account Login", ""#CYELLOW"You must enter a password below to continue!", "Login", "Leave");

new
EscPass[38],
Query[128];

mysql_real_escape_string(inputtext, EscPass);

format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = md5('%s')", pName(playerid), EscPass);

mysql_query(Query);
mysql_store_result();

if(mysql_num_rows() > 0)
{
LoadPlayerInfo(playerid);

SetPVarInt(playerid, "LoggedIN", 1);

SetPlayerMoney(playerid, Account[playerid][pMoney]);

SetPlayerScore(playerid, Account[playerid][pScore]);

SetPlayerColor(playerid, COLOUR_GREY);

SendClientMessage(playerid, COLOR_CADETBLUE, "You have been logged in");

Account[playerid][pConnect] += 1;
}
else
{
SendClientMessage(playerid, COLOUR_RED, "You have entered an incorrect password, try again!");
DialogInput(playerid, LOGIN, ""#CCADET"Account Login", ""#CYELLOW"You have 3 chances to enter a correct password..", "Login", "Leave");
}
mysql_free_result();
}
return 1;
}
}
This is under OnDialogResponse after the case REGISTER . The indentations are correct in the script just couldn't do it on the forums.

Load Player Info Here:
stock LoadPlayerInfo(iPlayer)
{
new Query[700];

if(mysql_fetch_row(Query))
{
sscanf(Query, "p<|>e<s[24]s[35]dddddddd>",Account[iPlayer]);
}
return 1;
}

Surferdude
06/10/2011, 12:53 AM
This just happened again to my server:

[17:25:17] CMySQLHandler::EscapeString(jefe(junior)1); - Escaped 13 characters to jefe(junior)1.
[17:25:17] >> mysql_query( Connection handle: 1 )
[17:25:17] CMySQLHandler::Query(SELECT * FROM `playerinfo` WHERE `user` = 'jefe(junior)1') - Successfully executed.
[17:25:17] >> mysql_store_result( Connection handle: 1 )
[17:25:17] CMySQLHandler::StoreResult() - Result was stored.
[17:25:17] >> mysql_num_rows( Connection handle: 1 )
[17:25:17] CMySQLHandler::NumRows() - Returned 1 row(s)
[17:25:17] >> mysql_free_result( Connection handle: 1 )
[17:25:17] CMySQLHandler::FreeResult() - Result was successfully free'd.
[17:25:27] >> mysql_real_escape_string( Connection handle: 1 )
[17:25:27] CMySQLHandler::EscapeString(dddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddddddddddddd dddddd); - Escaped 128 characters to dddddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddd.
[17:25:27] >> mysql_query( Connection handle: 1 )
[17:25:27] CMySQLHandler::Query(SELECT * FROM `playerinfo` WHERE `user` = '' AND `password` = md5('ddddddddddddddddddddddddddddddddddddddddddddd ddddddddddddddd) - An error has occured. (Error ID: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''dddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddd' at line 1)
[17:25:27] >> mysql_store_result( Connection handle: 1 )
[17:25:27] CMySQLHandler::StoreResult() - No data to store.
[17:25:27] >> mysql_num_rows( Connection handle: 1 )
[17:25:27] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
[17:25:27] >> mysql_free_result( Connection handle: 1 )
[17:25:27] CMySQLHandler::FreeResult() - The result is already empty.
[17:26:07]
[17:26:07] ---------------------------
[17:26:07] MySQL Debugging activated (10/05/11)
[17:26:07] ---------------------------

Honest_Player
06/10/2011, 01:42 AM
dont known much about mysql :D

Surferdude
06/10/2011, 02:25 AM
dont known much about mysql :D

Well im really trying to undertsand it with your guys reply's.

XFlawless
06/10/2011, 02:31 AM
format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = md5('%s')", pName(playerid), EscPass);


Your query is too inefficient, if you have 5k accounts it could cause a lag spike while fetching the data.

format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = md5('%s') LIMIT 1", pName(playerid), EscPass);


[17:25:27] CMySQLHandler::Query(SELECT * FROM `playerinfo` WHERE `user` = '' AND `password` = md5('ddddddddddddddddddddddddddddddddddddddddddddd ddddddddddddddd) - An error has occured. (Error ID: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''dddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddd' at line 1)
You are exceeding cell value.

[17:25:27] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
Your connection is dead with mysql server.

Check this for better SQL practices (http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/)

Surferdude
06/10/2011, 03:26 AM
You are exceeding cell value.

How can I fix this?

XFlawless
06/10/2011, 08:09 AM
How can I fix this?

Increase cell size.

System64
06/10/2011, 08:52 AM
You cannot call this function now. (Reason: Dead Connection)
If you are using XAMPP, check if MySQL is started!
Also increase Query size, example
new Query[700];

Surferdude
06/10/2011, 10:40 AM
You cannot call this function now. (Reason: Dead Connection)
If you are using XAMPP, check if MySQL is started!
Also increase Query size, example
new Query[700];

And how about EscPass[30]? Do I need to increase this also?

XFlawless
06/10/2011, 12:46 PM
And how about EscPass[30]? Do I need to increase this also?

Well you need to limit the input chars. @MySQL log it has escaped 128 chars.

Use strlen() (http://wiki.sa-mp.com/wiki/Strlen) to limit the input.


case LOGIN:
{
if(!response)
{
format(Msg, sizeof(Msg), ""#CBLUE"Player: "#CORANGE"%s(%d) "#CBLUE"has been kicked - Reason: Not loggin in!", pName(playerid), playerid);
SendClientMessageToAll(COLOR_GREY, Msg);
Kick(playerid);
}
else
{
// Make sure you modify registration input length.
if(strlen(inputtext) < 1 || strlen(inputtext) >= 32) DialogInput(playerid, LOGIN, ""#CCADET"Account Login", ""#CYELLOW"Your password is too lengthy or too short !", "Login", "Leave");

new
EscPass[32],
Query[200];

mysql_real_escape_string(inputtext, EscPass);

format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = md5('%s')", pName(playerid), EscPass);

mysql_query(Query);

if(mysql_num_rows() > 0)
{
LoadPlayerInfo(playerid);

SetPVarInt(playerid, "LoggedIN", 1);

SetPlayerMoney(playerid, Account[playerid][pMoney]);

SetPlayerScore(playerid, Account[playerid][pScore]);

SetPlayerColor(playerid, COLOUR_GREY);

SendClientMessage(playerid, COLOR_CADETBLUE, "You have been logged in");

Account[playerid][pConnect] += 1;
}
else
{
SendClientMessage(playerid, COLOUR_RED, "You have entered an incorrect password, try again!");
DialogInput(playerid, LOGIN, ""#CCADET"Account Login", ""#CYELLOW"You have 3 chances to enter a correct password..", "Login", "Leave");
}
mysql_free_result();
}
return 1;
}
}

Hope it works not sure try it and reply.

kirk
06/10/2011, 02:31 PM
new
EscPass[38],
Query[128];

mysql_real_escape_string(inputtext, EscPass);

format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = md5('%s')", pName(playerid), EscPass);

mysql_query(Query);
mysql_store_result();



stock LoadPlayerInfo(iPlayer)
{
new Query[700];

if(mysql_fetch_row(Query))
{
sscanf(Query, "p<|>e<s[24]s[35]dddddddd>",Account[iPlayer]);
}
return 1;
}

Dont escape a hashed string, it makes no sense, injections are done through a query... "SELECT * FROM players WHERE name = '%s' AND pass = '%s' ", value[...][...], value[...]);

yes this can be injected beacuse the "hacker" could log in with a name like " 'His_Name Or 1=1 -- "that would completly change your query and the "and pass = '%s'" would be deleted, so he would have access to the database.

I guess that name cant log in your server, the script would kick him but it was an example.

Now lets hash this code " ' Or 1=1 -- " .. it would output this " 55916c6e1451c9ef18a3ed876d15965e " i doubt your database could be injected using that code, so escaping hased string is a waste of time and memory cells.

So dont escape the password if you gonna hash it.. by the way i recommend you using Whirlpool http://forum.sa-mp.com/showthread.php?t=65290.

How many times do you call the LoadPlayerInfo function? if you call it 1 time dont make a function its a waste of time and the code wont be more efficient...

Would be more efficient if you thread the SELECT *... query http://forum.sa-mp.com/showthread.php?p=323895#post323895


Use mysql_fetch_field_row() instead of sscanf.
dont known much about mysql :D

Dont spam then.

Your query is too inefficient, if you have 5k accounts it could cause a lag spike while fetching the data.

format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = md5('%s') LIMIT 1", pName(playerid), EscPass);


[17:25:27] CMySQLHandler::Query(SELECT * FROM `playerinfo` WHERE `user` = '' AND `password` = md5('ddddddddddddddddddddddddddddddddddddddddddddd ddddddddddddddd) - An error has occured. (Error ID: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''dddddddddddddddddddddddddddddddddddddddddddddddd dddddddddddd' at line 1)
You are exceeding cell value.

[17:25:27] CMySQLHandler::NumRows() - You cannot call this function now. (Reason: Dead Connection)
Your connection is dead with mysql server.

Check this for better SQL practices (http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/)

First of all if you thread all your query's and optimizate their sizes i doubt your server would
glitch, i would like to see it myself.

Did you bother reading the debug code? the connection is not dead, try to understand it a bit, he using the SELECT query, which didnt get processed as it threw an error, he couldnt SELECT anything from the database so explain me how do you store the data if you got no data to store?

The database was up.
You cannot call this function now. (Reason: Dead Connection)
If you are using XAMPP, check if MySQL is started!
Also increase Query size, example
new Query[700];

700 cells query ? Why?

And how about EscPass[30]? Do I need to increase this also?

yes, md5 hash always outputs 32 hex digits, use [33] for example but as i alredy said dont escape hashed strings.