Overview:Errors 1422, 17630 and 2699 read respectively:
SYSTEM ERROR: Index %s in %s for recid %D could not be deleted (1422).
SYSTEM ERROR: Index <index> in <table> for recid <recid> partition <n> could not be deleted. (17630)
SYSTEM ERROR: proixdel failed for word indexing of array (2699)
When error 2699 occurs, these error messages are strictly related to
word indexes while 1422 and 17630 errors occur on
ordinary indexes. Other than this, they most often have the similar causes and must be investigated the same way.
This
Article discusses what these errors mean and ways to address them. Understanding of the 4GL/ABL language and the database engine are needed to benefit from this Article's content. This
Article does
not address the case where the table and index reported in errors 1422, 17630 or 2699 relate to a TEMP-TABLE.
In the following discussion, IDXBUILD, IDXFIX and IDXCHECK are referred to as shorthand for the following commands respectively:
$ proutil <dbname> -C idxbuild
$ proutil <dbname> -C idxfix
$ proutil <dbname> -C idxcheck
Review the Progress OpenEdge Documentation and other Articles for the exact Syntax, Parameters and Options available when running these commands.
When and why these errors occur:1422, 17630 and 2699 occur when a record is either being
deleted or
updated.
In the case of
a record being deleted, the following occurs (among other things):
- The record is fetched via a specific index (for example: indexA).
- The index entry in indexA related to the record is deleted.
- For every other index defined in the table:
- The values of the fields that participate in the index are retrieved from the record.
- The index is searched for an index entry that matches such values.
- The matching index entry is deleted.
- The record is deleted.
Errors 1422, 17630 and 2699 indicate that Step 3 could not find the index entry.
In the case of
a record being updated where the update is related to indexed fields (for example any of the fields in indexB), the following occurs (among other things):
- The record is fetched via a specific index (for example: indexA).
- The values of the fields that participate in indexB are retrieved from the record before the change.
- IndexB is searched for an index entry that matches these key values.
- The matching index entry is deleted.
- The indexed fields are changed.
- A new index entry reflecting the new values for the indexed fields is created.
- The new index entry is added to indexB.
Errors 1422, 17630 and 2699 indicate that Step 3 could not find any index entry.
When the above mentioned steps fail, the index that triggers the errors has relational integrity problems, as a Progress index is expected to have key entries for each and every user record.
Please note that errors 1422, 17630 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, 17630 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, 17630 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.
In most cases, no error message indicates explicitly when an index problem is introduced. Therefore usual debugging techniques are less useful here. For example, a 4GL program that reproduces errors 1422, 17630 or 2699 is of no use for investigating the cause of these errors; the example only shows the effect of the resulting index inconsistency, not its initial cause.
Experience has shown that these two errors occur most often because of the following:
- A duplicate record was added to an inactive unique key. Next, 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 error 1422, 17630 or 2699.
- If data exists within a table and an index definition is added as "UNIQUE" and "INACTIVE" then the true validation of the definition of the index is only done when an idxactivate or idxbuild is performed on the table. If one or more duplicate values exists within the field(s) that define uniqueness then the first record which has the values will be allowed to generate an index key, the subsequent records with "identical" values will not be allowed to have an index key created. This will generate an (1127) message during idxbuild for any records which fail uniqueness. When records, that do not have an index key, are modified or deleted the underlying code first attempts to delete all index keys for a record, but as a key is missing it fails with 1422 (old version message) or 17630 (new version message).
- A binary or bulk load was performed more than once for the same table when loading data into a database.
- Less frequently, 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, 17630 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 for example, crash recovery is skipped; that can lead to physical and logical inconsistencies in the database and errors 1422, 17630 and 2699 are among the well-known consequences of using -F. Refer to Article Consequences of using the -F startup option for further details on using -F in this context.
- When the no integrity (-i) option is active and the 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 code pages. 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 code page.
- 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 its codepage converted.
- A defect in the OpenEdge release.
For all the above points, except the last one, usual troubleshooting techniques apply: for example, the database log file will report usage of -F, -i, or -r.
The Operating System logs should be scanned for hardware errors: any problem reported in those log files must be addressed with utmost priority. In this case errors 1422, 17630 and 2699 are just one symptom of a problem that may compromise database integrity seriously in the near future, if it has not already done so.
Once the above issues have been completely ruled out, the remaining possibility is that errors 1422, 17630 and 2699 are the consequences of a Progress defect. The remainder of this Article addresses this subject.
If a defect is suspected ...Over time a number of defects that generate 1422, 17630 and/or 2699 have been fixed, it is therefore prudent to consider upgrading to the latest version possible for the environment before investigating further. For example, if running any version 9 product, please update to Progress 9.1E and apply the latest Service Pack. Similarly, if running any OpenEdge product, update to the latest commercial release plus the latest Service Pack.
Mature versions of Progress OpenEdge will not receive any defect fixes beyond those contained in the latest Service pack for that version.
Errors 1422, 17630 and 2699 are symptoms of an index consistency problem that potentially may have occurred months before. At the point in time when errors 1422, 17630 and 2699 occur, investigation on what actually caused the current inconsistencies is virtually impossible. At this point it is necessary to
focus on identifying the next time that the index inconsistency is introduced. Unfortunately, this is not an easy task.
To identify when the index inconsistency is introduced, apply the following steps:
- Install the latest Progress or OpenEdge version for the environment.
- Run idxfix or idxcheck on the whole database in order to identify which indexes have problems. If a problem is found, these tools will 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.
- In addition idxfix can fix the indexes based on the key values in the current record content. This can be used to limit the practical impact of the issue while gathering the data to troubleshoot it.
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.
- Identify which programs interact with the tables to which the problematic indexes belong. These are the programs that:
- Create and specifically, Delete records in the associated table.
- Modify one or more of the fields that participate in any index.
- 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
- Re-baseline all indexes. This increases confidence that future occurrences of 1422, 17630 or 2699 are due to index problems that were introduced in the database recently and establishes that the defect being researched was encountered again with the latest Progress or OpenEdge version. To re-baseline the indexes, run idxfix (online or offline) or idxbuild (offline only) on all indexes.
- 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 is expected from this is that run N comes out clean, while run N+1 will report index problems. At this point, it is established that logical index inconsistencies were introduced between those two runs.
- Identify which programs interacted in that time frame with the table(s) to which the inconsistent index(es) belong. The logging outlined at step 3 will help with this. Idxfix and idxcheck will report the RECID’s of the records for which the indexes are now inconsistent. Examine those records to determine by which programs they were created and/or modified.
If after running these steps there is still no reasonable certainty with regard to exactly which program caused the logical inconsistencies, repeat the above steps beginning with Step 3. Increase the granularity of the logging level, and consider 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 which can be investigated further by Progress Technical Support.
Where does Technical Support come into the picture?As mentioned previously, a database and a 4GL piece of code that show errors 1422 and/or 2699 alone 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 is reproduced when Technical Support can:
- Run idxcheck on the database with no index problems reported.
- Run the piece of code.
- Follow the list of actions provided.
- Run idxcheck a second time with idxcheck reporting errors.
Technical Support can provide assistance with narrowing down the problem as outlined in the previous section.
