From e1b5c612472b9d1acf47383c0bf75b555daff2e6 Mon Sep 17 00:00:00 2001 From: Master ScienceSim Date: Thu, 4 Feb 2010 13:19:30 -0800 Subject: Updated MySQL connection management to use the MySQL connection pooling. This should accommodate various timeout problems that exist with the current connection pool code in a more general and standard way. --- OpenSim/Data/MySQL/MySQLManager.cs | 428 ++++++++++++++++++------------------- 1 file changed, 211 insertions(+), 217 deletions(-) (limited to 'OpenSim/Data/MySQL/MySQLManager.cs') diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs index 243394e..ace2027 100644 --- a/OpenSim/Data/MySQL/MySQLManager.cs +++ b/OpenSim/Data/MySQL/MySQLManager.cs @@ -46,15 +46,12 @@ namespace OpenSim.Data.MySQL private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); /// - /// The database connection object - /// - private MySqlConnection dbcon; - - /// /// Connection string for ADO.net /// private string connectionString; + private object m_dbLock = new object(); + private const string m_waitTimeoutSelect = "select @@wait_timeout"; /// @@ -109,11 +106,11 @@ namespace OpenSim.Data.MySQL try { connectionString = connect; - dbcon = new MySqlConnection(connectionString); + //dbcon = new MySqlConnection(connectionString); try { - dbcon.Open(); + //dbcon.Open(); } catch(Exception e) { @@ -134,18 +131,21 @@ namespace OpenSim.Data.MySQL /// protected void GetWaitTimeout() { - MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon); - - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) { - if (dbReader.Read()) + dbcon.Open(); + + using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon)) { - m_waitTimeout - = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway; + 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 = DateTime.Now.Ticks; @@ -154,66 +154,9 @@ namespace OpenSim.Data.MySQL "[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() + public string ConnectionString { - //m_log.Debug("[REGION DB]: Checking connection"); - - long timeNow = 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 - public MySqlConnection Connection - { - get { return dbcon; } - } - - /// - /// Shuts down the database connection - /// - public void Close() - { - dbcon.Close(); - dbcon = null; - } - - /// - /// Reconnects to the database - /// - public void Reconnect() - { - m_log.Info("[REGION DB] Reconnecting database"); - - lock (dbcon) - { - try - { - // Close the DB connection - dbcon.Close(); - // Try reopen it - dbcon = new MySqlConnection(connectionString); - dbcon.Open(); - } - catch (Exception e) - { - m_log.Error("Unable to reconnect to database " + e.ToString()); - } - } + get { return connectionString; } } /// @@ -264,9 +207,13 @@ namespace OpenSim.Data.MySQL /// name of embedded resource public void ExecuteResourceSql(string name) { - CheckConnection(); - MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); - cmd.ExecuteNonQuery(); + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); + + MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); + cmd.ExecuteNonQuery(); + } } /// @@ -275,22 +222,29 @@ namespace OpenSim.Data.MySQL /// sql string to execute public void ExecuteSql(string sql) { - CheckConnection(); - MySqlCommand cmd = new MySqlCommand(sql, dbcon); - cmd.ExecuteNonQuery(); + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); + + MySqlCommand cmd = new MySqlCommand(sql, dbcon); + cmd.ExecuteNonQuery(); + } } public void ExecuteParameterizedSql(string sql, Dictionary parameters) { - CheckConnection(); - - MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand(); - cmd.CommandText = sql; - foreach (KeyValuePair param in parameters) + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) { - cmd.Parameters.AddWithValue(param.Key, param.Value); + dbcon.Open(); + + MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand(); + cmd.CommandText = sql; + foreach (KeyValuePair param in parameters) + { + cmd.Parameters.AddWithValue(param.Key, param.Value); + } + cmd.ExecuteNonQuery(); } - cmd.ExecuteNonQuery(); } /// @@ -299,35 +253,37 @@ namespace OpenSim.Data.MySQL /// public void GetTableVersion(Dictionary tableList) { - lock (dbcon) + lock (m_dbLock) { - 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()) + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) { - while (tables.Read()) + dbcon.Open(); + + using (MySqlCommand tablesCmd = new MySqlCommand( + "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", dbcon)) { - try + tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); + + using (MySqlDataReader tables = tablesCmd.ExecuteReader()) { - string tableName = (string) tables["TABLE_NAME"]; - string comment = (string) tables["TABLE_COMMENT"]; - if (tableList.ContainsKey(tableName)) + while (tables.Read()) { - tableList[tableName] = comment; + try + { + string tableName = (string)tables["TABLE_NAME"]; + string comment = (string)tables["TABLE_COMMENT"]; + if (tableList.ContainsKey(tableName)) + { + tableList[tableName] = comment; + } + } + catch (Exception e) + { + m_log.Error(e.Message, e); + } } } - catch (Exception e) - { - m_log.Error(e.ToString()); - } } - tables.Close(); } } } @@ -337,28 +293,27 @@ namespace OpenSim.Data.MySQL /// /// Runs a query with protection against SQL Injection by using parameterised input. /// + /// Database connection /// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y /// The parameters - index so that @y is indexed as 'y' /// A MySQL DB Command - public IDbCommand Query(string sql, Dictionary parameters) + public IDbCommand Query(MySqlConnection dbcon, string sql, Dictionary parameters) { try { - CheckConnection(); // Not sure if this one is necessary - - MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); + MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand(); dbcommand.CommandText = sql; foreach (KeyValuePair param in parameters) { dbcommand.Parameters.AddWithValue(param.Key, param.Value); } - return (IDbCommand) dbcommand; + return (IDbCommand)dbcommand; } catch (Exception e) { // Return null if it fails. - m_log.Error("Failed during Query generation: " + e.ToString()); + m_log.Error("Failed during Query generation: " + e.Message, e); return null; } } @@ -694,8 +649,6 @@ namespace OpenSim.Data.MySQL ret.Add(attachpoint, item); } - r.Close(); - return ret; } @@ -727,12 +680,17 @@ namespace OpenSim.Data.MySQL try { - IDbCommand result = Query(sql, parameters); + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); + + IDbCommand result = Query(dbcon, sql, parameters); - if (result.ExecuteNonQuery() == 1) - returnval = true; + if (result.ExecuteNonQuery() == 1) + returnval = true; - result.Dispose(); + result.Dispose(); + } } catch (Exception e) { @@ -828,12 +786,17 @@ namespace OpenSim.Data.MySQL try { - IDbCommand result = Query(sql, parameters); + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); - if (result.ExecuteNonQuery() == 1) - returnval = true; + IDbCommand result = Query(dbcon, sql, parameters); - result.Dispose(); + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } } catch (Exception e) { @@ -927,12 +890,17 @@ namespace OpenSim.Data.MySQL bool returnval = false; try { - IDbCommand result = Query(sql, parameters); + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); - if (result.ExecuteNonQuery() == 1) - returnval = true; + IDbCommand result = Query(dbcon, sql, parameters); - result.Dispose(); + if (result.ExecuteNonQuery() == 1) + returnval = true; + + result.Dispose(); + } } catch (Exception e) { @@ -1030,18 +998,23 @@ namespace OpenSim.Data.MySQL try { - IDbCommand result = Query(sql, parameters); - - // int x; - // if ((x = result.ExecuteNonQuery()) > 0) - // { - // returnval = true; - // } - if (result.ExecuteNonQuery() > 0) + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) { - returnval = true; + dbcon.Open(); + + IDbCommand result = Query(dbcon, sql, parameters); + + // int x; + // if ((x = result.ExecuteNonQuery()) > 0) + // { + // returnval = true; + // } + if (result.ExecuteNonQuery() > 0) + { + returnval = true; + } + result.Dispose(); } - result.Dispose(); } catch (Exception e) { @@ -1070,18 +1043,23 @@ namespace OpenSim.Data.MySQL { parameters["?uuid"] = uuid; - IDbCommand result = Query(sql, parameters); - - // int x; - // if ((x = result.ExecuteNonQuery()) > 0) - // { - // returnval = true; - // } - if (result.ExecuteNonQuery() > 0) + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) { - returnval = true; + dbcon.Open(); + + IDbCommand result = Query(dbcon, sql, parameters); + + // int x; + // if ((x = result.ExecuteNonQuery()) > 0) + // { + // returnval = true; + // } + if (result.ExecuteNonQuery() > 0) + { + returnval = true; + } + result.Dispose(); } - result.Dispose(); } catch (Exception e) { @@ -1122,18 +1100,23 @@ namespace OpenSim.Data.MySQL try { - IDbCommand result = Query(sql, parameters); - - // int x; - // if ((x = result.ExecuteNonQuery()) > 0) - // { - // returnval = true; - // } - if (result.ExecuteNonQuery() > 0) + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) { - returnval = true; + dbcon.Open(); + + IDbCommand result = Query(dbcon, sql, parameters); + + // int x; + // if ((x = result.ExecuteNonQuery()) > 0) + // { + // returnval = true; + // } + if (result.ExecuteNonQuery() > 0) + { + returnval = true; + } + result.Dispose(); } - result.Dispose(); } catch (Exception e) { @@ -1167,45 +1150,51 @@ namespace OpenSim.Data.MySQL bool returnval = false; // we want to send in byte data, which means we can't just pass down strings - try { - MySqlCommand cmd = (MySqlCommand) dbcon.CreateCommand(); - cmd.CommandText = sql; - cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString()); - cmd.Parameters.AddWithValue("?serial", appearance.Serial); - cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams); - cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes()); - cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight); - cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString()); - cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString()); - cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString()); - cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString()); - cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString()); - cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString()); - cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString()); - cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString()); - cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString()); - cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString()); - cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString()); - cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString()); - cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString()); - cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString()); - cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString()); - cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString()); - cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString()); - cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString()); - cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString()); - cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString()); - cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString()); - cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString()); - cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString()); - cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString()); - cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString()); - cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString()); - - if (cmd.ExecuteNonQuery() > 0) - returnval = true; - - cmd.Dispose(); + try + { + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); + + using (MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand()) + { + cmd.CommandText = sql; + cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString()); + cmd.Parameters.AddWithValue("?serial", appearance.Serial); + cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams); + cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes()); + cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight); + cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString()); + cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString()); + cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString()); + cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString()); + cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString()); + cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString()); + cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString()); + cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString()); + cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString()); + cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString()); + cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString()); + cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString()); + cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString()); + cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString()); + cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString()); + cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString()); + cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString()); + cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString()); + cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString()); + cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString()); + cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString()); + cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString()); + cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString()); + cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString()); + cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString()); + cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString()); + + if (cmd.ExecuteNonQuery() > 0) + returnval = true; + } + } } catch (Exception e) { @@ -1221,33 +1210,38 @@ namespace OpenSim.Data.MySQL { string sql = "delete from avatarattachments where UUID = ?uuid"; - MySqlCommand cmd = (MySqlCommand) dbcon.CreateCommand(); - cmd.CommandText = sql; - cmd.Parameters.AddWithValue("?uuid", agentID.ToString()); + using (MySqlConnection dbcon = new MySqlConnection(connectionString)) + { + dbcon.Open(); + + MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand(); + cmd.CommandText = sql; + cmd.Parameters.AddWithValue("?uuid", agentID.ToString()); - cmd.ExecuteNonQuery(); + cmd.ExecuteNonQuery(); - if (data == null) - return; + if (data == null) + return; - sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)"; + sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)"; - cmd = (MySqlCommand) dbcon.CreateCommand(); - cmd.CommandText = sql; + cmd = (MySqlCommand)dbcon.CreateCommand(); + cmd.CommandText = sql; - foreach (DictionaryEntry e in data) - { - int attachpoint = Convert.ToInt32(e.Key); + foreach (DictionaryEntry e in data) + { + int attachpoint = Convert.ToInt32(e.Key); - Hashtable item = (Hashtable)e.Value; + Hashtable item = (Hashtable)e.Value; - cmd.Parameters.Clear(); - cmd.Parameters.AddWithValue("?uuid", agentID.ToString()); - cmd.Parameters.AddWithValue("?attachpoint", attachpoint); - cmd.Parameters.AddWithValue("?item", item["item"]); - cmd.Parameters.AddWithValue("?asset", item["asset"]); + cmd.Parameters.Clear(); + cmd.Parameters.AddWithValue("?uuid", agentID.ToString()); + cmd.Parameters.AddWithValue("?attachpoint", attachpoint); + cmd.Parameters.AddWithValue("?item", item["item"]); + cmd.Parameters.AddWithValue("?asset", item["asset"]); - cmd.ExecuteNonQuery(); + cmd.ExecuteNonQuery(); + } } } } -- cgit v1.1