Port the safer database access and the cure for "Chicken Bones" which

are artefacts that can be created by fsck and would make standard
FSAssets choke. These refinements were tested on OSGrid.

1 files changed, 191 insertions, 188 deletions

diff --git a/OpenSim/Data/MySQL/MySQLFSAssetData.cs b/OpenSim/Data/MySQL/MySQLFSAssetData.cs
index 19e23b5..40bfd1f 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,11 @@ namespace OpenSim.Data.MySQL
 
             try
             {
-                OpenDatabase();
-
-                Migration m = new Migration(m_Connection, Assembly, "FSAssetStore");
-                m.Update();
+                using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
+                {
+                    Migration m = new Migration(conn, Assembly, "FSAssetStore");
+                    m.Update();
+                }
             }
             catch (MySqlException e)
             {
@@ -100,70 +99,33 @@ namespace OpenSim.Data.MySQL
 
         #endregion
 
-        private bool OpenDatabase()
-        {
-            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;
-        }
-
-        private void ExecuteNonQuery(MySqlCommand c)
+        private bool ExecuteNonQuery(MySqlCommand cmd)
         {
-            lock (m_connLock)
+            using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
             {
-                bool errorSeen = false;
-
-                while (true)
+                try
                 {
-                    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;
-                    }
+                    conn.Open();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString());
+                    return false;
+                }
 
-                    break;
+                cmd.Connection = conn;
+                try
+                {
+                    cmd.ExecuteNonQuery();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Query {0} failed with {1}", cmd.CommandText, e.ToString());
+                    return false;
                 }
             }
+
+            return true;
         }
 
         #region IFSAssetDataPlugin Members
@@ -172,63 +134,78 @@ 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);
+            AssetMetadata meta = new AssetMetadata();
 
-            if (!reader.Read())
+            using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
             {
-                reader.Close();
-                FreeCommand(cmd);
-                return null;
-            }
-            
-            AssetMetadata meta = new AssetMetadata();
+                try
+                {
+                    conn.Open();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString());
+                    return null;
+                }
 
-            hash = reader["hash"].ToString();
+                using (MySqlCommand cmd = conn.CreateCommand())
+                {
+                    cmd.CommandText = String.Format("select id, name, description, type, hash, create_time, asset_flags from {0} where id = ?id", m_Table);
+                    cmd.Parameters.AddWithValue("?id", id);
 
-            meta.ID = id;
-            meta.FullID = new UUID(id);
+                    using (IDataReader reader = cmd.ExecuteReader())
+                    {
+                        if (!reader.Read())
+                            return null;
 
-            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"]);
+                        hash = reader["hash"].ToString();
 
-            int AccessTime = Convert.ToInt32(reader["access_time"]);
+                        meta.ID = id;
+                        meta.FullID = new UUID(id);
 
-            reader.Close();
+                        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"]);
 
-            UpdateAccessTime(AccessTime, cmd);
+                        int AccessTime = Convert.ToInt32(reader["access_time"]);
+                        UpdateAccessTime(AccessTime);
+                    }
+                }
 
-            FreeCommand(cmd);
+            }
 
             return meta;
         }
 
-        private void UpdateAccessTime(int AccessTime, MySqlCommand cmd)
+        private void UpdateAccessTime(int AccessTime)
         {
             // 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);
+            using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
+            {
+                try
+                {
+                    conn.Open();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Database open failed with {0}", e.ToString());
+                    return;
+                }
 
-            cmd.ExecuteNonQuery();
-        }
+                using (MySqlCommand cmd = conn.CreateCommand())
+                {
+                    cmd.CommandText = String.Format("UPDATE {0} SET `access_time` = UNIX_TIMESTAMP() WHERE `id` = ?id", m_Table);
 
-        protected void FreeCommand(MySqlCommand cmd)
-        {
-            MySqlConnection c = cmd.Connection;
-            cmd.Dispose();
-            c.Close();
-            c.Dispose();
+                    cmd.ExecuteNonQuery();
+                }
+            }
         }
 
         public bool Store(AssetMetadata meta, string hash)
@@ -238,37 +215,35 @@ namespace OpenSim.Data.MySQL
                 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)
+                using (MySqlCommand cmd = new MySqlCommand())
                 {
-                    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.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);
 
-                    cmd.Dispose();
+                        ExecuteNonQuery(cmd);
 
-                    return true;
-                }
+                        return true;
+                    }
 
-                //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table);
+                    //cmd.CommandText = String.Format("update {0} set hash = ?hash, access_time = UNIX_TIMESTAMP() where id = ?id", m_Table);
 
-                //ExecuteNonQuery(cmd);
+                    //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());
+        m_log.Error(e.ToString());
                 return false;
             }
         }
