Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
sms_error_-_index_out_of_date [2019/01/27 11:30]
127.0.0.1 external edit 		sms_error_-_index_out_of_date [2019/06/13 19:37] (current)
smerritt [Explanation of Error]
Line 1: Line 1:
 ====== SMS Error - Index Out of Date ====== ====== SMS Error - Index Out of Date ======
  
 +===== Error Message =====
  
 +"Database Index Out of Date" …
  
-===== Error Message =====+===== Severity =====
  
 +**Moderate**. The chance for data loss is Moderate, but the user is unable to use the database until it is corrected. Occassionally, if the errors are in the Estimate Details or Order Details tables, it is not uncommon to lose line items for the order/estimate that was corrupted. For many users, this error occurs frequently. For others, it almost never occurs.
 +===== Explanation of Error =====
  
 +The BDE maintains the "last modified date" on the database file (DB and MB) as well as the indexes (PX and ___). This error message occurs when the date of one of the indexes does not match the database file. This indicates that the data was updated more recently than the index, hence the index is "out of date".
  
-"Database Index Out of Date" ...+<font 16px/inherit;;inherit;;inherit>**===== Possible Root Cause(s) ===== **</font>
  
 +It goes without saying that this error should never occur. Cyrious' leading theory (and it is not confirmed) is that when two different clients are updating the same table nearly simultaneously, the following sequence happens:
  
 +- Client A updates the Data File
  
-===== Severity =====+- Client B updates the Data File
  
-**Moderate**. The chance for data loss is Moderate, but the user is unable to use the database until it is corrected. Occassionally, if the errors are in the Estimate Details or Order Details tables, it is not uncommon to lose line items for the order/estimate that was corrupted. For many users, this error occurs frequently. For others, it almost never occurs.+- Client A updates the Index File
  
 +- Client B updates the Index File
  
 +except instead of being received in that order, the following sequence is recieved at the computer with the database …
  
-===== Explanation of Error =====+- Client A updates the Data File
  
 +- Client B updates the Data File
  
 +- Client B updates the Index File
  
-The BDE maintains the "last modified date" on the database file (DB and MB) as well as the indexes (PX and ___). This error message occurs when the date of one of the indexes does not match the database file. This indicates that the data was updated more recently than the index, hence the index is "out of date".+- Client A updates the Index File
  
 +From an IT point of view, the delivery order between two clients is not guarenteed. The result in this last scenerio is that the index date corresponds to Client A while the Data File date corresponds to Client B. Since the clocks on Client A and B are not syncronized to the millisecond, the BDE sees this as an "Out of Date" problem.
  
 +**<font 16px/inherit;;inherit;;inherit>===== Resolution =====</font>**
  
-===== Possible Root Cause(s) =====+* Reboot the Server First. This is very important to ensure that all of the file locks are removed, otherwise the rebuild may fail and data may be lost
  
 +* The primary fix for this issue is to use the [[:dbrebuild|]] utility to scan and rebuild the affected table.
  
 +* In rare cases, the indexes will be lost (removed) during the rebuild process. In this cases, it will be required to manually delete the indexes and insert the data into a new table with indexes. See [[:manually_rebuilding_sms_tables|Manually Rebuilding SMS Tables]]
  
-It goes without saying that this error should never occur. Cyrious' leading theory (and it is not confirmed) is that when two different clients are updating the same table nearly simultaneously, the following sequence happens: +* Another option when dbrebuild fails is the [[:borland_database_desktop|Borland Database Desktop]] utility. It uses the same DLL as the [[:dbrebuild|]] Utility but for reasons unknown is slightly better at salvaging databases with corrupted data.
-  - Client A updates the Data File +
-  - Client B updates the Data File +
-  - Client A updates the Index File +
-  - Client B updates the Index File+
  
 +**<font 16px/inherit;;inherit;;inherit>===== Resolution 2 =====</font>**
  
 +- Open the NewData folder //(If version 8.91 and above make sure the NewData tables are also updated to 8.91. A quick way to update is to rename NewData to 'Data' - meanwhile renaming Data to something else like 'Data' - and running SSLIP and SMS. The tables should update quickly during this process. Just don't forget to rename the folders again!)//
  
-except instead of being received in that order, the following sequence is recieved at the computer with the database ... +- Sort by Type
-  - Client A updates the Data File +
-  - Client B updates the Data File +
-  - Client B updates the Index File +
-  - Client A updates the Index File+
  
 +- Scroll down to the last PX file
  
 +- Copy everything below the last PX file
  
