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/MySQLRegionData.cs | 257 ++++++++++++++++++---------------- 1 file changed, 133 insertions(+), 124 deletions(-) (limited to 'OpenSim/Data/MySQL/MySQLRegionData.cs') diff --git a/OpenSim/Data/MySQL/MySQLRegionData.cs b/OpenSim/Data/MySQL/MySQLRegionData.cs index b0075e8..a1a08b1 100644 --- a/OpenSim/Data/MySQL/MySQLRegionData.cs +++ b/OpenSim/Data/MySQL/MySQLRegionData.cs @@ -38,16 +38,21 @@ namespace OpenSim.Data.MySQL public class MySqlRegionData : MySqlFramework, IRegionData { private string m_Realm; - private List m_ColumnNames = null; -// private int m_LastExpire = 0; + private List m_ColumnNames; + //private string m_connectionString; public MySqlRegionData(string connectionString, string realm) : base(connectionString) { m_Realm = realm; + m_connectionString = connectionString; - Migration m = new Migration(m_Connection, GetType().Assembly, "GridStore"); - m.Update(); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + Migration m = new Migration(dbcon, GetType().Assembly, "GridStore"); + m.Update(); + } } public List Get(string regionName, UUID scopeID) @@ -56,12 +61,13 @@ namespace OpenSim.Data.MySQL if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; - MySqlCommand cmd = new MySqlCommand(command); - - cmd.Parameters.AddWithValue("?regionName", regionName); - cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + using (MySqlCommand cmd = new MySqlCommand(command)) + { + cmd.Parameters.AddWithValue("?regionName", regionName); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); - return RunCommand(cmd); + return RunCommand(cmd); + } } public RegionData Get(int posX, int posY, UUID scopeID) @@ -70,17 +76,18 @@ namespace OpenSim.Data.MySQL if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; - MySqlCommand cmd = new MySqlCommand(command); - - cmd.Parameters.AddWithValue("?posX", posX.ToString()); - cmd.Parameters.AddWithValue("?posY", posY.ToString()); - cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + using (MySqlCommand cmd = new MySqlCommand(command)) + { + cmd.Parameters.AddWithValue("?posX", posX.ToString()); + cmd.Parameters.AddWithValue("?posY", posY.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); - List ret = RunCommand(cmd); - if (ret.Count == 0) - return null; + List ret = RunCommand(cmd); + if (ret.Count == 0) + return null; - return ret[0]; + return ret[0]; + } } public RegionData Get(UUID regionID, UUID scopeID) @@ -89,16 +96,17 @@ namespace OpenSim.Data.MySQL if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; - MySqlCommand cmd = new MySqlCommand(command); - - cmd.Parameters.AddWithValue("?regionID", regionID.ToString()); - cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + using (MySqlCommand cmd = new MySqlCommand(command)) + { + cmd.Parameters.AddWithValue("?regionID", regionID.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); - List ret = RunCommand(cmd); - if (ret.Count == 0) - return null; + List ret = RunCommand(cmd); + if (ret.Count == 0) + return null; - return ret[0]; + return ret[0]; + } } public List Get(int startX, int startY, int endX, int endY, UUID scopeID) @@ -107,74 +115,79 @@ namespace OpenSim.Data.MySQL if (scopeID != UUID.Zero) command += " and ScopeID = ?scopeID"; - MySqlCommand cmd = new MySqlCommand(command); - - cmd.Parameters.AddWithValue("?startX", startX.ToString()); - cmd.Parameters.AddWithValue("?startY", startY.ToString()); - cmd.Parameters.AddWithValue("?endX", endX.ToString()); - cmd.Parameters.AddWithValue("?endY", endY.ToString()); - cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); + using (MySqlCommand cmd = new MySqlCommand(command)) + { + cmd.Parameters.AddWithValue("?startX", startX.ToString()); + cmd.Parameters.AddWithValue("?startY", startY.ToString()); + cmd.Parameters.AddWithValue("?endX", endX.ToString()); + cmd.Parameters.AddWithValue("?endY", endY.ToString()); + cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString()); - return RunCommand(cmd); + return RunCommand(cmd); + } } public List RunCommand(MySqlCommand cmd) { List retList = new List(); - IDataReader result = ExecuteReader(cmd); - - while (result.Read()) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - RegionData ret = new RegionData(); - ret.Data = new Dictionary(); - - UUID regionID; - UUID.TryParse(result["uuid"].ToString(), out regionID); - ret.RegionID = regionID; - UUID scope; - UUID.TryParse(result["ScopeID"].ToString(), out scope); - ret.ScopeID = scope; - ret.RegionName = result["regionName"].ToString(); - ret.posX = Convert.ToInt32(result["locX"]); - ret.posY = Convert.ToInt32(result["locY"]); - ret.sizeX = Convert.ToInt32(result["sizeX"]); - ret.sizeY = Convert.ToInt32(result["sizeY"]); - - if (m_ColumnNames == null) + dbcon.Open(); + cmd.Connection = dbcon; + + using (IDataReader result = cmd.ExecuteReader()) { - m_ColumnNames = new List(); + while (result.Read()) + { + RegionData ret = new RegionData(); + ret.Data = new Dictionary(); + + UUID regionID; + UUID.TryParse(result["uuid"].ToString(), out regionID); + ret.RegionID = regionID; + UUID scope; + UUID.TryParse(result["ScopeID"].ToString(), out scope); + ret.ScopeID = scope; + ret.RegionName = result["regionName"].ToString(); + ret.posX = Convert.ToInt32(result["locX"]); + ret.posY = Convert.ToInt32(result["locY"]); + ret.sizeX = Convert.ToInt32(result["sizeX"]); + ret.sizeY = Convert.ToInt32(result["sizeY"]); + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + { + if (row["ColumnName"] != null) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + } - DataTable schemaTable = result.GetSchemaTable(); - foreach (DataRow row in schemaTable.Rows) + foreach (string s in m_ColumnNames) { - if (row["ColumnName"] != null) - m_ColumnNames.Add(row["ColumnName"].ToString()); + if (s == "uuid") + continue; + if (s == "ScopeID") + continue; + if (s == "regionName") + continue; + if (s == "locX") + continue; + if (s == "locY") + continue; + + ret.Data[s] = result[s].ToString(); } - } - foreach (string s in m_ColumnNames) - { - if (s == "uuid") - continue; - if (s == "ScopeID") - continue; - if (s == "regionName") - continue; - if (s == "locX") - continue; - if (s == "locY") - continue; - - ret.Data[s] = result[s].ToString(); + retList.Add(ret); + } } - - retList.Add(ret); } - result.Close(); - CloseReaderCommand(cmd); - return retList; } @@ -201,76 +214,72 @@ namespace OpenSim.Data.MySQL string[] fields = new List(data.Data.Keys).ToArray(); - MySqlCommand cmd = new MySqlCommand(); - - string update = "update `"+m_Realm+"` set locX=?posX, locY=?posY, sizeX=?sizeX, sizeY=?sizeY"; - foreach (string field in fields) + using (MySqlCommand cmd = new MySqlCommand()) { - update += ", "; - update += "`" + field + "` = ?"+field; - - cmd.Parameters.AddWithValue("?"+field, data.Data[field]); - } - - update += " where uuid = ?regionID"; + string update = "update `" + m_Realm + "` set locX=?posX, locY=?posY, sizeX=?sizeX, sizeY=?sizeY"; + foreach (string field in fields) + { + update += ", "; + update += "`" + field + "` = ?" + field; - if (data.ScopeID != UUID.Zero) - update += " and ScopeID = ?scopeID"; + cmd.Parameters.AddWithValue("?" + field, data.Data[field]); + } - cmd.CommandText = update; - cmd.Parameters.AddWithValue("?regionID", data.RegionID.ToString()); - cmd.Parameters.AddWithValue("?regionName", data.RegionName); - cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString()); - cmd.Parameters.AddWithValue("?posX", data.posX.ToString()); - cmd.Parameters.AddWithValue("?posY", data.posY.ToString()); - cmd.Parameters.AddWithValue("?sizeX", data.sizeX.ToString()); - cmd.Parameters.AddWithValue("?sizeY", data.sizeY.ToString()); + update += " where uuid = ?regionID"; - if (ExecuteNonQuery(cmd) < 1) - { - string insert = "insert into `" + m_Realm + "` (`uuid`, `ScopeID`, `locX`, `locY`, `sizeX`, `sizeY`, `regionName`, `" + - String.Join("`, `", fields) + - "`) values ( ?regionID, ?scopeID, ?posX, ?posY, ?sizeX, ?sizeY, ?regionName, ?" + String.Join(", ?", fields) + ")"; + if (data.ScopeID != UUID.Zero) + update += " and ScopeID = ?scopeID"; - cmd.CommandText = insert; + cmd.CommandText = update; + cmd.Parameters.AddWithValue("?regionID", data.RegionID.ToString()); + cmd.Parameters.AddWithValue("?regionName", data.RegionName); + cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString()); + cmd.Parameters.AddWithValue("?posX", data.posX.ToString()); + cmd.Parameters.AddWithValue("?posY", data.posY.ToString()); + cmd.Parameters.AddWithValue("?sizeX", data.sizeX.ToString()); + cmd.Parameters.AddWithValue("?sizeY", data.sizeY.ToString()); if (ExecuteNonQuery(cmd) < 1) { - cmd.Dispose(); - return false; + string insert = "insert into `" + m_Realm + "` (`uuid`, `ScopeID`, `locX`, `locY`, `sizeX`, `sizeY`, `regionName`, `" + + String.Join("`, `", fields) + + "`) values ( ?regionID, ?scopeID, ?posX, ?posY, ?sizeX, ?sizeY, ?regionName, ?" + String.Join(", ?", fields) + ")"; + + cmd.CommandText = insert; + + if (ExecuteNonQuery(cmd) < 1) + { + return false; + } } } - cmd.Dispose(); - return true; } public bool SetDataItem(UUID regionID, string item, string value) { - MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + - "` set `" + item + "` = ?" + item + " where uuid = ?UUID"); - - - cmd.Parameters.AddWithValue("?"+item, value); - cmd.Parameters.AddWithValue("?UUID", regionID.ToString()); + using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" + item + "` = ?" + item + " where uuid = ?UUID")) + { + cmd.Parameters.AddWithValue("?" + item, value); + cmd.Parameters.AddWithValue("?UUID", regionID.ToString()); - if (ExecuteNonQuery(cmd) > 0) - return true; + if (ExecuteNonQuery(cmd) > 0) + return true; + } return false; } public bool Delete(UUID regionID) { - MySqlCommand cmd = new MySqlCommand("delete from `" + m_Realm + - "` where uuid = ?UUID"); - - - cmd.Parameters.AddWithValue("?UUID", regionID.ToString()); + using (MySqlCommand cmd = new MySqlCommand("delete from `" + m_Realm + "` where uuid = ?UUID")) + { + cmd.Parameters.AddWithValue("?UUID", regionID.ToString()); - if (ExecuteNonQuery(cmd) > 0) - return true; + if (ExecuteNonQuery(cmd) > 0) + return true; + } return false; } -- cgit v1.1