aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim
diff options
context:
space:
mode:
authorJustin Clarke Casey2008-06-29 18:10:38 +0000
committerJustin Clarke Casey2008-06-29 18:10:38 +0000
commit31c63558c89b617fa46b86a9c2c8a14529a5b8ba (patch)
treea419fe32c11e4b238a3ef7523325776fcdac31cc /OpenSim
parentadded patch 1633, thanks Melanie. (diff)
downloadopensim-SC_OLD-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.zip
opensim-SC_OLD-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.tar.gz
opensim-SC_OLD-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.tar.bz2
opensim-SC_OLD-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.tar.xz
* Fix for http://opensimulator.org/mantis/view.php?id=1512
* Introduce experimental wait timeout checking to mysql region datastore code * This should mean that if the mysql connection has timed out, we should automatically reconnect and not fail or drop queries on region database manipulations
Diffstat (limited to 'OpenSim')
-rw-r--r--OpenSim/Data/MySQL/MySQLAssetData.cs2
-rw-r--r--OpenSim/Data/MySQL/MySQLDataStore.cs112
2 files changed, 95 insertions, 19 deletions
diff --git a/OpenSim/Data/MySQL/MySQLAssetData.cs b/OpenSim/Data/MySQL/MySQLAssetData.cs
index fc331cd..d66a5c2 100644
--- a/OpenSim/Data/MySQL/MySQLAssetData.cs
+++ b/OpenSim/Data/MySQL/MySQLAssetData.cs
@@ -191,7 +191,7 @@ namespace OpenSim.Data.MySQL
191 { 191 {
192 m_log.ErrorFormat( 192 m_log.ErrorFormat(
193 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString() 193 "[ASSETS DB]: MySql failure fetching asset {0}" + Environment.NewLine + e.ToString()
194 + Environment.NewLine + "Attempting reconnection", assetID); 194 + Environment.NewLine + "Reconnecting", assetID);
195 _dbConnection.Reconnect(); 195 _dbConnection.Reconnect();
196 } 196 }
197 } 197 }
diff --git a/OpenSim/Data/MySQL/MySQLDataStore.cs b/OpenSim/Data/MySQL/MySQLDataStore.cs
index 35e0ab7..97aaf6a 100644
--- a/OpenSim/Data/MySQL/MySQLDataStore.cs
+++ b/OpenSim/Data/MySQL/MySQLDataStore.cs
@@ -54,19 +54,32 @@ namespace OpenSim.Data.MySQL
54 private const string m_landSelect = "select * from land"; 54 private const string m_landSelect = "select * from land";
55 private const string m_landAccessListSelect = "select * from landaccesslist"; 55 private const string m_landAccessListSelect = "select * from landaccesslist";
56 private const string m_regionBanListSelect = "select * from regionban"; 56 private const string m_regionBanListSelect = "select * from regionban";
57 private const string m_regionSettingsSelect = "select * from regionsettings"; 57 private const string m_regionSettingsSelect = "select * from regionsettings";
58 58 private const string m_waitTimeoutSelect = "select @@wait_timeout";
59 59
60 private MySqlConnection m_connection;
61 private string m_connectionString;
62
60 /// <summary> 63 /// <summary>
61 /// We're only using this to version the table! 64 /// Wait timeout for our connection in ticks.
62 /// </summary> 65 /// </summary>
63 66 private long m_waitTimeout;
67
68 /// <summary>
69 /// Make our storage of the timeout this amount smaller than it actually is, to give us a margin on long
70 /// running database operations.
71 /// </summary>
72 private long m_waitTimeoutLeeway = 60 * TimeSpan.TicksPerSecond;
73
74 /// <summary>
75 /// Holds the last tick time that the connection was used.
76 /// </summary>
77 private long m_lastConnectionUse;
64 78
65 private DataSet m_dataSet; 79 private DataSet m_dataSet;
66 private MySqlDataAdapter m_primDataAdapter; 80 private MySqlDataAdapter m_primDataAdapter;
67 private MySqlDataAdapter m_shapeDataAdapter; 81 private MySqlDataAdapter m_shapeDataAdapter;
68 private MySqlDataAdapter m_itemsDataAdapter; 82 private MySqlDataAdapter m_itemsDataAdapter;
69 private MySqlConnection m_connection;
70 private MySqlDataAdapter m_terrainDataAdapter; 83 private MySqlDataAdapter m_terrainDataAdapter;
71 private MySqlDataAdapter m_landDataAdapter; 84 private MySqlDataAdapter m_landDataAdapter;
72 private MySqlDataAdapter m_landAccessListDataAdapter; 85 private MySqlDataAdapter m_landAccessListDataAdapter;
@@ -96,14 +109,18 @@ namespace OpenSim.Data.MySQL
96 /// </summary> 109 /// </summary>
97 /// <param name="connectionstring"></param> 110 /// <param name="connectionstring"></param>
98 /// <param name="persistPrimInventories"></param> 111 /// <param name="persistPrimInventories"></param>
99 public void Initialise(string connectionstring, bool persistPrimInventories) 112 public void Initialise(string connectionString, bool persistPrimInventories)
100 { 113 {
114 m_connectionString = connectionString;
115
101 m_dataSet = new DataSet(); 116 m_dataSet = new DataSet();
102 this.persistPrimInventories = persistPrimInventories; 117 this.persistPrimInventories = persistPrimInventories;
103 118
104 m_log.Info("[REGION DB]: MySql - connecting: " + connectionstring); 119 m_log.Info("[REGION DB]: MySql - connecting: " + m_connectionString);
105 m_connection = new MySqlConnection(connectionstring); 120 m_connection = new MySqlConnection(m_connectionString);
106 m_connection.Open(); 121 m_connection.Open();
122
123 GetWaitTimeout();
107 124
108 // This actually does the roll forward assembly stuff 125 // This actually does the roll forward assembly stuff
109 Assembly assem = GetType().Assembly; 126 Assembly assem = GetType().Assembly;
@@ -115,7 +132,6 @@ namespace OpenSim.Data.MySQL
115 132
116 m.Update(); 133 m.Update();
117 134
118
119 MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, m_connection); 135 MySqlCommand primSelectCmd = new MySqlCommand(m_primSelect, m_connection);
120 m_primDataAdapter = new MySqlDataAdapter(primSelectCmd); 136 m_primDataAdapter = new MySqlDataAdapter(primSelectCmd);
121 137
@@ -187,6 +203,58 @@ namespace OpenSim.Data.MySQL
187 m_regionSettingsDataAdapter.Fill(m_regionSettingsTable); 203 m_regionSettingsDataAdapter.Fill(m_regionSettingsTable);
188 } 204 }
189 } 205 }
206
207 /// <summary>
208 /// Get the wait_timeout value for our connection
209 /// </summary>
210 protected void GetWaitTimeout()
211 {
212 MySqlCommand cmd = new MySqlCommand(m_waitTimeoutSelect, m_connection);
213
214 using (MySqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.SingleRow))
215 {
216 if (dbReader.Read())
217 {
218 m_waitTimeout
219 = Convert.ToInt32(dbReader["@@wait_timeout"]) * TimeSpan.TicksPerSecond + m_waitTimeoutLeeway;
220 }
221
222 dbReader.Close();
223 cmd.Dispose();
224 }
225
226 m_lastConnectionUse = System.DateTime.Now.Ticks;
227
228 m_log.DebugFormat(
229 "[REGION DB]: Connection wait timeout {0} seconds", m_waitTimeout / TimeSpan.TicksPerSecond);
230 }
231
232 /// <summary>
233 /// Should be called before any db operation. This checks to see if the connection has not timed out
234 /// </summary>
235 protected void CheckConnection()
236 {
237 //m_log.Debug("[REGION DB]: Checking connection");
238
239 long timeNow = System.DateTime.Now.Ticks;
240 if (timeNow - m_lastConnectionUse > m_waitTimeout || m_connection.State != ConnectionState.Open)
241 {
242 m_log.DebugFormat("[REGION DB]: Database connection has gone away - reconnecting");
243
244 lock (m_connection)
245 {
246 m_connection.Close();
247 m_connection = new MySqlConnection(m_connectionString);
248 m_connection.Open();
249 }
250 }
251
252 // Strictly, we should set this after the actual db operation. But it's more convenient to set here rather
253 // than require the code to call another method - the timeout leeway should be large enough to cover the
254 // inaccuracy.
255 m_lastConnectionUse = timeNow;
256 }
257
190 /// <summary> 258 /// <summary>
191 /// Given a list of tables, return the version of the tables, as seen in the database 259 /// Given a list of tables, return the version of the tables, as seen in the database
192 /// </summary> 260 /// </summary>
@@ -201,6 +269,8 @@ namespace OpenSim.Data.MySQL
201 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname", 269 "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=?dbname",
202 dbcon); 270 dbcon);
203 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database); 271 tablesCmd.Parameters.AddWithValue("?dbname", dbcon.Database);
272
273 CheckConnection();
204 using (MySqlDataReader tables = tablesCmd.ExecuteReader()) 274 using (MySqlDataReader tables = tablesCmd.ExecuteReader())
205 { 275 {
206 while (tables.Read()) 276 while (tables.Read())
@@ -396,6 +466,7 @@ namespace OpenSim.Data.MySQL
396 466
397 lock (m_dataSet) 467 lock (m_dataSet)
398 { 468 {
469 CheckConnection();
399 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent); 470 DataRow[] primsForRegion = prims.Select(byRegion, orderByParent);
400 m_log.Info("[REGION DB]: " + 471 m_log.Info("[REGION DB]: " +
401 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID); 472 "Loaded " + primsForRegion.Length + " prims for region: " + regionUUID);
@@ -473,6 +544,7 @@ namespace OpenSim.Data.MySQL
473 { 544 {
474 lock (m_dataSet) 545 lock (m_dataSet)
475 { 546 {
547 CheckConnection();
476 //m_log.InfoFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID); 548 //m_log.InfoFormat("[DATASTORE]: Loading inventory for {0}, {1}", prim.Name, prim.UUID);
477 549
478 DataTable dbItems = m_itemsTable; 550 DataTable dbItems = m_itemsTable;
@@ -519,6 +591,8 @@ namespace OpenSim.Data.MySQL
519 using (cmd) 591 using (cmd)
520 { 592 {
521 delete.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID))); 593 delete.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID)));
594
595 CheckConnection();
522 delete.ExecuteNonQuery(); 596 delete.ExecuteNonQuery();
523 597
524 cmd.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID))); 598 cmd.Parameters.Add(new MySqlParameter("?RegionUUID", Util.ToRawUuidString(regionID)));
@@ -554,6 +628,7 @@ namespace OpenSim.Data.MySQL
554 628
555 lock (m_dataSet) 629 lock (m_dataSet)
556 { 630 {
631 CheckConnection();
557 using (MySqlDataReader row = cmd.ExecuteReader()) 632 using (MySqlDataReader row = cmd.ExecuteReader())
558 { 633 {
559 int rev = 0; 634 int rev = 0;
@@ -593,6 +668,7 @@ namespace OpenSim.Data.MySQL
593 { 668 {
594 lock (m_dataSet) 669 lock (m_dataSet)
595 { 670 {
671 CheckConnection();
596 using (MySqlCommand cmd = new MySqlCommand("delete from land where UUID=?UUID", m_connection)) 672 using (MySqlCommand cmd = new MySqlCommand("delete from land where UUID=?UUID", m_connection))
597 { 673 {
598 cmd.Parameters.Add(new MySqlParameter("?UUID", Util.ToRawUuidString(globalID))); 674 cmd.Parameters.Add(new MySqlParameter("?UUID", Util.ToRawUuidString(globalID)));
@@ -616,6 +692,7 @@ namespace OpenSim.Data.MySQL
616 { 692 {
617 lock (m_dataSet) 693 lock (m_dataSet)
618 { 694 {
695 CheckConnection();
619 DataTable land = m_landTable; 696 DataTable land = m_landTable;
620 DataTable landaccesslist = m_landAccessListTable; 697 DataTable landaccesslist = m_landAccessListTable;
621 698
@@ -654,6 +731,7 @@ namespace OpenSim.Data.MySQL
654 { 731 {
655 lock(m_dataSet) 732 lock(m_dataSet)
656 { 733 {
734 CheckConnection();
657 DataTable regionsettings = m_regionSettingsTable; 735 DataTable regionsettings = m_regionSettingsTable;
658 string searchExp = "regionUUID = '" + regionUUID.ToString() + "'"; 736 string searchExp = "regionUUID = '" + regionUUID.ToString() + "'";
659 DataRow[] rawsettings = regionsettings.Select(searchExp); 737 DataRow[] rawsettings = regionsettings.Select(searchExp);
@@ -669,6 +747,7 @@ namespace OpenSim.Data.MySQL
669 { 747 {
670 lock (m_dataSet) 748 lock (m_dataSet)
671 { 749 {
750 CheckConnection();
672 DataTable regionsettings = m_dataSet.Tables["regionsettings"]; 751 DataTable regionsettings = m_dataSet.Tables["regionsettings"];
673 752
674 DataRow settingsRow = regionsettings.Rows.Find(rs.RegionUUID.ToString()); 753 DataRow settingsRow = regionsettings.Rows.Find(rs.RegionUUID.ToString());
@@ -695,6 +774,7 @@ namespace OpenSim.Data.MySQL
695 List<RegionBanListItem> regionbanlist = new List<RegionBanListItem>(); 774 List<RegionBanListItem> regionbanlist = new List<RegionBanListItem>();
696 lock (m_dataSet) 775 lock (m_dataSet)
697 { 776 {
777 CheckConnection();
698 DataTable regionban = m_regionBanListTable; 778 DataTable regionban = m_regionBanListTable;
699 string searchExp = "regionUUID = '" + regionUUID.ToString() + "'"; 779 string searchExp = "regionUUID = '" + regionUUID.ToString() + "'";
700 DataRow[] rawbanlist = regionban.Select(searchExp); 780 DataRow[] rawbanlist = regionban.Select(searchExp);
@@ -724,6 +804,7 @@ namespace OpenSim.Data.MySQL
724 { 804 {
725 lock (m_dataSet) 805 lock (m_dataSet)
726 { 806 {
807 CheckConnection();
727 DataTable regionban = m_regionBanListTable; 808 DataTable regionban = m_regionBanListTable;
728 string searchExp = "regionUUID = '" + item.regionUUID.ToString() + "' AND bannedUUID = '" + item.bannedUUID.ToString() + "'"; 809 string searchExp = "regionUUID = '" + item.regionUUID.ToString() + "' AND bannedUUID = '" + item.bannedUUID.ToString() + "'";
729 DataRow[] rawbanlist = regionban.Select(searchExp); 810 DataRow[] rawbanlist = regionban.Select(searchExp);
@@ -748,6 +829,7 @@ namespace OpenSim.Data.MySQL
748 { 829 {
749 lock (m_dataSet) 830 lock (m_dataSet)
750 { 831 {
832 CheckConnection();
751 DataTable regionban = m_regionBanListTable; 833 DataTable regionban = m_regionBanListTable;
752 string searchExp = "regionUUID = '" + item.regionUUID.ToString() + "' AND bannedUUID = '" + item.bannedUUID.ToString() + "'"; 834 string searchExp = "regionUUID = '" + item.regionUUID.ToString() + "' AND bannedUUID = '" + item.bannedUUID.ToString() + "'";
753 DataRow[] rawbanlist = regionban.Select(searchExp); 835 DataRow[] rawbanlist = regionban.Select(searchExp);
@@ -760,10 +842,6 @@ namespace OpenSim.Data.MySQL
760 } 842 }
761 Commit(); 843 Commit();
762 } 844 }
763 if (m_connection.State != ConnectionState.Open)
764 {
765 m_connection.Open();
766 }
767 845
768 using 846 using
769 ( 847 (
@@ -773,6 +851,7 @@ namespace OpenSim.Data.MySQL
773 { 851 {
774 cmd.Parameters.Add(new MySqlParameter("?regionUUID", item.regionUUID.ToString())); 852 cmd.Parameters.Add(new MySqlParameter("?regionUUID", item.regionUUID.ToString()));
775 cmd.Parameters.Add(new MySqlParameter("?bannedUUID", item.bannedUUID.ToString())); 853 cmd.Parameters.Add(new MySqlParameter("?bannedUUID", item.bannedUUID.ToString()));
854 CheckConnection();
776 cmd.ExecuteNonQuery(); 855 cmd.ExecuteNonQuery();
777 } 856 }
778 857
@@ -788,6 +867,7 @@ namespace OpenSim.Data.MySQL
788 List<LandData> landDataForRegion = new List<LandData>(); 867 List<LandData> landDataForRegion = new List<LandData>();
789 lock (m_dataSet) 868 lock (m_dataSet)
790 { 869 {
870 CheckConnection();
791 DataTable land = m_landTable; 871 DataTable land = m_landTable;
792 DataTable landaccesslist = m_landAccessListTable; 872 DataTable landaccesslist = m_landAccessListTable;
793 string searchExp = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'"; 873 string searchExp = "RegionUUID = '" + Util.ToRawUuidString(regionUUID) + "'";
@@ -813,13 +893,9 @@ namespace OpenSim.Data.MySQL
813 /// </summary> 893 /// </summary>
814 public void Commit() 894 public void Commit()
815 { 895 {
816 if (m_connection.State != ConnectionState.Open)
817 {
818 m_connection.Open();
819 }
820
821 lock (m_dataSet) 896 lock (m_dataSet)
822 { 897 {
898 CheckConnection();
823 // DisplayDataSet(m_dataSet, "Region DataSet"); 899 // DisplayDataSet(m_dataSet, "Region DataSet");
824 900
825 m_primDataAdapter.Update(m_primTable); 901 m_primDataAdapter.Update(m_primTable);