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.

 

[Scott24x7]

The OP mentioned they are free tables. (Well, he said "They are not part of a DB", so I assume he meant they are not part of a DBC.
That said, I use essentially this process all the time. I have one table that is prone to corruption because it gets used in a process where images are scanned and OCRd, and when that fails, it can sometimes be bad news. The index gets corrupted frequently. So now before I even open the table, I do:

lCurValidate = SET("TABLEFALIDATE")
SET TABLE VALIDATE TO 0
USE TempOCR
DELETE TAG ALL
INDEX ON CONTACTID
SET TABLE VALIDATE TO lcValidate

This table is part of my DBC. The moment you issue DELETE TAG ALL it kills the .CDX associated with the table, and when you issue "INDEX ON" command, it creates a new file.
You can test that by not indexing, stopping in between, and looking at the files in the directory (can be run in command line, or issued one line at a time).

 

[EinTerraner]

OOOPS Guys...
I forgot to include the most important information. It was already very late and I was already tired of trying to locate the error.

 BLANK IN (.TmpAlias) ;                                            && wipe both groups
        FOR INLIST(BEDGRUP, Lo_Line.BEDGRUP, Tc_Line.BEDGRUP)

the error occurs whe i do the blank in my working area
if i seperate the BLANK like here

 BLANK IN (.TmpAlias) FOR BEDGRUP == Lo_Line.BEDGRUP
 BLANK IN (.TmpAlias) FOR BEDGRUP == Tc_Line.BEDGRUP

it occurs sometimes in the 1st or in the 2nd BLANK-Line

You're mention of "correct the record order" should be managed by a unique index

those 2 helpcursors, where i renumber the required line actually dnt need any indexes. only the mirrired GRID.Recordsource has the 2 mentioned tag's

The OP mentioned they are free tables.

actually the GRID.RecordSource is also a cursor. I dnt work directly on the source-file.

later on i'll try tho solve this problem with

     DELETE FOR INLIST(BEDGRUP, Lo_Line.BEDGRUP, Tc_Line.BEDGRUP)

** and in the re-inserting loop
    SELECT(.TmpAlias)
    LOCATE FOR DELETED()
    IF !FOUND(.TmpAlias)
        APPEND BLANK IN (.TmpAlias)
    ENDIF
    RECALL
    SCATTER NAME Lo_Data MEMO

here a overiew from all cursors and their settings in this form

 

[EinTerraner]

It's ridiculous how this error occurs. I've been using the process mentioned above for years when inserting external data blocks (BEDGRUP) or multiple rows (BEDPOSI).
Also the data recycling, where I fill empty and/or deleted data records with new data.
With the small difference that with this import, there can't actually be any empty data records.
Records that are deleted have their own deleted marker (not the DELETED one) and are renumbered at the end of the respective group.
Interestingly, this works if I use a CREATE TABLE instead of a CREATE CURSOR. The error didn't occur when I tested it in a loop with several hundred runs. When doing a loop test with a CURSOR, the error occurred after the 2nd round.

My goal was to combine the two, actually identical functions into a single method in the GRID.

 

[Chris Miller]

A free table instead of a cursor ot a dbc table instead of a cursor. A cursor can have some features only a DBC table can have, like long field names, but it can't have a primary index, Nevertheless a cursor compares more to a DBC table than a free table in the features it supports. I think they gave cursors all features you may need for a query results (like long field names) but not features a result doesn't inherit from whatever tables it queries, like priamry key index. Though you can define default values, which make no sense for cursors as query results. Anyway, it's a mix including some things available for DBC tables.

What are you doing with the cursor in the code? Are you using ALTER TABLE at some point? Are you using INSERT as xBase command to insert records before others, ie shifting records physically to insert new reocrds not at the end of the cursor? I know you get the error when using the BLANK command, which is another xBase command. Do you have autinc fields? Then use BLANK AUTOINC and even better BLANK DEFAULT AUTOINC. These new command options were introduced to allow record recycling together with defaults and autoinc, to have the defaults feature as if a record was created by INSERT-SQL or APPEND BLANK and not cause autoinc uniqueness violation, too.

 

[EinTerraner]

Do you have autinc fields?

nope, there is nothing special in the cursor. see the list of open/used files above

What are you doing with the cursor in the code?

removing several records (actually preparing them like a imported set of records), changing some fields and use the same procedure to reinsert them like inserting some importet records.

 

[Chris Miller]

You haven't answereed these questiuons:
Are you using ALTER TABLE on the cursor.
Are you using INSERT in a legacy style to insert records before existing ones? INSERT BEFORE BLANK or INSERT BLANK?

I'm asking because 'I think we're closing in on something that causes the index to fail. It's extremely unusual for an index of a cursor to get wrong, as a cursor by definition is local and exclusive. But there are known problems of using ALTER TABLE on a cursor even noted in the help and using the legacy form of INSERT [BEFORE] BLANK may also be contributing to an index problem.

 

[EinTerraner]

Are you using ALTER TABLE on the cursor.
Are you using INSERT in a legacy style to insert records before existing ones? INSERT BEFORE BLANK or INSERT BLANK?

none of both with and in this cursor.
I know, ALTER TABLE causes to loose the indexes. There usually fox drops a warning.

In the other function, in which I insert existing records, I check whether I have an empty record that is filled with GATHER NAME objname. If there is no recyclable record, then I use INSERT INTO (.RecordSource) FROM NAME objname.

But what I found out last night after uncountable test rounds. In the BLANK FOR, obviously not all records in the table are deleted, but in the index they are. The first record what should be deleted remains. The same effect occurs if I use a DELETE FOR instead of BLANK FOR.

And that ONLY if I do it in an auxiliary cursor using USE DBF(src) ALIAS(hlp) SHARED AGAIN.

If I do the game with the original cursor, which is also displayed in the grid, it works. Currently working code here.

    CASE EMPTY(BEDPOSI)                    && move groups
        IF (BEDGRUP > To_Line.BEDGRUP .and. EMPTY(To_Line.BEDGRUP)) ;
        .or. To_Line.BEDGRUP > Ln_Grup
            .Flash_On(1)
        ELSE
            SET ORDER TO ("") IN (.RecordSource)
            REPLACE BEDGRUP WITH -BEDGRUP ;
                FOR BEDGRUP == Lo_Line.BEDGRUP ;
                IN (.RecordSource)

            REPLACE BEDGRUP WITH Lo_Line.BEDGRUP ;
                FOR BEDGRUP == To_Line.BEDGRUP ;
                IN (.RecordSource)

            REPLACE BEDGRUP WITH To_Line.BEDGRUP ;
                FOR BEDGRUP == -Lo_Line.BEDGRUP ;
                IN (.RecordSource)

            Lc_Done = .t.
        ENDIF

    CASE !EMPTY(BEDPOSI) ;                && move position
    .and. EMPTY(BEDSUBP)
.......
** and on the end
    ThisForm.LockScreen = .t.                    && no screen flickering
    SET ORDER TO (Lc_Sort) IN (.RecordSource) && back to prev order
    GO TOP IN (.RecordSource)                 && jus to place the selected recod in the middle
    .Refresh()
    GOTO Lc_Posi IN (.RecordSource)        && Back to the Line whas moved
    ThisForm.LockScreen = .f.
    RETURN Lc_Done

 

[Chris Miller]

Deleted records remain in the index, indexes are not filterd FOR DELETE(), only BROWSE/SQLECT-SQL, LOCATE etc. will suppress deleted records.
Therefore you can easily get index violations reusing values that were in deleted records and still exist in a CDX. no matter if DBF or cursor. If you'd want an index to remove records that are deleted, you'd need to define indexes FOR NOT DELETED(), but that's not a good idea for rushmore optimization.

But it doesn't explain indexes getting corrupt. If you try to violate uniqueness within an index you get an error, but don't break an index.

So all that is still strange. When you blank a lot of records with BLANK FOR, you don't just purge (delete) records, you only blank fields, set them to empty values. If an index wouldn't allow double values, that again can lead to index violations throwing errors, but not corrupting an index.

Since your indexes are defined with a FOR clause, they are limited to only some records, not all, I still don't see how that would cause index corruptions that are about structural broken index data. Do you have (unintentionally) set TEMP to a network directory?

 

[EinTerraner]

Do you have (unintentionally) set TEMP to a network directory?

also no... see my text-file above,
All data used by the user is mirrored locally, as very often (60%-70%) work has to be done offline.
Synchronization with one of the 4 possible servers sometimes only takes place at the weekend.
And the editing itself takes place in a cursor and not in the original data.