mysql Db has gone away...

[Hirox]

So, Im having this problem:

I start my server, login correctly, and start playing. After an hour or so, if I try to logout, the command console says DB Error, Mysql server has gone away.

I tried googling and found that this variable in mysql database might be at fault:

SHOW VARIABLES LIKE 'wait_timeout';

In my server, it shows that it is only 7800 instead of the default 28800. So I asked my server provider to change it and he said he cannot.

So, I guess i need to find other solutions...

Maybe Charserv and Loginserv keep pinging the database to keep it alive?

I tried googling again and found this on one of the rathena source branches in google.

https://code.google....=svn9728&r=9728

on File inter_athena.conf

// Interval (in hours) at which servers do a ping on all sql-connections
// to keep them alive (the default mysql settings makes a connection time-out
// after 8 hours of inactivity).
// 0 disables (default). Enable it only if you are getting
// "MySQL server has gone Away" errors due to lack of activity on your server
connection_ping_interval: 7  

then I checked my inter_athena.conf and does not have that line, so I added it.

Unfortunately, it did not work. It has no effect whatsoever

So, again, I need to ask if any other file has something like this to keep Db alive?

Or again, maybe another solution for this problem?

Im thinking... maaybe a cronjob that stops loginserv and charserv and restarts it every hour?

Bump, anyone havin this issue too?

Edited November 30, 2012 by Hirox

[Asura]

Hi Hirox,

Is your MySQL server being hosted on the same machine as your Athena server?

[Hirox]

Yes it is. Although it is a shared server with other sites hosted in the same machine.

My solution so far has been shutting down loginaserv and charserv once every hour and then restart them both.

Of course, with a cronjob...

But it would be nice to have a real solution to this problem.

[Brian]

rAthena already re-pings the MySQL Server periodically.

The interval is based on the wait_timeout variable it gets from the MySQL Server.

trunk/src/common/sql.c - Sql_GetTimeout() function

"SHOW VARIABLES LIKE 'wait_timeout'"

trunk/src/common/sql.c - Sql_P_Keepalive()

/// Establishes keepalive (periodic ping) on the connection.
///
/// @return the keepalive timer id, or INVALID_TIMER
/// @private
static int Sql_P_Keepalive(Sql *self)
{
   uint32 timeout, ping_interval;

   // set a default value first
   timeout = 28800; // 8 hours

   // request the timeout value from the mysql server
   Sql_GetTimeout(self, &timeout);

   if (timeout < 60)
       timeout = 60;

   // establish keepalive
   ping_interval = timeout - 30; // 30-second reserve
   //add_timer_func_list(Sql_P_KeepaliveTimer, "Sql_P_KeepaliveTimer");
   return add_timer_interval(gettick() + ping_interval*1000, Sql_P_KeepaliveTimer, 0, (intptr_t)self, ping_interval*1000);
}

I'm guessing the cause is your host's restrictive MySQL settings. They probably have the MySQL Server configured for web-based applications (stuff like forums, CMS, and other websites that use PHP+MySQL). These typically use many concurrent, short MySQL connections...

whereas rAthena uses a few persistent MySQL connections.

Did he give a reason why he wouldn't change it?

[Hirox]

Yes, the reason is because it is preconfigured to websites and forums. Ive been with this host for almost two years, and just recently I opened a private RO server. (Though it is closed because of this problem)

I will change the function sql_p_keepalive... se if that helps.

Will post back if it solves the problem. (If not, Im planning on changing servers...)

[Hirox]

After testing for a whole day, I had no database timeouts.

I changed the timeout variable in the code, based on the tests of my server. For example, default is 28800. My server had 7800 but kept disconnecting. I tried lowering the number to 7000, 5000, and found out that 3600 is the lowest number that kept the database alive.

If you have a similar problem, you should try the same.

/// @return the keepalive timer id, or INVALID_TIMER
/// @private
static int Sql_P_Keepalive(Sql *self)
{
uint32 timeout, ping_interval;

// set a default value first
timeout = 3600; // lowest number I found that my server did not disconnect

// request the timeout value from the mysql server
//Sql_GetTimeout(self, &timeout); <removed this as it gave a higher number and kept disconnecting and you are already giving the variable.

if (timeout < 60)
timeout = 60;

So thats it. It can be counted as problem solved. Thank you very much.