aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLEstateData.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/MySQLEstateData.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 '')
-rw-r--r--OpenSim/Data/MySQL/MySQLEstateData.cs440
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
44 44
45 private const string m_waitTimeoutSelect = "select @@wait_timeout"; 45 private const string m_waitTimeoutSelect = "select @@wait_timeout";
46 46
47 private MySqlConnection m_connection;
48 private string m_connectionString; 47 private string m_connectionString;
49 private long m_waitTimeout; 48 private long m_waitTimeout;
50 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond; 49 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
@@ -67,24 +66,26 @@ namespace OpenSim.Data.MySQL
67 m_log.Debug("Exception: password not found in connection string\n" + e.ToString()); 66 m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
68 } 67 }
69 68
70 m_connection = new MySqlConnection(m_connectionString);
71 m_connection.Open();
72
73 GetWaitTimeout(); 69 GetWaitTimeout();
74 70
75 Assembly assem = GetType().Assembly; 71 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
76 Migration m = new Migration(m_connection, assem, "EstateStore"); 72 {
77 m.Update(); 73 dbcon.Open();
74
75 Assembly assem = GetType().Assembly;
76 Migration m = new Migration(dbcon, assem, "EstateStore");
77 m.Update();
78 78
79 Type t = typeof(EstateSettings); 79 Type t = typeof(EstateSettings);
80 m_Fields = t.GetFields(BindingFlags.NonPublic | 80 m_Fields = t.GetFields(BindingFlags.NonPublic |
81 BindingFlags.Instance | 81 BindingFlags.Instance |
82 BindingFlags.DeclaredOnly); 82 BindingFlags.DeclaredOnly);
83 83
84 foreach (FieldInfo f in m_Fields) 84 foreach (FieldInfo f in m_Fields)
85 { 85 {
86 if (f.Name.Substring(0, 2) == "m_") 86 if (f.Name.Substring(0, 2) == "m_")
87 m_FieldMap[f.Name.Substring(2)] = f; 87 m_FieldMap[f.Name.Substring(2)] = f;
88 }
88 } 89 }
89 } 90 }
90 91
@@ -95,47 +96,29 @@ namespace OpenSim.Data.MySQL
95 96
96 protected void GetWaitTimeout() 97 protected void GetWaitTimeout()
97 { 98 {
98 MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, 99 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
99 m_connection);
100
101 using (MySqlDataReader dbReader =
102 cmd.ExecuteReader(CommandBehavior.SingleRow))
103 { 100 {
104 if (dbReader.Read()) 101 dbcon.Open();
102
103 using (MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon))
105 { 104 {
106 m_waitTimeout 105 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
107 = Convert.ToInt32(dbReader["@@wait_timeout"]) * 106 {
108 TimeSpan.TicksPerSecond + m_waitTimeoutLeeway; 107 if (dbReader.Read())
108 {
109 m_waitTimeout
110 = Convert.ToInt32(dbReader["@@wait_timeout"]) *
111 TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
112 }
113 }
109 } 114 }
110 115
111 dbReader.Close(); 116 m_lastConnectionUse = DateTime.Now.Ticks;
112 cmd.Dispose();
113 }
114
115 m_lastConnectionUse = DateTime.Now.Ticks;
116
117 m_log.DebugFormat(
118 "[REGION DB]: Connection wait timeout {0} seconds",
119 m_waitTimeout / TimeSpan.TicksPerSecond);
120 }
121
122 protected void CheckConnection()
123 {
124 long timeNow = DateTime.Now.Ticks;
125 if (timeNow - m_lastConnectionUse > m_waitTimeout ||
126 m_connection.State != ConnectionState.Open)
127 {
128 m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
129 117
130 lock (m_connection) 118 m_log.DebugFormat(
131 { 119 "[REGION DB]: Connection wait timeout {0} seconds",
132 m_connection.Close(); 120 m_waitTimeout / TimeSpan.TicksPerSecond);
133 m_connection = new MySqlConnection(m_connectionString);
134 m_connection.Open();
135 }
136 } 121 }
137
138 m_lastConnectionUse = timeNow;
139 } 122 }
140 123
141 public EstateSettings LoadEstateSettings(UUID regionID) 124 public EstateSettings LoadEstateSettings(UUID regionID)
@@ -143,114 +126,111 @@ namespace OpenSim.Data.MySQL
143 EstateSettings es = new EstateSettings(); 126 EstateSettings es = new EstateSettings();
144 es.OnSave += StoreEstateSettings; 127 es.OnSave += StoreEstateSettings;
145 128
146 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"; 129 string sql = "select estate_settings." + String.Join(",estate_settings.", FieldList) +
130 " from estate_map left join estate_settings on estate_map.EstateID = estate_settings.EstateID where estate_settings.EstateID is not null and RegionID = ?RegionID";
147 131
148 CheckConnection(); 132 bool migration = true;
149 133
150 MySqlCommand cmd = m_connection.CreateCommand(); 134 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
151
152 cmd.CommandText = sql;
153 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
154
155 IDataReader r = cmd.ExecuteReader();
156
157 if (r.Read())
158 { 135 {
159 foreach (string name in FieldList) 136 dbcon.Open();
137
138 using (MySqlCommand cmd = dbcon.CreateCommand())
160 { 139 {
161 if (m_FieldMap[name].GetValue(es) is bool) 140 cmd.CommandText = sql;
162 { 141 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
163 int v = Convert.ToInt32(r[name]);
164 if (v != 0)
165 m_FieldMap[name].SetValue(es, true);
166 else
167 m_FieldMap[name].SetValue(es, false);
168 }
169 else if (m_FieldMap[name].GetValue(es) is UUID)
170 {
171 UUID uuid = UUID.Zero;
172 142
173 UUID.TryParse(r[name].ToString(), out uuid); 143 using (IDataReader r = cmd.ExecuteReader())
174 m_FieldMap[name].SetValue(es, uuid);
175 }
176 else
177 { 144 {
178 m_FieldMap[name].SetValue(es, r[name]); 145 if (r.Read())
146 {
147 migration = false;
148
149 foreach (string name in FieldList)
150 {
151 if (m_FieldMap[name].GetValue(es) is bool)
152 {
153 int v = Convert.ToInt32(r[name]);
154 if (v != 0)
155 m_FieldMap[name].SetValue(es, true);
156 else
157 m_FieldMap[name].SetValue(es, false);
158 }
159 else if (m_FieldMap[name].GetValue(es) is UUID)
160 {
161 UUID uuid = UUID.Zero;
162
163 UUID.TryParse(r[name].ToString(), out uuid);
164 m_FieldMap[name].SetValue(es, uuid);
165 }
166 else
167 {
168 m_FieldMap[name].SetValue(es, r[name]);
169 }
170 }
171 }
179 } 172 }
180 } 173 }
181 r.Close();
182 }
183 else
184 {
185 // Migration case
186 //
187 r.Close();
188
189 List<string> names = new List<string>(FieldList);
190 174
191 names.Remove("EstateID"); 175 if (migration)
176 {
177 // Migration case
178 List<string> names = new List<string>(FieldList);
192 179
193 sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")"; 180 names.Remove("EstateID");
194 181
195 cmd.CommandText = sql; 182 sql = "insert into estate_settings (" + String.Join(",", names.ToArray()) + ") values ( ?" + String.Join(", ?", names.ToArray()) + ")";
196 cmd.Parameters.Clear();
197 183
198 foreach (string name in FieldList) 184 using (MySqlCommand cmd = dbcon.CreateCommand())
199 {
200 if (m_FieldMap[name].GetValue(es) is bool)
201 {
202 if ((bool)m_FieldMap[name].GetValue(es))
203 cmd.Parameters.AddWithValue("?" + name, "1");
204 else
205 cmd.Parameters.AddWithValue("?" + name, "0");
206 }
207 else
208 { 185 {
209 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString()); 186 cmd.CommandText = sql;
187 cmd.Parameters.Clear();
188
189 foreach (string name in FieldList)
190 {
191 if (m_FieldMap[name].GetValue(es) is bool)
192 {
193 if ((bool)m_FieldMap[name].GetValue(es))
194 cmd.Parameters.AddWithValue("?" + name, "1");
195 else
196 cmd.Parameters.AddWithValue("?" + name, "0");
197 }
198 else
199 {
200 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
201 }
202 }
203
204 cmd.ExecuteNonQuery();
205
206 cmd.CommandText = "select LAST_INSERT_ID() as id";
207 cmd.Parameters.Clear();
208
209 using (IDataReader r = cmd.ExecuteReader())
210 {
211 r.Read();
212 es.EstateID = Convert.ToUInt32(r["id"]);
213 }
214
215 cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
216 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
217 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
218
219 // This will throw on dupe key
220 try { cmd.ExecuteNonQuery(); }
221 catch (Exception) { }
222
223 // Munge and transfer the ban list
224 cmd.Parameters.Clear();
225 cmd.CommandText = "insert into estateban select " + es.EstateID.ToString() + ", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = ?UUID";
226 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
227
228 try { cmd.ExecuteNonQuery(); }
229 catch (Exception) { }
230
231 es.Save();
210 } 232 }
211 } 233 }
212
213 cmd.ExecuteNonQuery();
214
215 cmd.CommandText = "select LAST_INSERT_ID() as id";
216 cmd.Parameters.Clear();
217
218 r = cmd.ExecuteReader();
219
220 r.Read();
221
222 es.EstateID = Convert.ToUInt32(r["id"]);
223
224 r.Close();
225
226 cmd.CommandText = "insert into estate_map values (?RegionID, ?EstateID)";
227 cmd.Parameters.AddWithValue("?RegionID", regionID.ToString());
228 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
229
230 // This will throw on dupe key
231 try
232 {
233 cmd.ExecuteNonQuery();
234 }
235 catch (Exception)
236 {
237 }
238
239 // Munge and transfer the ban list
240 //
241 cmd.Parameters.Clear();
242 cmd.CommandText = "insert into estateban select " + es.EstateID.ToString() + ", bannedUUID, bannedIp, bannedIpHostMask, '' from regionban where regionban.regionUUID = ?UUID";
243 cmd.Parameters.AddWithValue("?UUID", regionID.ToString());
244
245 try
246 {
247 cmd.ExecuteNonQuery();
248 }
249 catch (Exception)
250 {
251 }
252
253 es.Save();
254 } 234 }
255 235
256 LoadBanList(es); 236 LoadBanList(es);
@@ -265,29 +245,33 @@ namespace OpenSim.Data.MySQL
265 { 245 {
266 string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")"; 246 string sql = "replace into estate_settings (" + String.Join(",", FieldList) + ") values ( ?" + String.Join(", ?", FieldList) + ")";
267 247
268 CheckConnection(); 248 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
249 {
250 dbcon.Open();
269 251
270 MySqlCommand cmd = m_connection.CreateCommand(); 252 using (MySqlCommand cmd = dbcon.CreateCommand())
253 {
254 cmd.CommandText = sql;
271 255
272 cmd.CommandText = sql; 256 foreach (string name in FieldList)
257 {
258 if (m_FieldMap[name].GetValue(es) is bool)
259 {
260 if ((bool)m_FieldMap[name].GetValue(es))
261 cmd.Parameters.AddWithValue("?" + name, "1");
262 else
263 cmd.Parameters.AddWithValue("?" + name, "0");
264 }
265 else
266 {
267 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
268 }
269 }
273 270
274 foreach (string name in FieldList) 271 cmd.ExecuteNonQuery();
275 {
276 if (m_FieldMap[name].GetValue(es) is bool)
277 {
278 if ((bool)m_FieldMap[name].GetValue(es))
279 cmd.Parameters.AddWithValue("?" + name, "1");
280 else
281 cmd.Parameters.AddWithValue("?" + name, "0");
282 }
283 else
284 {
285 cmd.Parameters.AddWithValue("?" + name, m_FieldMap[name].GetValue(es).ToString());
286 } 272 }
287 } 273 }
288 274
289 cmd.ExecuteNonQuery();
290
291 SaveBanList(es); 275 SaveBanList(es);
292 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers); 276 SaveUUIDList(es.EstateID, "estate_managers", es.EstateManagers);
293 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess); 277 SaveUUIDList(es.EstateID, "estate_users", es.EstateAccess);
@@ -298,77 +282,89 @@ namespace OpenSim.Data.MySQL
298 { 282 {
299 es.ClearBans(); 283 es.ClearBans();
300 284
301 CheckConnection(); 285 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
302
303 MySqlCommand cmd = m_connection.CreateCommand();
304
305 cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
306 cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
307
308 IDataReader r = cmd.ExecuteReader();
309
310 while (r.Read())
311 { 286 {
312 EstateBan eb = new EstateBan(); 287 dbcon.Open();
313 288
314 UUID uuid = new UUID(); 289 using (MySqlCommand cmd = dbcon.CreateCommand())
315 UUID.TryParse(r["bannedUUID"].ToString(), out uuid); 290 {
291 cmd.CommandText = "select bannedUUID from estateban where EstateID = ?EstateID";
292 cmd.Parameters.AddWithValue("?EstateID", es.EstateID);
316 293
317 eb.BannedUserID = uuid; 294 using (IDataReader r = cmd.ExecuteReader())
318 eb.BannedHostAddress = "0.0.0.0"; 295 {
319 eb.BannedHostIPMask = "0.0.0.0"; 296 while (r.Read())
320 es.AddBan(eb); 297 {
298 EstateBan eb = new EstateBan();
299
300 UUID uuid = new UUID();
301 UUID.TryParse(r["bannedUUID"].ToString(), out uuid);
302
303 eb.BannedUserID = uuid;
304 eb.BannedHostAddress = "0.0.0.0";
305 eb.BannedHostIPMask = "0.0.0.0";
306 es.AddBan(eb);
307 }
308 }
309 }
321 } 310 }
322 r.Close();
323 } 311 }
324 312
325 private void SaveBanList(EstateSettings es) 313 private void SaveBanList(EstateSettings es)
326 { 314 {
327 CheckConnection(); 315 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
328 316 {
329 MySqlCommand cmd = m_connection.CreateCommand(); 317 dbcon.Open();
330 318
331 cmd.CommandText = "delete from estateban where EstateID = ?EstateID"; 319 using (MySqlCommand cmd = dbcon.CreateCommand())
332 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString()); 320 {
321 cmd.CommandText = "delete from estateban where EstateID = ?EstateID";
322 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
333 323
334 cmd.ExecuteNonQuery(); 324 cmd.ExecuteNonQuery();
335 325
336 cmd.Parameters.Clear(); 326 cmd.Parameters.Clear();
337 327
338 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )"; 328 cmd.CommandText = "insert into estateban (EstateID, bannedUUID, bannedIp, bannedIpHostMask, bannedNameMask) values ( ?EstateID, ?bannedUUID, '', '', '' )";
339 329
340 foreach (EstateBan b in es.EstateBans) 330 foreach (EstateBan b in es.EstateBans)
341 { 331 {
342 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString()); 332 cmd.Parameters.AddWithValue("?EstateID", es.EstateID.ToString());
343 cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString()); 333 cmd.Parameters.AddWithValue("?bannedUUID", b.BannedUserID.ToString());
344 334
345 cmd.ExecuteNonQuery(); 335 cmd.ExecuteNonQuery();
346 cmd.Parameters.Clear(); 336 cmd.Parameters.Clear();
337 }
338 }
347 } 339 }
348 } 340 }
349 341
350 void SaveUUIDList(uint EstateID, string table, UUID[] data) 342 void SaveUUIDList(uint EstateID, string table, UUID[] data)
351 { 343 {
352 CheckConnection(); 344 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
353 345 {
354 MySqlCommand cmd = m_connection.CreateCommand(); 346 dbcon.Open();
355 347
356 cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID"; 348 using (MySqlCommand cmd = dbcon.CreateCommand())
357 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString()); 349 {
350 cmd.CommandText = "delete from " + table + " where EstateID = ?EstateID";
351 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
358 352
359 cmd.ExecuteNonQuery(); 353 cmd.ExecuteNonQuery();
360 354
361 cmd.Parameters.Clear(); 355 cmd.Parameters.Clear();
362 356
363 cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )"; 357 cmd.CommandText = "insert into " + table + " (EstateID, uuid) values ( ?EstateID, ?uuid )";
364 358
365 foreach (UUID uuid in data) 359 foreach (UUID uuid in data)
366 { 360 {
367 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString()); 361 cmd.Parameters.AddWithValue("?EstateID", EstateID.ToString());
368 cmd.Parameters.AddWithValue("?uuid", uuid.ToString()); 362 cmd.Parameters.AddWithValue("?uuid", uuid.ToString());
369 363
370 cmd.ExecuteNonQuery(); 364 cmd.ExecuteNonQuery();
371 cmd.Parameters.Clear(); 365 cmd.Parameters.Clear();
366 }
367 }
372 } 368 }
373 } 369 }
374 370
@@ -376,25 +372,29 @@ namespace OpenSim.Data.MySQL
376 { 372 {
377 List<UUID> uuids = new List<UUID>(); 373 List<UUID> uuids = new List<UUID>();
378 374
379 CheckConnection(); 375 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
380 376 {
381 MySqlCommand cmd = m_connection.CreateCommand(); 377 dbcon.Open();
382
383 cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
384 cmd.Parameters.AddWithValue("?EstateID", EstateID);
385 378
386 IDataReader r = cmd.ExecuteReader(); 379 using (MySqlCommand cmd = dbcon.CreateCommand())
380 {
381 cmd.CommandText = "select uuid from " + table + " where EstateID = ?EstateID";
382 cmd.Parameters.AddWithValue("?EstateID", EstateID);
387 383
388 while (r.Read()) 384 using (IDataReader r = cmd.ExecuteReader())
389 { 385 {
390 // EstateBan eb = new EstateBan(); 386 while (r.Read())
387 {
388 // EstateBan eb = new EstateBan();
391 389
392 UUID uuid = new UUID(); 390 UUID uuid = new UUID();
393 UUID.TryParse(r["uuid"].ToString(), out uuid); 391 UUID.TryParse(r["uuid"].ToString(), out uuid);
394 392
395 uuids.Add(uuid); 393 uuids.Add(uuid);
394 }
395 }
396 }
396 } 397 }
397 r.Close();
398 398
399 return uuids.ToArray(); 399 return uuids.ToArray();
400 } 400 }