Indexs that just disappear

[David Sparks]

I have a customer with an Application that keeps loosing indexes.

I have had many problems with this app. It was designed by someone else, and I have yet to figure all of the relationships, etc.

Every so often, they start having strange problems, and when we look at the indexes (Table, desing indexes)there are only a couple of them left where there should be several.

The only explanation I can come up with is that they are low on disk space, (only 70 meg left) and it may have someting to do with corrupted swap files.

Rebuilding indexes does nothing. I have to copy old .CDX files over and then rebuild.

Any ideas?

[Steve Workings]

RE: Indexs that just disappear

Hi David --

Low disk space could be a factor.

Another thing to try:

Define Indexes and remove them all. (Before removing, you can clipboard them all and save them in the clipboard for the last step)

Delete the .cdx file.

Run ddd maintenance on that table's data dictionaries to clean everything out.

Put the indexes back.

[Stan Mathews]

RE: Indexs that just disappear

Steve,

I'm interested in your reply. You say low disk space could ba a factor but don't indicate why. I am familiar with the disappearing index problem. My company has one particular table with sixteen indexes. The table resides on a server with about 10 gig of available disk space. About once a month, give or take a week, all but one of the defined indexes is no longer defined. Fairly simple matter to cut and paste the definitions from a backup and rebuild, but does anyone have a clue as to what is happening?

Thanks,

Stan

[Steve Workings]

RE: Indexs that just disappear

Well, low disk space isn't a factor for you!

I don't know what's happening. In fact, I'm building a module for my customer (in my copious free time) that will periodically (every few minutes) check to insure that some of the critical indexes exist, that their names are not truncated, and the expression is correct.

If you have repeated troubles with indexes in a particular table, following my advice above may be helpful.

- Steve

[Jim Rowlen]

RE: Indexs that just disappear

We've found that indexes can disappear when we crash (server or worksation), and that any indexes that come back are the result of someone loading a set which uses the table (the index is built by A5 twhen opening a set if it isn't there). Corruption can also result in missing indexes. Hopefully the memo fields (if you have any) aren't corrupted since it can be a major undertaking to find and fix corrupted memos in a big table.

Can you isolate what is being done when you lose the indexes? It might give more insight into trying troubleshoot this. For example, does it always happen with a particular form/report, in a set or not, form with memo fields, etc. Maybe when a particular script or operation is run?

[Stan Mathews]

RE: Indexs that just disappear

Thirty users, in various combinations, usually no more than ten at a time. Possibility of at least that many different forms in use, or more than that number of reports being printed, some with "on the fly" queries generated. Over two hundred tables, two major sets, most of the tables are import results, summaries, and crosstabs used to gather information (monthly and yearly totals, sales and inventory) from Oracle tables to feed to the Alpha tables.

I supppose the "check every so often to see if the indexes are still there" approach is the best idea. I have a fairly simple routine in my Foxpro (DOS) Point of Sale program that logs every instance of an operator accessing a menu choice. The same sort of snippet in the oninit event of every form, browse, report, etc. combined with the index check would narrow down the posssibilities.

Now to find the time to implement the code, to find out when something is happening that shouldn't be possible.

[Stan Mathews]

RE: Indexs that just disappear

Jim, Steve,

Sorry, didn't mean to sound flippant or dismissive. I appreciate the ideas. No hard feelings, just don't enjoy the prospect of doing the work.

How can a shared tablename.cdx be "overwritten" or "edited and rebuilt" when there are never less than three machines with write access to the tablename.dbf. This is a networked system. I have to get the other users off the system (at least out of the table and the sets to which it is linked) to fix the index problem.

Thanks again.

Stan

[JerryBrightbill]

RE: Indexs that just disappear

Stan,
I had an application with the same problem. I even created a copy of each .cdx file for every table and created a xbasic script to copy the saved .cdx over the corrupted ones and then reindex every table.

Some time ago I posted a message about losing indexes. Selwyn replied that A5 will drop an index if an expression of filter in the index will not calculate, particularly if one of the filters is used to filter deleted records. I worked hard to simplify my indexes and now the problem is very infrequent. I tried to eliminate as many filters as possible, and now always pack a table when records are deleted.

You may want to search the archives for posts on lost indexes. There were a number of different posts which proved helpful.

Jerry

[ken tjia]

RE: Indexs that just disappear

Hi David,

I have the same experience in the past and BILL HANISBERG tought me to make sure I use INDEX Name shorter than say 8 digit charcters, since then this problem dissappear for good.

