SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 08/05/2020, 09:41 PM   #1
xRadical3
Gangsta
 
Join Date: May 2017
Location: Iran
Posts: 700
Reputation: 63
Exclamation Problem load player password from database takes long time

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
xRadical3 is offline   Reply With Quote
Old 08/05/2020, 10:03 PM   #2
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,863
Reputation: 1693
Default Re: Problem load player password from database takes long time

- 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.
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 08/05/2020, 10:43 PM   #3
Antenastyle
Big Clucker
 
Join Date: Apr 2017
Posts: 111
Reputation: 4
Default Re: Problem load player password from database takes long time

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?
Antenastyle is offline   Reply With Quote
Old 08/05/2020, 10:47 PM   #4
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,863
Reputation: 1693
Default Re: Problem load player password from database takes long time

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
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 08/05/2020, 11:25 PM   #5
xRadical3
Gangsta
 
Join Date: May 2017
Location: Iran
Posts: 700
Reputation: 63
Default Re: Problem load player password from database takes long time

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.
xRadical3 is offline   Reply With Quote
Old 08/05/2020, 11:56 PM   #6
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,863
Reputation: 1693
Default Re: Problem load player password from database takes long time

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.
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 09/05/2020, 12:09 AM   #7
xRadical3
Gangsta
 
Join Date: May 2017
Location: Iran
Posts: 700
Reputation: 63
Default Re: Problem load player password from database takes long time

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.
xRadical3 is offline   Reply With Quote
Old 09/05/2020, 12:22 AM   #8
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,863
Reputation: 1693
Default Re: Problem load player password from database takes long time

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).
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 09/05/2020, 01:24 PM   #9
Calisthenics
High-roller
 
Join Date: May 2018
Posts: 1,228
Reputation: 205
Default Re: Problem load player password from database takes long time

Create UNIQUE INDEX for `playername` column and set log to ERROR | WARNING.
__________________
Calisthenics is offline   Reply With Quote
Old 10/05/2020, 01:26 PM   #10
GTLS
High-roller
 
GTLS's Avatar
 
Join Date: Aug 2014
Location: India
Posts: 1,071
Reputation: 119
Default Re: Problem load player password from database takes long time

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

I don't help for rep. I help cuz I was helped in the past.


Retired from SAMP Scene. Might open SAMP Forums once in a while.

Some treats for ya. Click em
Basic House System
Simple Speedo
Base Login/Register Script

GTLS is offline   Reply With Quote
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


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql table doesn't load/save, or load take very long time Inuro Scripting Help 5 22/08/2017 09:21 PM
SQLite query takes long time to perform jbankss Scripting Help 12 10/01/2017 02:19 PM
Takes long to load! Jigsaw123 Scripting Help 3 08/08/2014 01:58 PM
Server taking a long time to load Jackatron99 Server Support 7 19/04/2014 02:42 PM
SA-MP Takes long to load Mul_Tsafe_22 Client Support 1 28/11/2009 05:49 PM


All times are GMT. The time now is 03:36 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.