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.

1 files changed, 220 insertions, 220 deletions

diff --git a/OpenSim/Data/MySQL/MySQLEstateData.cs b/OpenSim/Data/MySQL/MySQLEstateData.cs
index e8694fc..2eae2d8 100644
--- a/OpenSim/Data/MySQL/MySQLEstateData.cs
+++ b/OpenSim/Data/MySQL/MySQLEstateData.cs
@@ -44,7 +44,6 @@ namespace OpenSim.Data.MySQL
 
         private const string m_waitTimeoutSelect = "select @@wait_timeout";
 
-        private MySqlConnection m_connection;
         private string m_connectionString;
         private long m_waitTimeout;
         private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
@@ -67,24 +66,26 @@ namespace OpenSim.Data.MySQL
                 m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
             }
 
-            m_connection = new MySqlConnection(m_connectionString);
-            m_connection.Open();
-
             GetWaitTimeout();
 
-            Assembly assem = GetType().Assembly;
-            Migration m = new Migration(m_connection, assem, "EstateStore");
-            m.Update();
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
+
+                Assembly assem = GetType().Assembly;
+                Migration m = new Migration(dbcon, assem, "EstateStore");
+                m.Update();
 
-            Type t = typeof(EstateSettings);
-            m_Fields = t.GetFields(BindingFlags.NonPublic |
-                                   BindingFlags.Instance |
-                                   BindingFlags.DeclaredOnly);
+                Type t = typeof(EstateSettings);
+                m_Fields = t.GetFields(BindingFlags.NonPublic |
+                                       BindingFlags.Instance |
+                                       BindingFlags.DeclaredOnly);
 
-            foreach (FieldInfo f in m_Fields)
-            {
-                if (f.Name.Substring(0, 2) == "m_")
-                    m_FieldMap[f.Name.Substring(2)] = f;
+                foreach (FieldInfo f in m_Fields)
+                {
+                    if (f.Name.Substring(0, 2) == "m_")
+                        m_FieldMap[f.Name.Substring(2)] = f;
+                }
             }
         }
 
@@ -95,47 +96,29 @@ namespace OpenSim.Data.MySQL
 
         protected void GetWaitTimeout()
         {
-            MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect,
-                    m_connection);
-
-            using (MySqlDataReader dbReader =
-                    cmd.ExecuteReader(CommandBehavior.SingleRow))
+            using (MySqlConnection dbcon = new MySqlConnection(m_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;
-
-            m_log.DebugFormat(
-                "[REGION DB]: Connection wait timeout {0} seconds",
-                m_waitTimeout / TimeSpan.TicksPerSecond);
-        }
-
-        protected void CheckConnection()
-        {
-            long timeNow = DateTime.Now.Ticks;
-            if (timeNow - m_lastConnectionUse > m_waitTimeout ||
-                    m_connection.State != ConnectionState.Open)
-            {
-                m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
+                m_lastConnectionUse = DateTime.Now.Ticks;
 
-                lock (m_connection)
-                {
-                    m_connection.Close();
-                    m_connection = new MySqlConnection(m_connectionString);
-                    m_connection.Open();
-                }
+                m_log.DebugFormat(
+                    "[REGION DB]: Connection wait timeout {0} seconds",
+                    m_waitTimeout / TimeSpan.TicksPerSecond);
             }
-
-            m_lastConnectionUse = timeNow;
         }
 
         public EstateSettings LoadEstateSettings(UUID regionID)
@@ -143,114 +126,111 @@ namespace OpenSim.Data.MySQL
             EstateSettings es = new EstateSettings();
             es.OnSave += StoreEstateSettings;
 
-            string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) + " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
+            string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) +
+                " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
 
-            CheckConnection();
+            bool migration = true;
 
