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

How to build reliable MySQL connection on server start?


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Dragokas
Veteran Member
Join Date: Nov 2017
Location: Ukraine on fire
Old 12-16-2019 , 03:00   How to build reliable MySQL connection on server start?
Reply With Quote #1

Hi,

rarely I'm receiving the following error: "Can't connect to MySQL server" right after server restart (sometimes).

My SQL DB is external. I don't think there is any problem with hoster.
sm: 1.10.6454

Log:
Spoiler


PHP Code:
public void OnPluginStart()
{
    
HookEvent("round_end",             Event_SQL_Save,         EventHookMode_PostNoCopy);
}

public 
void OnConfigsExecuted()
{
    if (!
g_hDB)
    {
        if (
SQL_CheckConfig(HX_DATABASE_CFG))
        {
            
Database.Connect(SQL_Callback_ConnectHX_DATABASE_CFG);
        }
    }
}

Action Timer_SQL_ReConnect(Handle timer)
{
    
OnConfigsExecuted();
}

public 
void SQL_Callback_Connect (Database db, const char[] errorany data)
{
    const 
int MAX_ATTEMPT 20;
    static 
int iAttempt;
    
g_hDB db;
    if (!
db)
    {
        ++
iAttempt;
        
LogError("Attempt #%i. %s"iAttempterror);
        
        if (
iAttempt MAX_ATTEMPT)
        {
            
CreateTimer(3.0Timer_SQL_ReConnect_TIMER_FLAG_NO_MAPCHANGE);
        }
        else {
            
iAttempt 0;
        }
        return;
    }
    
SQL_OnConnected();
}

void SQL_OnConnected()
{
}

public 
void Event_SQL_Save(Event event, const char[] namebool dontBroadcast)
{
    
// ...
    
    
delete g_hDB;

1) Is OnConfigsExecuted() correct place for starting connection?
2) Do I need to close handle on plugin end?
3) What is a best practice to keep db connection alive? How sm handle this, e.g. when connection established and it was lost for some time (several sec. or so)?
4) Did I accomlished it correctly (in reliability meaning) so I close(?) connection on round_end (and Map End) and restore it again each time?
__________________
Expert of CMD/VBS/VB6. Malware analyst. L4D fun (Bloody Witch & FreeZone)
[My plugins] [My tools] [GitHub] [Articles] [HiJackThis+] [Donate]
Dragokas is offline
MAGNAT2645
Senior Member
Join Date: Nov 2015
Location: AlliedMods.net
Old 12-16-2019 , 13:07   Re: How to build reliable MySQL connection on server start?
Reply With Quote #2

I don't think that you need to reconnect to DB on every round.
Also, you don't need to close DB handle on plugin end, because SourceMod does it for you automatically.
__________________
MAGNAT2645 is offline
Neuro Toxin
Veteran Member
Join Date: Oct 2013
Location: { closing the void; }
Old 12-16-2019 , 17:23   Re: How to build reliable MySQL connection on server start?
Reply With Quote #3

If the db fails during connection I wouldnt bother reattempting connect.

Connect once on plugin start and thats it.

Your connection will stay alive.
__________________
Neuro Toxin is offline
Dragokas
Veteran Member
Join Date: Nov 2017
Location: Ukraine on fire
Old 01-09-2020 , 03:45   Re: How to build reliable MySQL connection on server start?
Reply With Quote #4

So, any ideas why that error sometimes happens on server start?

I can't believe in coincidence. I have no problems with DB hoster / or other connection errors during gameplay. One of those 20 attemps each 3 sec. definitely should have been successful.

Quote:
Originally Posted by Neuro Toxin View Post
If the db fails during connection I wouldnt bother reattempting connect.
Why do you think so?
__________________
Expert of CMD/VBS/VB6. Malware analyst. L4D fun (Bloody Witch & FreeZone)
[My plugins] [My tools] [GitHub] [Articles] [HiJackThis+] [Donate]
Dragokas is offline
Ilusion9
Veteran Member
Join Date: Jun 2018
Location: Romania
Old 01-10-2020 , 06:51   Re: How to build reliable MySQL connection on server start?
Reply With Quote #5

Connect to the database when the plugin starts and save the db globally. If you lose connection, there's no need to reconnect.
__________________
Ilusion9 is offline
Dragokas
Veteran Member
Join Date: Nov 2017
Location: Ukraine on fire
Old 01-10-2020 , 10:54   Re: How to build reliable MySQL connection on server start?
Reply With Quote #6

