diff options
5 files changed, 114 insertions, 38 deletions
diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp index 5e5ab97e075..af7d7201d4f 100755 --- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp +++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp @@ -22,7 +22,9 @@ bool CharacterDatabaseConnection::Open() if (!MySQLConnection::Open()) return false; - m_stmts.resize(MAX_CHARACTERDATABASE_STATEMENTS); + if (!m_reconnecting) + m_stmts.resize(MAX_CHARACTERDATABASE_STATEMENTS); + /* ################################## LOAD YOUR PREPARED STATEMENTS HERE diff --git a/src/server/shared/Database/Implementation/LoginDatabase.cpp b/src/server/shared/Database/Implementation/LoginDatabase.cpp index 33aa650f9d9..794f156a417 100755 --- a/src/server/shared/Database/Implementation/LoginDatabase.cpp +++ b/src/server/shared/Database/Implementation/LoginDatabase.cpp @@ -22,7 +22,8 @@ bool LoginDatabaseConnection::Open() if (!MySQLConnection::Open()) return false; - m_stmts.resize(MAX_LOGINDATABASE_STATEMENTS); + if (!m_reconnecting) + m_stmts.resize(MAX_LOGINDATABASE_STATEMENTS); /* ################################## diff --git a/src/server/shared/Database/Implementation/WorldDatabase.cpp b/src/server/shared/Database/Implementation/WorldDatabase.cpp index 2b83b210bc5..8be56873278 100755 --- a/src/server/shared/Database/Implementation/WorldDatabase.cpp +++ b/src/server/shared/Database/Implementation/WorldDatabase.cpp @@ -22,7 +22,8 @@ bool WorldDatabaseConnection::Open() if (!MySQLConnection::Open()) return false; - m_stmts.resize(MAX_WORLDDATABASE_STATEMENTS); + if (!m_reconnecting) + m_stmts.resize(MAX_WORLDDATABASE_STATEMENTS); /* ################################## 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 diff --git a/src/server/shared/Database/MySQLConnection.h b/src/server/shared/Database/MySQLConnection.h index 847e3a84633..11e94c7844e 100755 --- a/src/server/shared/Database/MySQLConnection.h +++ b/src/server/shared/Database/MySQLConnection.h @@ -16,6 +16,7 @@ */ #include <ace/Activation_Queue.h> + #include "DatabaseWorkerPool.h" #include "Util.h" @@ -88,11 +89,6 @@ class MySQLConnection void Ping() { mysql_ping(m_Mysql); } protected: - MYSQL* GetHandle() { return m_Mysql; } - MySQLPreparedStatement* GetPreparedStatement(uint32 index); - void PrepareStatement(uint32 index, const char* sql, bool async = false); - std::vector<MySQLPreparedStatement*> m_stmts; //! PreparedStatements storage - bool LockIfReady() { /// Tries to acquire lock. If lock is acquired by another thread @@ -106,6 +102,17 @@ class MySQLConnection m_Mutex.release(); } + MYSQL* GetHandle() { return m_Mysql; } + MySQLPreparedStatement* GetPreparedStatement(uint32 index); + void PrepareStatement(uint32 index, const char* sql, bool async = false); + + protected: + std::vector<MySQLPreparedStatement*> m_stmts; //! PreparedStatements storage + bool m_reconnecting; //! Are we reconnecting? + + private: + bool _HandleMySQLErrno(uint32 errNo); + private: ACE_Activation_Queue* m_queue; //! Queue shared with other asynchroneous connections. DatabaseWorker* m_worker; //! Core worker task. |