-            MySqlCommand cmd = m_connection.CreateCommand();
-
-            cmd.CommandText = sql;
-            cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
-
-            IDataReader r = cmd.ExecuteReader();
-
-            if (r.Read())
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
             {
-                foreach (string name in FieldList)
+                dbcon.Open();
+
+                using (MySqlCommand cmd = dbcon.CreateCommand())
                 {
-                    if (m_FieldMap[name].GetValue(es) is bool)
-                    {
-                        int v = Convert.ToInt32(r[name]);
-                        if (v != 0)
-                            m_FieldMap[name].SetValue(es, true);
-                        else
-                            m_FieldMap[name].SetValue(es, false);
-                    }
-                    else if (m_FieldMap[name].GetValue(es) is UUID)
-                    {
-                        UUID uuid = UUID.Zero;
+                    cmd.CommandText = sql;
+                    cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
 
-                        UUID.TryParse(r[name].ToString(), out uuid);
-                        m_FieldMap[name].SetValue(es, uuid);
-                    }
-                    else
+                    using (IDataReader r = cmd.ExecuteReader())
                     {
-                        m_FieldMap[name].SetValue(es, r[name]);
+                        if (r.Read())
+                        {
+                            migration = false;
+
+                            foreach (string name in FieldList)
+                            {
+                                if (m_FieldMap[name].GetValue(es) is bool)
+                                {
+                                    int v = Convert.ToInt32(r[name]);
+                                    if (v != 0)
+                                        m_FieldMap[name].SetValue(es, true);
+                                    else
+                                        m_FieldMap[name].SetValue(es, false);
+                                }
+                                else if (m_FieldMap[name].GetValue(es) is UUID)
+                                {
+                                    UUID uuid = UUID.Zero;
+
+                                    UUID.TryParse(r[name].ToString(), out uuid);
+                                    m_FieldMap[name].SetValue(es, uuid);
+                                }
+                                else
+                                {
+                                    m_FieldMap[name].SetValue(es, r[name]);
+                                }
+                            }
+                        }
                     }
                 }
-                r.Close();
-            }
-            else
-            {
-                // Migration case
-                //
-                r.Close();
-
-                List<string> names = new List<string>(FieldList);
 
-                names.Remove("EstateID");
+                if (migration)
+                {
+                    // Migration case
+                    List<string> names = new List<string>(FieldList);
 
-                sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
+                    names.Remove("EstateID");
 
-                cmd.CommandText = sql;
-                cmd.Parameters.Clear();
+                    sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
 
-                foreach (string name in FieldList)
-                {
-                    if (m_FieldMap[name].GetValue(es) is bool)
-                    {
-                        if ((bool)m_FieldMap[name].GetValue(es))
-                            cmd.Parameters.AddWithValue("?" + name, "1");
-                        else
-                            cmd.Parameters.AddWithValue("?" + name, "0");
-                    }
-                    else
+                    using (MySqlCommand cmd = dbcon.CreateCommand())
                     {
-                        cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
+                        cmd.CommandText = sql;
+                        cmd.Parameters.Clear();
+
+                        foreach (string name in FieldList)
+                        {
+                            if (m_FieldMap[name].GetValue(es) is bool)
+                            {
+                                if ((bool)m_FieldMap[name].GetValue(es))
+                                    cmd.Parameters.AddWithValue("?" + name, "1");
+                                else
+                                    cmd.Parameters.AddWithValue("?" + name, "0");
+                            }
+                            else
+                            {
+                                cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
+                            }
+                        }
+
+                        cmd.ExecuteNonQuery();
+
+                        cmd.CommandText = "select LAST_INSERT_ID() as id";
+                        cmd.Parameters.Clear();
+
+                        using (IDataReader r = cmd.ExecuteReader())
+                        {
+                            r.Read();
+                            es.EstateID = Convert.ToUInt32(r["id"]);
+                        }
+
+                        cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
+                        cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
+                        cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
+
+                        // This will throw on dupe key
+                        try { cmd.ExecuteNonQuery(); }
+                        catch (Exception) { }
+
+                        // Munge and transfer the ban list
+                        cmd.Parameters.Clear();
+                        cmd.CommandText = "insert into estateban select " + es.EstateID.ToString() + ", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = ?UUID";
+                        cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
+
+                        try { cmd.ExecuteNonQuery(); }
+                        catch (Exception) { }
+
+                        es.Save();
                     }
                 }
-
-                cmd.ExecuteNonQuery();
-
-                cmd.CommandText = "select LAST_INSERT_ID() as id";
-                cmd.Parameters.Clear();
-
-                r = cmd.ExecuteReader();
-
-                r.Read();
-
-                es.EstateID = Convert.ToUInt32(r["id"]);
-
-                r.Close();
-
-                cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
-                cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
-                cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
-
-                // This will throw on dupe key
-                try
-                {
-                    cmd.ExecuteNonQuery();
-                }
-                catch (Exception)
-                {
-                }
-
-                // Munge and transfer the ban list
-                //
-                cmd.Parameters.Clear();
-                cmd.CommandText = "insert into estateban select " + es.EstateID.ToString() + ", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = ?UUID";
-                cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
-
-                try
-                {
-                    cmd.ExecuteNonQuery();
-                }
-                catch (Exception)
-                {
-                }
-
-                es.Save();
             }
 
             LoadBanList(es);
@@ -265,29 +245,33 @@ namespace OpenSim.Data.MySQL
         {
             string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
 
-            CheckConnection();
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
 
-            MySqlCommand cmd = m_connection.CreateCommand();
+                using (MySqlCommand cmd = dbcon.CreateCommand())
+                {
+                    cmd.CommandText = sql;
 
-            cmd.CommandText = sql;
+                    foreach (string name in FieldList)
+                    {
+                        if (m_FieldMap[name].GetValue(es) is bool)
+                        {
+                            if ((bool)m_FieldMap[name].GetValue(es))
+                                cmd.Parameters.AddWithValue("?" + name, "1");
+                            else
+                                cmd.Parameters.AddWithValue("?" + name, "0");
+                        }
+                        else
+                        {
+                            cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
+                        }
+                    }
 
-            foreach (string name in FieldList)
-            {
-                if (m_FieldMap[name].GetValue(es) is bool)
-                {
-                    if ((bool)m_FieldMap[name].GetValue(es))
-                        cmd.Parameters.AddWithValue("?" + name, "1");
-                    else
-                        cmd.Parameters.AddWithValue("?" + name, "0");
-                }
-                else
-                {
-                    cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
+                    cmd.ExecuteNonQuery();
                 }
             }
 
-            cmd.ExecuteNonQuery();
-
             SaveBanList(es);
             SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
             SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
@@ -298,77 +282,89 @@ namespace OpenSim.Data.MySQL
         {
             es.ClearBans();
 
-            CheckConnection();
-
-            MySqlCommand cmd = m_connection.CreateCommand();
-
-            cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
-            cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
-
-            IDataReader r = cmd.ExecuteReader();
-
-            while (r.Read())
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
             {
-                EstateBan eb = new EstateBan();
+                dbcon.Open();
 
-                UUID uuid = new UUID();
-                UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
+                using (MySqlCommand cmd = dbcon.CreateCommand())
+                {
+                    cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
+                    cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
 
-                eb.BannedUserID = uuid;
-                eb.BannedHostAddress = "0.0.0.0";
-                eb.BannedHostIPMask = "0.0.0.0";
-                es.AddBan(eb);
+                    using (IDataReader r = cmd.ExecuteReader())
+                    {
+                        while (r.Read())
+                        {
+                            EstateBan eb = new EstateBan();
+
+                            UUID uuid = new UUID();
+                            UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
+
+                            eb.BannedUserID = uuid;
+                            eb.BannedHostAddress = "0.0.0.0";
+                            eb.BannedHostIPMask = "0.0.0.0";
+                            es.AddBan(eb);
+                        }
+                    }
+                }
             }
-            r.Close();
         }
 
         private void SaveBanList(EstateSettings es)
         {
-            CheckConnection();
-
-            MySqlCommand cmd = m_connection.CreateCommand();
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
 
-            cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
-            cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
+                using (MySqlCommand cmd = dbcon.CreateCommand())
+                {
+                    cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
+                    cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
 
-            cmd.ExecuteNonQuery();
+                    cmd.ExecuteNonQuery();
 
-            cmd.Parameters.Clear();
+                    cmd.Parameters.Clear();
 
-            cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
+                    cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
 
-            foreach (EstateBan b in es.EstateBans)
-            {
-                cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
-                cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
+                    foreach (EstateBan b in es.EstateBans)
+                    {
+                        cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
+                        cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
 
-                cmd.ExecuteNonQuery();
-                cmd.Parameters.Clear();
+                        cmd.ExecuteNonQuery();
+                        cmd.Parameters.Clear();
+                    }
+                }
             }
         }
 
         void SaveUUIDList(uint EstateID, string table, UUID[] data)
         {
-            CheckConnection();
-
-            MySqlCommand cmd = m_connection.CreateCommand();
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
 
-            cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
-            cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
+                using (MySqlCommand cmd = dbcon.CreateCommand())
+                {
+                    cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
+                    cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
 
-            cmd.ExecuteNonQuery();
+                    cmd.ExecuteNonQuery();
 
-            cmd.Parameters.Clear();
+                    cmd.Parameters.Clear();
 
-            cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
+                    cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
 
-            foreach (UUID uuid in data)
-            {
-                cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
-                cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
+                    foreach (UUID uuid in data)
+                    {
+                        cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
+                        cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
 
-                cmd.ExecuteNonQuery();
-                cmd.Parameters.Clear();
+                        cmd.ExecuteNonQuery();
+                        cmd.Parameters.Clear();
+                    }
+                }
             }
         }
 
@@ -376,25 +372,29 @@ namespace OpenSim.Data.MySQL
         {
             List<UUID> uuids = new List<UUID>();
 
-            CheckConnection();
-
-            MySqlCommand cmd = m_connection.CreateCommand();
-
-            cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
-            cmd.Parameters.AddWithValue("?EstateID", EstateID);
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
 
-            IDataReader r = cmd.ExecuteReader();
+                using (MySqlCommand cmd = dbcon.CreateCommand())
+                {
+                    cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
+                    cmd.Parameters.AddWithValue("?EstateID", EstateID);
 
-            while (r.Read())
-            {
-                // EstateBan eb = new EstateBan();
+                    using (IDataReader r = cmd.ExecuteReader())
+                    {
+                        while (r.Read())
+                        {
+                            // EstateBan eb = new EstateBan();
 
-                UUID uuid = new UUID();
-                UUID.TryParse(r["uuid"].ToString(), out uuid);
+                            UUID uuid = new UUID();
+                            UUID.TryParse(r["uuid"].ToString(), out uuid);
 
-                uuids.Add(uuid);
+                            uuids.Add(uuid);
+                        }
+                    }
+                }
             }
-            r.Close();
 
             return uuids.ToArray();
         }