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/MySQLAssetData.cs | 355 ++++++++++++++++------------------- 1 file changed, 158 insertions(+), 197 deletions(-) (limited to 'OpenSim/Data/MySQL/MySQLAssetData.cs') diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs index 6a4ccd7..666c22f 100644 --- a/OpenSim/Data/MySQL/MySQLAssetData.cs +++ b/OpenSim/Data/MySQL/MySQLAssetData.cs @@ -43,10 +43,13 @@ namespace OpenSim.Data.MySQL { private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); - private MySQLManager _dbConnection; + private string m_connectionString; + private object m_dbLock = new object(); #region IPlugin Members + public override string Version { get { return "1.0.0.0"; } } + /// /// Initialises Asset interface /// @@ -58,63 +61,29 @@ namespace OpenSim.Data.MySQL /// /// /// connect string - override public void Initialise(string connect) + public override void Initialise(string connect) { - // TODO: This will let you pass in the connect string in - // the config, though someone will need to write that. - if (connect == String.Empty) - { - // This is old seperate config file - m_log.Warn("no connect string, using old mysql_connection.ini instead"); - Initialise(); - } - else - { - _dbConnection = new MySQLManager(connect); - } + m_connectionString = connect; // This actually does the roll forward assembly stuff Assembly assem = GetType().Assembly; - Migration m = new Migration(_dbConnection.Connection, assem, "AssetStore"); - m.Update(); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + Migration m = new Migration(dbcon, assem, "AssetStore"); + m.Update(); + } } - /// - /// Initialises Asset interface - /// - /// - /// Loads and initialises the MySQL storage plugin - /// uses the obsolete mysql_connection.ini - /// - /// - /// - /// DEPRECATED and shouldn't be used public override void Initialise() { - IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); - string hostname = GridDataMySqlFile.ParseFileReadValue("hostname"); - string database = GridDataMySqlFile.ParseFileReadValue("database"); - string username = GridDataMySqlFile.ParseFileReadValue("username"); - string password = GridDataMySqlFile.ParseFileReadValue("password"); - string pooling = GridDataMySqlFile.ParseFileReadValue("pooling"); - string port = GridDataMySqlFile.ParseFileReadValue("port"); - - _dbConnection = new MySQLManager(hostname, database, username, password, pooling, port); - + throw new NotImplementedException(); } public override void Dispose() { } /// - /// Database provider version - /// - override public string Version - { - get { return _dbConnection.getVersion(); } - } - - /// /// The name of this DB provider /// override public string Name @@ -135,46 +104,43 @@ namespace OpenSim.Data.MySQL override public AssetBase GetAsset(UUID assetID) { AssetBase asset = null; - lock (_dbConnection) + lock (m_dbLock) { - _dbConnection.CheckConnection(); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); - MySqlCommand cmd = - new MySqlCommand( + using (MySqlCommand cmd = new MySqlCommand( "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id", - _dbConnection.Connection); - cmd.Parameters.AddWithValue("?id", assetID.ToString()); - - try - { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + dbcon)) { - if (dbReader.Read()) - { - asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]); - asset.Data = (byte[]) dbReader["data"]; - asset.Description = (string) dbReader["description"]; + cmd.Parameters.AddWithValue("?id", assetID.ToString()); - string local = dbReader["local"].ToString(); - if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) - asset.Local = true; - else - asset.Local = false; - - asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); + try + { + using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if (dbReader.Read()) + { + asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]); + asset.Data = (byte[])dbReader["data"]; + asset.Description = (string)dbReader["description"]; + + string local = dbReader["local"].ToString(); + if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) + asset.Local = true; + else + asset.Local = false; + + asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); + } + } + } + catch (Exception e) + { + m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message); } - dbReader.Close(); - cmd.Dispose(); } - if (asset != null) - UpdateAccessTime(asset); - } - catch (Exception e) - { - m_log.ErrorFormat( - "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString() - + Environment.NewLine + "Reconnecting", assetID); - _dbConnection.Reconnect(); } } return asset; @@ -187,55 +153,57 @@ namespace OpenSim.Data.MySQL /// On failure : Throw an exception and attempt to reconnect to database override public void StoreAsset(AssetBase asset) { - lock (_dbConnection) + lock (m_dbLock) { - _dbConnection.CheckConnection(); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); - MySqlCommand cmd = - new MySqlCommand( - "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, data)" + - "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?data)", - _dbConnection.Connection); + MySqlCommand cmd = + new MySqlCommand( + "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, data)" + + "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?data)", + dbcon); - string assetName = asset.Name; - if (asset.Name.Length > 64) - { - assetName = asset.Name.Substring(0, 64); - m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add"); - } - - string assetDescription = asset.Description; - if (asset.Description.Length > 64) - { - assetDescription = asset.Description.Substring(0, 64); - m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); - } - - // need to ensure we dispose - try - { - using (cmd) + string assetName = asset.Name; + if (asset.Name.Length > 64) { - // create unix epoch time - int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); - cmd.Parameters.AddWithValue("?id", asset.ID); - cmd.Parameters.AddWithValue("?name", assetName); - cmd.Parameters.AddWithValue("?description", assetDescription); - cmd.Parameters.AddWithValue("?assetType", asset.Type); - cmd.Parameters.AddWithValue("?local", asset.Local); - cmd.Parameters.AddWithValue("?temporary", asset.Temporary); - cmd.Parameters.AddWithValue("?create_time", now); - cmd.Parameters.AddWithValue("?access_time", now); - cmd.Parameters.AddWithValue("?data", asset.Data); - cmd.ExecuteNonQuery(); - cmd.Dispose(); + assetName = asset.Name.Substring(0, 64); + m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add"); + } + + string assetDescription = asset.Description; + if (asset.Description.Length > 64) + { + assetDescription = asset.Description.Substring(0, 64); + m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add"); + } + + // need to ensure we dispose + try + { + using (cmd) + { + // create unix epoch time + int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); + cmd.Parameters.AddWithValue("?id", asset.ID); + cmd.Parameters.AddWithValue("?name", assetName); + cmd.Parameters.AddWithValue("?description", assetDescription); + cmd.Parameters.AddWithValue("?assetType", asset.Type); + cmd.Parameters.AddWithValue("?local", asset.Local); + cmd.Parameters.AddWithValue("?temporary", asset.Temporary); + cmd.Parameters.AddWithValue("?create_time", now); + cmd.Parameters.AddWithValue("?access_time", now); + cmd.Parameters.AddWithValue("?data", asset.Data); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + } + catch (Exception e) + { + m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}", + asset.FullID, asset.Name, e.Message); } - } - catch (Exception e) - { - m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Attempting reconnect. Error: {2}", - asset.FullID, asset.Name, e.Message); - _dbConnection.Reconnect(); } } } @@ -245,35 +213,36 @@ namespace OpenSim.Data.MySQL // Writing to the database every time Get() is called on an asset is killing us. Seriously. -jph return; - lock (_dbConnection) + lock (m_dbLock) { - _dbConnection.CheckConnection(); - - MySqlCommand cmd = - new MySqlCommand("update assets set access_time=?access_time where id=?id", - _dbConnection.Connection); - - // need to ensure we dispose - try + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (cmd) + dbcon.Open(); + MySqlCommand cmd = + new MySqlCommand("update assets set access_time=?access_time where id=?id", + dbcon); + + // need to ensure we dispose + try + { + using (cmd) + { + // create unix epoch time + int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); + cmd.Parameters.AddWithValue("?id", asset.ID); + cmd.Parameters.AddWithValue("?access_time", now); + cmd.ExecuteNonQuery(); + cmd.Dispose(); + } + } + catch (Exception e) { - // create unix epoch time - int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); - cmd.Parameters.AddWithValue("?id", asset.ID); - cmd.Parameters.AddWithValue("?access_time", now); - cmd.ExecuteNonQuery(); - cmd.Dispose(); + m_log.ErrorFormat( + "[ASSETS DB]: " + + "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() + + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); } } - catch (Exception e) - { - m_log.ErrorFormat( - "[ASSETS DB]: " + - "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString() - + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name); - _dbConnection.Reconnect(); - } } } @@ -287,37 +256,30 @@ namespace OpenSim.Data.MySQL { bool assetExists = false; - lock (_dbConnection) + lock (m_dbLock) { - _dbConnection.CheckConnection(); - - MySqlCommand cmd = - new MySqlCommand( - "SELECT id FROM assets WHERE id=?id", - _dbConnection.Connection); - - cmd.Parameters.AddWithValue("?id", uuid.ToString()); - - try + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + dbcon.Open(); + using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon)) { - if (dbReader.Read()) + cmd.Parameters.AddWithValue("?id", uuid.ToString()); + + try { - assetExists = true; + using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) + { + if (dbReader.Read()) + assetExists = true; + } + } + catch (Exception e) + { + m_log.ErrorFormat( + "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid); } - - dbReader.Close(); - cmd.Dispose(); } } - catch (Exception e) - { - m_log.ErrorFormat( - "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString() - + Environment.NewLine + "Attempting reconnection", uuid); - _dbConnection.Reconnect(); - } } return assetExists; @@ -335,38 +297,39 @@ namespace OpenSim.Data.MySQL { List retList = new List(count); - lock (_dbConnection) + lock (m_dbLock) { - _dbConnection.CheckConnection(); - - MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id FROM assets LIMIT ?start, ?count", _dbConnection.Connection); - cmd.Parameters.AddWithValue("?start", start); - cmd.Parameters.AddWithValue("?count", count); - - try + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - using (MySqlDataReader dbReader = cmd.ExecuteReader()) + dbcon.Open(); + MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id FROM assets LIMIT ?start, ?count", dbcon); + cmd.Parameters.AddWithValue("?start", start); + cmd.Parameters.AddWithValue("?count", count); + + try { - while (dbReader.Read()) + using (MySqlDataReader dbReader = cmd.ExecuteReader()) { - AssetMetadata metadata = new AssetMetadata(); - metadata.Name = (string) dbReader["name"]; - metadata.Description = (string) dbReader["description"]; - metadata.Type = (sbyte) dbReader["assetType"]; - metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. - metadata.FullID = new UUID((string) dbReader["id"]); - - // Current SHA1s are not stored/computed. - metadata.SHA1 = new byte[] {}; - - retList.Add(metadata); + while (dbReader.Read()) + { + AssetMetadata metadata = new AssetMetadata(); + metadata.Name = (string)dbReader["name"]; + metadata.Description = (string)dbReader["description"]; + metadata.Type = (sbyte)dbReader["assetType"]; + metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. + metadata.FullID = new UUID((string)dbReader["id"]); + + // Current SHA1s are not stored/computed. + metadata.SHA1 = new byte[] { }; + + retList.Add(metadata); + } } } - } - catch (Exception e) - { - m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString() + Environment.NewLine + "Attempting reconnection"); - _dbConnection.Reconnect(); + catch (Exception e) + { + m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString()); + } } } @@ -374,7 +337,5 @@ namespace OpenSim.Data.MySQL } #endregion - - } } -- cgit v1.1