Salesforce

IDXCHECK not Validating all keys for each record

Printable View
« Go Back

Information

 
TitleIDXCHECK not Validating all keys for each record
URL NameIDXCHECK-not-Validating-all-keys-for-each-record
Article Number000139754
EnvironmentProduct: OpenEdge
Version: 11.6.3, 11.7.x, 12.x
OS: All supported platforms
Question/Problem Description
IDXCHECK online does not detect corruption, in order that index repair operations can take place ahead of end-users suffering application outages.
Only and offline IDXCHECK find corruption error 8779

The default IDXCHECK online validation options do not report index corruption

The same IDXCHECK offline validation options does report that more index entries exist than records (15913)
* 1 - Validate physical consistency of index blocks
* 2 - Validate keys for each record
* 4 - Validate key order

When only IDXCHECK "2 - Validate keys for each record" is run online or offline, no index corruption is reported

 
Steps to Reproduce
Clarifying Information
Online or offline IDXCHECK Option 2 does not find that there are missing records for the key entries when run in the following combinations:
  • IDXCH_2
  • IDXCH_1&2
  • IDXCH_2&3
  • IDXCH_1&2&3
It is only when "Validate keys for each record" is combined with Option 4 - Validate key order /offline/, that indexes missing records entries are found 
"(15913) - 24791 entries, but 24789 records found"
When Option 3 is included, error 17297 detects orphan keys.

Online IDXCHECK no corruption is found, offline corruption is found (15913, see above) with the following Option combinations:
  • IDXCH_2&4
  • IDXCH_1&2&4
  • IDXCH_2&3&4
  • IDXCH_1&2&3&4
Error Message(17297) SYSTEM ERROR: Failed to get record <recid> of table/partition <table>/<partn> in upcScanIdx, error -1

(15913) SYSTEM ERROR: Index <index-num> (<owner-name>.<tablename>, <indexname>) contains <entry-count> entries, but <record-count> records found.
(8779) SYSTEM ERROR: Index <index-num> (<owner-name>.<tablename>, <indexname>) contains <entry-count> entries, but <record-count> records found.

(8829) Index <num> (<tablename>, <indexname>): Found invalid key <> recid <recid>.
Defect Number
Enhancement Number
Cause
The reason why Online IDXCHECK no corruption is found while IDXCHECK offline corruption is found, is because in this database the corruption is that some keys do not have records associated with them. 

The 8779 / 15913 error indicates that the total number of records does not match the total number of keys for an index.
  • This is a valid statement only if the table involved in idxcheck does not change during the check. 
  • This is true for offline idxcheck because no other user can alter the table.
  • When idxcheck runs online, it's no longer valid to assert that the total number of records should match the total number of keys for an index, 
    because other users can add/delete records which changes the total number of records and keys while idxcheck is running. 
Option 2 of idxcheck (Validate keys for each record) does not detect orphaned keys, it scans all records and check if the keys associated with the records are valid or not. When Option 3 (Validate record for each key) is included, it can detect orphaned keys.
Resolution
To detect index keys that do not have records associated with them, run IDXCHECK online with the Option: L- Lock table during the check.
When the table is locked, IDXCHECK will verify the total number of records and keys.  For further information refer to Article: To remove the indexes that were created (without records) use online: IDXFIX Option 2.  Scan indexes for invalid index entries.

Keys without records will be deleted:
Index <index number> (<owner name>.<table name>, <index name>):  Deleted key <key value> recid <record identifier>. (8828)

Example: proutil [dbname] -C idxfix < idxfix.in 

Where the 'idxfix.in' is a text file with the following content:
2
T
17 <- Insert the menu table number here
G
A
G
Y
Y
Y
<there is an empty line here at the end of this file>
Workaround
Notes
Keyword Phrase
Last Modified Date6/15/2022 12:41 PM
Powered by