aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLGridData.cs
diff options
context:
space:
mode:
authorMaster ScienceSim2010-02-04 13:19:30 -0800
committerJohn Hurliman2010-02-05 18:07:59 -0800
commite1b5c612472b9d1acf47383c0bf75b555daff2e6 (patch)
tree083896698038fbdad59c2bd3adeba9b290c5ce1b /OpenSim/Data/MySQL/MySQLGridData.cs
parentFixing an incorrect logging message in insertUserRow (diff)
downloadopensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.zip
opensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.gz
opensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.bz2
opensim-SC_OLD-e1b5c612472b9d1acf47383c0bf75b555daff2e6.tar.xz
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.
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLGridData.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLGridData.cs338
1 files changed, 125 insertions, 213 deletions
diff --git a/OpenSim/Data/MySQL/MySQLGridData.cs b/OpenSim/Data/MySQL/MySQLGridData.cs
index 1ec2609..f4e7b85 100644
--- a/OpenSim/Data/MySQL/MySQLGridData.cs
+++ b/OpenSim/Data/MySQL/MySQLGridData.cs
@@ -31,6 +31,7 @@ using System.Data;
31using System.Reflection; 31using System.Reflection;
32using System.Threading; 32using System.Threading;
33using log4net; 33using log4net;
34using MySql.Data.MySqlClient;
34using OpenMetaverse; 35using OpenMetaverse;
35using OpenSim.Framework; 36using OpenSim.Framework;
36 37
@@ -43,49 +44,9 @@ namespace OpenSim.Data.MySQL
43 { 44 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 45 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 46
46 /// <summary> 47 private MySQLManager m_database;
47 /// MySQL Database Manager 48 private object m_dbLock = new object();
48 /// </summary> 49 private string m_connectionString;
49 private MySQLManager database;
50
51
52 /// <summary>
53 /// Better DB manager. Swap-in replacement too.
54 /// </summary>
55 public Dictionary<int, MySQLSuperManager> m_dbconnections = new Dictionary<int, MySQLSuperManager>();
56
57 public int m_maxConnections = 10;
58 public int m_lastConnect;
59
60 public MySQLSuperManager GetLockedConnection()
61 {
62 int lockedCons = 0;
63 while (true)
64 {
65 m_lastConnect++;
66
67 // Overflow protection
68 if (m_lastConnect == int.MaxValue)
69 m_lastConnect = 0;
70
71 MySQLSuperManager x = m_dbconnections[m_lastConnect % m_maxConnections];
72 if (!x.Locked)
73 {
74 x.GetLock();
75 return x;
76 }
77
78 lockedCons++;
79 if (lockedCons > m_maxConnections)
80 {
81 lockedCons = 0;
82 Thread.Sleep(1000); // Wait some time before searching them again.
83 m_log.Debug(
84 "WARNING: All threads are in use. Probable cause: Something didnt release a mutex properly, or high volume of requests inbound.");
85 }
86 }
87 }
88
89 50
90 override public void Initialise() 51 override public void Initialise()
91 { 52 {
@@ -106,49 +67,17 @@ namespace OpenSim.Data.MySQL
106 /// <param name="connect">connect string.</param> 67 /// <param name="connect">connect string.</param>
107 override public void Initialise(string connect) 68 override public void Initialise(string connect)
108 { 69 {
109 if (connect != String.Empty) 70 m_connectionString = connect;
110 { 71 m_database = new MySQLManager(connect);
111 database = new MySQLManager(connect);
112 72
113 m_log.Info("Creating " + m_maxConnections + " DB connections..."); 73 // This actually does the roll forward assembly stuff
114 for (int i = 0; i < m_maxConnections; i++) 74 Assembly assem = GetType().Assembly;
115 {
116 m_log.Info("Connecting to DB... [" + i + "]");
117 MySQLSuperManager msm = new MySQLSuperManager();
118 msm.Manager = new MySQLManager(connect);
119 m_dbconnections.Add(i, msm);
120 }
121 75
122 } 76 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
123 else
124 { 77 {
125 m_log.Warn("Using deprecated mysql_connection.ini. Please update database_connect in GridServer_Config.xml and we'll use that instead"); 78 Migration m = new Migration(dbcon, assem, "GridStore");
126 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); 79 m.Update();
127 string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
128 string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
129 string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
130 string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
131 string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
132 string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
133
134 database = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
135 settingPooling, settingPort);
136
137 m_log.Info("Creating " + m_maxConnections + " DB connections...");
138 for (int i = 0; i < m_maxConnections; i++)
139 {
140 m_log.Info("Connecting to DB... [" + i + "]");
141 MySQLSuperManager msm = new MySQLSuperManager();
142 msm.Manager = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
143 settingPooling, settingPort);
144 m_dbconnections.Add(i, msm);
145 }
146 } 80 }
147
148 // This actually does the roll forward assembly stuff
149 Assembly assem = GetType().Assembly;
150 Migration m = new Migration(database.Connection, assem, "GridStore");
151 m.Update();
152 } 81 }
153 82
154 /// <summary> 83 /// <summary>
@@ -156,7 +85,6 @@ namespace OpenSim.Data.MySQL
156 /// </summary> 85 /// </summary>
157 override public void Dispose() 86 override public void Dispose()
158 { 87 {
159 database.Close();
160 } 88 }
161 89
162 /// <summary> 90 /// <summary>
@@ -187,8 +115,6 @@ namespace OpenSim.Data.MySQL
187 /// <returns>Array of sim profiles</returns> 115 /// <returns>Array of sim profiles</returns>
188 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax) 116 override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
189 { 117 {
190 MySQLSuperManager dbm = GetLockedConnection();
191
192 try 118 try
193 { 119 {
194 Dictionary<string, object> param = new Dictionary<string, object>(); 120 Dictionary<string, object> param = new Dictionary<string, object>();
@@ -197,35 +123,33 @@ namespace OpenSim.Data.MySQL
197 param["?xmax"] = xmax.ToString(); 123 param["?xmax"] = xmax.ToString();
198 param["?ymax"] = ymax.ToString(); 124 param["?ymax"] = ymax.ToString();
199 125
200 IDbCommand result = 126 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
201 dbm.Manager.Query( 127 {
202 "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax", 128 dbcon.Open();
203 param); 129
204 IDataReader reader = result.ExecuteReader(); 130 using (IDbCommand result = m_database.Query(dbcon,
131 "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
132 param))
133 {
134 using (IDataReader reader = result.ExecuteReader())
135 {
136 RegionProfileData row;
205 137
206 RegionProfileData row; 138 List<RegionProfileData> rows = new List<RegionProfileData>();
207 139
208 List<RegionProfileData> rows = new List<RegionProfileData>(); 140 while ((row = m_database.readSimRow(reader)) != null)
141 rows.Add(row);
209 142
210 while ((row = dbm.Manager.readSimRow(reader)) != null) 143 return rows.ToArray();
211 { 144 }
212 rows.Add(row); 145 }
213 } 146 }
214 reader.Close();
215 result.Dispose();
216
217 return rows.ToArray();
218 } 147 }
219 catch (Exception e) 148 catch (Exception e)
220 { 149 {
221 dbm.Manager.Reconnect(); 150 m_log.Error(e.Message, e);
222 m_log.Error(e.ToString());
223 return null; 151 return null;
224 } 152 }
225 finally
226 {
227 dbm.Release();
228 }
229 } 153 }
230 154
231 /// <summary> 155 /// <summary>
@@ -236,42 +160,38 @@ namespace OpenSim.Data.MySQL
236 /// <returns>A list of sim profiles</returns> 160 /// <returns>A list of sim profiles</returns>
237 override public List<RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum) 161 override public List<RegionProfileData> GetRegionsByName(string namePrefix, uint maxNum)
238 { 162 {
239 MySQLSuperManager dbm = GetLockedConnection();
240
241 try 163 try
242 { 164 {
243 Dictionary<string, object> param = new Dictionary<string, object>(); 165 Dictionary<string, object> param = new Dictionary<string, object>();
244 param["?name"] = namePrefix + "%"; 166 param["?name"] = namePrefix + "%";
245 167
246 IDbCommand result = 168 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
247 dbm.Manager.Query( 169 {
170 dbcon.Open();
171
172 using (IDbCommand result = m_database.Query(dbcon,
248 "SELECT * FROM regions WHERE regionName LIKE ?name", 173 "SELECT * FROM regions WHERE regionName LIKE ?name",
249 param); 174 param))
250 IDataReader reader = result.ExecuteReader(); 175 {
176 using (IDataReader reader = result.ExecuteReader())
177 {
178 RegionProfileData row;
251 179
252 RegionProfileData row; 180 List<RegionProfileData> rows = new List<RegionProfileData>();
253 181
254 List<RegionProfileData> rows = new List<RegionProfileData>(); 182 while (rows.Count < maxNum && (row = m_database.readSimRow(reader)) != null)
183 rows.Add(row);
255 184
256 while (rows.Count < maxNum && (row = dbm.Manager.readSimRow(reader)) != null) 185 return rows;
257 { 186 }
258 rows.Add(row); 187 }
259 } 188 }
260 reader.Close();
261 result.Dispose();
262
263 return rows;
264 } 189 }
265 catch (Exception e) 190 catch (Exception e)
266 { 191 {
267 dbm.Manager.Reconnect(); 192 m_log.Error(e.Message, e);
268 m_log.Error(e.ToString());
269 return null; 193 return null;
270 } 194 }
271 finally
272 {
273 dbm.Release();
274 }
275 } 195 }
276 196
277 /// <summary> 197 /// <summary>
@@ -281,32 +201,30 @@ namespace OpenSim.Data.MySQL
281 /// <returns>Sim profile</returns> 201 /// <returns>Sim profile</returns>
282 override public RegionProfileData GetProfileByHandle(ulong handle) 202 override public RegionProfileData GetProfileByHandle(ulong handle)
283 { 203 {
284 MySQLSuperManager dbm = GetLockedConnection();
285
286 try 204 try
287 { 205 {
288 Dictionary<string, object> param = new Dictionary<string, object>(); 206 Dictionary<string, object> param = new Dictionary<string, object>();
289 param["?handle"] = handle.ToString(); 207 param["?handle"] = handle.ToString();
290
291 IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE regionHandle = ?handle", param);
292 IDataReader reader = result.ExecuteReader();
293 208
294 RegionProfileData row = dbm.Manager.readSimRow(reader); 209 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
295 reader.Close(); 210 {
296 result.Dispose(); 211 dbcon.Open();
297 212
298 return row; 213 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE regionHandle = ?handle", param))
214 {
215 using (IDataReader reader = result.ExecuteReader())
216 {
217 RegionProfileData row = m_database.readSimRow(reader);
218 return row;
219 }
220 }
299 } 221 }
222 }
300 catch (Exception e) 223 catch (Exception e)
301 { 224 {
302 dbm.Manager.Reconnect(); 225 m_log.Error(e.Message, e);
303 m_log.Error(e.ToString());
304 return null; 226 return null;
305 } 227 }
306 finally
307 {
308 dbm.Release();
309 }
310 } 228 }
311 229
312 /// <summary> 230 /// <summary>
@@ -316,30 +234,29 @@ namespace OpenSim.Data.MySQL
316 /// <returns>The sim profile</returns> 234 /// <returns>The sim profile</returns>
317 override public RegionProfileData GetProfileByUUID(UUID uuid) 235 override public RegionProfileData GetProfileByUUID(UUID uuid)
318 { 236 {
319 MySQLSuperManager dbm = GetLockedConnection();
320
321 try 237 try
322 { 238 {
323 Dictionary<string, object> param = new Dictionary<string, object>(); 239 Dictionary<string, object> param = new Dictionary<string, object>();
324 param["?uuid"] = uuid.ToString(); 240 param["?uuid"] = uuid.ToString();
325 241
326 IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE uuid = ?uuid", param); 242 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
327 IDataReader reader = result.ExecuteReader(); 243 {
328 244 dbcon.Open();
329 RegionProfileData row = dbm.Manager.readSimRow(reader);
330 reader.Close();
331 result.Dispose();
332 245
333 return row; 246 using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE uuid = ?uuid", param))
247 {
248 using (IDataReader reader = result.ExecuteReader())
249 {
250 RegionProfileData row = m_database.readSimRow(reader);
251 return row;
252 }
253 }
334 } 254 }
255 }
335 catch (Exception e) 256 catch (Exception e)
336 { 257 {
337 dbm.Manager.Reconnect(); 258 m_log.Error(e.Message, e);
338 m_log.Error(e.ToString());
339 return null; 259 return null;
340 } finally
341 {
342 dbm.Release();
343 } 260 }
344 } 261 }
345 262
@@ -351,37 +268,36 @@ namespace OpenSim.Data.MySQL
351 { 268 {
352 if (regionName.Length > 2) 269 if (regionName.Length > 2)
353 { 270 {
354 MySQLSuperManager dbm = GetLockedConnection();
355
356 try 271 try
357 { 272 {
358 Dictionary<string, object> param = new Dictionary<string, object>(); 273 Dictionary<string, object> param = new Dictionary<string, object>();
359 // Add % because this is a like query. 274 // Add % because this is a like query.
360 param["?regionName"] = regionName + "%"; 275 param["?regionName"] = regionName + "%";
361 // Order by statement will return shorter matches first. Only returns one record or no record.
362 IDbCommand result =
363 dbm.Manager.Query(
364 "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
365 param);
366 IDataReader reader = result.ExecuteReader();
367 276
368 RegionProfileData row = dbm.Manager.readSimRow(reader); 277 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
369 reader.Close(); 278 {
370 result.Dispose(); 279 dbcon.Open();
371 280
372 return row; 281 // Order by statement will return shorter matches first. Only returns one record or no record.
282 using (IDbCommand result = m_database.Query(dbcon,
283 "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
284 param))
285 {
286 using (IDataReader reader = result.ExecuteReader())
287 {
288 RegionProfileData row = m_database.readSimRow(reader);
289 return row;
290 }
291 }
292 }
373 } 293 }
374 catch (Exception e) 294 catch (Exception e)
375 { 295 {
376 dbm.Manager.Reconnect(); 296 m_log.Error(e.Message, e);
377 m_log.Error(e.ToString());
378 return null; 297 return null;
379 } 298 }
380 finally
381 {
382 dbm.Release();
383 }
384 } 299 }
300
385 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters"); 301 m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
386 return null; 302 return null;
387 } 303 }
@@ -393,17 +309,16 @@ namespace OpenSim.Data.MySQL
393 /// <returns>Successful?</returns> 309 /// <returns>Successful?</returns>
394 override public DataResponse StoreProfile(RegionProfileData profile) 310 override public DataResponse StoreProfile(RegionProfileData profile)
395 { 311 {
396 MySQLSuperManager dbm = GetLockedConnection(); 312 try
397 try { 313 {
398 if (dbm.Manager.insertRegion(profile)) 314 if (m_database.insertRegion(profile))
399 {
400 return DataResponse.RESPONSE_OK; 315 return DataResponse.RESPONSE_OK;
401 } 316 else
402 return DataResponse.RESPONSE_ERROR; 317 return DataResponse.RESPONSE_ERROR;
403 } 318 }
404 finally 319 catch
405 { 320 {
406 dbm.Release(); 321 return DataResponse.RESPONSE_ERROR;
407 } 322 }
408 } 323 }
409 324
@@ -415,18 +330,16 @@ namespace OpenSim.Data.MySQL
415 //public DataResponse DeleteProfile(RegionProfileData profile) 330 //public DataResponse DeleteProfile(RegionProfileData profile)
416 override public DataResponse DeleteProfile(string uuid) 331 override public DataResponse DeleteProfile(string uuid)
417 { 332 {
418 MySQLSuperManager dbm = GetLockedConnection(); 333 try
419 334 {
420 335 if (m_database.deleteRegion(uuid))
421 try {
422 if (dbm.Manager.deleteRegion(uuid))
423 {
424 return DataResponse.RESPONSE_OK; 336 return DataResponse.RESPONSE_OK;
425 } 337 else
426 return DataResponse.RESPONSE_ERROR; 338 return DataResponse.RESPONSE_ERROR;
427 } finally 339 }
340 catch
428 { 341 {
429 dbm.Release(); 342 return DataResponse.RESPONSE_ERROR;
430 } 343 }
431 } 344 }
432 345
@@ -477,33 +390,32 @@ namespace OpenSim.Data.MySQL
477 /// <returns></returns> 390 /// <returns></returns>
478 override public ReservationData GetReservationAtPoint(uint x, uint y) 391 override public ReservationData GetReservationAtPoint(uint x, uint y)
479 { 392 {
480 MySQLSuperManager dbm = GetLockedConnection();
481
482 try 393 try
483 { 394 {
484 Dictionary<string, object> param = new Dictionary<string, object>(); 395 Dictionary<string, object> param = new Dictionary<string, object>();
485 param["?x"] = x.ToString(); 396 param["?x"] = x.ToString();
486 param["?y"] = y.ToString(); 397 param["?y"] = y.ToString();
487 IDbCommand result = 398
488 dbm.Manager.Query( 399 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
489 "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y", 400 {
490 param); 401 dbcon.Open();
491 IDataReader reader = result.ExecuteReader(); 402
492 403 using (IDbCommand result = m_database.Query(dbcon,
493 ReservationData row = dbm.Manager.readReservationRow(reader); 404 "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
494 reader.Close(); 405 param))
495 result.Dispose(); 406 {
496 407 using (IDataReader reader = result.ExecuteReader())
497 return row; 408 {
409 ReservationData row = m_database.readReservationRow(reader);
410 return row;
411 }
412 }
413 }
498 } 414 }
499 catch (Exception e) 415 catch (Exception e)
500 { 416 {
501 dbm.Manager.Reconnect(); 417 m_log.Error(e.Message, e);
502 m_log.Error(e.ToString());
503 return null; 418 return null;
504 } finally
505 {
506 dbm.Release();
507 } 419 }
508 } 420 }
509 } 421 }