From 7fea52be3542c1eea884f92ea14285560923e57d Mon Sep 17 00:00:00 2001 From: Charles Krinke Date: Thu, 3 Jul 2008 22:30:16 +0000 Subject: Mantis#1661. Thank you kindly, CMickeyb for a patch that: patch attached to check for timeouts on mysql connections *before* operations occur that are likely to timeout. if timeout occurs or the connections is down, it is reconnected before the operation fails. --- OpenSim/Data/MySQL/MySQLAssetData.cs | 6 ++ OpenSim/Data/MySQL/MySQLInventoryData.cs | 24 +++++++ OpenSim/Data/MySQL/MySQLManager.cs | 113 +++++++++++++++++++++---------- 3 files changed, 107 insertions(+), 36 deletions(-) (limited to 'OpenSim/Data') diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs index 21d730d..3557243 100644 --- a/OpenSim/Data/MySQL/MySQLAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLAssetData.cs @@ -162,6 +162,8 @@ namespace OpenSim.Data.MySQL AssetBase asset = null; lock (_dbConnection) { + _dbConnection.CheckConnection(); + MySqlCommand cmd = new MySqlCommand( "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id", @@ -213,6 +215,8 @@ namespace OpenSim.Data.MySQL return; } + _dbConnection.CheckConnection(); + MySqlCommand cmd = new MySqlCommand( "REPLACE INTO assets(id, name, description, assetType, local, temporary, data)" + @@ -266,6 +270,8 @@ namespace OpenSim.Data.MySQL lock (_dbConnection) { + _dbConnection.CheckConnection(); + MySqlCommand cmd = new MySqlCommand( "SELECT id FROM assets WHERE id=?id", diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs index 911958c..5bde40a 100644 --- a/OpenSim/Data/MySQL/MySQLInventoryData.cs +++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs @@ -219,6 +219,8 @@ namespace OpenSim.Data.MySQL { List items = new List(); + database.CheckConnection(); + MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid", database.Connection); @@ -253,6 +255,8 @@ namespace OpenSim.Data.MySQL { lock (database) { + database.CheckConnection(); + MySqlCommand result = new MySqlCommand( "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", @@ -292,6 +296,8 @@ namespace OpenSim.Data.MySQL { lock (database) { + database.CheckConnection(); + MySqlCommand result = new MySqlCommand( "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", @@ -344,6 +350,8 @@ namespace OpenSim.Data.MySQL { lock (database) { + database.CheckConnection(); + MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid", database.Connection); @@ -421,6 +429,8 @@ namespace OpenSim.Data.MySQL { lock (database) { + database.CheckConnection(); + MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection); result.Parameters.AddWithValue("?uuid", itemID.ToString()); @@ -482,6 +492,8 @@ namespace OpenSim.Data.MySQL { lock (database) { + database.CheckConnection(); + MySqlCommand result = new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection); result.Parameters.AddWithValue("?uuid", folderID.ToString()); @@ -522,6 +534,8 @@ namespace OpenSim.Data.MySQL try { + database.CheckConnection(); + MySqlCommand result = new MySqlCommand(sql, database.Connection); result.Parameters.AddWithValue("?inventoryID", item.ID.ToString()); result.Parameters.AddWithValue("?assetID", item.AssetID.ToString()); @@ -574,6 +588,8 @@ namespace OpenSim.Data.MySQL { try { + database.CheckConnection(); + MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection); cmd.Parameters.AddWithValue("?uuid", itemID.ToString()); @@ -600,6 +616,8 @@ namespace OpenSim.Data.MySQL "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES "; sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)"; + database.CheckConnection(); + MySqlCommand cmd = new MySqlCommand(sql, database.Connection); cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString()); @@ -640,6 +658,8 @@ namespace OpenSim.Data.MySQL string sql = "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID"; + database.CheckConnection(); + MySqlCommand cmd = new MySqlCommand(sql, database.Connection); cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString()); @@ -695,6 +715,8 @@ namespace OpenSim.Data.MySQL { try { + database.CheckConnection(); + MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection); cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); @@ -719,6 +741,8 @@ namespace OpenSim.Data.MySQL { try { + database.CheckConnection(); + MySqlCommand cmd = new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection); cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs index 61fd039..cf4bce3 100644 --- a/OpenSim/Data/MySQL/MySQLManager.cs +++ b/OpenSim/Data/MySQL/MySQLManager.cs @@ -54,6 +54,24 @@ namespace OpenSim.Data.MySQL /// private string connectionString; + private const string m_waitTimeoutSelect = "select @@wait_timeout"; + + /// + /// Wait timeout for our connection in ticks. + /// + private long m_waitTimeout; + + /// + /// Make our storage of the timeout this amount smaller than it actually is, to give us a margin on long + /// running database operations. + /// + private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond; + + /// + /// Holds the last tick time that the connection was used. + /// + private long m_lastConnectionUse; + /// /// Initialises and creates a new MySQL connection and maintains it. /// @@ -102,6 +120,7 @@ namespace OpenSim.Data.MySQL } m_log.Info("[MYSQL]: Connection established"); + GetWaitTimeout(); } catch (Exception e) { @@ -110,6 +129,51 @@ namespace OpenSim.Data.MySQL } /// + /// Get the wait_timeout value for our connection + /// + protected void GetWaitTimeout() + { + MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon); + + using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if (dbReader.Read()) + { + m_waitTimeout + = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway; + } + + dbReader.Close(); + cmd.Dispose(); + } + + m_lastConnectionUse = System.DateTime.Now.Ticks; + + m_log.DebugFormat( + "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond); + } + + /// + /// Should be called before any db operation. This checks to see if the connection has not timed out + /// + public void CheckConnection() + { + //m_log.Debug("[REGION DB]: Checking connection"); + + long timeNow = System.DateTime.Now.Ticks; + if (timeNow - m_lastConnectionUse > m_waitTimeout || dbcon.State != ConnectionState.Open) + { + m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting"); + Reconnect(); + } + + // Strictly, we should set this after the actual db operation. But it's more convenient to set here rather + // than require the code to call another method - the timeout leeway should be large enough to cover the + // inaccuracy. + m_lastConnectionUse = timeNow; + } + + /// /// Get the connection being used /// /// MySqlConnection Object @@ -132,6 +196,8 @@ namespace OpenSim.Data.MySQL /// public void Reconnect() { + m_log.Info("[REGION DB] Reconnecting database"); + lock (dbcon) { try @@ -197,6 +263,7 @@ namespace OpenSim.Data.MySQL /// name of embedded resource public void ExecuteResourceSql(string name) { + CheckConnection(); MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); cmd.ExecuteNonQuery(); } @@ -207,6 +274,7 @@ namespace OpenSim.Data.MySQL /// sql string to execute public void ExecuteSql(string sql) { + CheckConnection(); MySqlCommand cmd = new MySqlCommand(sql, dbcon); cmd.ExecuteNonQuery(); } @@ -219,11 +287,14 @@ namespace OpenSim.Data.MySQL { lock (dbcon) { + CheckConnection(); + MySqlCommand tablesCmd = new MySqlCommand( "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", dbcon); tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); + using (MySqlDataReader tables = tablesCmd.ExecuteReader()) { while (tables.Read()) @@ -259,6 +330,8 @@ namespace OpenSim.Data.MySQL { try { + CheckConnection(); // Not sure if this one is necessary + MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); dbcommand.CommandText = sql; foreach (KeyValuePair param in parameters) @@ -268,43 +341,11 @@ namespace OpenSim.Data.MySQL return (IDbCommand) dbcommand; } - catch + catch (Exception e) { - lock (dbcon) - { - // Close the DB connection - dbcon.Close(); - - // Try to reopen it - try - { - dbcon = new MySqlConnection(connectionString); - dbcon.Open(); - } - catch (Exception e) - { - m_log.Error("Unable to reconnect to database " + e); - } - - // Run the query again - try - { - MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); - dbcommand.CommandText = sql; - foreach (KeyValuePair param in parameters) - { - dbcommand.Parameters.AddWithValue(param.Key, param.Value); - } - - return (IDbCommand) dbcommand; - } - catch (Exception e) - { - // Return null if it fails. - m_log.Error("Failed during Query generation: " + e.ToString()); - return null; - } - } + // Return null if it fails. + m_log.Error("Failed during Query generation: " + e.ToString()); + return null; } } -- cgit v1.1