Clients fail to update records with error 11206 due to 32KB record/field limit

Clients fail to update records with error 11206
IDXBUILD fails to build records keys with error 6859
Binary dump/load fails with error 12370
IDXCHECK reports error 15913 and 8783 errors for every index on that table's recids reported in 6859
IDXCHECK  hangs on the final 2805 message with the following stack: 
 

PROUTIL dbscan reports no errors on blocks and records

A record cannot exceed 32 KB, similarly a field in a table has a maximum length of 31,991 byte ( a one-field table in other words). There is additionally the index limit: An index entry is limited to ~ 1970 characters when largekeys (LKE) are enabled (since 10.1B on 4 or 8KB database blocksize), otherwise 188 bytes: What are the index limits in OpenEdge?

• The update request fails with error 11206, which prevents writing this updated data to the record in question and should UNDO the transaction.
• The IDXBUILD 6859 error, confirms the index cannot be built for the key field and the subsequent IDXCHECK 15913 8783 messages confirm there is no index for these records.
• The stack thrown by IDXCHECK is due to the allocated memory not being freed up properly as some of these records data exceeds 32K size 

This situation can sometimes be caused by skipping crash recovery (forcing into the database), without undertaking required corrective actions: Consequences of using the -F startup option
 

The associated records need to be dumped, deleted and then modified within limits before they are reloaded, so that they are no longer hitting the 32K limit.

• The exported information will show the content of the records and specifically that of the key fields, which will provide further clues as to what is causing the record expansion. 
• While only the recids in question need to be fixed, it is recommend to also take a look at those records in this table that are approaching the record limit and may cause similar problems in the near future.

1.   Isolate records approaching 32K limits

DEFINE VARIABLE i AS INTEGER NO-UNDO.

FOR EACH <tablename> NO-LOCK:
    IF RECORD-LENGTH(<tablename>) >= 30100 THEN
    IF AVAILABLE <tablename> THEN DO:
        OUTPUT TO bigrecs.txt APPEND.
        PUT RECID(<tablename>) RECORD-LENGTH(<tablename>) SKIP.
        EXPORT <tablename>.
        OUTPUT CLOSE.
        i = i + 1.
    END.
END.
DISP i.

2.   Delete these Records

The records can be deleted using the reported RECIDS through IDXFIX, Option 6. Refer to Article

• How to delete a record and its index entries using IDXFIX?   

or by using the recid to delete the record with ABL:
 

FIND FIRST <tablename> WHERE RECID(<tablename>) = <RECID>.
DELETE <tablename>.

3.  Modify the record content and import the records back

Open ‘bigrecs.txt’ created in Step 1. After reviewing and amending the content accordingly, rename the file (fixthisrecord2) to use to import the records.

INPUT FROM VALUE ('./fixthisrecord2.out'). 

DO WHILE NOT ERROR-STATUS:ERROR TRANSACTION: 
CREATE <tablename>. 
IMPORT <tablename>. 
END.

Similarly, LKE index key are limited to approximately 1970 characters. This means that while the record length may not be anywhere near 32 KB, the sum of the key-fields may be approaching the index size limit. This is not an exact undertaking due to the dynamic nature of the tree structure and in particular index compression algorithms. Never-the-less, an exercise worth undertaking to re-consider the value of having indexes with such large values.

1.  Dump the .df for the table through the Data Admin Utility
2.  Isolate the key fields for the index
3.  Report on the isolated records associated with large key-fields for further investigation

DEFINE VARIABLE i AS INTEGER NO-UNDO.

FOR EACH < tablename > NO-LOCK:

IF LENGTH(< tablename >.< key-field1 >,"RAW") + LENGTH(< tablename >.< key-field2 >,"RAW") > 1900 THEN
IF AVAILABLE < tablename > THEN DO:
	OUTPUT TO bigidxContent.txt APPEND.
	PUT RECID(< tablename >) RECORD-LENGTH(< tablename >) LENGTH(< tablename >.< key-field1 >,"RAW") + LENGTH(< tablename >.< key-field2 >,"RAW") SKIP.
	// EXPORT < tablename >.< key-field1 > < tablename >.< key-field2 >.
	OUTPUT CLOSE.
	i = i + 1.
	END. /* if avail */
END. /* for each */
DISP i.

 

Progress Article: 

 DBTOOL SQL WIDTH crashes with "Unable to upgrade record"