Delete and re-create index, from what?

[mibosoft]

Hi,
When my application is shut down abnormally, the cdx files get corrupted and all data entered since last open of the database is lost. I have read threads about (and seen code) for deleting tags and recreating them in index files but I don't understand how to do this as I can't open a table with a corrupt index and when deleting the .cdx file, there is nothing to re-create the index tags from!? Obviously I have missed something here :-|
/Micael

 

[Mike Gagnon]

Yes you are missing somethings.
1. Go to window explorer, find the cdx file associated with your table and delete it.
2. In the command window of VFP, open the table exclusivly, it will complain about the missing cdx file, but one of the option is to delete the reference to the index file in the header of the table. But once you delete the reference, the table opens.
3. In our system, for each table in our database, anytime we make changes or additions to the indexes, ot get recorded in a seperate table, where if we need to recreate the index, we have all the info we need.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[JRB-Bldr]

Here are a couple of suggestions...

1. Before you need it again, get the various Indicies and their expressions from the data tables which are NOT corrupted and save it off - most of us do so in a separate reference table.

By using TAG() in conjunction with SYS(14) you should be able to determine the Index TAG's and associated Expressions. Look in your VFP Help files on how to use these commands.

2. Mike's suggestion will allow you to open the data table with the corrupted index file by eliminating it. That is a good idea.

But you need to know what Indicies WERE in the table so that you can re-create them.

If you happen to have an old backup version of the table, open it separately and determine its indicies and associated expressions.

If you don't have a backup, you can a more tedious method is generally called for.

Go through your source code to determine how the table is used....

* What Order is set (Index Tag name) and what fields are used in what manner (Index Expression) when it is used as a Child table in a Relation to another Parent table.

* When a Seek is performed on the table itself, what Order is set and what is the SEEK Expression? That is another clue to what the index TAG and Expression were.

Good Luck,
JRB-Bldr

 

[mibosoft]

Thank's Mike and JRB-Bldr for your answers.

I wan't to handle this programatically so I think I will do as follows;

1. Catch the error 114 "Index does not match the table. Delete the index file and re-create the index." and programatically delete the .cdx files.

2. Catch the error 1707 "Structural .CDX file is not found." and have a "RETRY" command in it only so that the table really will open.

3. Run all "index on aaa tag bbb" again. (I have a fixed number of index tags that is setup at the time the databse is created so I think this will work)

/Micael

 

[Stella740pl]

You can SET SAFETY to OFF so that the table would open without .CDX and not generating errors.

You can also take a look at faq184-1033 for a good piece of code accomplishing your task.

 

[craigber]

Frequent index problems is not normal. You're getting some good advice, but you're treating the symptoms and not the problem. There are things you can do to avoid the problems in the first place. See

http://www.craigberntson.com/Articles/kb005.htm

for some ideas.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[mibosoft]

Hi Craig,
Thank's for the info. Index problems are not frequent among my customers but from time to time it happens that they turn their PC off without exiting the application.

Another problem is when users are working in databases (exclusive or shared) over a LAN. If the LAN goes down the same index problem will happen.

I am thinking of adding FLUSH in dialog unload events or something to minimize loss of data if a crash happens.

/Micael

 

[craigber]

Micael,

There are other things you can do. I've charged 3x my standard rate to fix problems due to people turning off their PC. It's quite effective when you hit them in the pocketbook.

Using data buffering also helps alot. UPS on the server is a must.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[Evbcs]

For every system we maintain a permanent, stand alone program (.PRG or several linked .PRGs) to rebuild indexes for all tables used within the system. The underlying philosophy is that if one index is corrupt, there may be others that haven't shown up yet, so we refresh all the tables, all at once.

The program basically contains the code describe by /Micael above.

CDX files are normally deleted only if one of the errors mentioned is trapped when the table is opened, otherwise the the DELETE TAG ALL command is done to clear previous tags and then each tag is re-created.

The key to making this work is unconditionally maintaining the discipline for developing and maintaining the systems that, "ALL index changes MUST BE done through this program, and changes are NEVER made directly on tables." To implement with an existing system follow JRB-Bldr's instructions to build the .PRG the first time.

To modify the indexes for a table during development from the command mode, simply copy the associated code for the table in question, paste it to the command window and run it directly from the command window.

Keep in mind that files must be used exclusively, so all users must be off the system when rebuilding the indexes. I don't charge 3x the rate, but I do make a big thing about the "inconsiderate person" who shut down their machine and caused the inconvenience.

We have been using this methodology for over 20 years (starting with dBASE II) and it has served us well.

 

[craigsboyd]

normanbates,

Have a look at the code I provided in thread184-755689. The code there will create a table of all the necessary index information for a database allowing you to recreate the indexes later.

SweetPotato Software Website
My Blog

 

[mibosoft]

Evbcs and Craig,
I think I got it now. All indexes are setup in a single routine. If the index file gets corrupt, this routine is executed via the error handler. In that way there is no need to create a table of existing indexes. I also think about having this routine called every time the database is opened. In that way I can add new indexes without upgrading the database (I store a version number in the db "comment" field to decide if the db shall be upgraded).

Thanks,
Micael

 

[Evbcs]

Michael,
Keep in mind that the files need to opened exclusively to create or re-index a cdx and all users should be off the system.

Evbcs