| Attached Files |  0001-Changed-handling-of-mysql-connections-to-open-and-cl.patch [^] (18,448 bytes) 2015-12-30 22:25 [Show Content] [Hide Content]From d8b94512d737ed145e00f76e5b4fc9c389192e19 Mon Sep 17 00:00:00 2001
From: Orion Pseudo <john@pseudospace.net>
Date: Thu, 31 Dec 2015 00:54:06 -0500
Subject: [PATCH] Changed handling of mysql connections to open and close when
needed (same as MySQLAssetData and MySQLXAssetData). *As opposed to a single
always on connection that could time out when idle.
Changed handling of commands, readers, and connections to ensure proper disposal.
---
OpenSim/Data/MySQL/MySQLFSAssetData.cs | 436 ++++++++++++++++-----------------
1 file changed, 210 insertions(+), 226 deletions(-)
diff --git a/OpenSim/Data/MySQL/MySQLFSAssetData.cs b/OpenSim/Data/MySQL/MySQLFSAssetData.cs
index 19e23b5..5bbeb3a 100644
--- a/OpenSim/Data/MySQL/MySQLFSAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLFSAssetData.cs
@@ -41,10 +41,8 @@ namespace OpenSim.Data.MySQL
{
private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
- protected MySqlConnection m_Connection = null;
protected string m_ConnectionString;
protected string m_Table;
- protected Object m_connLock = new Object();
/// <summary>
/// Number of days that must pass before we update the access time on an asset when it has been fetched
@@ -75,10 +73,15 @@ namespace OpenSim.Data.MySQL
try
{
- OpenDatabase();
+ using(MySqlConnection dbcon = new MySqlConnection(m_ConnectionString))
+ {
+ dbcon.Open();
- Migration m = new Migration(m_Connection, Assembly, "FSAssetStore");
- m.Update();
+ Migration m = new Migration(dbcon, Assembly, "FSAssetStore");
+ m.Update();
+
+ dbcon.Close();
+ }
}
catch (MySqlException e)
{
@@ -100,70 +103,30 @@ namespace OpenSim.Data.MySQL
#endregion
- private bool OpenDatabase()
+ private MySqlDataReader ExecuteReader(MySqlCommand c)
{
- try
- {
- m_Connection = new MySqlConnection(m_ConnectionString);
-
- m_Connection.Open();
- }
- catch (MySqlException e)
- {
- m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}",
- e.Message.ToString());
-
- return false;
- }
-
- return true;
- }
-
- private IDataReader ExecuteReader(MySqlCommand c)
- {
- IDataReader r = null;
- MySqlConnection connection = (MySqlConnection) ((ICloneable)m_Connection).Clone();
- connection.Open();
- c.Connection = connection;
-
- r = c.ExecuteReader();
-
- return r;
- }
+ try
+ {
+ return c.ExecuteReader();
+ }
+ catch(MySqlException)
+ {
+ m_log.ErrorFormat("[FSASSETS] MySQL reader command: {0}", c.CommandText);
+ throw;
+ }
+ }
private void ExecuteNonQuery(MySqlCommand c)
{
- lock (m_connLock)
- {
- bool errorSeen = false;
-
- while (true)
- {
- try
- {
- c.ExecuteNonQuery();
- }
- catch (MySqlException)
- {
- System.Threading.Thread.Sleep(500);
-
- m_Connection.Close();
- m_Connection = (MySqlConnection) ((ICloneable)m_Connection).Clone();
- m_Connection.Open();
- c.Connection = m_Connection;
-
- if (!errorSeen)
- {
- errorSeen = true;
- continue;
- }
- m_log.ErrorFormat("[FSASSETS] MySQL command: {0}", c.CommandText);
- throw;
- }
-
- break;
- }
- }
+ try
+ {
+ c.ExecuteNonQuery();
+ }
+ catch(MySqlException)
+ {
+ m_log.ErrorFormat("[FSASSETS] MySQL command: {0}", c.CommandText);
+ throw;
+ }
}
#region IFSAssetDataPlugin Members
@@ -172,105 +135,105 @@ namespace OpenSim.Data.MySQL
{
hash = String.Empty;
- MySqlCommand cmd = new MySqlCommand();
-
- cmd.CommandText = String.Format("select id, name, description, type, hash, create_time, access_time, asset_flags from {0} where id = ?id", m_Table);
- cmd.Parameters.AddWithValue("?id", id);
-
- IDataReader reader = ExecuteReader(cmd);
-
- if (!reader.Read())
- {
- reader.Close();
- FreeCommand(cmd);
- return null;
- }
+ using(MySqlConnection dbcon = new MySqlConnection(m_ConnectionString))
+ using(MySqlCommand cmd = new MySqlCommand())
+ {
+ dbcon.Open();
+
+ cmd.Connection = dbcon;
+ cmd.CommandText = String.Format("select id, name, description, type, hash, create_time, access_time, asset_flags from {0} where id = ?id", m_Table);
+ cmd.Parameters.AddWithValue("?id", id);
+
+ AssetMetadata meta = new AssetMetadata();
+ int AccessTime;
+
+ using(IDataReader reader = ExecuteReader(cmd))
+ {
+ if(!reader.Read())
+ {
+ reader.Close();
+ return null;
+ }
- AssetMetadata meta = new AssetMetadata();
-
- hash = reader["hash"].ToString();
-
- meta.ID = id;
- meta.FullID = new UUID(id);
-
- meta.Name = reader["name"].ToString();
- meta.Description = reader["description"].ToString();
- meta.Type = (sbyte)Convert.ToInt32(reader["type"]);
- meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
- meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"]));
- meta.Flags = (AssetFlags)Convert.ToInt32(reader["asset_flags"]);
-
- int AccessTime = Convert.ToInt32(reader["access_time"]);
-
- reader.Close();
-
- UpdateAccessTime(AccessTime, cmd);
-
- FreeCommand(cmd);
-
- return meta;
- }
-
- private void UpdateAccessTime(int AccessTime, MySqlCommand cmd)
- {
- // Reduce DB work by only updating access time if asset hasn't recently been accessed
- // 0 By Default, Config option is "DaysBetweenAccessTimeUpdates"
- if (DaysBetweenAccessTimeUpdates > 0 && (DateTime.UtcNow - Utils.UnixTimeToDateTime(AccessTime)).TotalDays < DaysBetweenAccessTimeUpdates)
- return;
-
- cmd.CommandText = String.Format("UPDATE {0} SET `access_time` = UNIX_TIMESTAMP() WHERE `id` = ?id", m_Table);
-
- cmd.ExecuteNonQuery();
- }
-
- protected void FreeCommand(MySqlCommand cmd)
- {
- MySqlConnection c = cmd.Connection;
- cmd.Dispose();
- c.Close();
- c.Dispose();
+ hash = reader["hash"].ToString();
+
+ meta.ID = id;
+ meta.FullID = new UUID(id);
+
+ meta.Name = reader["name"].ToString();
+ meta.Description = reader["description"].ToString();
+ meta.Type = (sbyte)Convert.ToInt32(reader["type"]);
+ meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
+ meta.CreationDate = Util.ToDateTime(Convert.ToInt32 (reader["create_time"]));
+ meta.Flags = (AssetFlags)Convert.ToInt32(reader["asset_flags"]);
+
+ AccessTime = Convert.ToInt32(reader["access_time"]);
+
+ reader.Close();
+ }
+
+ // Reduce DB work by only updating access time if asset hasn't recently been accessed
+ // 0 By Default, Config option is "DaysBetweenAccessTimeUpdates"
+ if (!(DaysBetweenAccessTimeUpdates > 0 && (DateTime.UtcNow - Utils.UnixTimeToDateTime (AccessTime)).TotalDays < DaysBetweenAccessTimeUpdates))
+ {
+ using(MySqlCommand uatCmd = new MySqlCommand())
+ {
+ uatCmd.Connection = dbcon;
+ uatCmd.CommandText = String.Format("UPDATE {0} SET `access_time` = UNIX_TIMESTAMP() WHERE `id` = ?id", m_Table);
+ uatCmd.Parameters.AddWithValue("id", meta.ID);
+
+ ExecuteNonQuery(uatCmd);
+ }
+ }
+
+ dbcon.Close();
+ return meta;
+ }
}
public bool Store(AssetMetadata meta, string hash)
- {
- try
- {
- string oldhash;
- AssetMetadata existingAsset = Get(meta.ID, out oldhash);
-
- MySqlCommand cmd = m_Connection.CreateCommand();
-
- cmd.Parameters.AddWithValue("?id", meta.ID);
- cmd.Parameters.AddWithValue("?name", meta.Name);
- cmd.Parameters.AddWithValue("?description", meta.Description);
- cmd.Parameters.AddWithValue("?type", meta.Type.ToString());
- cmd.Parameters.AddWithValue("?hash", hash);
- cmd.Parameters.AddWithValue("?asset_flags", meta.Flags);
-
- if (existingAsset == null)
- {
- cmd.CommandText = String.Format("insert into {0} (id, name, description, type, hash, asset_flags, create_time, access_time) values ( ?id, ?name, ?description, ?type, ?hash, ?asset_flags, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())", m_Table);
-
- ExecuteNonQuery(cmd);
-
- cmd.Dispose();
-
- return true;
- }
-
- //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table);
-
- //ExecuteNonQuery(cmd);
-
- cmd.Dispose();
- return false;
- }
- catch(Exception e)
- {
- m_log.Error("[FSAssets] Failed to store asset with ID " + meta.ID);
- m_log.Error(e.ToString());
- return false;
- }
+ {
+ using (MySqlConnection dbcon = new MySqlConnection(m_ConnectionString))
+ using (MySqlCommand cmd = new MySqlCommand())
+ {
+ try
+ {
+ string oldhash;
+ AssetMetadata existingAsset = Get(meta.ID, out oldhash);
+
+ if (existingAsset == null)
+ {
+ dbcon.Open();
+ cmd.Connection = dbcon;
+
+ cmd.Parameters.AddWithValue("?id", meta.ID);
+ cmd.Parameters.AddWithValue("?name", meta.Name);
+ cmd.Parameters.AddWithValue("?description", meta.Description);
+ cmd.Parameters.AddWithValue("?type", meta.Type.ToString());
+ cmd.Parameters.AddWithValue("?hash", hash);
+ cmd.Parameters.AddWithValue("?asset_flags", meta.Flags);
+
+ cmd.CommandText = String.Format("insert into {0} (id, name, description, type, hash, asset_flags, create_time, access_time) values ( ?id, ?name, ?description, ?type, ?hash, ?asset_flags, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())", m_Table);
+
+ ExecuteNonQuery(cmd);
+
+ dbcon.Close();
+ return true;
+ }
+
+ //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table);
+
+ //ExecuteNonQuery(cmd);
+
+ return false;
+ }
+ catch(Exception e)
+ {
+ m_log.Error("[FSAssets] Failed to store asset with ID " + meta.ID);
+ m_log.Error(e.ToString());
+ return false;
+ }
+ }
}
/// <summary>
@@ -288,18 +251,24 @@ namespace OpenSim.Data.MySQL
string ids = "'" + string.Join("','", uuids) + "'";
string sql = string.Format("select id from {1} where id in ({0})", ids, m_Table);
- using (MySqlCommand cmd = m_Connection.CreateCommand())
+ using(MySqlConnection dbcon = new MySqlConnection(m_ConnectionString))
+ using(MySqlCommand cmd = new MySqlCommand())
{
+ dbcon.Open();
+
+ cmd.Connection = dbcon;
cmd.CommandText = sql;
- using (MySqlDataReader dbReader = cmd.ExecuteReader())
+ using(MySqlDataReader dbReader = ExecuteReader(cmd))
{
- while (dbReader.Read())
+ while(dbReader.Read())
{
UUID id = DBGuid.FromDB(dbReader["ID"]);
exists.Add(id);
}
}
+
+ dbcon.Close();
}
bool[] results = new bool[uuids.Length];
@@ -310,31 +279,43 @@ namespace OpenSim.Data.MySQL
public int Count()
{
- MySqlCommand cmd = m_Connection.CreateCommand();
-
- cmd.CommandText = String.Format("select count(*) as count from {0}", m_Table);
+ using(MySqlConnection dbcon = new MySqlConnection(m_ConnectionString))
+ using(MySqlCommand cmd = new MySqlCommand())
+ {
+ dbcon.Open();
- IDataReader reader = ExecuteReader(cmd);
+ cmd.Connection = dbcon;
+ cmd.CommandText = String.Format("select count(*) as count from {0}", m_Table);
- reader.Read();
+ using (MySqlDataReader reader = ExecuteReader(cmd))
+ {
+ reader.Read();
- int count = Convert.ToInt32(reader["count"]);
+ int count = Convert.ToInt32(reader ["count"]);
- reader.Close();
- FreeCommand(cmd);
+ reader.Close();
+ return count;
+ }
- return count;
+ dbcon.Close();
+ }
}
public bool Delete(string id)
{
- using (MySqlCommand cmd = m_Connection.CreateCommand())
+ using(MySqlConnection dbcon = new MySqlConnection(m_ConnectionString))
+ using(MySqlCommand cmd = new MySqlCommand())
{
+ dbcon.Open();
+
+ cmd.Connection = dbcon;
cmd.CommandText = String.Format("delete from {0} where id = ?id", m_Table);
cmd.Parameters.AddWithValue("?id", id);
ExecuteNonQuery(cmd);
+
+ dbcon.Close();
}
return true;
@@ -342,71 +323,74 @@ namespace OpenSim.Data.MySQL
public void Import(string conn, string table, int start, int count, bool force, FSStoreDelegate store)
{
- MySqlConnection importConn;
-
- try
- {
- importConn = new MySqlConnection(conn);
-
- importConn.Open();
- }
- catch (MySqlException e)
- {
- m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}",
- e.Message.ToString());
-
- return;
- }
-
- int imported = 0;
-
- MySqlCommand cmd = importConn.CreateCommand();
-
- string limit = String.Empty;
- if (count != -1)
- {
- limit = String.Format(" limit {0},{1}", start, count);
- }
+ using(MySqlConnection importConn = new MySqlConnection (conn))
+ {
+ try
+ {
+ importConn.Open();
+ }
+ catch (MySqlException e)
+ {
+ m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}", e.Message.ToString ());
+ return;
+ }
+
+ int imported = 0;
+
+ using(MySqlCommand cmd = importConn.CreateCommand ())
+ {
+ cmd.Connection = importConn;
+
+ string limit = String.Empty;
+ if(count != -1)
+ {
+ limit = String.Format(" limit {0},{1}", start, count);
+ }
- cmd.CommandText = String.Format("select * from {0}{1}", table, limit);
+ cmd.CommandText = String.Format("select * from {0}{1}", table, limit);
- MainConsole.Instance.Output("Querying database");
- IDataReader reader = cmd.ExecuteReader();
+ MainConsole.Instance.Output("Querying database");
- MainConsole.Instance.Output("Reading data");
+ using (IDataReader reader = cmd.ExecuteReader())
+ {
- while (reader.Read())
- {
- if ((imported % 100) == 0)
- {
- MainConsole.Instance.Output(String.Format("{0} assets imported so far", imported));
- }
+ MainConsole.Instance.Output("Reading data");
+
+ while(reader.Read())
+ {
+ if((imported % 100) == 0)
+ {
+ MainConsole.Instance.Output(String.Format ("{0} assets imported so far", imported));
+ }
- AssetBase asset = new AssetBase();
- AssetMetadata meta = new AssetMetadata();
+ AssetBase asset = new AssetBase();
+ AssetMetadata meta = new AssetMetadata();
- meta.ID = reader["id"].ToString();
- meta.FullID = new UUID(meta.ID);
+ meta.ID = reader["id"].ToString();
+ meta.FullID = new UUID(meta.ID);
- meta.Name = reader["name"].ToString();
- meta.Description = reader["description"].ToString();
- meta.Type = (sbyte)Convert.ToInt32(reader["assetType"]);
- meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
- meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"]));
+ meta.Name = reader["name"].ToString();
+ meta.Description = reader["description"].ToString();
+ meta.Type = (sbyte)Convert.ToInt32(reader["assetType"]);
+ meta.ContentType = SLUtil.SLAssetTypeToContentType(meta.Type);
+ meta.CreationDate = Util.ToDateTime(Convert.ToInt32(reader["create_time"]));
- asset.Metadata = meta;
- asset.Data = (byte[])reader["data"];
+ asset.Metadata = meta;
+ asset.Data = (byte[])reader["data"];
- store(asset, force);
+ store (asset, force);
- imported++;
- }
+ imported++;
+ }
+
+ reader.Close();
+ }
+ }
- reader.Close();
- cmd.Dispose();
- importConn.Close();
+ importConn.Close();
- MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported));
+ MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported));
+ }
}
#endregion
--
2.5.0
|
Relationships 
Relationships 