Problem load player password from database takes long time
#1

Here is my code:
pawn Code:
new query[128];
mysql_format(SQLHandle, query, sizeof query, "select * from `accounts` where `playername` = '%e';", PlayerName(playerid));
mysql_tquery(SQLHandle, query, "OnPlayerCheckData", "d", playerid);

public OnPlayerCheckData(playerid)
{
    if(cache_num_rows())
    {
        cache_get_value_name(0, "password", PlayerInfo[playerid][password], 128);
        //login dialogs...
    } else {
        //register dialogs...
    }
    return 1;
}
It takes about 6 , 7 seconds to show the login dialogs!
No problem with registering queries..
I have never had such a problem,
can anyone help? Thanks
Reply
#2

- Try checking if it's not something else in the code somehow (during those 6/7 seconds, can other things be done? For example, create a simple command (eg. "/printmessage") that sends some message). If it doesn't work during those 6-7 seconds, something else is up
- Is the MySQL host on the same system the SAMP server is running on? If not, consider doing that
- If you're using a seperate function to load data you might want to just fetch "password" from the user instead of everything (*)
- Check the harddrive being used by the MySQL server to store data. -If it's quite old it could be nearing it's dying days. MySQL read to and writes from files so it the harddrive is bad it could create huge laggs
- Also check your CPU and RAM usage during these operations to see if something is happening there.
Reply
#3

Do you have the SQL DB and the server in the same location/server. I mean, is it tall hosted locally or are they on different servers?
Reply
#4

Quote:
Originally Posted by Antenastyle
View Post
Do you have the SQL DB and the server in the same location/server. I mean, is it tall hosted locally or are they on different servers?
You might want to consider reading comments -___-
Quote:
Originally Posted by Kwarde
- Is the MySQL host on the same system the SAMP server is running on? If not, consider doing that
Reply
#5

Quote:
Originally Posted by Kwarde
View Post
- Try checking if it's not something else in the code somehow (during those 6/7 seconds, can other things be done? For example, create a simple command (eg. "/printmessage") that sends some message). If it doesn't work during those 6-7 seconds, something else is up
- Is the MySQL host on the same system the SAMP server is running on? If not, consider doing that
- If you're using a seperate function to load data you might want to just fetch "password" from the user instead of everything (*)
- Check the harddrive being used by the MySQL server to store data. -If it's quite old it could be nearing it's dying days. MySQL read to and writes from files so it the harddrive is bad it could create huge laggs
- Also check your CPU and RAM usage during these operations to see if something is happening there.
Solved.
But I don't understand why it was solved like this:
pawn Code:
mysql_format(SQLHandle, query, sizeof query, "select * from `accounts` where `playername` = '%e';", PlayerName(playerid));
//replaced with
format(query, sizeof query, "select * from `accounts` where `playername` = '%s'", PlayerName(playerid));
Anyway, thank you for your solutions.
Reply
#6

Playernames can't hold characters anyway that could influence the query (sql injection) so it was not needed anyway.
However that is weird tho.. are you sure there was not something else going on? Keep an eye out for it.
Reply
#7

Quote:
Originally Posted by Kwarde
View Post
Playernames can't hold characters anyway that could influence the query (sql injection) so it was not needed anyway.
However that is weird tho.. are you sure there was not something else going on? Keep an eye out for it.
CPU or RAM was probably high at the time. I restarted vps.
I don't know exactly, maybe it was an attack.
Now that I'm testing, `mysql_format` works well too.
Reply
#8

Make sure to keep an eye out for it -check your processes/usage now and then (I prefer htop myself).

Instead of restarting VPS you could first try restarting the mysql server and eventually checking for duplicate samp03svr processes (or samp-server.exe if you're on Windows, ofc).
Reply
#9

Create UNIQUE INDEX for `playername` column and set log to ERROR | WARNING.
Reply
#10

And use index to get the column instead of writing 'passworw' in cache_get_value() (instead of cache_get_value_name() ) its way faster.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)