Our major VFP 5.0 SP3 system (and now VFP 6.0 SP3) has exhibited intermittant index corruption. As this is intended to be a 24 hour per day, 7 day per week system, this is of course a serious problem to us. The problem only occured on tables where we were recycling records in a HIGH contention situation - multiple processes (exe's) going against the same DBFs. We tried to get a solution to this problem about a half year ago through Microsoft, but the question never got through to the people that could address the question. At DevCon, one of our team got ahold of Microsoft's Doug Green, and gave him a demo program that would demonstrate the programatic index corruption. He immediately took an interest and has now given us a solution which we share below.

Bob

...............................
We have tracked down the problem and identified it as a bug in VFP but have not come up with a safe fix as yet. To make it short and sweet the corruption is happening when one client has appended or deleted the last record of the table.

There is a multi user timing issue there, so the simple workaround is to just put a -1 with RecCount() as shown in the Indexbreaker2.prg that I have attached. I hope this helps you out and I will continue to work on a more permanent fix for this in the next version.

Thx. Doug Shepard
Microsoft Visual FoxPro

....................................
Doug,

1. Just to make sure I understand: Can I correctly paraphrase your workaround as being "Don't recycle the last record in the table"?
[Doug] Yes, that would be an accurate paraphrase.

2. Should your workaround work for both buffered and unbuffered tables?
[Doug] Yes

3. We saw index corruption disappear (or at least greatly improve - no occurrence yet) by using buffered tables instead of unbuffered tables. Is this in fact a solution, or are we still at risk using only our change to buffering, without using your workaround?
[Doug] I was not sure at first but after testing this out I have found that any type of buffering will prevent the corruption from happening. The corruption will only happen on a unbuffered table when recycling the last record in heavy traffic.

4. We have been using a flag field (lDeleted) to flag a recyclable record (as seen in the IndexBreaker program) rather than simply using the built in FoxPro DELETED status that comes free with each record. We did this after hearing a rumor that the DELETED flag might be causing index corruption. Was it just a rumor? With your workaround of not recycling the last record, can we now get rid of our lDeleted field and go back to using the DELETED record flag?
[Doug] With the exception of this problem we are not aware of any DELETED Index Corruption problems. If you can find an example of one I would be very interested in taking a look at it.

5. Is this a serious enough bug that it may come out in a service pack? If it is up to voting - we sure think that the solution (when found) should be released immediately (desperation).
[Doug] Unfortunately I'm not the one that makes those types of decisions. This will definitely be looked at for any service packs that get released but their is a rather extensive review process that it has to go through first. I will put your vote in for you though.

6. Are we free to describe the problem, and your comments, to other VFP user on such forums as the Universal Thread. Can (should) we name you (without giving out your email address of course).
[Doug] I have no problem with you making this bug public knowledge as long as you provide the workaround with it as well. Feel free to use my name but I would appreciate it if you would leave my email out. :)