Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


"Houston, we have a problem..."
How to recognize and prevent corrupt tables and indexes
by Mike Robinson
© 2003 Sundial Services
http://www.sundialservices.com

Introduction

The Paradox database system has been popular for many years because it has proven to be very reliable. With proper care and feeding, a Paradox database can reliably handle very large files, and a respectable number (say, up to 30) simultaneous users, and it can be relied upon to give consistently good performance. But... sometimes things do "go wrong." Database tables can suddenly become unusable, and when they do, the messages ("index out of date," "corrupt table other than header", etc.) might be fairly incomprehensible. In this article I'll describe some of the messages you might see, explain what's happened, and advise what to do about it. I'll also give you some suggestions for preventing these problems in the first place.


"Index Out of Date":

Physically, a single Paradox table is a collection of related files, all occurring in the same directory. For example, a CUSTOMER table consists of all of the following files and possibly more:

CUSTOMER.DB Rows of the table
CUSTOMER.MB Memo and graphic fields (if any)
CUSTOMER.PX Primary index
CUSTOMER.Xnn
CUSTOMER.Ynn
Secondary indexes (two files per index)

As you can see, since one table consists of many files, when any change is made to any one of them, corresponding changes must usually be made to several others so that all of the files remain "up to date" with each other at all times. Paradox uses change counters in the header portion of each file to ensure that this is always the case. If at any time the change-counters are not correct, you get the message: "Index out of date."

Paradox's table-repair utility can fix this problem but it often deletes indexes, validity checks and other important information in doing so.


"Corrupt File" Messages:

Each file in a Paradox table family has a (conceptually) similar structure: first there is a header describing the file's contents, and then there is the data itself. If Paradox cannot recognize the header, it says that there is a "corrupt file" of some kind. (There are several variants of this message, and I'm lumping them all together.)

