diff options
author | Justin Clarke Casey | 2008-06-29 18:10:38 +0000 |
---|---|---|
committer | Justin Clarke Casey | 2008-06-29 18:10:38 +0000 |
commit | 31c63558c89b617fa46b86a9c2c8a14529a5b8ba (patch) | |
tree | a419fe32c11e4b238a3ef7523325776fcdac31cc /OpenSim | |
parent | added patch 1633, thanks Melanie. (diff) | |
download | opensim-SC-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.zip opensim-SC-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.tar.gz opensim-SC-31c63558c89b617fa46b86a9c2c8a14529a5b8ba.tar.bz2 opensim-SC-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 '')
-rw-r--r-- | OpenSim/Data/MySQL/MySQLAssetData.cs | 2 | ||||
-rw-r--r-- | OpenSim/Data/MySQL/MySQLDataStore.cs | 112 |
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); |