Core/DBLayer:

- Add generic HandleMySQLErrno() method.
- Add the ability to re-execute a certain statement if errorhandler resolved the situation succesfuly.
- Add support for MySQL errno´s 2006, 2013, 2048, 2055 - fixes automatic reconnection behaviour.
- Cleanup in affected code

NOTE: You should still be smart enough to configure your worldserver and authserver´s MaxPingTime to be lower than your MySQL´s wait_time.

--HG--
branch : trunk

1 files changed, 95 insertions, 30 deletions

diff --git a/src/server/shared/Database/MySQLConnection.cpp b/src/server/shared/Database/MySQLConnection.cpp
index e2acdb5d29e..b41cab510ed 100755
--- a/src/server/shared/Database/MySQLConnection.cpp
+++ b/src/server/shared/Database/MySQLConnection.cpp
@@ -37,7 +37,8 @@ m_queue(NULL),
 m_worker(NULL),
 m_Mysql(NULL),
 m_connectionInfo(connInfo),
-m_connectionFlags(CONNECTION_SYNCH)
+m_connectionFlags(CONNECTION_SYNCH),
+m_reconnecting(false)
 {
 }
 
@@ -45,7 +46,8 @@ MySQLConnection::MySQLConnection(ACE_Activation_Queue* queue, MySQLConnectionInf
 m_queue(queue),
 m_Mysql(NULL),
 m_connectionInfo(connInfo),
-m_connectionFlags(CONNECTION_ASYNC)
+m_connectionFlags(CONNECTION_ASYNC),
+m_reconnecting(false)
 {
     m_worker = new DatabaseWorker(m_queue, this);
 }
@@ -116,31 +118,21 @@ bool MySQLConnection::Open()
 
     if (m_Mysql)
     {
-        sLog.outSQLDriver("MySQL client library: %s", mysql_get_client_info());
-        sLog.outSQLDriver("MySQL server ver: %s ", mysql_get_server_info(m_Mysql));
-        if (mysql_get_server_version(m_Mysql) != mysql_get_client_version())
-            sLog.outSQLDriver("[WARNING] MySQL client/server version mismatch; may conflict with behaviour of prepared statements.");
+        if (!m_reconnecting)
+        {
+            sLog.outSQLDriver("MySQL client library: %s", mysql_get_client_info());
+            sLog.outSQLDriver("MySQL server ver: %s ", mysql_get_server_info(m_Mysql));
+            if (mysql_get_server_version(m_Mysql) != mysql_get_client_version())
+                sLog.outSQLDriver("[WARNING] MySQL client/server version mismatch; may conflict with behaviour of prepared statements.");
+        }
 
         sLog.outDetail("Connected to MySQL database at %s", m_connectionInfo.host.c_str());
-        if (!mysql_autocommit(m_Mysql, 1))
-            sLog.outSQLDriver("AUTOCOMMIT SUCCESSFULLY SET TO 1");
-        else
-            sLog.outSQLDriver("AUTOCOMMIT NOT SET TO 1");
+        mysql_autocommit(m_Mysql, 1);
 
         // set connection properties to UTF8 to properly handle locales for different
         // server configs - core sends data in UTF8, so MySQL must expect UTF8 too
         Execute("SET NAMES `utf8`");
         Execute("SET CHARACTER SET `utf8`");
-
-    #if MYSQL_VERSION_ID >= 50003
-        my_bool my_true = (my_bool)1;
-        if (mysql_options(m_Mysql, MYSQL_OPT_RECONNECT, &my_true))
-            sLog.outSQLDriver("Failed to turn on MYSQL_OPT_RECONNECT.");
-        else
-            sLog.outSQLDriver("Successfully turned on MYSQL_OPT_RECONNECT.");
-    #else
-        #warning "Your mySQL client lib version does not support reconnecting after a timeout.\nIf this causes you any trouble we advice you to upgrade your mySQL client libs to at least mySQL 5.0.13 to resolve this problem."
-    #endif
         return true;
     }
     else
@@ -163,8 +155,14 @@ bool MySQLConnection::Execute(const char* sql)
 
         if (mysql_query(m_Mysql, sql))
         {
+            uint32 lErrno = mysql_errno(m_Mysql);
+
             sLog.outSQLDriver("SQL: %s", sql);
-            sLog.outSQLDriver("SQL ERROR: %s", mysql_error(m_Mysql));
+            sLog.outSQLDriver("ERROR: [%u] %s", lErrno, mysql_error(m_Mysql));
+
+            if (_HandleMySQLErrno(lErrno))  // If it returns true, an error was handled succesfuly (ie reconnection)
+                return Execute(sql);       // Try again
+
             return false;
         }
         else if (sLog.GetSQLDriverQueryLogging())
@@ -199,16 +197,26 @@ bool MySQLConnection::Execute(PreparedStatement* stmt)
 
         if (mysql_stmt_bind_param(msql_STMT, msql_BIND))
         {
-            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error binding params:  %s",
-                index, m_connectionInfo.database.c_str(), mysql_stmt_error(msql_STMT));
+            uint32 lErrno = mysql_errno(m_Mysql);
+            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error binding params:  [%u] %s",
+                index, m_connectionInfo.database.c_str(), lErrno, mysql_stmt_error(msql_STMT));
+            
+            if (_HandleMySQLErrno(lErrno))  // If it returns true, an error was handled succesfuly (ie reconnection)
+                return Execute(stmt);       // Try again
+
             m_mStmt->ClearParameters();
             return false;
         }
 
         if (mysql_stmt_execute(msql_STMT))
         {
-            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error executing:  %s",
-                index, m_connectionInfo.database.c_str(), mysql_stmt_error(msql_STMT));
+            uint32 lErrno = mysql_errno(m_Mysql);
+            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error executing:  [%u] %s",
+                index, m_connectionInfo.database.c_str(), lErrno, mysql_stmt_error(msql_STMT));
+            
+            if (_HandleMySQLErrno(lErrno))  // If it returns true, an error was handled succesfuly (ie reconnection)
+                return Execute(stmt);       // Try again
+
             m_mStmt->ClearParameters();
             return false;
         }
