index does not match the table

[EinTerraner]

This error occurs after several runs of the exchange procedure. Sometimes it fires after 2 runs or even after 10 rounds.



I have a table, sorted by group-number, line-number and subtext-number (like "1.7.3") all are numeric
The result should look something like this
before moving -> after moving ->
To move a group (or a row/sub-row), you just have to swap the corresponding numbers. I have the following code for this.

WITH THIS && MyGrid.DataMove(Tc_Line as Object)
** Use a mirror-cursor because .RecordSource might have a active filter
    USE DBF(.RecordSource) ALIAS(.TmpAlias) IN SELECT(.TmpAlias) SHARED AGAIN 
** Lo_Line.BEDGRUP = Destination
** Tc_Line.BEDGRUP = Source
    SELECT * FROM (.TmpAlias) ;
        INTO CURSOR (.SrcAlias) READWRITE ;               && fetch source group
        WHERE Lo_Line.BEDGRUP == BEDGRUP
    SELECT * FROM (.TmpAlias) ;
        INTO CURSOR (.DstAlias) READWRITE ;              && fetch target group
        WHERE Tc_Line.BEDGRUP == BEDGRUP
    REPLACE BEDGRUP WITH Lo_Line.BEDGRUP ;        && Rename target group to source group
        IN (.DstAlias) ;
        FOR .t.
    REPLACE BEDGRUP WITH Tc_Line.BEDGRUP ;        && Rename source group to target group
        IN (.SrcAlias) ;
        FOR .t.
    BLANK IN (.TmpAlias) ;                                            && wipe both groups
        FOR INLIST(BEDGRUP, Lo_Line.BEDGRUP, Tc_Line.BEDGRUP)
    Lc_Done = .t.

and to reinsert those two xchanged groups/Lines

** Replace moved Group back to table
** recycle blank lines.
** otherwise table becomes large
    IF Lc_Done
        GO TOP IN (.SrcAlias)
        DO WHILE !EOF(.SrcAlias)
            SELECT(.SrcAlias)
            SCATTER NAME Lo_Line MEMO
            SELECT(.TmpAlias)
            LOCATE FOR EMPTY(BEDNUMM)
            IF !FOUND(.TmpAlias)
                APPEND BLANK IN (.TmpAlias)
            ENDIF
            GATHER NAME Lo_Line MEMO
            SKIP IN (.SrcAlias)
        ENDDO

        GO TOP IN (.DstAlias)
        DO WHILE !EOF(.DstAlias)
            SELECT(.DstAlias)
            SCATTER NAME Lo_Line MEMO
            SELECT(.TmpAlias)
            LOCATE FOR EMPTY(BEDNUMM)
            IF !FOUND(.TmpAlias)
                APPEND BLANK IN (.TmpAlias)
            ENDIF
            GATHER NAME Lo_Line MEMO
            SKIP IN (.DstAlias)
        ENDDO
    ENDIF

any idea what i'm doing wrong?

 

[Scott24x7]

What indexes exist on the table? Are they compound? Are they creating a unique index?

How big is the table?
Also, why are you doing it this way?

Can you explain what you are trying to achieve (what your business logic is in this case)?

 

[EinTerraner]

What indexes exist on the table? Are they compound? Are they creating a unique index?

there actually two tags. native table. not in a DB

* structure
* DOCID C(8)
* BEDGRUP,BEDPOSI,BEDSUBP N(3)
index on DOCID+trans(bedgrup, "@L 999")+;
               trans(bedposi, "@L 999")+;
               trans(bedsubp, "@L 999") ;
        tag ID_EDIT ;
        for !empty(bedgrup)
index on DOCID+trans(bedgrup, "@L 999")+;
               trans(bedposi, "@L 999")+;
               trans(bedsubp, "@L 999") ;
        tag ID_GSUM ;
        for bedposi=999

How big is the table?

not really, but might contain up to 500 rows, splitted in 5-30 groups (BEDGRUP) and in the group several positions (BEDPOSI) and within the positions s few detail-lines (BEDSUBP)

Also, why are you doing it this way?

the code above is only a snippet from a whole CASE-construct. here is a rough functional description

case SWAP BEDGRUP 2/3
case move BEDPOSI 5 to BEDGRUP 4 as BEDPOSI 3
case import BEDGRUP 4,5,6 between BEDGRUP 2/3
case insert BEDPOSI before/after current BEDPOSI or end of BEDGRUP
case delete any BEDGRUP or BEDPOSI or BEDSUBP

Within these CASE blocks, it is first checked whether the process is valid. For example, the first group cannot be moved up or the last existing group cannot be moved down.
Then the renumbering and space release are prepared and acknowledged with Lc_DONE = .t.

