aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/MySQL/MySQLManager.cs
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/MySQL/MySQLManager.cs')
-rw-r--r--OpenSim/Data/MySQL/MySQLManager.cs113
1 files changed, 77 insertions, 36 deletions
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