aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--OpenSim/Data/MySQL/MySQLAssetData.cs6
-rw-r--r--OpenSim/Data/MySQL/MySQLInventoryData.cs24
-rw-r--r--OpenSim/Data/MySQL/MySQLManager.cs113
3 files changed, 107 insertions, 36 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
index 21d730d..3557243 100644
--- a/OpenSim/Data/MySQL/MySQLAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -162,6 +162,8 @@ namespace OpenSim.Data.MySQL
162 AssetBase asset = null; 162 AssetBase asset = null;
163 lock (_dbConnection) 163 lock (_dbConnection)
164 { 164 {
165 _dbConnection.CheckConnection();
166
165 MySqlCommand cmd = 167 MySqlCommand cmd =
166 new MySqlCommand( 168 new MySqlCommand(
167 "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id", 169 "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id",
@@ -213,6 +215,8 @@ namespace OpenSim.Data.MySQL
213 return; 215 return;
214 } 216 }
215 217
218 _dbConnection.CheckConnection();
219
216 MySqlCommand cmd = 220 MySqlCommand cmd =
217 new MySqlCommand( 221 new MySqlCommand(
218 "REPLACE INTO assets(id, name, description, assetType, local, temporary, data)" + 222 "REPLACE INTO assets(id, name, description, assetType, local, temporary, data)" +
@@ -266,6 +270,8 @@ namespace OpenSim.Data.MySQL
266 270
267 lock (_dbConnection) 271 lock (_dbConnection)
268 { 272 {
273 _dbConnection.CheckConnection();
274
269 MySqlCommand cmd = 275 MySqlCommand cmd =
270 new MySqlCommand( 276 new MySqlCommand(
271 "SELECT id FROM assets WHERE id=?id", 277 "SELECT id FROM assets WHERE id=?id",
diff --git a/OpenSim/Data/MySQL/MySQLInventoryData.cs b/OpenSim/Data/MySQL/MySQLInventoryData.cs
index 911958c..5bde40a 100644
--- a/OpenSim/Data/MySQL/MySQLInventoryData.cs
+++ b/OpenSim/Data/MySQL/MySQLInventoryData.cs
@@ -219,6 +219,8 @@ namespace OpenSim.Data.MySQL
219 { 219 {
220 List<InventoryItemBase> items = new List<InventoryItemBase>(); 220 List<InventoryItemBase> items = new List<InventoryItemBase>();
221 221
222 database.CheckConnection();
223
222 MySqlCommand result = 224 MySqlCommand result =
223 new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid", 225 new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid",
224 database.Connection); 226 database.Connection);
@@ -253,6 +255,8 @@ namespace OpenSim.Data.MySQL
253 { 255 {
254 lock (database) 256 lock (database)
255 { 257 {
258 database.CheckConnection();
259
256 MySqlCommand result = 260 MySqlCommand result =
257 new MySqlCommand( 261 new MySqlCommand(
258 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", 262 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
@@ -292,6 +296,8 @@ namespace OpenSim.Data.MySQL
292 { 296 {
293 lock (database) 297 lock (database)
294 { 298 {
299 database.CheckConnection();
300
295 MySqlCommand result = 301 MySqlCommand result =
296 new MySqlCommand( 302 new MySqlCommand(
297 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid", 303 "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
@@ -344,6 +350,8 @@ namespace OpenSim.Data.MySQL
344 { 350 {
345 lock (database) 351 lock (database)
346 { 352 {
353 database.CheckConnection();
354
347 MySqlCommand result = 355 MySqlCommand result =
348 new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid", 356 new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid",
349 database.Connection); 357 database.Connection);
@@ -421,6 +429,8 @@ namespace OpenSim.Data.MySQL
421 { 429 {
422 lock (database) 430 lock (database)
423 { 431 {
432 database.CheckConnection();
433
424 MySqlCommand result = 434 MySqlCommand result =
425 new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection); 435 new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection);
426 result.Parameters.AddWithValue("?uuid", itemID.ToString()); 436 result.Parameters.AddWithValue("?uuid", itemID.ToString());
@@ -482,6 +492,8 @@ namespace OpenSim.Data.MySQL
482 { 492 {
483 lock (database) 493 lock (database)
484 { 494 {
495 database.CheckConnection();
496
485 MySqlCommand result = 497 MySqlCommand result =
486 new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection); 498 new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection);
487 result.Parameters.AddWithValue("?uuid", folderID.ToString()); 499 result.Parameters.AddWithValue("?uuid", folderID.ToString());
@@ -522,6 +534,8 @@ namespace OpenSim.Data.MySQL
522 534
523 try 535 try
524 { 536 {
537 database.CheckConnection();
538
525 MySqlCommand result = new MySqlCommand(sql, database.Connection); 539 MySqlCommand result = new MySqlCommand(sql, database.Connection);
526 result.Parameters.AddWithValue("?inventoryID", item.ID.ToString()); 540 result.Parameters.AddWithValue("?inventoryID", item.ID.ToString());
527 result.Parameters.AddWithValue("?assetID", item.AssetID.ToString()); 541 result.Parameters.AddWithValue("?assetID", item.AssetID.ToString());
@@ -574,6 +588,8 @@ namespace OpenSim.Data.MySQL
574 { 588 {
575 try 589 try
576 { 590 {
591 database.CheckConnection();
592
577 MySqlCommand cmd = 593 MySqlCommand cmd =
578 new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection); 594 new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection);
579 cmd.Parameters.AddWithValue("?uuid", itemID.ToString()); 595 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
@@ -600,6 +616,8 @@ namespace OpenSim.Data.MySQL
600 "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES "; 616 "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES ";
601 sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)"; 617 sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)";
602 618
619 database.CheckConnection();
620
603 MySqlCommand cmd = new MySqlCommand(sql, database.Connection); 621 MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
604 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); 622 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
605 cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString()); 623 cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString());
@@ -640,6 +658,8 @@ namespace OpenSim.Data.MySQL
640 string sql = 658 string sql =
641 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID"; 659 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
642 660
661 database.CheckConnection();
662
643 MySqlCommand cmd = new MySqlCommand(sql, database.Connection); 663 MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
644 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString()); 664 cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
645 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString()); 665 cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString());
@@ -695,6 +715,8 @@ namespace OpenSim.Data.MySQL
695 { 715 {
696 try 716 try
697 { 717 {
718 database.CheckConnection();
719
698 MySqlCommand cmd = 720 MySqlCommand cmd =
699 new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection); 721 new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection);
700 cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); 722 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
@@ -719,6 +741,8 @@ namespace OpenSim.Data.MySQL
719 { 741 {
720 try 742 try
721 { 743 {
744 database.CheckConnection();
745
722 MySqlCommand cmd = 746 MySqlCommand cmd =
723 new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection); 747 new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection);
724 cmd.Parameters.AddWithValue("?uuid", folderID.ToString()); 748 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
diff --git a/OpenSim/Data/MySQL/MySQLManager.cs b/OpenSim/Data/MySQL/MySQLManager.cs
index 61fd039..cf4bce3 100644
--- a/OpenSim/Data/MySQL/MySQLManager.cs
+++ b/OpenSim/Data/MySQL/MySQLManager.cs
@@ -54,6 +54,24 @@ namespace OpenSim.Data.MySQL
54 /// </summary> 54 /// </summary>
55 private string connectionString; 55 private string connectionString;
56 56
57 private const string m_waitTimeoutSelect = "select @@wait_timeout";
58
59 /// <summary>
60 /// Wait timeout for our connection in ticks.
61 /// </summary>
62 private long m_waitTimeout;
63
64 /// <summary>
65 /// Make our storage of the timeout this amount smaller than it actually is, to give us a margin on long
66 /// running database operations.
67 /// </summary>
68 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
69
70 /// <summary>
71 /// Holds the last tick time that the connection was used.
72 /// </summary>
73 private long m_lastConnectionUse;
74
57 /// <summary> 75 /// <summary>
58 /// Initialises and creates a new MySQL connection and maintains it. 76 /// Initialises and creates a new MySQL connection and maintains it.
59 /// </summary> 77 /// </summary>
@@ -102,6 +120,7 @@ namespace OpenSim.Data.MySQL
102 } 120 }
103 121
104 m_log.Info("[MYSQL]: Connection established"); 122 m_log.Info("[MYSQL]: Connection established");
123 GetWaitTimeout();
105 } 124 }
106 catch (Exception e) 125 catch (Exception e)
107 { 126 {
@@ -110,6 +129,51 @@ namespace OpenSim.Data.MySQL
110 } 129 }
111 130
112 /// <summary> 131 /// <summary>
132 /// Get the wait_timeout value for our connection
133 /// </summary>
134 protected void GetWaitTimeout()
135 {
136 MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon);
137
138 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
139 {
140 if (dbReader.Read())
141 {
142 m_waitTimeout
143 = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
144 }
145
146 dbReader.Close();
147 cmd.Dispose();
148 }
149
150 m_lastConnectionUse = System.DateTime.Now.Ticks;
151
152 m_log.DebugFormat(
153 "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond);
154 }
155
156 /// <summary>
157 /// Should be called before any db operation. This checks to see if the connection has not timed out
158 /// </summary>
159 public void CheckConnection()
160 {
161 //m_log.Debug("[REGION DB]: Checking connection");
162
163 long timeNow = System.DateTime.Now.Ticks;
164 if (timeNow - m_lastConnectionUse > m_waitTimeout || dbcon.State != ConnectionState.Open)
165 {
166 m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
167 Reconnect();
168 }
169
170 // Strictly, we should set this after the actual db operation. But it's more convenient to set here rather
171 // than require the code to call another method - the timeout leeway should be large enough to cover the
172 // inaccuracy.
173 m_lastConnectionUse = timeNow;
174 }
175
176 /// <summary>
113 /// Get the connection being used 177 /// Get the connection being used
114 /// </summary> 178 /// </summary>
115 /// <returns>MySqlConnection Object</returns> 179 /// <returns>MySqlConnection Object</returns>
@@ -132,6 +196,8 @@ namespace OpenSim.Data.MySQL
132 /// </summary> 196 /// </summary>
133 public void Reconnect() 197 public void Reconnect()
134 { 198 {
199 m_log.Info("[REGION DB] Reconnecting database");
200
135 lock (dbcon) 201 lock (dbcon)
136 { 202 {
137 try 203 try
@@ -197,6 +263,7 @@ namespace OpenSim.Data.MySQL
197 /// <param name="name">name of embedded resource</param> 263 /// <param name="name">name of embedded resource</param>
198 public void ExecuteResourceSql(string name) 264 public void ExecuteResourceSql(string name)
199 { 265 {
266 CheckConnection();
200 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon); 267 MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
201 cmd.ExecuteNonQuery(); 268 cmd.ExecuteNonQuery();
202 } 269 }
@@ -207,6 +274,7 @@ namespace OpenSim.Data.MySQL
207 /// <param name="sql">sql string to execute</param> 274 /// <param name="sql">sql string to execute</param>
208 public void ExecuteSql(string sql) 275 public void ExecuteSql(string sql)
209 { 276 {
277 CheckConnection();
210 MySqlCommand cmd = new MySqlCommand(sql, dbcon); 278 MySqlCommand cmd = new MySqlCommand(sql, dbcon);
211 cmd.ExecuteNonQuery(); 279 cmd.ExecuteNonQuery();
212 } 280 }
@@ -219,11 +287,14 @@ namespace OpenSim.Data.MySQL
219 { 287 {
220 lock (dbcon) 288 lock (dbcon)
221 { 289 {
290 CheckConnection();
291
222 MySqlCommand tablesCmd = 292 MySqlCommand tablesCmd =
223 new MySqlCommand( 293 new MySqlCommand(
224 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", 294 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
225 dbcon); 295 dbcon);
226 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); 296 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
297
227 using (MySqlDataReader tables = tablesCmd.ExecuteReader()) 298 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
228 { 299 {
229 while (tables.Read()) 300 while (tables.Read())
@@ -259,6 +330,8 @@ namespace OpenSim.Data.MySQL
259 { 330 {
260 try 331 try
261 { 332 {
333 CheckConnection(); // Not sure if this one is necessary
334
262 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand(); 335 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
263 dbcommand.CommandText = sql; 336 dbcommand.CommandText = sql;
264 foreach (KeyValuePair<string, string> param in parameters) 337 foreach (KeyValuePair<string, string> param in parameters)
@@ -268,43 +341,11 @@ namespace OpenSim.Data.MySQL
268 341
269 return (IDbCommand) dbcommand; 342 return (IDbCommand) dbcommand;
270 } 343 }
271 catch 344 catch (Exception e)
272 { 345 {
273 lock (dbcon) 346 // Return null if it fails.
274 { 347 m_log.Error("Failed during Query generation: " + e.ToString());
275 // Close the DB connection 348 return null;
276 dbcon.Close();
277
278 // Try to reopen it
279 try
280 {
281 dbcon = new MySqlConnection(connectionString);
282 dbcon.Open();
283 }
284 catch (Exception e)
285 {
286 m_log.Error("Unable to reconnect to database " + e);
287 }
288
289 // Run the query again
290 try
291 {
292 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
293 dbcommand.CommandText = sql;
294 foreach (KeyValuePair<string, string> param in parameters)
295 {
296 dbcommand.Parameters.AddWithValue(param.Key, param.Value);
297 }
298
299 return (IDbCommand) dbcommand;
300 }
301 catch (Exception e)
302 {
303 // Return null if it fails.
304 m_log.Error("Failed during Query generation: " + e.ToString());
305 return null;
306 }
307 }
308 } 349 }
309 } 350 }
310 351