@@ -245,16 +253,26 @@ bool MySQLConnection::_Query(PreparedStatement* stmt, MYSQL_RES **pResult, uint6
 
         if (mysql_stmt_bind_param(msql_STMT, msql_BIND))
         {
-            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error binding params:  %s",
-                index, m_connectionInfo.database.c_str(), mysql_stmt_error(msql_STMT));
+            uint32 lErrno = mysql_errno(m_Mysql);
+            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error binding params:  [%u] %s",
+                index, m_connectionInfo.database.c_str(), lErrno, mysql_stmt_error(msql_STMT));
+            
+            if (_HandleMySQLErrno(lErrno))  // If it returns true, an error was handled succesfuly (ie reconnection)
+                return _Query(stmt, pResult, pRowCount, pFieldCount);   // Try again
+
             m_mStmt->ClearParameters();
             return false;
         }
 
         if (mysql_stmt_execute(msql_STMT))
         {
-            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error executing:  %s",
-                index, m_connectionInfo.database.c_str(), mysql_stmt_error(msql_STMT));
+            uint32 lErrno = mysql_errno(m_Mysql);
+            sLog.outSQLDriver("[ERROR]: PreparedStatement (id: %u, database: `%s`) error executing:  [%u] %s",
+                index, m_connectionInfo.database.c_str(), lErrno, mysql_stmt_error(msql_STMT));
+            
+            if (_HandleMySQLErrno(lErrno))  // If it returns true, an error was handled succesfuly (ie reconnection)
+                return _Query(stmt, pResult, pRowCount, pFieldCount);    // Try again
+
             m_mStmt->ClearParameters();
             return false;
         }
@@ -302,8 +320,13 @@ bool MySQLConnection::_Query(const char *sql, MYSQL_RES **pResult, MYSQL_FIELD *
 
         if (mysql_query(m_Mysql, sql))
         {
+            uint32 lErrno = mysql_errno(m_Mysql);
             sLog.outSQLDriver("SQL: %s", sql);
-            sLog.outSQLDriver("query ERROR: %s", mysql_error(m_Mysql));
+            sLog.outSQLDriver("ERROR: [%u] %s", lErrno, mysql_error(m_Mysql));
+
+            if (_HandleMySQLErrno(lErrno))      // If it returns true, an error was handled succesfuly (ie reconnection)
+                return _Query(sql, pResult, pFields, pRowCount, pFieldCount);    // We try again 
+
             return false;
         }
         else if (sLog.GetSQLDriverQueryLogging())
@@ -326,6 +349,7 @@ bool MySQLConnection::_Query(const char *sql, MYSQL_RES **pResult, MYSQL_FIELD *
     }
 
     *pFields = mysql_fetch_fields(*pResult);
+
     return true;
 }
 
@@ -357,6 +381,10 @@ MySQLPreparedStatement* MySQLConnection::GetPreparedStatement(uint32 index)
 
 void MySQLConnection::PrepareStatement(uint32 index, const char* sql, bool async)
 {
+    // For reconnection case
+    if (m_reconnecting)
+        delete m_stmts[index];
+
     // Check if specified query should be prepared on this connection
     // ie. don't prepare async statements on synchronous connections
     // to save memory that will not be used.
@@ -401,3 +429,40 @@ PreparedResultSet* MySQLConnection::Query(PreparedStatement* stmt)
     }
     return new PreparedResultSet(stmt->m_stmt->GetSTMT(), result, rowCount, fieldCount);
 }
+
+bool MySQLConnection::_HandleMySQLErrno(uint32 errNo)
+{
+    sLog.outDebug("%s", __FUNCTION__);
+
+    switch (errNo)
+    {
+        case 2006:  // "MySQL server has gone away"
+        case 2013:  // "Lost connection to MySQL server during query"
+        case 2048:  // "Invalid connection handle"
+        case 2055:  // "Lost connection to MySQL server at '%s', system error: %d"
+        {
+            m_reconnecting = true;
+            uint64 oldThreadId = mysql_thread_id(GetHandle());
+            mysql_close(GetHandle());
+            if (this->Open())                           // Don't remove 'this' pointer unless you want to skip loading all prepared statements....
+            {
+                sLog.outSQLDriver("Connection to the MySQL server is active.");
+                if (oldThreadId != mysql_thread_id(GetHandle()))
+                    sLog.outSQLDriver("Succesfuly reconnected to %s @%s:%s (%s).", 
+                        m_connectionInfo.database.c_str(), m_connectionInfo.host.c_str(), m_connectionInfo.port_or_socket.c_str(),
+                            (m_connectionFlags & CONNECTION_ASYNC) ? "asynchronous" : "synchronous");
+
+                m_reconnecting = false;
+                return true;
+            }
+
+            uint32 lErrno = mysql_errno(GetHandle());   // It's possible this attempted reconnect throws 2006 at us. To prevent crazy recursive calls, sleep here.
+            ACE_OS::sleep(3);                           // Sleep 3 seconds
+            return _HandleMySQLErrno(lErrno);           // Call self (recursive)
+        }
+
+        default:
+            sLog.outSQLDriver("Unhandled MySQL errno %u. Unexpected behaviour possible.", errNo);
+            return false;
+    }
+}
\ No newline at end of file