Please check and reduce the name's length. Avoid fancy decription:"Index of father name etc"... Just use IDX001
and have your own reference somewhere.

Any comment ?

Ken Tjia

[JerryBrightbill]

RE: Indexs that just disappear

Some additional thoughts. Just saw Ken's reply below and I concur. If you use long name for indexes, them seem to be saved in the data dictionary. If the data dictionary becomes corrupted, the index names can be corrupted. If you stay with 10 digit names, this does not seem to be a problem.

I found Selwyn origianl mention of idexes being dropped. I searched using index, Selwyn Index,"Lost index", and other combinations in the search field. I finally found it. The complete post is as follows:

Index Disappearing (Just one this time)
Author: selwyn rabins
Date: 01-21-2000 17:57
File:

alpha five will automatically remove an index if the index expression does not parse.

what is the index expression for this table?

[Stan Mathews]

RE: Indexs that just disappear

No long names, 1 filter, and when the indexes are dropped, sku always remains as the only defined index.
Tag Expression Filter Direction
Apr_Flyer_APRFLPAGE Ascending All
Aug_Flyer_AUGFLPAGE Ascending All
Aug_HighwaAUGHWPAGE Ascending All
Category CATEGORY Ascending All
Dept DEPT Ascending All
Fall_CatalFCATPAGE Ascending All
Feb_Flyer_FEBFLPAGE Ascending All
Itemcd ITM_CD Ascending All
Jun_Flyer_JUNFLPAGE Ascending All
Mar_Flyer_MARFLPAGE Ascending All
Master_CatMCATPAGE Ascending All
May_Flyer_MAYFLPAGE Ascending All
Nov_Flyer_NOVFLPAGE Ascending All
Oct_Flyer_OCTFLPAGE Ascending All
Oct_HighwaOCTHWPAGE Ascending All
Product_GrPRODGRP Ascending All
Sep_Flyer_SEPFLPAGE Ascending All
Sep_HighwaSEPHWPAGE Ascending All
Sku SKU Ascending All
Spring_CatSCATPAGE Ascending All
Summer_CatSUCATPAGE Ascending All
Summer_Pw_SUPW SUPW=.T. Ascending All
Totalsale0TSLYTTL Ascending All
TotalsalesTSTTL Ascending All
Winter_CatWCATPAGE Ascending All

Thanks all for your interest.

Stan

[Tom Cone Jr]

RE: Indexs that just disappear

Stan, in the interest of keeping things simple and perhaps more reliable, have you considered reducing the number of indexes? Some of the tagnames you list suggest that they might be replaced with queries created when needed, and then discarded. Though it's possible (indeed, very likely) that I infer too much here.

In my own work I try never to use filtered indexes and I limit the indexes to those which are essential for the user to navigate through the tables during data entry. Everything else I create using a query when needed. My system probably runs slower, but it's been very predictable. Haven't lost any index definitions, ever, except once when the customer's hard drive crashed.

-- tom

[Ohlen Cartmell]

RE: Indexs that just disappear

FWIW

When I transfer from my development computer at home to my running machine at the office using Pkzip, I find several corrupted indexes result. Each of these share the same first characters and each have underscores between words. I plan to rename these indexes to no longer than 8 characters and eliminate the underscores when I get around to it. It's not a big problem for me since the tables are not huge and I don't distribute the app and it only takes a minute to find and correct the problem. But I believe scrambled and lost indexes have something to do with long index names and their relationship to Pkzip, Windows98, and DOS.

[Peter.Greulich]

RE: Indexs that just disappear

In the past, before the common use of Iomega Zip & Jazz drives, I too would get lost or corrupted indexes using PK-Zip programs. Now if only my Iomega Jazz drives wouldn't break down!

[Elliott Lester]

RE: Indexs that just disappear

Stan, we have found through experience that it is best to limit your indexes to 10 spaces, use underscores no blanks.

Previously I have seen people recommend 8 spaces (old dos)
but our experience shows 10 spaces works well. If you crash with more than 10 spaces, it truncates to 10 spaces. This truncation can lead to cascading deletes of your indexes as A5 parses through with a mission to find the correct index that it is looking for, but now is named something else; but of course, no always.

Also, we keep our field names to be under 10 spaces for the same reason. Nor should you put blanks in forms either.

Now all of these are based upon our observations.

I personally have found that if someone is entering or modifying a record--so that the index is open, the delete of
indexes can occur upon a crash which we relate (but do not
really have evidence of same) to the heap lock problems--memory allocation/deallowcation issues.

Good luck