Mantis#1661. Thank you kindly, CMickeyb for a patch that:

patch attached to check for timeouts on mysql connections *before* 
operations occur that are likely to timeout. if timeout occurs or 
the connections is down, it is reconnected before the operation fails.

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
             AssetBase asset = null;
             lock (_dbConnection)
             {
+                _dbConnection.CheckConnection();
+
                 MySqlCommand cmd =
                     new MySqlCommand(
                         "SELECT name, description, assetType, local, temporary, data FROM assets WHERE id=?id",
@@ -213,6 +215,8 @@ namespace OpenSim.Data.MySQL
                     return;
                 }
 
+                _dbConnection.CheckConnection();
+
                 MySqlCommand cmd =
                     new MySqlCommand(
                         "REPLACE INTO assets(id, name, description, assetType, local, temporary, data)" +
@@ -266,6 +270,8 @@ namespace OpenSim.Data.MySQL
 
             lock (_dbConnection)
             {
+                _dbConnection.CheckConnection();
+                
                 MySqlCommand cmd =
                     new MySqlCommand(
                         "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
                 {
                     List<InventoryItemBase> items = new List<InventoryItemBase>();
 
+                    database.CheckConnection();
+
                     MySqlCommand result =
                         new MySqlCommand("SELECT * FROM inventoryitems WHERE parentFolderID = ?uuid",
                                          database.Connection);
@@ -253,6 +255,8 @@ namespace OpenSim.Data.MySQL
             {
                 lock (database)
                 {
+                    database.CheckConnection();
+
                     MySqlCommand result =
                         new MySqlCommand(
                             "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
@@ -292,6 +296,8 @@ namespace OpenSim.Data.MySQL
             {
                 lock (database)
                 {
+                    database.CheckConnection();
+
                     MySqlCommand result =
                         new MySqlCommand(
                             "SELECT * FROM inventoryfolders WHERE parentFolderID = ?zero AND agentID = ?uuid",
@@ -344,6 +350,8 @@ namespace OpenSim.Data.MySQL
             {
                 lock (database)
                 {
+                    database.CheckConnection();
+
                     MySqlCommand result =
                         new MySqlCommand("SELECT * FROM inventoryfolders WHERE parentFolderID = ?uuid",
                                          database.Connection);
@@ -421,6 +429,8 @@ namespace OpenSim.Data.MySQL
             {
                 lock (database)
                 {
+                    database.CheckConnection();
+
                     MySqlCommand result =
                         new MySqlCommand("SELECT * FROM inventoryitems WHERE inventoryID = ?uuid", database.Connection);
                     result.Parameters.AddWithValue("?uuid", itemID.ToString());
@@ -482,6 +492,8 @@ namespace OpenSim.Data.MySQL
             {
                 lock (database)
                 {
+                    database.CheckConnection();
+
                     MySqlCommand result =
                         new MySqlCommand("SELECT * FROM inventoryfolders WHERE folderID = ?uuid", database.Connection);
                     result.Parameters.AddWithValue("?uuid", folderID.ToString());
@@ -522,6 +534,8 @@ namespace OpenSim.Data.MySQL
 
             try
             {
+                database.CheckConnection();
+
                 MySqlCommand result = new MySqlCommand(sql, database.Connection);
                 result.Parameters.AddWithValue("?inventoryID", item.ID.ToString());
                 result.Parameters.AddWithValue("?assetID", item.AssetID.ToString());
@@ -574,6 +588,8 @@ namespace OpenSim.Data.MySQL
         {
             try
             {
+                database.CheckConnection();
+
                 MySqlCommand cmd =
                     new MySqlCommand("DELETE FROM inventoryitems WHERE inventoryID=?uuid", database.Connection);
                 cmd.Parameters.AddWithValue("?uuid", itemID.ToString());
@@ -600,6 +616,8 @@ namespace OpenSim.Data.MySQL
                 "REPLACE INTO inventoryfolders (folderID, agentID, parentFolderID, folderName, type, version) VALUES ";
             sql += "(?folderID, ?agentID, ?parentFolderID, ?folderName, ?type, ?version)";
 
+            database.CheckConnection();
+
             MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
             cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
             cmd.Parameters.AddWithValue("?agentID", folder.Owner.ToString());
@@ -640,6 +658,8 @@ namespace OpenSim.Data.MySQL
             string sql =
                 "UPDATE inventoryfolders SET parentFolderID=?parentFolderID WHERE folderID=?folderID";
 
+            database.CheckConnection();
+
             MySqlCommand cmd = new MySqlCommand(sql, database.Connection);
             cmd.Parameters.AddWithValue("?folderID", folder.ID.ToString());
             cmd.Parameters.AddWithValue("?parentFolderID", folder.ParentID.ToString());
@@ -695,6 +715,8 @@ namespace OpenSim.Data.MySQL
         {
             try
             {
+                database.CheckConnection();
+
                 MySqlCommand cmd =
                     new MySqlCommand("DELETE FROM inventoryfolders WHERE folderID=?uuid", database.Connection);
                 cmd.Parameters.AddWithValue("?uuid", folderID.ToString());
@@ -719,6 +741,8 @@ namespace OpenSim.Data.MySQL
         {
             try
             {
+                database.CheckConnection();
+
                 MySqlCommand cmd =
                     new MySqlCommand("DELETE FROM inventoryitems WHERE parentFolderID=?uuid", database.Connection);
                 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
         /// </summary>
         private string connectionString;
 
+        private const string m_waitTimeoutSelect = "select @@wait_timeout";
+        
+        /// <summary>
+        /// Wait timeout for our connection in ticks.
+        /// </summary>
+        private long m_waitTimeout;
+
+        /// <summary>
+        /// Make our storage of the timeout this amount smaller than it actually is, to give us a margin on long
+        /// running database operations.
+        /// </summary>
+        private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
+
+        /// <summary>
+        /// Holds the last tick time that the connection was used.
+        /// </summary>
+        private long m_lastConnectionUse;  
+
         /// <summary>
         /// Initialises and creates a new MySQL connection and maintains it.
         /// </summary>
@@ -102,6 +120,7 @@ namespace OpenSim.Data.MySQL
                 }
 
                 m_log.Info("[MYSQL]: Connection established");
+                GetWaitTimeout();
             }
             catch (Exception e)
             {
@@ -110,6 +129,51 @@ namespace OpenSim.Data.MySQL
         }
 
         /// <summary>
+        /// Get the wait_timeout value for our connection
+        /// </summary>
+        protected void GetWaitTimeout()
+        {
+            MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, dbcon);
+
+            using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
+            {
+                if (dbReader.Read())
+                {
+                    m_waitTimeout
+                        = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
+                }
+
+                dbReader.Close();
+                cmd.Dispose();
+            }
+
+            m_lastConnectionUse = System.DateTime.Now.Ticks;
+
+            m_log.DebugFormat(
+                "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond);
+        }
+
+        /// <summary>
+        /// Should be called before any db operation.  This checks to see if the connection has not timed out
+        /// </summary>
+        public void CheckConnection()
+        {
+            //m_log.Debug("[REGION DB]: Checking connection");
+
+            long timeNow = System.DateTime.Now.Ticks;
+            if (timeNow - m_lastConnectionUse > m_waitTimeout || dbcon.State != ConnectionState.Open)
+            {
+                m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
+                Reconnect();
+            }
+
+            // Strictly, we should set this after the actual db operation.  But it's more convenient to set here rather
+            // than require the code to call another method - the timeout leeway should be large enough to cover the
+            // inaccuracy.
+            m_lastConnectionUse = timeNow;
+        }
+        
+        /// <summary>
         /// Get the connection being used
         /// </summary>
         /// <returns>MySqlConnection Object</returns>
@@ -132,6 +196,8 @@ namespace OpenSim.Data.MySQL
         /// </summary>
         public void Reconnect()
         {
+            m_log.Info("[REGION DB] Reconnecting database");
+
             lock (dbcon)
             {
                 try
@@ -197,6 +263,7 @@ namespace OpenSim.Data.MySQL
         /// <param name="name">name of embedded resource</param>
         public void ExecuteResourceSql(string name)
         {
+            CheckConnection();
             MySqlCommand cmd = new MySqlCommand(getResourceString(name), dbcon);
             cmd.ExecuteNonQuery();
         }
@@ -207,6 +274,7 @@ namespace OpenSim.Data.MySQL
         /// <param name="sql">sql string to execute</param>
         public void ExecuteSql(string sql)
         {
+            CheckConnection();
             MySqlCommand cmd = new MySqlCommand(sql, dbcon);
             cmd.ExecuteNonQuery();
         }
@@ -219,11 +287,14 @@ namespace OpenSim.Data.MySQL
         {
             lock (dbcon)
             {
+                CheckConnection(); 
+                
                 MySqlCommand tablesCmd =
                     new MySqlCommand(
                         "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
                         dbcon);
                 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
+                
                 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
                 {
                     while (tables.Read())
@@ -259,6 +330,8 @@ namespace OpenSim.Data.MySQL
         {
             try
             {
+                CheckConnection(); // Not sure if this one is necessary
+
                 MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
                 dbcommand.CommandText = sql;
                 foreach (KeyValuePair<string, string> param in parameters)
@@ -268,43 +341,11 @@ namespace OpenSim.Data.MySQL
 
                 return (IDbCommand) dbcommand;
             }
-            catch
+            catch (Exception e)
             {
-                lock (dbcon)
-                {
-                    // Close the DB connection
-                    dbcon.Close();
-
-                    // Try to reopen it
-                    try
-                    {
-                        dbcon = new MySqlConnection(connectionString);
-                        dbcon.Open();
-                    }
-                    catch (Exception e)
-                    {
-                        m_log.Error("Unable to reconnect to database " + e);
-                    }
-
-                    // Run the query again
-                    try
-                    {
-                        MySqlCommand dbcommand = (MySqlCommand) dbcon.CreateCommand();
-                        dbcommand.CommandText = sql;
-                        foreach (KeyValuePair<string, string> param in parameters)
-                        {
-                            dbcommand.Parameters.AddWithValue(param.Key, param.Value);
-                        }
-
-                        return (IDbCommand) dbcommand;
-                    }
-                    catch (Exception e)
-                    {
-                        // Return null if it fails.
-                        m_log.Error("Failed during Query generation: " + e.ToString());
-                        return null;
-                    }
-                }
+                // Return null if it fails.
+                m_log.Error("Failed during Query generation: " + e.ToString());
+                return null;
             }
         }