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/MySQLManager.cs | 428 ++++++++++++++++++-------------------
1 file changed, 211 insertions(+), 217 deletions(-)
(limited to 'OpenSim/Data/MySQL/MySQLManager.cs')
diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs
index 243394e..ace2027 100644
--- a/OpenSim/Data/MySQL/MySQLManager.cs
+++ b/OpenSim/Data/MySQL/MySQLManager.cs
@@ -46,15 +46,12 @@ namespace OpenSim.Data.MySQL
private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
///
- /// The database connection object
- ///
- private MySqlConnection dbcon;
-
- ///
/// Connection string for ADO.net
///
private string connectionString;
+ private object m_dbLock = new object();
+
private const string m_waitTimeoutSelect = "select @@wait_timeout";
///
@@ -109,11 +106,11 @@ namespace OpenSim.Data.MySQL
try
{
connectionString = connect;
- dbcon = new MySqlConnection(connectionString);
+ //dbcon = new MySqlConnection(connectionString);
try
{
- dbcon.Open();
+ //dbcon.Open();
}
catch(Exception e)
{
@@ -134,18 +131,21 @@ namespace OpenSim.Data.MySQL
///
protected void GetWaitTimeout()
{
- MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon);
-
- using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+ using (MySqlConnection dbcon = new MySqlConnection(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;
@@ -154,66 +154,9 @@ namespace OpenSim.Data.MySQL
"[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond);
}
- ///
- /// Should be called before any db operation. This checks to see if the connection has not timed out
- ///
- public void CheckConnection()
+ public string ConnectionString
{
- //m_log.Debug("[REGION DB]: Checking connection");
-
- long timeNow = DateTime.Now.Ticks;
- if (timeNow - m_lastConnectionUse > m_waitTimeout || dbcon.State != ConnectionState.Open)
- {
- m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
- Reconnect();
- }
-
- // Strictly, we should set this after the actual db operation. But it's more convenient to set here rather
- // than require the code to call another method - the timeout leeway should be large enough to cover the
- // inaccuracy.
- m_lastConnectionUse = timeNow;
- }
-
- ///
- /// Get the connection being used
- ///
- /// MySqlConnection Object
- public MySqlConnection Connection
- {
- get { return dbcon; }
- }
-
- ///
- /// Shuts down the database connection
- ///
- public void Close()
- {
- dbcon.Close();
- dbcon = null;
- }
-
- ///
- /// Reconnects to the database
- ///
- public void Reconnect()
- {
- m_log.Info("[REGION DB] Reconnecting database");
-
- lock (dbcon)
- {
- try
- {
- // Close the DB connection
- dbcon.Close();
- // Try reopen it
- dbcon = new MySqlConnection(connectionString);
- dbcon.Open();
- }
- catch (Exception e)
- {
- m_log.Error("Unable to reconnect to database " + e.ToString());
- }
- }
+ get { return connectionString; }
}
///
@@ -264,9 +207,13 @@ namespace OpenSim.Data.MySQL
/// name of embedded resource
public void ExecuteResourceSql(string name)
{
- CheckConnection();
- MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
- cmd.ExecuteNonQuery();
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
+
+ MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
+ cmd.ExecuteNonQuery();
+ }
}
///
@@ -275,22 +222,29 @@ namespace OpenSim.Data.MySQL
/// sql string to execute
public void ExecuteSql(string sql)
{
- CheckConnection();
- MySqlCommand cmd = new MySqlCommand(sql, dbcon);
- cmd.ExecuteNonQuery();
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
+
+ MySqlCommand cmd = new MySqlCommand(sql, dbcon);
+ cmd.ExecuteNonQuery();
+ }
}
public void ExecuteParameterizedSql(string sql, Dictionary parameters)
{
- CheckConnection();
-
- MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
- cmd.CommandText = sql;
- foreach (KeyValuePair param in parameters)
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
{
- cmd.Parameters.AddWithValue(param.Key, param.Value);
+ dbcon.Open();
+
+ MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
+ cmd.CommandText = sql;
+ foreach (KeyValuePair param in parameters)
+ {
+ cmd.Parameters.AddWithValue(param.Key, param.Value);
+ }
+ cmd.ExecuteNonQuery();
}
- cmd.ExecuteNonQuery();
}
///
@@ -299,35 +253,37 @@ namespace OpenSim.Data.MySQL
///
public void GetTableVersion(Dictionary tableList)
{
- lock (dbcon)
+ lock (m_dbLock)
{
- CheckConnection();
-
- MySqlCommand tablesCmd =
- new MySqlCommand(
- "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
- dbcon);
- tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
-
- using (MySqlDataReader tables = tablesCmd.ExecuteReader())
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
{
- while (tables.Read())
+ dbcon.Open();
+
+ using (MySqlCommand tablesCmd = new MySqlCommand(
+ "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", dbcon))
{
- try
+ tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
+
+ using (MySqlDataReader tables = tablesCmd.ExecuteReader())
{
- string tableName = (string) tables["TABLE_NAME"];
- string comment = (string) tables["TABLE_COMMENT"];
- if (tableList.ContainsKey(tableName))
+ while (tables.Read())
{
- tableList[tableName] = comment;
+ try
+ {
+ string tableName = (string)tables["TABLE_NAME"];
+ string comment = (string)tables["TABLE_COMMENT"];
+ if (tableList.ContainsKey(tableName))
+ {
+ tableList[tableName] = comment;
+ }
+ }
+ catch (Exception e)
+ {
+ m_log.Error(e.Message, e);
+ }
}
}
- catch (Exception e)
- {
- m_log.Error(e.ToString());
- }
}
- tables.Close();
}
}
}
@@ -337,28 +293,27 @@ namespace OpenSim.Data.MySQL
///
/// Runs a query with protection against SQL Injection by using parameterised input.
///
+ /// Database connection
/// The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y
/// The parameters - index so that @y is indexed as 'y'
/// A MySQL DB Command
- public IDbCommand Query(string sql, Dictionary parameters)
+ public IDbCommand Query(MySqlConnection dbcon, string sql, Dictionary parameters)
{
try
{
- CheckConnection(); // Not sure if this one is necessary
-
- MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
+ MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand();
dbcommand.CommandText = sql;
foreach (KeyValuePair param in parameters)
{
dbcommand.Parameters.AddWithValue(param.Key, param.Value);
}
- return (IDbCommand) dbcommand;
+ return (IDbCommand)dbcommand;
}
catch (Exception e)
{
// Return null if it fails.
- m_log.Error("Failed during Query generation: " + e.ToString());
+ m_log.Error("Failed during Query generation: " + e.Message, e);
return null;
}
}
@@ -694,8 +649,6 @@ namespace OpenSim.Data.MySQL
ret.Add(attachpoint, item);
}
- r.Close();
-
return ret;
}
@@ -727,12 +680,17 @@ namespace OpenSim.Data.MySQL
try
{
- IDbCommand result = Query(sql, parameters);
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
+
+ IDbCommand result = Query(dbcon, sql, parameters);
- if (result.ExecuteNonQuery() == 1)
- returnval = true;
+ if (result.ExecuteNonQuery() == 1)
+ returnval = true;
- result.Dispose();
+ result.Dispose();
+ }
}
catch (Exception e)
{
@@ -828,12 +786,17 @@ namespace OpenSim.Data.MySQL
try
{
- IDbCommand result = Query(sql, parameters);
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
- if (result.ExecuteNonQuery() == 1)
- returnval = true;
+ IDbCommand result = Query(dbcon, sql, parameters);
- result.Dispose();
+ if (result.ExecuteNonQuery() == 1)
+ returnval = true;
+
+ result.Dispose();
+ }
}
catch (Exception e)
{
@@ -927,12 +890,17 @@ namespace OpenSim.Data.MySQL
bool returnval = false;
try
{
- IDbCommand result = Query(sql, parameters);
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
- if (result.ExecuteNonQuery() == 1)
- returnval = true;
+ IDbCommand result = Query(dbcon, sql, parameters);
- result.Dispose();
+ if (result.ExecuteNonQuery() == 1)
+ returnval = true;
+
+ result.Dispose();
+ }
}
catch (Exception e)
{
@@ -1030,18 +998,23 @@ namespace OpenSim.Data.MySQL
try
{
- IDbCommand result = Query(sql, parameters);
-
- // int x;
- // if ((x = result.ExecuteNonQuery()) > 0)
- // {
- // returnval = true;
- // }
- if (result.ExecuteNonQuery() > 0)
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
{
- returnval = true;
+ dbcon.Open();
+
+ IDbCommand result = Query(dbcon, sql, parameters);
+
+ // int x;
+ // if ((x = result.ExecuteNonQuery()) > 0)
+ // {
+ // returnval = true;
+ // }
+ if (result.ExecuteNonQuery() > 0)
+ {
+ returnval = true;
+ }
+ result.Dispose();
}
- result.Dispose();
}
catch (Exception e)
{
@@ -1070,18 +1043,23 @@ namespace OpenSim.Data.MySQL
{
parameters["?uuid"] = uuid;
- IDbCommand result = Query(sql, parameters);
-
- // int x;
- // if ((x = result.ExecuteNonQuery()) > 0)
- // {
- // returnval = true;
- // }
- if (result.ExecuteNonQuery() > 0)
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
{
- returnval = true;
+ dbcon.Open();
+
+ IDbCommand result = Query(dbcon, sql, parameters);
+
+ // int x;
+ // if ((x = result.ExecuteNonQuery()) > 0)
+ // {
+ // returnval = true;
+ // }
+ if (result.ExecuteNonQuery() > 0)
+ {
+ returnval = true;
+ }
+ result.Dispose();
}
- result.Dispose();
}
catch (Exception e)
{
@@ -1122,18 +1100,23 @@ namespace OpenSim.Data.MySQL
try
{
- IDbCommand result = Query(sql, parameters);
-
- // int x;
- // if ((x = result.ExecuteNonQuery()) > 0)
- // {
- // returnval = true;
- // }
- if (result.ExecuteNonQuery() > 0)
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
{
- returnval = true;
+ dbcon.Open();
+
+ IDbCommand result = Query(dbcon, sql, parameters);
+
+ // int x;
+ // if ((x = result.ExecuteNonQuery()) > 0)
+ // {
+ // returnval = true;
+ // }
+ if (result.ExecuteNonQuery() > 0)
+ {
+ returnval = true;
+ }
+ result.Dispose();
}
- result.Dispose();
}
catch (Exception e)
{
@@ -1167,45 +1150,51 @@ namespace OpenSim.Data.MySQL
bool returnval = false;
// we want to send in byte data, which means we can't just pass down strings
- try {
- MySqlCommand cmd = (MySqlCommand) dbcon.CreateCommand();
- cmd.CommandText = sql;
- cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString());
- cmd.Parameters.AddWithValue("?serial", appearance.Serial);
- cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams);
- cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes());
- cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight);
- cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString());
- cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString());
- cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString());
- cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString());
- cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString());
- cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString());
- cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString());
- cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString());
- cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString());
- cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString());
- cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString());
- cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString());
- cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString());
- cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString());
- cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString());
- cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString());
- cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString());
- cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString());
- cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString());
- cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString());
- cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString());
- cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString());
- cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString());
- cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString());
- cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString());
- cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString());
-
- if (cmd.ExecuteNonQuery() > 0)
- returnval = true;
-
- cmd.Dispose();
+ try
+ {
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
+
+ using (MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand())
+ {
+ cmd.CommandText = sql;
+ cmd.Parameters.AddWithValue("?owner", appearance.Owner.ToString());
+ cmd.Parameters.AddWithValue("?serial", appearance.Serial);
+ cmd.Parameters.AddWithValue("?visual_params", appearance.VisualParams);
+ cmd.Parameters.AddWithValue("?texture", appearance.Texture.GetBytes());
+ cmd.Parameters.AddWithValue("?avatar_height", appearance.AvatarHeight);
+ cmd.Parameters.AddWithValue("?body_item", appearance.BodyItem.ToString());
+ cmd.Parameters.AddWithValue("?body_asset", appearance.BodyAsset.ToString());
+ cmd.Parameters.AddWithValue("?skin_item", appearance.SkinItem.ToString());
+ cmd.Parameters.AddWithValue("?skin_asset", appearance.SkinAsset.ToString());
+ cmd.Parameters.AddWithValue("?hair_item", appearance.HairItem.ToString());
+ cmd.Parameters.AddWithValue("?hair_asset", appearance.HairAsset.ToString());
+ cmd.Parameters.AddWithValue("?eyes_item", appearance.EyesItem.ToString());
+ cmd.Parameters.AddWithValue("?eyes_asset", appearance.EyesAsset.ToString());
+ cmd.Parameters.AddWithValue("?shirt_item", appearance.ShirtItem.ToString());
+ cmd.Parameters.AddWithValue("?shirt_asset", appearance.ShirtAsset.ToString());
+ cmd.Parameters.AddWithValue("?pants_item", appearance.PantsItem.ToString());
+ cmd.Parameters.AddWithValue("?pants_asset", appearance.PantsAsset.ToString());
+ cmd.Parameters.AddWithValue("?shoes_item", appearance.ShoesItem.ToString());
+ cmd.Parameters.AddWithValue("?shoes_asset", appearance.ShoesAsset.ToString());
+ cmd.Parameters.AddWithValue("?socks_item", appearance.SocksItem.ToString());
+ cmd.Parameters.AddWithValue("?socks_asset", appearance.SocksAsset.ToString());
+ cmd.Parameters.AddWithValue("?jacket_item", appearance.JacketItem.ToString());
+ cmd.Parameters.AddWithValue("?jacket_asset", appearance.JacketAsset.ToString());
+ cmd.Parameters.AddWithValue("?gloves_item", appearance.GlovesItem.ToString());
+ cmd.Parameters.AddWithValue("?gloves_asset", appearance.GlovesAsset.ToString());
+ cmd.Parameters.AddWithValue("?undershirt_item", appearance.UnderShirtItem.ToString());
+ cmd.Parameters.AddWithValue("?undershirt_asset", appearance.UnderShirtAsset.ToString());
+ cmd.Parameters.AddWithValue("?underpants_item", appearance.UnderPantsItem.ToString());
+ cmd.Parameters.AddWithValue("?underpants_asset", appearance.UnderPantsAsset.ToString());
+ cmd.Parameters.AddWithValue("?skirt_item", appearance.SkirtItem.ToString());
+ cmd.Parameters.AddWithValue("?skirt_asset", appearance.SkirtAsset.ToString());
+
+ if (cmd.ExecuteNonQuery() > 0)
+ returnval = true;
+ }
+ }
}
catch (Exception e)
{
@@ -1221,33 +1210,38 @@ namespace OpenSim.Data.MySQL
{
string sql = "delete from avatarattachments where UUID = ?uuid";
- MySqlCommand cmd = (MySqlCommand) dbcon.CreateCommand();
- cmd.CommandText = sql;
- cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
+ using (MySqlConnection dbcon = new MySqlConnection(connectionString))
+ {
+ dbcon.Open();
+
+ MySqlCommand cmd = (MySqlCommand)dbcon.CreateCommand();
+ cmd.CommandText = sql;
+ cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
- cmd.ExecuteNonQuery();
+ cmd.ExecuteNonQuery();
- if (data == null)
- return;
+ if (data == null)
+ return;
- sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)";
+ sql = "insert into avatarattachments (UUID, attachpoint, item, asset) values (?uuid, ?attachpoint, ?item, ?asset)";
- cmd = (MySqlCommand) dbcon.CreateCommand();
- cmd.CommandText = sql;
+ cmd = (MySqlCommand)dbcon.CreateCommand();
+ cmd.CommandText = sql;
- foreach (DictionaryEntry e in data)
- {
- int attachpoint = Convert.ToInt32(e.Key);
+ foreach (DictionaryEntry e in data)
+ {
+ int attachpoint = Convert.ToInt32(e.Key);
- Hashtable item = (Hashtable)e.Value;
+ Hashtable item = (Hashtable)e.Value;
- cmd.Parameters.Clear();
- cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
- cmd.Parameters.AddWithValue("?attachpoint", attachpoint);
- cmd.Parameters.AddWithValue("?item", item["item"]);
- cmd.Parameters.AddWithValue("?asset", item["asset"]);
+ cmd.Parameters.Clear();
+ cmd.Parameters.AddWithValue("?uuid", agentID.ToString());
+ cmd.Parameters.AddWithValue("?attachpoint", attachpoint);
+ cmd.Parameters.AddWithValue("?item", item["item"]);
+ cmd.Parameters.AddWithValue("?asset", item["asset"]);
- cmd.ExecuteNonQuery();
+ cmd.ExecuteNonQuery();
+ }
}
}
}
--
cgit v1.1