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, 80 insertions, 76 deletions

diff --git a/OpenSim/Data/MySQL/MySQLUserAccountData.cs b/OpenSim/Data/MySQL/MySQLUserAccountData.cs
index d48144d..3cb0010 100644
--- a/OpenSim/Data/MySQL/MySQLUserAccountData.cs
+++ b/OpenSim/Data/MySQL/MySQLUserAccountData.cs
@@ -38,16 +38,21 @@ namespace OpenSim.Data.MySQL
     public class MySqlUserAccountData : MySqlFramework, IUserAccountData
     {
         private string m_Realm;
-        private List<string> m_ColumnNames = null;
-//        private int m_LastExpire = 0;
+        private List<string> m_ColumnNames;
+        // private string m_connectionString;
 
         public MySqlUserAccountData(string connectionString, string realm)
                 : base(connectionString)
         {
             m_Realm = realm;
+            m_connectionString = connectionString;
 
-            Migration m = new Migration(m_Connection, GetType().Assembly, "UserStore");
-            m.Update();
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
+                Migration m = new Migration(dbcon, GetType().Assembly, "UserStore");
+                m.Update();
+            }
         }
 
         public List<UserAccountData> Query(UUID principalID, UUID scopeID, string query)
@@ -64,49 +69,49 @@ namespace OpenSim.Data.MySQL
             if (scopeID != UUID.Zero)
                 command += " and ScopeID = ?scopeID";
 
-            MySqlCommand cmd = new MySqlCommand(command);
+            using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+            {
+                dbcon.Open();
+                MySqlCommand cmd = new MySqlCommand(command, dbcon);
 
-            cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
-            cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
+                cmd.Parameters.AddWithValue("?principalID", principalID.ToString());
+                cmd.Parameters.AddWithValue("?scopeID", scopeID.ToString());
 
-            IDataReader result = ExecuteReader(cmd);
+                IDataReader result = cmd.ExecuteReader();
 
-            if (result.Read())
-            {
-                ret.PrincipalID = principalID;
-                UUID scope;
-                UUID.TryParse(result["ScopeID"].ToString(), out scope);
-                ret.ScopeID = scope;
-
-                if (m_ColumnNames == null)
+                if (result.Read())
                 {
-                    m_ColumnNames = new List<string>();
-
-                    DataTable schemaTable = result.GetSchemaTable();
-                    foreach (DataRow row in schemaTable.Rows)
-                        m_ColumnNames.Add(row["ColumnName"].ToString());
+                    ret.PrincipalID = principalID;
+                    UUID scope;
+                    UUID.TryParse(result["ScopeID"].ToString(), out scope);
+                    ret.ScopeID = scope;
+
+                    if (m_ColumnNames == null)
+                    {
+                        m_ColumnNames = new List<string>();
+
+                        DataTable schemaTable = result.GetSchemaTable();
+                        foreach (DataRow row in schemaTable.Rows)
+                            m_ColumnNames.Add(row["ColumnName"].ToString());
+                    }
+
+                    foreach (string s in m_ColumnNames)
+                    {
+                        if (s == "UUID")
+                            continue;
+                        if (s == "ScopeID")
+                            continue;
+
+                        ret.Data[s] = result[s].ToString();
+                    }
+
+                    return ret;
                 }
-
-                foreach (string s in m_ColumnNames)
+                else
                 {
-                    if (s == "UUID")
-                        continue;
-                    if (s == "ScopeID")
-                        continue;
-
-                    ret.Data[s] = result[s].ToString();
+                    return null;
                 }
-
-                result.Close();
-                CloseReaderCommand(cmd);
-
-                return ret;
             }
-
-            result.Close();
-            CloseReaderCommand(cmd);
-
-            return null;
         }
 
         public bool Store(UserAccountData data)
@@ -118,61 +123,60 @@ namespace OpenSim.Data.MySQL
 
             string[] fields = new List<string>(data.Data.Keys).ToArray();
 
-            MySqlCommand cmd = new MySqlCommand();
-
-            string update = "update `"+m_Realm+"` set ";
-            bool first = true;
-            foreach (string field in fields)
+            using (MySqlCommand cmd = new MySqlCommand())
             {
-                if (!first)
-                    update += ", ";
-                update += "`" + field + "` = ?"+field;
-
-                first = false;
-
-                cmd.Parameters.AddWithValue("?"+field, data.Data[field]);
-            }
+                string update = "update `" + m_Realm + "` set ";
+                bool first = true;
+                foreach (string field in fields)
+                {
+                    if (!first)
+                        update += ", ";
+                    update += "`" + field + "` = ?" + field;
 
-            update += " where UUID = ?principalID";
+                    first = false;
 
-            if (data.ScopeID != UUID.Zero)
-                update += " and ScopeID = ?scopeID";
+                    cmd.Parameters.AddWithValue("?" + field, data.Data[field]);
+                }
 
-            cmd.CommandText = update;
-            cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString());
-            cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
+                update += " where UUID = ?principalID";
 
-            if (ExecuteNonQuery(cmd) < 1)
-            {
-                string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" +
-                        String.Join("`, `", fields) +
-                        "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")";
+                if (data.ScopeID != UUID.Zero)
+                    update += " and ScopeID = ?scopeID";
 
-                cmd.CommandText = insert;
+                cmd.CommandText = update;
+                cmd.Parameters.AddWithValue("?principalID", data.PrincipalID.ToString());
+                cmd.Parameters.AddWithValue("?scopeID", data.ScopeID.ToString());
 
                 if (ExecuteNonQuery(cmd) < 1)
                 {
-                    cmd.Dispose();
-                    return false;
+                    string insert = "insert into `" + m_Realm + "` (`UUID`, `ScopeID`, `" +
+                            String.Join("`, `", fields) +
+                            "`) values (?principalID, ?scopeID, ?" + String.Join(", ?", fields) + ")";
+
+                    cmd.CommandText = insert;
+
+                    if (ExecuteNonQuery(cmd) < 1)
+                    {
+                        cmd.Dispose();
+                        return false;
+                    }
                 }
             }
 
-            cmd.Dispose();
-
             return true;
         }
 
         public bool SetDataItem(UUID principalID, 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", principalID.ToString());
+            using (MySqlCommand cmd = new MySqlCommand("update `" + m_Realm + "` set `" +
+                item + "` = ?" + item + " where UUID = ?UUID"))
+            {
+                cmd.Parameters.AddWithValue("?" + item, value);
+                cmd.Parameters.AddWithValue("?UUID", principalID.ToString());
 
-            if (ExecuteNonQuery(cmd) > 0)
-                return true;
+                if (ExecuteNonQuery(cmd) > 0)
+                    return true;
+            }
 
             return false;
         }