@@ -283,26 +258,42 @@ namespace OpenSim.Data.MySQL
             if (uuids.Length == 0)
                 return new bool[0];
 
+            bool[] results = new bool[uuids.Length];
+            for (int i = 0; i < uuids.Length; i++)
+                results[i] = false;
+
             HashSet<UUID> exists = new HashSet<UUID>();
 
             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 conn = new MySqlConnection(m_ConnectionString))
             {
-                cmd.CommandText = sql;
+                try
+                {
+                    conn.Open();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Failed to open database: {0}", e.ToString());
+                    return results;
+                }
 
-                using (MySqlDataReader dbReader = cmd.ExecuteReader())
+                using (MySqlCommand cmd = conn.CreateCommand())
                 {
-                    while (dbReader.Read())
+                    cmd.CommandText = sql;
+
+                    using (MySqlDataReader dbReader = cmd.ExecuteReader())
                     {
-                        UUID id = DBGuid.FromDB(dbReader["ID"]);
-                        exists.Add(id);
+                        while (dbReader.Read())
+                        {
+                            UUID id = DBGuid.FromDB(dbReader["ID"]);
+                            exists.Add(id);
+                        }
                     }
                 }
             }
 
-            bool[] results = new bool[uuids.Length];
             for (int i = 0; i < uuids.Length; i++)
                 results[i] = exists.Contains(uuids[i]);
             return results;
@@ -310,102 +301,114 @@ namespace OpenSim.Data.MySQL
 
         public int Count()
         {
-            MySqlCommand cmd = m_Connection.CreateCommand();
+            int count = 0;
 
-            cmd.CommandText = String.Format("select count(*) as count from {0}", m_Table);
+            using (MySqlConnection conn = new MySqlConnection(m_ConnectionString))
+            {
+                try
+                {
+                    conn.Open();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Failed to open database: {0}", e.ToString());
+                    return 0;
+                }
 
-            IDataReader reader = ExecuteReader(cmd);
+                MySqlCommand cmd = conn.CreateCommand();
 
-            reader.Read();
+                cmd.CommandText = String.Format("select count(*) as count from {0}", m_Table);
 
-            int count = Convert.ToInt32(reader["count"]);
+                using (IDataReader reader = cmd.ExecuteReader())
+                {
+                    reader.Read();
 
-            reader.Close();
-            FreeCommand(cmd);
+                    count = Convert.ToInt32(reader["count"]);
+                }
+            }
 
             return count;
         }
 
         public bool Delete(string id)
         {
-            using (MySqlCommand cmd = m_Connection.CreateCommand())
-            {
-                cmd.CommandText = String.Format("delete from {0} where id = ?id", m_Table);
+            MySqlCommand cmd = new MySqlCommand();
 
-                cmd.Parameters.AddWithValue("?id", id);
+            cmd.CommandText = String.Format("delete from {0} where id = ?id", m_Table);
 
-                ExecuteNonQuery(cmd);
-            }
+            cmd.Parameters.AddWithValue("?id", id);
+
+            ExecuteNonQuery(cmd);
+
+            cmd.Dispose();
 
             return true;
         }
 
         public void Import(string conn, string table, int start, int count, bool force, FSStoreDelegate store)
         {
-            MySqlConnection importConn;
-
-            try
-            {
-                importConn = new MySqlConnection(conn);
+            int imported = 0;
 
-                importConn.Open();
-            }
-            catch (MySqlException e)
+            using (MySqlConnection importConn = new MySqlConnection(conn))
             {
-                m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}",
-                        e.Message.ToString());
+                try
+                {
+                    importConn.Open();
+                }
+                catch (MySqlException e)
+                {
+                    m_log.ErrorFormat("[FSASSETS]: Can't connect to database: {0}",
+                            e.Message.ToString());
 
-                return;
-            }
+                    return;
+                }
 
-            int imported = 0;
+                using (MySqlCommand cmd = importConn.CreateCommand())
+                {
+                    string limit = String.Empty;
+                    if (count != -1)
+                    {
+                        limit = String.Format(" limit {0},{1}", start, count);
+                    }
 
-            MySqlCommand cmd = importConn.CreateCommand();
+                    cmd.CommandText = String.Format("select * from {0}{1}", table, limit);
 
-            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);
+                    MainConsole.Instance.Output("Querying database");
+                    using (IDataReader reader = cmd.ExecuteReader())
+                    {
+                        MainConsole.Instance.Output("Reading data");
 
-            MainConsole.Instance.Output("Querying database");
-            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");
+                            AssetBase asset = new AssetBase();
+                            AssetMetadata meta = new AssetMetadata();
 
-            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();
+                            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();
-            cmd.Dispose();
-            importConn.Close();
-
             MainConsole.Instance.Output(String.Format("Import done, {0} assets imported", imported));
         }