index does not match table 2

[kimsue]

I have a customer that is running several foxpro 2.6 windows applications (written by me) from the file server (as opposed to installing the software on the individual hard drives and accessing (sharing) the data on the server. She is having a lot of problems with ERROR 114 (index does not match table). There are 120-150 data files in the data directory. Most files are less than 3 meg, but a few are as big as 25 meg. She has a reindex procedure that she probably runs quite often that will recreate the indexes. Lately, we have had to physically delete the cdx file in order to correct the problem. Any suggestions??
Thanks for your time. These are her hardware specifications:
***
we have novell netware for our network system with Ethernet wiring. We do NOT have windows nt running on the server. Just novell. Our computers are running windows 98 and windows 2000.

as we speak our f: drive has a capacity of 2.86 gb with 2.71 gb used with 144 mb free. our g drive has 4.81 gb capacity with 2.83 used and 1.98 free.
***

 

[Scott24x7]

Kimsue,
The most frequent cause of Index corruptions over Netware, is due to users "Rebooting", or improper shutdown of applications with the tables still open. This happens frequently if they have a machine "Lock UP", or they are just not used to an environment where they follow "Good shutdown" procedures.
In the event of a lockup, you can minimize this effect by "Releasing" the user from the server. This actually allows the server to shut down the connection to the table gracefully, and keeps index corruptions from happening so frequently. Also, the more users do this, the worse it is.
Typically, any table that might be open during the "Shutdown" is at risk, but the one that is MOST at risk is whatever the currently selected work area is. If a user is going to be away from the system, even for a few minutes, encourage them as well, to not stay in a "Screen", but maybe retreat up to a "Top-line" menu level, where you usually don't have tables open.
Also, with your reindexing process, ensure you are NOT using Fox's REINDEX command. This causes bad, bad, bad problems in the index itself, and can bloat them to rediculous sizes. (This is due to the fact that sometimes, not always, and I was never able to put my finger on the WHY, but it is a known Fox "Gotcha", it does not "Delete" the old index values, but instead creates new ones in the same file. The old one's don't really effect much, but the index bloats, and does get slower). I personally also found that indexes were more prone to corruption when created with "REINDEX".
If you're not already doing so (and perhaps you are), I recommend this process instead:

PROCEDURE REINDEX

SELECT <table>
DELETE TAG ALL
*
INDEX ON <expression> TAG <firsttag>
INDEX ON <expression> TAG <secondtag>

etc., until you have rebuilt all of your indexes.
As an added measure, I will usually take the same oppertunity to Pack the tables, but I usually do that as a seperate item. If I issue a &quot;Pack&quot;, you must always issue a &quot;REINDEX&quot;.

Some other little known facts about Fox indexes and corruption, avoid the following pitfalls as well:

DO NOT use any TRIM() functions (LTRIM, RTRIM, ALLTRIM) in your index expressions. These, in the best case scneario, will yield unreliable search results, and lead to index corruption when new records are added.

Avoid REINDEXING with Deleted recods in your table, especially if SET DELETED ON is set.

Ensure that your index expressions create unique values. (Yes Virginia, every index should be a candidate...) Even if you have to concatinate fields to creat a unique index value, in 2.x indexes, it is highly recommended. (Or, if you are just not able to do it, that table should be REINDEX at minimu weekly.)

The final note, try to limit your index's to less than 6 tags. (Unless you have a truely static table, then you can exceed that number).


Hope this will solve some of your problems. I've worked with Netware and 2.x for 10 years now, and I've gotten pretty good at dealing with index corruptions.

Best Regards,
Scott

Please let me know if this has helped

 

[mohrorless]

Kimsue,

Scott was right on the mark with what you need to do. Let me fill you in on what we do here (we have an app city wide here and in some locations it has index probalem (ususlly when the LAN at the location crashes) and in other locations we never have a problem.

We force a weekly reindex onthe files in the system (we have a DBF with the date of the last reindex in it, the first login to the system after a week from the last reindex will attempt to reindex). That is the routine maint. for the files. We also have a seperate EXE that I wrote that will clean up and fix the index files when they get too bloated or clunky.

For each DBF the exe will delete tag all, then pack the dbf to get rid of the deleted records, finally each index is then recreated (index on x tag y).

Of course all of the users need to be out of the system for this to work. In out case if the exe needs to be run, the users will be out of the system because of a problem.

Good Luck!

 

[kimsue]

Thanks for your help. Some of the cdx files are not deleted prior to being recreated in the index routine. I will make sure to add that. I do not use the REINDEX command. I always create the indexes AFTER a PACK.

 

[mohrorless]

Kimsue,

Thanks for the star and good luck!

 

[Scott24x7]

Mohrorless,
Okay, I just HAVE to ask... what's with the alien???
Best Regards,
Scott

Please let me know if this has helped

 

[mohrorless]

Scott,

You got me. What alien?

John

 

[Scott24x7]

The red alien in the bottom of your tag line... (or, am I nuts, and that's not an alien???)
Best Regards,
Scott

Please let me know if this has helped

 

[mohrorless]

Scott,

That's not a red alien - it's suppoosed to be a smiley face version of SpiderMan! Spidey is my favorite superhero & when I saw it in the smiley list I couldn't resist.

John

 

[Scott24x7]

AAAAAAhhhhhhhh... it's all clear to me now... :-O
Best Regards,
Scott

Please let me know if this has helped