Quote:
Originally Posted by Ilusion9 View Post
Connect to the database when the plugin starts and save the db globally.
I see no difference to expected result if I will connect to DB on plugin start instead of OnConfigsExecuted.

Quote:
Originally Posted by Ilusion9 View Post
If you lose connection, there's no need to reconnect.
Why?
__________________
Expert of CMD/VBS/VB6. Malware analyst. L4D fun (Bloody Witch & FreeZone)
[My plugins] [My tools] [GitHub] [Articles] [HiJackThis+] [Donate]
Dragokas is offline
Ilusion9
Veteran Member
Join Date: Jun 2018
Location: Romania
Old 01-10-2020 , 11:39   Re: How to build reliable MySQL connection on server start?
Reply With Quote #7

Quote:
Originally Posted by Dragokas View Post
Why?
If you connect to the database successfully and then you lose connection, you don't have to connect again (if you save the database globally - example - in hDatabase variable. The hDatabase will be valid when the dabatase is on again.

Just connect to the database OnMapStart (or OnConfigsExecuted) and check if the db is null.

PHP Code:

Database hDatabase
;

public 
void OnPluginStart()
{
    
HookEvent("round_end"Event_SQL_SaveEventHookMode_PostNoCopy);
}

public 
void OnConfigsExecuted()
{
    if (!
hDatabase)
    {
        if (
SQL_CheckConfig(HX_DATABASE_CFG))
        {
            
Database.Connect(SQL_Callback_ConnectHX_DATABASE_CFG);
        }
    }


public 
void SQL_Callback_Connect (Database db, const char[] errorany data)
{
    if (!
db)
    {
        
LogError("Could not connect to the database: %s"error);
        return;
    }

    
hDatabase db;
}

public 
void Event_SQL_Save(Event event, const char[] namebool dontBroadcast)
{
    if (!
hDatabase)
    {
        return; 
// no db connection
    
}

    
// ...
    
    // delete g_hDB; // why are you deleting the db here ???

This should work well, except deleting the database when the round ends.
__________________

Last edited by Ilusion9; 01-10-2020 at 11:52.
Ilusion9 is offline
Dragokas
Veteran Member
Join Date: Nov 2017
Location: Ukraine on fire
Old 01-10-2020 , 23:22   Re: How to build reliable MySQL connection on server start?
Reply With Quote #8

Ohh, I understand that from the first answer of MAGNAT. Thank you.

That doesn't explain why "Can't connect to MySQL server" error happen on server start.
__________________
Expert of CMD/VBS/VB6. Malware analyst. L4D fun (Bloody Witch & FreeZone)
[My plugins] [My tools] [GitHub] [Articles] [HiJackThis+] [Donate]
Dragokas is offline
MAGNAT2645
Senior Member
Join Date: Nov 2015
Location: AlliedMods.net
Old 01-16-2020 , 09:30   Re: How to build reliable MySQL connection on server start?
Reply With Quote #9

Your DB might be overloaded with slow queries (that's not 100% true, but you should try to dig this way).
I had similar problem when used non-threaded queries (like DBStatement Execute inside loop) without DB Lock.

EDIT:

There can be several problems:
1) DB server might be stopped (or disabled). Just check server status.
2) Firewall may be blocking your connections.
3) Wrong login or/and password (or configuration generally).
4) DB User doesn't have permissions.
5) Check slow queries log. Also try to run some queries to see if your DB server responds normally.
__________________

Last edited by MAGNAT2645; 01-16-2020 at 09:39.
MAGNAT2645 is offline
Dragokas
Veteran Member
Join Date: Nov 2017
Location: Ukraine on fire
Old 01-16-2020 , 10:36   Re: How to build reliable MySQL connection on server start?
Reply With Quote #10

Thanks for help.

Plugin written with threaded queries only.
DB is free of loading most of the time.

1) Report from hoster panel: no incidents during that week.
2) 3306, 5432 ports are on firewall exclusions.
3) It is correct.
4) User has permissions.
5) Dunno how and where logs are stored. But, I'll try found acrticles on it.
MySQL Query browser answer:
Quote:
3500 rows fetched in 0,14 sec.
Problem is no more reproduced during 2 weeks.

Very appreciate, if you have more thoughts.
__________________
Expert of CMD/VBS/VB6. Malware analyst. L4D fun (Bloody Witch & FreeZone)
[My plugins] [My tools] [GitHub] [Articles] [HiJackThis+] [Donate]
Dragokas is offline
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

Forum Jump


All times are GMT -4. The time now is 06:46.


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