Rebuilding indexes on machine move

Apr 20, 2012 at 5:38 AM

When moving Esent databases between machines, it is possible for the machine unicode sort order to change, resulting in problems in querying esent indexes.

Is there a way to reset those? Force a rebuilt of the indexes?

Ideally, I would like to have it done through the API.

Developer
May 4, 2012 at 9:00 AM

Yes, changing Unicode sort orders is an unpleasant fact of life. And it's not just for moving files across machines, but also when upgrading OS versions, and sometimes Service Packs.

We only support deleting the 'corrupt' indices (actually out of date), and not automatically rebuilding the already-existing indices. You'll need to enumerate the indices yourself and recreate them. And you can't do that if the Unicode index is a Primary (i.e. Clustered) index. See the comment below on JET_param.EnableIndexChecking.

One simpler way is to do an offline defrag (esentutl.exe -d, or JetCompact), but that involves re-writing the entire database and creating a new file. http://msdn.microsoft.com/en-us/library/windows/desktop/gg269284(v=exchg.10).aspx

-martin

        /// <summary>
        /// When this parameter is true, every database is checked at JetAttachDatabase time for
        /// indexes over Unicode key columns that were built using an older version of the NLS
        /// library in the operating system. This must be done because the database engine persists
        /// the sort keys generated by LCMapStringW and the value of these sort keys change from release to release.
        /// If a primary index is detected to be in this state then JetAttachDatabase will always fail with
        /// JET_err.PrimaryIndexCorrupted.
        /// If any secondary indexes are detected to be in this state then there are two possible outcomes.
        /// If AttachDatabaseGrbit.DeleteCorruptIndexes was passed to JetAttachDatabase then these indexes
        /// will be deleted and JET_wrnCorruptIndexDeleted will be returned from JetAttachDatabase. These
        /// indexes will need to be recreated by your application. If AttachDatabaseGrbit.DeleteCorruptIndexes
        /// was not passed to JetAttachDatabase then the call will fail with JET_errSecondaryIndexCorrupted.
        /// </summary>
        EnableIndexChecking = 45,
 

May 4, 2012 at 9:56 AM
Hm...
Can I force it to use a specific sort order?
I did set the lcid for the index, but I don't know if that would be consistent or not

On Fri, May 4, 2012 at 12:00 PM, martinc <notifications@codeplex.com> wrote:

From: martinc

Yes, changing Unicode sort orders is an unpleasant fact of life. And it's not just for moving files across machines, but also when upgrading OS versions, and sometimes Service Packs.

We only support deleting the 'corrupt' indices (actually out of date), and not automatically rebuilding the already-existing indices. You'll need to enumerate the indices yourself and recreate them. And you can't do that if the Unicode index is a Primary (i.e. Clustered) index. See the comment below on JET_param.EnableIndexChecking.

One simpler way is to do an offline defrag (esentutl.exe -d, or JetCompact), but that involves re-writing the entire database and creating a new file. http://msdn.microsoft.com/en-us/library/windows/desktop/gg269284(v=exchg.10).aspx

-martin

/// <summary>
/// When this parameter is true, every database is checked at JetAttachDatabase time for
/// indexes over Unicode key columns that were built using an older version of the NLS
/// library in the operating system. This must be done because the database engine persists
/// the sort keys generated by LCMapStringW and the value of these sort keys change from release to release.
/// If a primary index is detected to be in this state then JetAttachDatabase will always fail with
/// JET_err.PrimaryIndexCorrupted.
/// If any secondary indexes are detected to be in this state then there are two possible outcomes.
/// If AttachDatabaseGrbit.DeleteCorruptIndexes was passed to JetAttachDatabase then these indexes
/// will be deleted and JET_wrnCorruptIndexDeleted will be returned from JetAttachDatabase. These
/// indexes will need to be recreated by your application. If AttachDatabaseGrbit.DeleteCorruptIndexes
/// was not passed to JetAttachDatabase then the call will fail with JET_errSecondaryIndexCorrupted.
/// </summary>
EnableIndexChecking = 45,

Read the full discussion online.

To add a post to this discussion, reply to this email (ManagedEsent@discussions.codeplex.com)

To start a new discussion for this project, email ManagedEsent@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Developer
May 4, 2012 at 7:32 PM

Unfortunately not.

