aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLAssetData.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/MySQLAssetData.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/MySQLAssetData.cs355
1 files changed, 158 insertions, 197 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
index 6a4ccd7..666c22f 100644
--- a/OpenSim/Data/MySQL/MySQLAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -43,10 +43,13 @@ namespace OpenSim.Data.MySQL
43 { 43 {
44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType); 44 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
45 45
46 private MySQLManager _dbConnection; 46 private string m_connectionString;
47 private object m_dbLock = new object();
47 48
48 #region IPlugin Members 49 #region IPlugin Members
49 50
51 public override string Version { get { return "1.0.0.0"; } }
52
50 /// <summary> 53 /// <summary>
51 /// <para>Initialises Asset interface</para> 54 /// <para>Initialises Asset interface</para>
52 /// <para> 55 /// <para>
@@ -58,63 +61,29 @@ namespace OpenSim.Data.MySQL
58 /// </para> 61 /// </para>
59 /// </summary> 62 /// </summary>
60 /// <param name="connect">connect string</param> 63 /// <param name="connect">connect string</param>
61 override public void Initialise(string connect) 64 public override void Initialise(string connect)
62 { 65 {
63 // TODO: This will let you pass in the connect string in 66 m_connectionString = connect;
64 // the config, though someone will need to write that.
65 if (connect == String.Empty)
66 {
67 // This is old seperate config file
68 m_log.Warn("no connect string, using old mysql_connection.ini instead");
69 Initialise();
70 }
71 else
72 {
73 _dbConnection = new MySQLManager(connect);
74 }
75 67
76 // This actually does the roll forward assembly stuff 68 // This actually does the roll forward assembly stuff
77 Assembly assem = GetType().Assembly; 69 Assembly assem = GetType().Assembly;
78 Migration m = new Migration(_dbConnection.Connection, assem, "AssetStore");
79 70
80 m.Update(); 71 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
72 {
73 dbcon.Open();
74 Migration m = new Migration(dbcon, assem, "AssetStore");
75 m.Update();
76 }
81 } 77 }
82 78
83 /// <summary>
84 /// <para>Initialises Asset interface</para>
85 /// <para>
86 /// <list type="bullet">
87 /// <item>Loads and initialises the MySQL storage plugin</item>
88 /// <item>uses the obsolete mysql_connection.ini</item>
89 /// </list>
90 /// </para>
91 /// </summary>
92 /// <remarks>DEPRECATED and shouldn't be used</remarks>
93 public override void Initialise() 79 public override void Initialise()
94 { 80 {
95 IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini"); 81 throw new NotImplementedException();
96 string hostname = GridDataMySqlFile.ParseFileReadValue("hostname");
97 string database = GridDataMySqlFile.ParseFileReadValue("database");
98 string username = GridDataMySqlFile.ParseFileReadValue("username");
99 string password = GridDataMySqlFile.ParseFileReadValue("password");
100 string pooling = GridDataMySqlFile.ParseFileReadValue("pooling");
101 string port = GridDataMySqlFile.ParseFileReadValue("port");
102
103 _dbConnection = new MySQLManager(hostname, database, username, password, pooling, port);
104
105 } 82 }
106 83
107 public override void Dispose() { } 84 public override void Dispose() { }
108 85
109 /// <summary> 86 /// <summary>
110 /// Database provider version
111 /// </summary>
112 override public string Version
113 {
114 get { return _dbConnection.getVersion(); }
115 }
116
117 /// <summary>
118 /// The name of this DB provider 87 /// The name of this DB provider
119 /// </summary> 88 /// </summary>
120 override public string Name 89 override public string Name
@@ -135,46 +104,43 @@ namespace OpenSim.Data.MySQL
135 override public AssetBase GetAsset(UUID assetID) 104 override public AssetBase GetAsset(UUID assetID)
136 { 105 {
137 AssetBase asset = null; 106 AssetBase asset = null;
138 lock (_dbConnection) 107 lock (m_dbLock)
139 { 108 {
140 _dbConnection.CheckConnection(); 109 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
110 {
111 dbcon.Open();
141 112
142 MySqlCommand cmd = 113 using (MySqlCommand cmd = new MySqlCommand(
143 new MySqlCommand(
144 "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id", 114 "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id",
145 _dbConnection.Connection); 115 dbcon))
146 cmd.Parameters.AddWithValue("?id", assetID.ToString());
147
148 try
149 {
150 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
151 { 116 {
152 if (dbReader.Read()) 117 cmd.Parameters.AddWithValue("?id", assetID.ToString());
153 {
154 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]);
155 asset.Data = (byte[]) dbReader["data"];
156 asset.Description = (string) dbReader["description"];
157 118
158 string local = dbReader["local"].ToString(); 119 try
159 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase)) 120 {
160 asset.Local = true; 121 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
161 else 122 {
162 asset.Local = false; 123 if (dbReader.Read())
163 124 {
164 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]); 125 asset = new AssetBase(assetID, (string)dbReader["name"], (sbyte)dbReader["assetType"]);
126 asset.Data = (byte[])dbReader["data"];
127 asset.Description = (string)dbReader["description"];
128
129 string local = dbReader["local"].ToString();
130 if (local.Equals("1") || local.Equals("true", StringComparison.InvariantCultureIgnoreCase))
131 asset.Local = true;
132 else
133 asset.Local = false;
134
135 asset.Temporary = Convert.ToBoolean(dbReader["temporary"]);
136 }
137 }
138 }
139 catch (Exception e)
140 {
141 m_log.Error("[ASSETS DB]: MySql failure fetching asset " + assetID + ": " + e.Message);
165 } 142 }
166 dbReader.Close();
167 cmd.Dispose();
168 } 143 }
169 if (asset != null)
170 UpdateAccessTime(asset);
171 }
172 catch (Exception e)
173 {
174 m_log.ErrorFormat(
175 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString()
176 + Environment.NewLine + "Reconnecting", assetID);
177 _dbConnection.Reconnect();
178 } 144 }
179 } 145 }
180 return asset; 146 return asset;
@@ -187,55 +153,57 @@ namespace OpenSim.Data.MySQL
187 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks> 153 /// <remarks>On failure : Throw an exception and attempt to reconnect to database</remarks>
188 override public void StoreAsset(AssetBase asset) 154 override public void StoreAsset(AssetBase asset)
189 { 155 {
190 lock (_dbConnection) 156 lock (m_dbLock)
191 { 157 {
192 _dbConnection.CheckConnection(); 158 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
159 {
160 dbcon.Open();
193 161
194 MySqlCommand cmd = 162 MySqlCommand cmd =
195 new MySqlCommand( 163 new MySqlCommand(
196 "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, data)" + 164 "replace INTO assets(id, name, description, assetType, local, temporary, create_time, access_time, data)" +
197 "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?data)", 165 "VALUES(?id, ?name, ?description, ?assetType, ?local, ?temporary, ?create_time, ?access_time, ?data)",
198 _dbConnection.Connection); 166 dbcon);
199 167
200 string assetName = asset.Name; 168 string assetName = asset.Name;
201 if (asset.Name.Length > 64) 169 if (asset.Name.Length > 64)
202 {
203 assetName = asset.Name.Substring(0, 64);
204 m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add");
205 }
206
207 string assetDescription = asset.Description;
208 if (asset.Description.Length > 64)
209 {
210 assetDescription = asset.Description.Substring(0, 64);
211 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
212 }
213
214 // need to ensure we dispose
215 try
216 {
217 using (cmd)
218 { 170 {
219 // create unix epoch time 171 assetName = asset.Name.Substring(0, 64);
220 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 172 m_log.Warn("[ASSET DB]: Name field truncated from " + asset.Name.Length + " to " + assetName.Length + " characters on add");
221 cmd.Parameters.AddWithValue("?id", asset.ID); 173 }
222 cmd.Parameters.AddWithValue("?name", assetName); 174
223 cmd.Parameters.AddWithValue("?description", assetDescription); 175 string assetDescription = asset.Description;
224 cmd.Parameters.AddWithValue("?assetType", asset.Type); 176 if (asset.Description.Length > 64)
225 cmd.Parameters.AddWithValue("?local", asset.Local); 177 {
226 cmd.Parameters.AddWithValue("?temporary", asset.Temporary); 178 assetDescription = asset.Description.Substring(0, 64);
227 cmd.Parameters.AddWithValue("?create_time", now); 179 m_log.Warn("[ASSET DB]: Description field truncated from " + asset.Description.Length + " to " + assetDescription.Length + " characters on add");
228 cmd.Parameters.AddWithValue("?access_time", now); 180 }
229 cmd.Parameters.AddWithValue("?data", asset.Data); 181
230 cmd.ExecuteNonQuery(); 182 // need to ensure we dispose
231 cmd.Dispose(); 183 try
184 {
185 using (cmd)
186 {
187 // create unix epoch time
188 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
189 cmd.Parameters.AddWithValue("?id", asset.ID);
190 cmd.Parameters.AddWithValue("?name", assetName);
191 cmd.Parameters.AddWithValue("?description", assetDescription);
192 cmd.Parameters.AddWithValue("?assetType", asset.Type);
193 cmd.Parameters.AddWithValue("?local", asset.Local);
194 cmd.Parameters.AddWithValue("?temporary", asset.Temporary);
195 cmd.Parameters.AddWithValue("?create_time", now);
196 cmd.Parameters.AddWithValue("?access_time", now);
197 cmd.Parameters.AddWithValue("?data", asset.Data);
198 cmd.ExecuteNonQuery();
199 cmd.Dispose();
200 }
201 }
202 catch (Exception e)
203 {
204 m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Error: {2}",
205 asset.FullID, asset.Name, e.Message);
232 } 206 }
233 }
234 catch (Exception e)
235 {
236 m_log.ErrorFormat("[ASSET DB]: MySQL failure creating asset {0} with name \"{1}\". Attempting reconnect. Error: {2}",
237 asset.FullID, asset.Name, e.Message);
238 _dbConnection.Reconnect();
239 } 207 }
240 } 208 }
241 } 209 }
@@ -245,35 +213,36 @@ namespace OpenSim.Data.MySQL
245 // Writing to the database every time Get() is called on an asset is killing us. Seriously. -jph 213 // Writing to the database every time Get() is called on an asset is killing us. Seriously. -jph
246 return; 214 return;
247 215
248 lock (_dbConnection) 216 lock (m_dbLock)
249 { 217 {
250 _dbConnection.CheckConnection(); 218 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
251
252 MySqlCommand cmd =
253 new MySqlCommand("update assets set access_time=?access_time where id=?id",
254 _dbConnection.Connection);
255
256 // need to ensure we dispose
257 try
258 { 219 {
259 using (cmd) 220 dbcon.Open();
221 MySqlCommand cmd =
222 new MySqlCommand("update assets set access_time=?access_time where id=?id",
223 dbcon);
224
225 // need to ensure we dispose
226 try
227 {
228 using (cmd)
229 {
230 // create unix epoch time
231 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow);
232 cmd.Parameters.AddWithValue("?id", asset.ID);
233 cmd.Parameters.AddWithValue("?access_time", now);
234 cmd.ExecuteNonQuery();
235 cmd.Dispose();
236 }
237 }
238 catch (Exception e)
260 { 239 {
261 // create unix epoch time 240 m_log.ErrorFormat(
262 int now = (int)Utils.DateTimeToUnixTime(DateTime.UtcNow); 241 "[ASSETS DB]: " +
263 cmd.Parameters.AddWithValue("?id", asset.ID); 242 "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString()
264 cmd.Parameters.AddWithValue("?access_time", now); 243 + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
265 cmd.ExecuteNonQuery();
266 cmd.Dispose();
267 } 244 }
268 } 245 }
269 catch (Exception e)
270 {
271 m_log.ErrorFormat(
272 "[ASSETS DB]: " +
273 "MySql failure updating access_time for asset {0} with name {1}" + Environment.NewLine + e.ToString()
274 + Environment.NewLine + "Attempting reconnection", asset.FullID, asset.Name);
275 _dbConnection.Reconnect();
276 }
277 } 246 }
278 247
279 } 248 }
@@ -287,37 +256,30 @@ namespace OpenSim.Data.MySQL
287 { 256 {
288 bool assetExists = false; 257 bool assetExists = false;
289 258
290 lock (_dbConnection) 259 lock (m_dbLock)
291 { 260 {
292 _dbConnection.CheckConnection(); 261 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
293
294 MySqlCommand cmd =
295 new MySqlCommand(
296 "SELECT id FROM assets WHERE id=?id",
297 _dbConnection.Connection);
298
299 cmd.Parameters.AddWithValue("?id", uuid.ToString());
300
301 try
302 { 262 {
303 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) 263 dbcon.Open();
264 using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM assets WHERE id=?id", dbcon))
304 { 265 {
305 if (dbReader.Read()) 266 cmd.Parameters.AddWithValue("?id", uuid.ToString());
267
268 try
306 { 269 {
307 assetExists = true; 270 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
271 {
272 if (dbReader.Read())
273 assetExists = true;
274 }
275 }
276 catch (Exception e)
277 {
278 m_log.ErrorFormat(
279 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString(), uuid);
308 } 280 }
309
310 dbReader.Close();
311 cmd.Dispose();
312 } 281 }
313 } 282 }
314 catch (Exception e)
315 {
316 m_log.ErrorFormat(
317 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString()
318 + Environment.NewLine + "Attempting reconnection", uuid);
319 _dbConnection.Reconnect();
320 }
321 } 283 }
322 284
323 return assetExists; 285 return assetExists;
@@ -335,38 +297,39 @@ namespace OpenSim.Data.MySQL
335 { 297 {
336 List<AssetMetadata> retList = new List<AssetMetadata>(count); 298 List<AssetMetadata> retList = new List<AssetMetadata>(count);
337 299
338 lock (_dbConnection) 300 lock (m_dbLock)
339 { 301 {
340 _dbConnection.CheckConnection(); 302 using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
341
342 MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id FROM assets LIMIT ?start, ?count", _dbConnection.Connection);
343 cmd.Parameters.AddWithValue("?start", start);
344 cmd.Parameters.AddWithValue("?count", count);
345
346 try
347 { 303 {
348 using (MySqlDataReader dbReader = cmd.ExecuteReader()) 304 dbcon.Open();
305 MySqlCommand cmd = new MySqlCommand("SELECT name,description,assetType,temporary,id FROM assets LIMIT ?start, ?count", dbcon);
306 cmd.Parameters.AddWithValue("?start", start);
307 cmd.Parameters.AddWithValue("?count", count);
308
309 try
349 { 310 {
350 while (dbReader.Read()) 311 using (MySqlDataReader dbReader = cmd.ExecuteReader())
351 { 312 {
352 AssetMetadata metadata = new AssetMetadata(); 313 while (dbReader.Read())
353 metadata.Name = (string) dbReader["name"]; 314 {
354 metadata.Description = (string) dbReader["description"]; 315 AssetMetadata metadata = new AssetMetadata();
355 metadata.Type = (sbyte) dbReader["assetType"]; 316 metadata.Name = (string)dbReader["name"];
356 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct. 317 metadata.Description = (string)dbReader["description"];
357 metadata.FullID = new UUID((string) dbReader["id"]); 318 metadata.Type = (sbyte)dbReader["assetType"];
358 319 metadata.Temporary = Convert.ToBoolean(dbReader["temporary"]); // Not sure if this is correct.
359 // Current SHA1s are not stored/computed. 320 metadata.FullID = new UUID((string)dbReader["id"]);
360 metadata.SHA1 = new byte[] {}; 321
361 322 // Current SHA1s are not stored/computed.
362 retList.Add(metadata); 323 metadata.SHA1 = new byte[] { };
324
325 retList.Add(metadata);
326 }
363 } 327 }
364 } 328 }
365 } 329 catch (Exception e)
366 catch (Exception e) 330 {
367 { 331 m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString());
368 m_log.Error("[ASSETS DB]: MySql failure fetching asset set" + Environment.NewLine + e.ToString() + Environment.NewLine + "Attempting reconnection"); 332 }
369 _dbConnection.Reconnect();
370 } 333 }
371 } 334 }
372 335
@@ -374,7 +337,5 @@ namespace OpenSim.Data.MySQL
374 } 337 }
375 338
376 #endregion 339 #endregion
377
378
379 } 340 }
380} 341}