How do you prevent the database from crashing from illegal PC shutdown 1

[teajean]

hi!

To organize my application, I had my dbf, cdx, fpt, dbc, dcx, dct files in one directory: myData. My main program which is outside myData opens my database with

OPEN DATABASE myData\myDatabase

Everything was running smoothly, when one day I illegally restarted my PC while my program was running by pressing the reset button. When I executed my application to test it again, I was prompted with the error:

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

With further reading I found the solution that I needed to delete the .DCX file and I did. That solved some of the problem after I validated the database and was able to fix some of the errors that followed afterwards. With this, I need to know how do you prevent the database from crashing when you illegally shutdown your computer? Any code or algorithm will very be helpful & appreciated.

In connection to my database crashing, here's another situation that I can't solve:

I am 100% percent sure that my CDX files aren't missing from their directories but my program always yields the error:

Structural .CDX file is not found.

When I press IGNORE, my program resumes executing. I cancel the program afterwards, and check my CDX files again - there isn't any cdx file missing that is associated with the database. Also I have rebuilt all the indexes, both CDX and DCX files, but still have the same error whenever I execute my program. How do I get rid of it? Also, is there a way of determining which table's cdx file is missing? How? Thanks a lot!

 

[jimoo]

teajean

I don't know of any code or algorithm that will prevent your situation, where the power went out. I think you are at the mercy of the OS.

As for the other problem. It sounds like you still have corrupt indexes. Just reindexing a corrupt index will not always fix it. A better method is deleteding the index file (cdx) and recreating them.

Prior to doing this:
1. make a backup so you can reference them later if necessary.

2. Open them and write down all the keys.

3. You can put the index code in a prg so you will have later if it happens again.

INDEX ON eExpression TAG TagName [OF CDXFileName]


Summary: DELETE and RECREATE all indexes. It's a bit of work, but if you're having this many problems you may wish to consider it.

What do the other think?



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Mike Lewis]

Hi Teajean,

First, I agree with everything that Jim said.

In addition, you might consider using the FLUSH command. This can help avoid corruption by writing all unsaved edits to disk. However, to use it effectively, you would have to execute it every time you update a table, which is tedious and could also have a bad effect on performance.

If this is an application which you are using yourself, my advice would be not to bother with anything special. As Jim said, there is no effective way of guarding against a loss of power. Provided you understand the issues, you can deal with it as it happens.

If this is a mass-market application, that's a different matter. What you want to do there is to provide an easy user-friendly way for users to fix corruption and bad indexes without calling on you to do it for them. My solution to that problem is to use Stonefield Database Toolkit (

http://www.stonefield.com)

to provide an automatic repair feature.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[dbMark]

Although few administators can or will do an install for every computer, uninterruptable power supplies (UPS) can minimize issues from power outages, but even those don't work perfectly every time. And of course there's always the klutz who manually turns the workstation off wih your program running!

As for deleting and then recreating each tag from the default compound index, here is some code to do so, but of course test before using:

PRIVATE x, tx, kx, dx, maxtags, taglist
maxtags=20  && set meximum tag count here
DIMENSION taglist[maxtags,3]
* assumes table is open in current work area
* save tag data
FOR x=1 TO maxtags
   taglist[x,1]=IIF(LEN(TAG(x))=0,"",TAG(x))
   taglist[x,2]=IIF(LEN(TAG(x))=0,"",KEY(x))
   taglist[x,3]=IIF(DESCENDING(x),"DESCENDING","")
   * more code needed for COLLATE,FOR,UNIQUE,CANDIDATE
NEXT
* delete tags
DO WHILE LEN(TAG(1))>0
   DELETE TAG (TAG(1))
ENDDO
* recreate tags
FOR X=1 TO maxtags
   tx=taglist[x,1]
   kx=taglist[x,2]
   dx=taglist[x,3]
   IF TYPE("tx")="C" .AND. LEN(tx)>0
      INDEX ON &kx TAG (tx) &dx
   ELSE
      EXIT  && no more tags
   ENDIF
NEXT

dbMark

 

[teajean]

Thanks very much guys for all the advice. To Jim, the deletion and recreation of indexes worked for me. To dbMark, thanks for the code. To Mike, I still have to think about using the FLUSH command before using it for my program. As for the Stonefield Database Toolkit you suggested, I'm going to try it today and see how it works. But based on the reviews that I've read, I'm sure I'm going to find it very useful.

Thanks again!

But I've a question about the code dbMark gave me:

* delete tags
DO WHILE LEN(TAG(1))>0
DELETE TAG (TAG(1))
ENDDO

(TAG(1)) is a name expression right?

Before I got dbMark's code, I created my own and I thought it would work:

* Assume that table is already open

LOCAL lnTagCnt AS Number

FOR lnTagCnt = 1 TO TAGCOUNT() THEN
DELETE TAG (TAG(lnTagCnt))
ENDFOR

(TAG(lnTagCnt)) is a name expression too, right?

My code works only at the 1st loop. On the next loop, I get the message: SYNTAX ERROR: DELETE TAG (TAG(lnTagCnt)). I've read in VFP help that a name expression operates like a macro substitution and :

Macro substitution statements that appear in DO WHILE, FOR, and SCAN are evaluated only at the start of the loop and are not reevaluated on subsequent iterations. Any changes to the variable or array element that occur within the loop are not recognized.

Does this explain my error? But when I used dbMark's code, I didn't get any error. Why's that?

Thanks very much again for all the help.

 

[clarkrj]

All my apps have a housekeeping routine that will delete then recreate indexes. It is not allways convienent to run round the country fixing this sort of problem.

If you have any calculated tables, it is advisable to recreate these as well.

 

[dbMark]

teajean,

I wonder if you had just 2 tags in the table. If you deleted the first tag, then the next loop would have tried to delete the second tag which had taken the place of the first tag in the first loop. Hope that's not confusing.

Your code might work if the loop went in reverse order such as STEP -1. Think of the parameter for TAG() as a count of the number of tags. If you remove the first tag, then what was before tag 2 is now tag 1, what was before tag 3 is now tag 2, etc. That's why my code kept deleting tag #1 until there was no more.

If you have 4 tags, the tag numbers are 1, 2, 3 and 4.
If you remove any tag, the tag numbers are 1, 2 and 3.
If you remove any tag, the tag numbers are 1 and 2.
If you remove any tag, the tag number is 1.

dbMark

 

[teajean]

dbmark, i do have 2 tags. thanks very much for the explanation. i now understand.

you guys really have been very helpful. thanks too to clarkrj. and of course, thanks again to mike & jim.. :0)