Data corruption: Index updated, table not

[chpicker]

I discovered the other day that the index on my A/R table was corrupted. No problem, I thought...pulled everyone out of the forms that use it, locked the table down, and REINDEXed it.

Yesterday I discovered there were 23 transactions from that day that hadn't written their data to the table. Not only that, but no program errors had come up for the person that did them (and I trust this individual to come to me with any errors that pop up...she wouldn't have ignored them). So I started digging to try to figure out why.

Today, as I'm going through the backup data from the other day (backed up after the incident but before the REINDEX), I performed a SEEK on data that should have been added from one of those 23 transactions and got this error:

Index does not match the table.  Delete the index file and re-create the index.

This leads me to believe that, during those 23 transactions, the index was updated but the table was not. Has anyone run into this? Any idea how this could possibly be verified and trapped so if it happens again we know about it?

 

[DSummZZZ]

First, let me point out that REINDEX is not always reliable. If you ever have index file corruption, th best thing to do is delete the index file and recreate it from scratch using:

INDEX ON expression TAG tagname

Now for the update problem, if the index is corrupt, the SEEK could actually find a record matching the SEEK criteria, even if it is the wrong record. i have had this happen before. If it does, of course it could update the record. Maybe even 23 times, before anyone realizes it. This will also update the index file. A sure way to know if you are positioned on the correct record is to compare some values. For instance, do a SEEK. The compare the fields to the values of the SEEK expression:

IF SEEK(m.lname + m.fname)
IF m.lname + m.fname == table.lastname + table.firstname
*... update record
ELSE
*... "Possible corruption" stuff here
ENDIF
ELSE
*... "Not found" stuff here
ENDIF

Dave S.

 

[chpicker]

The 23 transactions I referred to weren't changing any data. In fact, there is NOTHING in my program that ever alters data in this table. The only thing that EVER happens is records added using SQL INSERT. However, for all 23 of these transactions...the records they should have added simply weren't there.

I discovered the index was corrupt because I was browsing the table after issuing SET ORDER TO and found a record that was out of order. After reindexing, they were ok again (to all appearances, anyway...I'll delete the index file and reissue the INDEX commands next chance I get).

 

[DSummZZZ]

Hmmm. That's strange then. With SQL/INSERT happening and not reporting an error, you would think it was still updating something, even if it was a wrong table.

The main reason I say that REINDEX isn't reliable is that I have had the actual keys in the index header file become corrupted also. This makes Fox write bad key values to the index file. So you may still have corruption and not realize it. Sometimes you can see this scenario by doing a DISPLAY STATUS. The bad keys will look funky on the screen.

Dave S.

 

[rhyd]

exactly the same thing has happenned to my table. sql-inserts that occasionally fail silently without any error! this sort of thing makes me not want to use foxpro in mission critical stuff.... check out mysql - million times faster (over the network) and 100% reliable in my experience!

 

[chpicker]

You will always find reasons to and reasons not to use every type of system in existence. They all have their strengths and weaknesses.

One of the best strengths of FoxPro is the unbelievable simplicity with which you can create a running database program. If you're creating a Client/Server system, by all means use MySQL. The program that I maintain doesn't. It accesses FoxPro tables in a shared folder on the server. Granted, it SHOWS that the developers took advantage of how quick and easy you can write a program in Fox, but it works for the most part.

To be honest, I would love to rewrite this thing as a Client/Server using MySQL. However, that's up to my boss. He doesn't really want to pay me for a month to do that.

Ian

 

[rhyd]

yeah i'm actually in exactly the same boat as you. My company has tons of fox stuff (lots still running in 2.5 - that is Dos!). The move to visual was at first a real treat coz you get a really good gui design tool, and nearly fully compliant sql-92 (much better than mysql manages).

The systems I build use shared tables available on a file server, and the client can be many miles from the server with a low bandwidth connection running complex querriies with small result-sets on tables of several hundred megabytes. The fact performance is generally good is a tribute to good-old rushmore tech...

But the system has to be available 24/7 with no regular window to rebuild the index.

When I discovered that it is possible to do an SQL-INSERT that fails without generating an error (because the insert did happen it just didn't update the corrupt index) - that was it for me. You can't build big real world systems if that sort of error is gonna creep up and bite you every so often.

I recently handed in my notice, partially because I don't want to be supporting foxpro (random library bug) code.

IMO visual foxpro is just a dead product MS keeps around because the upsizing wizard will eventually make MS-SQL server customers of us all.... NOT ME!

 

[rickesilaw]

Dropping of records is usually network related, especially if Novell. We use VFP in over 1000 sites and have not had any occurences of "dropped records". Usually it was the programmer or the network when data did get lost.