Status: Verified
GOAL:
How to troubleshoot index corruption errors
GOAL:
How to identify the root cause of indexing errors 1422 and 2699
GOAL:
How to troubleshoot errors 1422, 2699, 12002 and 12112.
GOAL:
What to do to solve errors 1422, 2699, 12002 and 12112.
GOAL:
What errors 1422, 2699, 12002 and 12112 mean.
GOAL:
SYSTEM ERROR: Index in for recid could not be deleted. (1422)
GOAL:
SYSTEM ERROR: proixdel failed for word indexing of array (2699)
GOAL:
SYSTEM ERROR: proixdel failed for word indexing of array, index of table . (12002)
GOAL:
Unable to delete old values for the index of a word-indexed field (index <index-name> of table <table-name>). The index may be damaged. (12112)
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
Overview
Errors 1422 and 2699 read respectively:
SYSTEM ERROR: Index %s in %s for recid %D could not be deleted (1422).
SYSTEM ERROR: proixdel failed for word indexing of array (2699)
The errors 12002 and 12112 replace the error 2699 in later OpenEdge releases. For the purpose of this Solution, these three are identical.
These error messages are strictly related to word indexes when error 2699 occurs, while 1422 occurs on ?ordinary? indexes but, other than that, they most often have the similar causes and must be investigated the same way.
This Solution discusses what these errors mean and ways to address them. To read it, understanding of the 4GL and of the database engine is needed.
This Solution does not address the case where the table and index reported in error 1422 or 2699 relate to a 4GL TEMP-TABLE.
Also, in the following we refer to idxbuild, idxfix and idxcheck as shorthand for the following commands respectively:
proutil <dbname> -C idxbuild
proutil <dbname> -C idxfix
proutil <dbname> -C idxcheck
Please review the Progress documentation and other Solutions for the exact syntax and the possible startup options for these commands.
When and why these errors occur
1422 and 2699 occur when a record is either being deleted or updated.
In case the record is being deleted, what happens is (among other things):
1) The record is fetched via a specific index (for example: indexA).
2) The index entry in indexA related to the record is deleted.
3) For every other index defined in the table:
a) The values of the fields that participate in the index are retrieved from the record.
b) The index is searched for an index entry that matches such values.
c) The matching index entry is deleted.
4) The record is deleted.
Errors 1422 and 2699 indicate that step 3b could not find any index entry.
In case the record is being updated and the update is related to indexed fields (for example any of the fields in indexB), what happens is (among other things):
1) The record is fetched via a specific index (for example: indexA).
2) The values of the fields that participate in indexB are retrieved from the record before the change.
3) indexB is searched for an index entry that matches such values.
4) The matching index entry is deleted.
5) The indexed fields are changed.
6) A new index entry reflecting the new values for the indexed fields is created.
7) The new index entry is added to indexB.
Errors 1422 and 2699 indicate that step 3 could not find any index entry.
If the mentioned steps fail, the index that triggers the errors has relational integrity problems, as a Progress index is expected to have entries for each and every user record.
Please note that errors 1422 and 2699 do not belong to the class of ?index corruption? problems: properly speaking, index corruption occurs when index nodes point to non existing nodes, or to nodes in other indexes, or other similar problems where the physical integrity of the index is compromised; instead, errors 1422 and 2699 indicate that an index entry is missing, and this is more of an issue related to the logical consistency between the set of index entries in the index and the set of records in the database.
The possible causes
One thing to always keep in mind when dealing with errors 1422 and 2699 is that, although they do indicate a relational integrity problem, they give us no information whatsoever with regard to how and when the integrity problem was introduced in the .first place.
As a matter of fact, in most cases there is no error message to tell us explicitly that an index problem has been introduced; therefore the usual debugging techniques do not really apply here: for example, a 4GL example that reproduces errors 1422 or 2699 is of no use for investigating the real cause of these errors, because that example only shows the effect of the index inconsistency, not its cause.
Experience has proven that the two errors occur mostly because of the following:
- A duplicate record was added to an inactive unique key; then, the unique index was (re)activated via idxbuild or an offline idxfix. In this case, because the index is unique Progress could only add one of the duplicate record keys to the index during idxbuild or idxfix; later, a 4GL program updating or deleting the record that was not added to the index may generate 1422 or 2699.
- It?s rare, but certainly possible, that hardware corruption can lead to this problem. For example, a disk failure for the disk that holds database extents with index blocks may damage one of those extents, and thus one of the index blocks in that extent. Usually such hardware failures result in other, more varied symptoms at the same time as the errors 1422 or 2699, up to and including symptoms in other applications running on the same machines.
- When access is forced into a database using proutil -C truncate bi ?F, crash recovery is skipped; that can lead to physical and logical inconsistencies in the database and errors 1422 and 2699 are among the well-known consequences of using ?F (please see Solution P24330 for further details on using -F in this context).
- When the ?i option is active and a database crashes for any reason, Progress cannot go through crash recovery. The consequences and actions to take are identical to those described at the previous point.
- When the ?r option is active and a system failure occurs (such as a power outage), Progress cannot go through crash recovery either. The consequences and actions to take are identical to those described at the previous point.
- Usage of incompatible codepages. If codepage conversions fail or are skipped, searching the index or comparing a record with it's index keys may fail. This is most likely to be seen in databases that use the undefined codepage.
- Incorrect use of word break tables (the required ones are not loaded, or the wrong one is used). This impacts word-indexes; the word break rules determine how the text being indexed is broken up into the individual words on which the index keys are based. This is typically introduced when a new database is set up, or if an existing one has it's codepage converted.
- A bug in the OpenEdge release.
For all the above points, except the last one, the usual troubleshooting techniques apply: for example, the database log file will report usage of -F, -i, or -r.
Also, the operating system logs should be scanned for hardware errors: any problem reported in those log files must be addressed with utmost priority, because clearly errors 1422 and 2699 are just the tip of a problem that may compromise database integrity seriously in the near future (if it has not already done so).
Once the above has been completely ruled out, the remaining possibility is that errors 1422 and 2699 are the consequences of a bug, which is the subject of the rest of this document.
If a bug is suspected ...
Because over time a number of bugs that generate 1422 and/or 2699 have been fixed, investigation of these errors on an old version of our product does not really make sense, so the very first requirement is to update to the latest version possible for your environment. For example, if you are running any version 9 product, please update to 9.1E .plus the latest Service Pack for your platform. Similarly, if you are running any OpenEdge product, please update to the latest commercial release plus the latest Service Pack.
Be informed that mature versions of Progress (such as version 8) will not receive any bug fixes further to those contained in the latest Service pack or Patch level for that version.
After the new version has been installed, we have to remember that 1422 and 2699 are symptoms of an index consistency problem that potentially may have occurred months before.
At the point in time when 1422 and 2699 occur, investigation on what actually caused the current inconsistencies is virtually impossible, so we have to focus on identifying the next time that the index inconsistency is introduced.
Be warned that unfortunately this is not going to be an easy task.
To identify when the index inconsistency is introduced, apply the following steps:
1) Install the latest Progress or OpenEdge version for your environment. As outlined before, there is no point in trying to narrow down errors 1422 and 2699 when an old, possibly buggy version is installed.
2) Run idxfix or idxcheck on the whole database in order to identify which indexes have problems. If a problem is found, these tools will also report the actual index key values in the indexes and the expected key value based on the contents of the record, which is useful debugging information.
Do not use idxbuild when attempting to identify the root cause of the issue. Idxbuild ignores the index' current state and blindly rebuilds the index keys from scratch. As a result, while it will eliminate the symptoms, it destroys the evidence needed for further investigation in the process.
3) Identify which programs interact with the tables to which the problematic indexes belong. These are the programs that:
- create or delete records in the associated table,
- modify one or more of the fields that participate in any index.
4) Add logging to these programs to trace the program flow. Key points to log are:
- At what time the programs are run and by whom
- When do the transactions and their subtransactions start, when they end and how they end (back out or commit)
- What values are assigned to the record at which time, and within which (sub-)transaction
5) Re-baseline all indexes, so that we can be confident that future occurrences of 1422 or 2699 are due to index problems that were introduced in the database recently, and so that we know for certain that the bug we are researching was hit again with the later Progress or OpenEdge version. In order to do so, run idxfix (online or offline) or idxbuild (offline only) on all indexes.
6) Schedule idxfix (online) or idxcheck (offline) on the affected indexes to be run on the database periodically and as frequently as possible; output from idxfix or idxcheck should be redirected to a file for later inspection. What we expect from this is that run N comes out clean, while run N+1 will report index problems. At this point, we are certain that logical index inconsistencies were introduced between those two runs.
7) Identify which programs interacted in that time frame with the table(s) to which the inconsistent index(es) belong.
In order to do so, the logging outlined at step 3 will be most helpful; also, idxfix and idxcheck should report the RECID?s of the records for which the indexes are now inconsistent, and looking at those records may help understand by which programs they were created and/or modified.
If, by running these steps, there is still no reasonable certainty with regard to exactly which program caused the logical inconsistencies, the only option. is to re-iterate from step 3, increasing the granularity of the logging level, and possibly temporarily disabling as many of the programs that interact with the problematic indexes as possible.
Once it has been determined how the index inconsistencies are introduced, it should be possible to build a reproducible scenario (in a lab environment) where it can be investigated further by Technical Support.
Where does Technical Support come into the picture?
As mentioned before, a database and a 4GL piece of code that show errors 1422 and/or 2699 will not help in finding out what caused the index problem in the first place.
What Technical Support needs is a database, plus a piece of code and a list of actions that will show clearly that an index is not being properly updated and kept in sync with the set of records in said database. In general, the problem can be confidently reproduced when Technical Support can:
- Run idxcheck on the database, and idxcheck will not report any index problems whatsoever.
- Run the piece of code.
- Follow the list of actions.
- Run idxcheck a second time, but this time idxcheck will report errors.
Until this can be done, it?s not possible to speak of a ?reproducible case? for the bug.
Technical Support can provide help in the stage of narrowing down the problem, as outlined in the previous section. .