Obviously, this is probably a very severe condition from which true data recovery might not be possible. The file may have been overwritten, or there might be a physical I/O error on the drive. (Today's modern "ridiculously large" disk-drives appear to be much more prone to this, in my experience.)

It is also possible that Microsoft's ScanDisk tool might be part of the problem. If ScanDisk says that "ScanDisk found errors on this drive and fixed them all," it might indeed have repaired the file system but may have done so at the expense of some files. (Look for C:\FILEnnnn.CHK files, which may contain fragments of data from files.) The problem is that database files were probably open (that is to say, "actively in use") when the failure occurred. For technical reasons that need not concern us here, recovering the contents of an open disk file is much more difficult and uncertain than when the file was closed.

(ScanDisk has gotten to be somewhat of a "politically correct" program... It will tell you exactly what it finds and which files are affected if you ask it to, but most people choose the slightly-too-convenient "automatically fix errors" option instead. Don't do that. If you're running Paradox, you need to know every "gory detail.")

Also note: "Corrupt file" messages can also occur for reasons that apparently have nothing at all to do with file corruption. If you have tables that use language-drivers other than the default "US ASCII," and the language settings of the index-files are not the same (this shouldn't be possible, but it is...), or if this particular language driver is not installed on your machine, the message "corrupt file - other than header" has been known to occur. But this isn't the only cause of this message.


Missing Data, GPFs During Table Access:

Sometimes the table will open up just fine ... but there are huge chunks of records missing! The file is there, as big as it ever was, but it contains almost no records. What has happened in this case are what I call "broken chains." To understand what I mean, let's look at the physical structure of an imaginary Paradox table that contains three records: "Hickory," "Dickory," and "Dock." (Yes, I am being too-simplistic here, for the sake of example. Let's pretend that each "block" of the file somehow contains only one database "record," shall we?)

File Header:   first block=2, last block=1; free blocks list=empty.
0 3 Dock
3 0 Hickory
1 2 Dickory

At first blush, it looks like those blocks are out of order... and what are those two columns of numbers for? The answer is that the blocks have been placed in a doubly-linked list. The file header says that the first block in the list is #2, and that the last block in the list is #1, and we can see that this is so. The number in the first column is the block-number of the "next" block; the number in the second column is "previous." So you can see that block #2 ("Hickory") points ahead to #3 ("Dickory"), and thence to #1 ("Dock"). The zero indicates "end of list." You can run the list backward using the numbers in the second column. Paradox uses one list to maintain the blocks that contain active records, and a second list (not shown) to hold free blocks that are waiting to be recycled.

Now suppose what would happen if that list became fouled up. What might happen if the list got a loop in it? What if two blocks somehow pointed to the same "next block?" What if the "next block" number was impossibly large or small? What if there was a chunk of blocks that were not accessible on either list at all? What if you could reach a block when going in the forward direction but not backward? All of these are realistic possibilities if the chain-structure is damaged. Even though the data is "still there" (i.e. the file is still as large as it ought to be), it isn't accessible.

Many versions of Paradox will abort (GPF), sometimes quite cryptically, when they encounter an "impossible" pointer-value. If the problem occurs when navigating through the table, it's reasonable to assume the pointer chains are corrupt.

Fortunately, Paradox's "Table Repair" utility will fairly-reliably fix this problem by reading the table-file one block at a time (not following the chains) and placing the records into a recovery table. The ChimneySweep® product sold by our company accomplishes the same result "in place" by direct repair to the pointer structure.


Inaccessible memo-fields:

Paradox provides several "Memo" field types to store large amounts of text, graphics, and binary data. You can encounter a problem where a memo-field appears to have some data in it (you see the usual "scrap of text" in a table view grid), but an error occurs when trying to access the data. Furthermore this problem usually occurs only for specific records.

This problem is caused by damage to the tablename.MB file mentioned earlier, and/or by errors in the pointer-information which links memo-data to a particular record. While Paradox Table Repair can detect these errors, it often "fixes" them by setting the affected memo-fields to blank! Our ChimneySweep® product can fix the problem by in-place repair in some, but not all cases. Full recovery from this problem can require the use of a backup-copy.


An Ounce Of Prevention...

Plainly, the best "solution" to table problems is to prevent them, and to make very sure that you have a reliable recovery-strategy ready at hand should they occur. Here are some specific pointers:
  • Always have a backup!
    Never forget that it is sometimes not possible to recover your data by any means except to recover the data from a current, "known good" backup. A copy that was made on an unreliable tape-unit sometimes last week ("It's around here somewhere... Maybe Emily knows where it is? What? Whaddaya mean 'she got downsized?' ...") just won't do! The backup must be recent, and what is more, you must know where it is and know that you can restore it. It's perfectly all right to "back up to disk," especially if the backup copy is on a different disk drive. The backup must also be complete and intact: you can't use a backup copy unless all of the files in the database were successfully copied at the same time. (Our ChimneySweep® product can make backups and can schedule them, too.)

  • Get uninterruptible power supplies for all computers!
    A so-called "UPS box" does much more than prevent your computer from fizzling out when the power flickers as it sometimes does. It will also condition the incoming power, ensuring that the power that's delivered to your computer is precisely what it should be, even when someone's using the photocopier next door. Modern disk-drives are direct current devices which do not tolerate even very-slight differences in line voltage or frequency, and such anomalies are not unheard-of even in modern office buildings. (Especially in modern office buildings!)

  • Urge users to be patient, and to turn off their machines orderly:
    Studies have shown that users wait less than 15 seconds before rebooting their machines, while claiming that the "machine locked up" and sincerely believing that the wait was much longer. They also become conditioned to this and reboot even sooner if delays occur frequently. But delays can occur under many circumstances when several people are accessing the same table.

    1. Consider setting the Lock Retry option to a shorter value to minimize the time that Paradox waits for a lock to be obtained. The default is around 10 seconds, and Paradox is quite unresponsive during that time, appearing indeed to have "locked up."
    2. Emphasize to users that it is very important to finish what they're doing, close Paradox, and wait for it to close completely, before shutting down their machines, which they should do only with the Windows Shut Down option. (The same applies to "logging off the network.")
    3. Remind users that when they've finished editing a record, they should save the changes, so that the "Locked" indicator no longer appears.

  • When installing a new workstation, check the configuration carefully:
    When a new workstation is being put on-line, make the necessary BDE Configuration changes on a local copy of the configuration file, and test to be sure that it can access the database correctly. In particular make sure that the NET FILE DIR and LOCAL SHARE settings are correct. Confirm that when this workstation opens a record and begins editing it, another workstation indeed cannot edit the same record. Also confirm that when this workstation attempts to edit a record that another user is editing, this workstation is indeed prevented from doing so.

  • Check network hardware periodically:
    An old or poorly-installed office network is no bargain. Malfunctioning equipment, crimped or damaged cable, and "old slow boxes" can cause tremendous unreliability that is very difficult to diagnose without the proper equipment. Somewhere in town you'll find a qualified network-installation specialist who has the right gear and who will, for a nominal fee (well worth spending!) give your network an electronic bill of health.

  • Don't make problems come looking for you ...
    Go looking for them! ChimneySweep® can be used to pro-actively scan each table very rapidly, looking for corrupted tables and indexes before they surface as production problems. (It can do this right before doing a backup.)

  • If problems "suddenly appear," find out why!
    All other things being equal, a computer will behave tomorrow exactly as it did today. So will a computer network. Therefore, if a problem "suddenly appears," don't treat it as situation-normal: find out why! It helps to keep a diary of the changes that are made and the problems that are reported and how they are resolved. Any problem that "suddenly appears" is either an indication of failing hardware or the consequence of a recent change.

In Conclusion...

Paradox is an excellent foundation for a robust, reliable database. Many thousands of copies of this system (actually one of the oldest for microcomputers) are in use around the world every day. Achieving ongoing reliable service from this system is very much a matter of vigilance and discipline. Your data is priceless, but a surprisingly small amount of effort ... if religiously and consistently applied ... is all you need to keep it available at your fingertips with Paradox.

The author is the technical director of Sundial Services, the publisher of the ChimneySweep® database maintenance tool since 1996.
http://www.sundialservices.com Opinions are those of the author not the company and are provided without warranty. © 2003.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 30 Apr 2004
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2004 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.