Setting the LCID to 1033 (BTW: Windows 8 ESENT now supports locale IDs, like "en-us" and "de-de") determines how the characters are sorted compared to one another -- does U-umlaut get sorted next to 'u'? What about the German Double-S? I know enough to recognize that sorting is a hard problem, and the team responsible for it has constantly shifting targets as new characters get introduced.

Anyway: The sort order does change from version to version, and there is currently no way to specify that you want downlevel sorting (e.g. 1033 sort as of Windows 7 SP1).

-martin

Aug 13, 2012 at 9:38 PM

Hello Martin,

I am currently having this problem. I have upgraded my build machine to Windows 7 from Vista
and the indexes are no longer working.
These are build-once (no update) databases and I run the defrag in code when built.

Is there anyway to automatically determine the need to upgrade the indexes in case a system upgrade
happens on the end user machine? I wish to determine and rebuild the index without the user interfering.

Best regards,
Paul. 

Developer
Aug 17, 2012 at 12:22 AM

Hi Paul,

You should set the EnableIndexChecking parameter. Here's what the C++ header file says about it. It unfortunately defaulted to 'false' in Win7 (and probably Vista too).

// These parameters control what happens when ESENT attaches to a database that was last used under a different build of the OS (the OS version
// is stamped in the database header).
//
// If JET_paramEnableIndexChecking is TRUE JetAttachDatabase() will delete indexes if JET_bitDbDeleteCorruptIndexes or return an error if
// the grbit was not specified and there are indexes which need deletion. If it is set to FALSE then JetAttachDatabase() will succeed, even
// if there are potentially corrupt indexes.

The sucky part about using JetAttachDatabase( JET_biDbDeleteCorruptIndexes ) (aka AttachDatabaseGrbit.DeleteCorruptIndexes) is that it will delete the out-of-date indices, but it is up to the application (you) to rebuild them.

And I see that the comments don't mention an important detail: These apply only to secondary indices. If a primary (clustered) index is on Unicode data, you need to do an offline defrag of the entire database, which can be fairly cosly (depends on the size of your data).

Aug 17, 2012 at 12:40 AM

Hello Martin,

Thanks for the update and the information. I failed to mention this part; the creation of the storage
is essentially through the persistent dictionary. What will you advice in this case?
For the direct use of the essent, the created database is deleted on application exit.

I have no problem rebuilding the entire index of the database, I did that and the error went away,
just wished I could automatic this for the end-user when the product is shipped.
I could modify the essent persistent dictionary source codes, if needed - just give me some tips
as to how to implement this. 

Again, thanks for the support.

Paul.

Developer
Aug 17, 2012 at 12:59 AM

PersistentDictionary hasn't been getting the attention it deserves.

Drat, I see that it (PersistentDictionary) uses a index over Unicode data.

Two possibilities:

-Catch the exception, and call defragdatabase yourself

-Modify the AttachDatabase() call to catch the exception and defrag the database from within PersistentDictionary code.

A warning: I can't accept code changes from outside, so if you change PersistentDictionary it will be your fork.

 

-martin

Aug 17, 2012 at 1:11 AM

Hello Martin,

Thanks for the quick response.

There was no exception, just some retrievals are successful but most are not.
What type of exception is expected in this case?

I am actually using a forked version, because I had to use both direct access and
the persistent dictionary in the same application the the Globals was not working
well in this case.

Best regards,
Paul. 

Developer
Aug 17, 2012 at 1:16 AM

Well, since you've already forked the code, you should change this line to true:

PersistentDictionary.cs:118:            this.instance.Parameters.EnableIndexChecking = false;       // TODO: fix unicode indexes

The exception should happen when you attach the database, one of these:

#define JET_errPrimaryIndexCorrupted		-1413 /* Primary index is corrupt. The database must be defragmented or the table deleted. */
#define JET_errSecondaryIndexCorrupted		-1414 /* Secondary index is corrupt. The database must be defragmented or the affected index must be deleted. If the corrupt index is over Unicode text, a likely cause a sort-order change. */

Or EsentPrimaryIndexCorruptedException EsentSecondaryIndexCorruptedException  in the C# world.

-martin

Aug 17, 2012 at 1:23 AM

Hello Martin,

Thank you, thank you very much. That is really useful. I really appreciate this great support.

Best regards,
Paul. 

Aug 21, 2012 at 6:13 PM

Hello,

Setting the this.instance.Parameters.EnableIndexChecking to true is not causing any exception to be thrown.

What is still missing?

Best regards,
Paul.