-From an IT point of view, the delivery order between two clients is not guarenteed. The result in this last scenerio is that the index date corresponds to Client A while the Data File date corresponds to Client B. Since the clocks on Client A and B are not syncronized to the millisecond, the BDE sees this as an "Out of Date" problem.+- Paste in the Data folder
  
 +- **Select NO to all** (this replaces only the missing indexes
  
 +- [[:dbrebuild|]] //**Make note of the error logs in these cases and which index(es) are throwing the error. If the Index out of Date error returns after Resolutions 1 and 2 have been performed and the same index (example: CustomerOrderIndexDesc) is at fault then proceed to Resolution 3 which replaces the table entirely:** //
  
-===== Resolution ===== +<font 16px/inherit;;inherit;;inherit>**===== Resolution 3 =====**</font>
-  * Reboot the Server First. This is very important to ensure that all of the file locks are removed, otherwise the rebuild may fail and data may be lost +
-  * The primary fix for this issue is to use the [[dbrebuild]] utility to scan and rebuild the affected table. +
-  * In rare cases, the indexes will be lost (removed) during the rebuild process. In this cases, it will be required to manually delete the indexes and insert the data into a new table with indexes. See [[manually_rebuilding_sms_tables|Manually Rebuilding SMS Tables]] +
-  * Another option when dbrebuild fails is the [[borland_database_desktop|Borland Database Desktop]] utility. It uses the same DLL as the [[dbrebuild]] Utility but for reasons unknown is slightly better at salvaging databases with corrupted data.+
  
 +- Verify the affected table(s) using the Error Logs and, potentially, dbrebuild
  
 +- Open DBBrowser and create a Temporary Alias pointing to the NewData tables (see Step 1 of Resolution 2 if customer is 8.91 or above)
  
-===== Resolution 2 ===== +- Browse the relevant tables to make sure there is no data present in the NewData tables. Delete any records if necessary including Zero IDs - Run a query to insert data from the tables in the Data folder to the blank tables in NewData. The example at the end of this Resolution uses Order Database, but you will need to adjust this accordingly
-  - Open the NewData folder //(If version 8.91 and above make sure the NewData tables are also updated to 8.91. A quick way to update is to rename NewData to 'Data' - meanwhile renaming Data to something else like '__Data' - and running SSLIP and SMS. The tables should update quickly during this process. Just don't forget to rename the folders again!)// +
-  - Sort by Type +
-  - Scroll down to the last PX file +
-  - Copy everything below the last PX file +
-  - Paste in the Data folder +
-  - **Select NO to all** (this replaces only the missing indexes +
-  - [[dbrebuild]]+
  
 +- Verify in DBBrowser that the data transferred successfully to the NewData tables and then close DBBrowser - Remove the affected table(s) plus indexes from the current Data folder
  
 +- Copy over the NewData table(s) and indexes to the Data folder
  
-//**Make note of the error logs in these cases and which index(es) are throwing the error. If the Index out of Date error returns after Resolutions 1 and 2 have been performed and the same index (example: CustomerOrderIndexDesc) is at fault then proceed to Resolution 3 which replaces the table entirely:**//+- Run a dbrebuild scan on the table(s) to see if it needs to be rebuilt. If so, rebuild it - Launch SSLIP and SMS - confirm that the error no longer appears
  
 +**<font 16px/inherit;;inherit;;inherit>SQL 1</font>**
  
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Adjustments Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Adjustments Database"
 +</code>
  
-===== Resolution 3 ===== +**<font 16px/inherit;;inherit;;inherit>SQL 2</font>**
-  - Verify the affected table(s) using the Error Logs and, potentially, dbrebuild +
-  - Open DBBrowser and create a Temporary Alias pointing to the NewData tables (see Step 1 of Resolution 2 if customer is 8.91 or above) +
-  - Browse the relevant tables to make sure there is no data present in the NewData tables. Delete any records if necessary including Zero IDs +
-  - Run a query to insert data from the tables in the Data folder to the blank tables in NewData. The example at the end of this Resolution uses Order Database, but you will need to adjust this accordingly +
-  - Verify in DBBrowser that the data transferred successfully to the NewData tables and then close DBBrowser +
-  - Remove the affected table(s) plus indexes from the current Data folder +
-  - Copy over the NewData table(s) and indexes to the Data folder +
-  - Run a dbrebuild scan on the table(s) to see if it needs to be rebuilt. If so, rebuild it +
-  - Launch SSLIP and SMS - confirm that the error no longer appears+
  
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Contact Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Contact Database"
 +</code>
  
 +**<font 16px/inherit;;inherit;;inherit>SQL 3</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Customer Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Customer Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 4</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Estimate Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Estimate Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 5</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Estimate Details Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Estimate Details Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 6</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Estimate Modifiers Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Estimate Modifiers Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 7</font>**
 +
 +<code sql>
 +
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\GL Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\GL Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 8</font>**
  
 <code sql> <code sql>
Line 91: Line 141:
 </code> </code>
  
 +**<font 16px/inherit;;inherit;;inherit>SQL 9</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Order Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 10</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Details Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Order Details Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 11</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Modifiers Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Order Modifiers Database"
 +</code>
 +
 +**<font 16px/inherit;;inherit;;inherit>SQL 12</font>**
 +
 +<code sql>
 +INSERT INTO "\\server\Cyrious$\SMS\NewData\Order Modifiers Database"
 +SELECT *
 +FROM "\\server\Cyrious$\SMS\Data\Order Modifiers Database"
 +</code>
 +</code>