Bug #868

0:Invalid account (2101) error caused by Shard Unifier Service and MySQL

Added by kerozcak over 8 years ago. Updated over 7 years ago.

Status:Closed Start date:05/10/2010
Priority:Normal Due date:
Assignee:kervala % Done:

100%
Category:Services: General
Target version:Version 0.8.0

Description

After some time of server inactivity, shard unifier service disconnects from mysql and doesn't recoonect which results with an error while trying to log in.

WRN b78a36b0 mysql_wrapper.cpp 212 query vigo/SU-0 : Mysql error : MySQL server has gone away
WRN b78a36b0 mysql_wrapper.cpp 213 query vigo/SU-0 : in query 'SELECT user_id, user_name, current_char, current_session, current_activity, current_status, public_level, account_type, content_access_level, description, lang, cookie, current_domain_id, add_privileges FROM ring_users WHERE user_id = '1'' :

One way to fix it, would be to add auto reconnect in the code.
http://blog.taragana.com/index.php/archive/how-to-enable-disable-auto-reconnect-in-mysql/

mysql_wrapper.diff (983 Bytes) Magnifier kerozcak, 05/19/2010 11:11 am

Related issues

duplicated by Ryzom - Bug #1141: SU: mysql server has gone away Rejected 10/26/2010

History

#1 Updated by RichardGreen over 8 years ago

This might not be an issue in Ryzom Core, this might be MySQL itself. If you look in mysql_wrapper.cpp at line 192 ( CConnection::query function) you can see that CR_SERVER_GONE_ERROR is explicitly checked as a result from mysql_query_real for and the connection is reopened (line 195).
The warnings that you can see are after the attempted reconnection and the query has again failed with the server going away.

Also the code MYSQL_OPT_RECONNECT is in admin_service.cpp (line 651) so a quick patch could move that code into mysql_wrapper.cpp.

Rich Green
(Also this might be totally wrong as its 5min investigation while at work on a break :) )

#2 Updated by rti over 8 years ago

I once solved such an issue like that:

bool validate() {
  if(mysql_ping(&m_mysql) != 0) {
    if(!mysql_real_connect(&m_mysql,
           DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DBNAME, 0, NULL, 0)) {
      return false;
    }
  }
  return true;
}

// then to query
if(validate()) {
  mysql_real_query(...);
}

#3 Updated by Kane over 8 years ago

What do you guys considering fixing or modifying? MySQL or the Server?

#4 Updated by kerozcak over 8 years ago

Quick fix, but it seems to work well.

#5 Updated by molator over 7 years ago

I have tested kerozcak's fix, it's fine.

#6 Updated by sfb over 7 years ago

  • Target version set to Version 0.8.0

#7 Updated by kervala over 7 years ago

  • Status changed from New to Assigned
  • Assignee set to kervala

#8 Updated by kervala over 7 years ago

  • Status changed from Assigned to Resolved
  • % Done changed from 0 to 100

Applied in changeset r1382.

#9 Updated by kervala over 7 years ago

Thanks a lot kerozcak for the patch :)

#10 Updated by kervala over 7 years ago

  • Status changed from Resolved to Closed

#11 Updated by Naush over 7 years ago

Hi,

I don't really understand the reason why you drop the mysql compatibility <5.0 ?
What in this patch is superior to debug the actual reconnection method ?

Regards

#12 Updated by kervala over 7 years ago

Naush wrote:

Hi,

I don't really understand the reason why you drop the mysql compatibility <5.0 ?
What in this patch is superior to debug the actual reconnection method ?

Regards

If you have a better proposition, please provide a patch :)

#13 Updated by kerozcak over 7 years ago

Naush wrote:

Hi,

I don't really understand the reason why you drop the mysql compatibility <5.0 ?
What in this patch is superior to debug the actual reconnection method ?

Regards

AFAIK there was no reconnecting at all before this patch.

#14 Updated by Naush over 7 years ago

If you have a better proposition, please provide a patch :)

A proposition yes, a patch certainly not !
Producing this patch will result in a big amount of time for Q&A, and I don't want to spend hours just for a single line of code :)

Also the following point is still open :
- mysql_real_query API have changed ?
- Yes, Google return the same implementation from various project
- No, Mysql documentation is clear, return !=0 , form 3.23 up to 5.5
- Mysql 3.23 API have automatic reconnection by default ( Slack9.0 & RH6.2 )

So the problem is mysql_real_query have never returned a mysql_errno().
It's just returning 0 if OK, -1 on 3.23 and 1 on 5.x

Adding : result = mysql_errno(_MysqlContext); before if(CR_SERVER_GONE_ERROR) is working out of the box.

Happy Testing :p

Also available in: Atom PDF