Can you explain what you are trying to achieve (what your business logic is in this case)?

there are more needs for this usage.
1.) Correct the recorded order of the groups/positions/detail lines
2.) Import entire groups/positions with detail lines from another document (pre-defined specifications)
3.) Insert a new group/position/detail line

2.) Record several groups with n positions (+ n detail lines) from an already recorded but not completed processing description for resubmission. And they can be inserted in the middle of the document. A restructuring of the group order may also be necessary here.

the next problem we have. the user is creating new documents offline. far away from a stable internet-connection and other civilisation facilities (far out in the deep forest). so he's creating some new documents without a valid DOCID. This will be given, as soon he might reconnect to the office-computer. usually only towards the weekend (Thursday or Friday)

 

[Scott24x7]

Well it's all small data, so what you could do is any time you reprocess this with reordering the set of records, is to rebuild your index.
I would suggest DON'T do this with REINDEX, rather what you have above.

The indexes are both compound, and risk not being able to create a unique ID (what that means is even your compound index doesn't create a unique value when all those expressions are added together... so that can confuse the index. For simple example, what if two records are ABCDEFGH+999+999+999 and then another record is the same (Your index evaluates to ABCDEFGH999999999. Also, what is the risk that any value of bedgrup, bedposi or bedsubp is greater than 999? In that case your TRANSFORM command will yield ***. If this is happening in more than one record, you may be confusing the index. Indexes work best when they create a "Unique" combined expression.
The index then becomes corrupt, and the error you get back may state what you are seeing "Index doesn't match table" especially if this is happening frequently and randomly.

Since you mention the tables are rarely even 500 records long, the time to reindex in your code is sub 1 second, unnoticable.
So after your DO LOOP where you rearrange everything you could call this:

SELECT (.SrcAlias)
DELETE TAG ALL
index on DOCID+trans(bedgrup, "@L 999")+;
               trans(bedposi, "@L 999")+;
               trans(bedsubp, "@L 999") ;
        tag ID_EDIT ;
        for !empty(bedgrup)
*
index on DOCID+trans(bedgrup, "@L 999")+;
               trans(bedposi, "@L 999")+;
               trans(bedsubp, "@L 999") ;
        tag ID_GSUM ;
        for bedposi=999
*
SELECT (.DestAlias)
index on DOCID+trans(bedgrup, "@L 999")+;
               trans(bedposi, "@L 999")+;
               trans(bedsubp, "@L 999") ;
        tag ID_EDIT ;
        for !empty(bedgrup)
*
index on DOCID+trans(bedgrup, "@L 999")+;
               trans(bedposi, "@L 999")+;
               trans(bedsubp, "@L 999") ;
        tag ID_GSUM ;
        for bedposi=999

It's a little "kludge" but it seems it would solve the problem, as the index would get rebuilt.
The rearranging you're doing and the index's ability to keep that together seems to be in question here.

Since I don't really understand what you're doing in practicality, it's kind of the best I can suggest at this point.
Probably there is a better way to achieve what you're trying to accomplish, it's not clear to me why you think you need to rearrange these, but I'm sure you have a good reason for it.
You're mention of "correct the record order" should be managed by a unique index, and then setting that index on and refreshing the view. You shouldn't be reordering the records to fit the table index so that you get them in an order you want them in. That sounds a little crazy to me.

 

[Chris Miller]

I think that error does not point out that the index is corrupt, but that the DBC information about indexes differes with the CDX header.

 

[Scott24x7]

Hi Chris, while I don't disagree with you, isn't that a form of corruption???
I was just looking for a single ubiquitous term, since my "fix" doesn't care what the error specifically is.

 

[Chris Miller]

Not sure, Scott. It would depend on whether DELETE TAG ALL also gets rid of the index related records of the DBC and cleans it that way. The problem, I think is that a DBC also maintains some information about indexes, not only the CDX file. And how they get out of sync is a bit beyond me. In theory it would need something like renaming DBF files, moving them to belong to another DBC that lacks the index information or such things, which don't happen here.

The advice to recreate indexes could help, but I'd also investigate what leads to such errors.

I got index corruptions because of the drreaded oplocks problem with SMB protocol, but I think that error was index corrupted, not "the index does not match the table", that's a different situation.

Looked up the error message, and the description of it recommends recreating the indexes. What it literally recommends is deleting the CDX file, but then you just get problems even opening the DBF, just use what Scott recommends: DELETE TAG ALL, that does not just delete the CDX but also removes any records about indexes in the DBC.