Forum Stats

  • 3,770,464 Users
  • 2,253,118 Discussions
  • 7,875,466 Comments

Discussions

One of the secondary indexes is not complete

googol4u
googol4u Member Posts: 6
edited Mar 16, 2014 9:27PM in Berkeley DB Java Edition

Hi,

I have had a entity that have 18847 records.  It contains a primary key and several secondary keys.  from the following verify output, we can see, all indexes are complete except for ProductId.  What should I do to correct this error?

Verifying database persist#gdlogs#test.TableWhProductStorageCard

Checking tree for persist#gdlogs#test.TableWhProductStorageCard

BTree: Composition of btree, types and counts of nodes.

        binCount=149

        binEntriesHistogram=[40-49%: 1; 80-89%: 1; 90-99%: 147]

        binsByLevel=[level 1: count=149]

        deletedLNCount=0

        inCount=3

        insByLevel=[level 2: count=2; level 3: count=1]

        lnCount=18,847

        mainTreeMaxDepth=3

BTree: Composition of btree, types and counts of nodes.

Verifying database persist#gdlogs#test.TableWhProductStorageCard#BatchNo

Checking tree for persist#gdlogs#test.TableWhProductStorageCard#BatchNo

BTree: Composition of btree, types and counts of nodes.

        binCount=243

        binEntriesHistogram=[40-49%: 43; 50-59%: 121; 60-69%: 30; 70-79%: 23; 80-89%: 17; 90-99%: 9]

        binsByLevel=[level 1: count=243]

        deletedLNCount=0

        inCount=4

        insByLevel=[level 2: count=3; level 3: count=1]

        lnCount=18,847

        mainTreeMaxDepth=3

BTree: Composition of btree, types and counts of nodes.

this secondary index is correct. (the lnCount is the same as primary index)


Verifying database persist#gdlogs#test.TableWhProductStorageCard#ProductId

Checking tree for persist#gdlogs#test.TableWhProductStorageCard#ProductId

BTree: Composition of btree, types and counts of nodes.

        binCount=168

        binEntriesHistogram=[40-49%: 16; 50-59%: 47; 60-69%: 39; 70-79%: 26; 80-89%: 26; 90-99%: 14]

        binsByLevel=[level 1: count=168]

        deletedLNCount=0

        inCount=3

        insByLevel=[level 2: count=2; level 3: count=1]

        lnCount=14,731

        mainTreeMaxDepth=3

BTree: Composition of btree, types and counts of nodes.

this index is not complete.(lnCount is less than the primary index)  So when use this index to iterate the rows, only the first 14731 record will be returned.


Best Answer

  • Greybird-Oracle
    Greybird-Oracle Member Posts: 2,690
    Accepted Answer

    Apparently, somehow your secondary index DB has become out of sync with your primary.  Normally this is caused by not using a transactional store (EntityStore.setTransactional).  But whatever the cause, I'll describe how to correct the situation by rebuilding the index.

    1) Take your application off-line so that no other operations are occurring.

    2) Make a backup in case something goes wrong during this procedure.

    3) Do not open the EntityStore yet.

    4) Remove the index database that is out of sync (persist#gdlogs#test.TableWhProductStorageCard#ProductId) by calling Environment.removeDatabase with this name.

    5) Rebuild the index database by simply opening the EntityStore.  This will take longer than usual, since the index will be rebuilt before the EntityStore constructor returns.

    6) Confirm that the index was rebuilt correctly.

    7) Bring your application back on-line.

    --mark

Answers

  • googol4u
    googol4u Member Posts: 6

    When I try to delete any record whose primary key is greater than 14731.  An Exception will raise:

    com.sleepycat.je.SecondaryIntegrityException: (JE 5.0.103) Secondary is corrupt: the primary record contains a key that is not present in the secondary

  • Greybird-Oracle
    Greybird-Oracle Member Posts: 2,690
    Accepted Answer

    Apparently, somehow your secondary index DB has become out of sync with your primary.  Normally this is caused by not using a transactional store (EntityStore.setTransactional).  But whatever the cause, I'll describe how to correct the situation by rebuilding the index.

    1) Take your application off-line so that no other operations are occurring.

    2) Make a backup in case something goes wrong during this procedure.

    3) Do not open the EntityStore yet.

    4) Remove the index database that is out of sync (persist#gdlogs#test.TableWhProductStorageCard#ProductId) by calling Environment.removeDatabase with this name.

    5) Rebuild the index database by simply opening the EntityStore.  This will take longer than usual, since the index will be rebuilt before the EntityStore constructor returns.

    6) Confirm that the index was rebuilt correctly.

    7) Bring your application back on-line.

    --mark

  • googol4u
    googol4u Member Posts: 6

    Thanks a lot!

    One more question:  Anyway to detect this?  because when this happens, no way to detect that the secondary index are out of sync with primary unless the deleted key is out of the range of the secondary key.  even insertion will not cause any exception or cause the index to auto sync with the primary.

  • Greybird-Oracle
    Greybird-Oracle Member Posts: 2,690

    We don't have any tools for scanning and matching primary records with to secondary indexes, although you could write one.  When accessing records you may get the SecondaryIntegrityException (as you know), but this is probably not a desirable way to detect the problem.

    Are you calling StoreConfig.setTransactional(true)?

    --mark

    Greybird-Oracle
  • googol4u
    googol4u Member Posts: 6

    No.  I do not use transaction.  Because the data has already been imported to the database and I am just wring some program to read the data, during this period, I have added some secondary indexes into some of the entities and then I found that some of the secondary indexes are not completed.  So, for this kind of operation (adding indexes to entities by annotations), is it true that using transaction can 100% avoiding secondary index corruption?

  • Greybird-Oracle
    Greybird-Oracle Member Posts: 2,690

    Yes, you should use transactions.  See:

    SecondaryDatabase (Oracle - Berkeley DB Java Edition API)

    All you have to do is call StoreConfig.setTransactional(true).  You don't need to create an explicit transaction, since auto-commit will be used.

    --mark

    Greybird-Oracle
This discussion has been closed.