AlliedModders - How to build reliable MySQL connection on server start?

AlliedModders (https://forums.alliedmods.net/index.php)

- Scripting (https://forums.alliedmods.net/forumdisplay.php?f=107)

- - How to build reliable MySQL connection on server start? (https://forums.alliedmods.net/showthread.php?t=320326)

How to build reliable MySQL connection on server start?

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_Connect, HX_DATABASE_CFG);

        }

    }

}



Action Timer_SQL_ReConnect(Handle timer)

{

    OnConfigsExecuted();

}



public void SQL_Callback_Connect (Database db, const char[] error, any data)

{

    const int MAX_ATTEMPT = 20;

    static int iAttempt;

    g_hDB = db;

    if (!db)

    {

        ++iAttempt;

        LogError("Attempt #%i. %s", iAttempt, error);

        

        if (iAttempt < MAX_ATTEMPT)

        {

            CreateTimer(3.0, Timer_SQL_ReConnect, _, TIMER_FLAG_NO_MAPCHANGE);

        }

        else {

            iAttempt = 0;

        }

        return;

    }

    SQL_OnConnected();

}



void SQL_OnConnected()

{

}



public void Event_SQL_Save(Event event, const char[] name, bool 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?

Re: How to build reliable MySQL connection on server start?

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.

Re: How to build reliable MySQL connection on server start?

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

Connect once on plugin start and thats it.

Your connection will stay alive.

Re: How to build reliable MySQL connection on server start?

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 (Post 2677164)

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

Why do you think so?

Re: How to build reliable MySQL connection on server start?

Connect to the database when the plugin starts and save the db globally. If you lose connection, there's no need to reconnect.

Re: How to build reliable MySQL connection on server start?

Quote:

Originally Posted by Ilusion9 (Post 2679624)

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 (Post 2679624)

If you lose connection, there's no need to reconnect.

Why?

Re: How to build reliable MySQL connection on server start?

Quote:

Originally Posted by Dragokas (Post 2679654)

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_Save, EventHookMode_PostNoCopy);
}

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

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

    hDatabase = db;
}

public void Event_SQL_Save(Event event, const char[] name, bool 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.

Re: How to build reliable MySQL connection on server start?

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.

Re: How to build reliable MySQL connection on server start?

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.

Re: How to build reliable MySQL connection on server start?

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.