Home

Damaged Files and Hidden Corruption in FileMaker Pro (A Guide to "Recover")

FileMaker Recovery ... Posted 10/13/2003 ... Updated 09/23/2008
Gregory Durniak

Printer Friendly Version: [link]

The following information is based on my experience as a FileMaker developer, FileMaker's TechInfo Knowledge Base, and on numerous discussions with FileMaker Tech Support, and FileMaker's Engineers.

The "Hidden" Corruption Problem:

FileMaker Pro, and FileMaker Server will open and run files that are corrupt. I have seen this happen multiple times:

• a file that crashes only when two guests are on the same list layout, and a script is triggered to set a number field. This file ran for a full year before the corruption was discovered ( All the backups were corrupt )
• a file that runs fine, until you save as clone, and you find that three layouts are missing in the clone copy. This file did not crash. It was damaged when it was copied to an IMac with a faulty hard drive. This file ran for a week before the corruption was discovered.
• a file with an annoying pause after any user action. This file is much too large to rebuild.
• a file with over 190,000 records, that suddenly displays "Records: 0", and appears blank, even though these records are still visible, and in use, in a related portal.

I understand that files can be corrupted by power surges, and by bad hard drives, and can NOT be prevented. What I don't understand is why FileMaker can not detect it. There is no validation utility.

When damaged files run, backups and "master" clones are useless. It is difficult to develop an effective backup strategy, when there is no way to be sure that the files you are backing up are OK (see FM9 improvements below).

This problem was actually documented in FileMaker Answer ID 2943 "Strategies for Protecting Files Whose Structures are as Important as Their Data" [link] "... Any time you make changes to your file’s structure ... you will need to save a new master clone. Place your new master clone in a safe place... At this point, you have a clone with no records, but you may also have a file structure that is possibly damaged due to corruption carried over from the original file ..."

This problem was also noted in "FileMaker Server Best Practices" (Rev 03-2003 p.13) [link] "Any system failure causing FileMaker Server to shutdown inappropriately could result in corrupted files ... Even if the files seem to re-open but have to go through a consistency check or recovery, some corruption could be buried in the file"

What are your chances of getting "hidden" corruption? The four failures above are the result of five different organizations, each running an average of 24 files, for 5 years, using FileMaker's best practices.

Removing Hidden Corruption:

• FileMaker's "File Recovery Service" will attempt to restore both data and structure (Answer ID 1633) [link], but there are no guarantees, and a 3-6 week wait

• Upgrading to a new FileMaker file format may help. I was able to "fix" one of my hidden corruption files, by upgrading an entire solution from FileMaker 4 to 5. Therefore, upgrading to FileMaker 9 is worth a try ( but not as easy as upgrading to 5 ). The FileMaker 8 Tech Brief "Migration Foundations" [link] claims that "FileMaker does a very good job of converting databases, even those with some structural corruption. The resultant FileMaker files should be corruption free however as with most issues of file corruption, the conversion engine may not satisfactorily convert unhealthy files".

• FileMaker folklore is filled with "sure-fire" methods for dealing with corruption, e.g. "export data to a text file, import back into a recovered clone, then save a compressed copy". These methods apply (often repeatedly) recover, save as clone, and save as compressed in various combinations, and may, or may not, work

• FileMaker's "official" method to recover and rebuild files is described in Answer ID 1580 "How To Organize The Recovery Process When Recovering Related Databases" [link]. If your files are not stable, this is always worth a try. It will clean up your data and structure, just keep in mind that it may not remove the corruption, and may instead remove unexpected portions of your files. Also, for the export data step, you can use a "merge" file, as explained below. It's easier.

Removing Data Corruption, Export / Import:

• Export your text, number, date, and time fields to a "merge" (text) file, then import back into a clone, using "matching field names". Be sure to check that any repeating fields import properly. No need to export calculation, or summary fields. Container fields must be restored separately. [link]

• For FM6, check the text export for "illegal" ASCII characters per Answer ID 949 [link]

• FM7 and higher supports UTF-8, so there are no "illegal" characters. However, people have reported problems, when text is pasted or imported from other applications. This is unconfirmed.

Removing Corrupt Records:

• If FileMaker crashes during data export, or while browsing, there may be a corrupt record. See FileMaker Answer ID 4666 "Removing Corrupt Records From a File" [link]

Removing a Damaged Layout:

• If a layout object is damaged, it may be difficult to open that layout in order to delete it. See FileMaker Answer ID 4704 "Deleting a Damaged Layout" [link]

Terms:

• Unfortunately, since they are stored together, FileMaker uses the terms "data" and "structure" interchangeably, which can be confusing e.g. "The underlying action of Consistency check and Recover is to preserve as much of the data as possible. In this context, data refers to tables, records, layouts, scripts, and field definitions". For this article, I use "structure" to mean everything left in a clone, and "data" to mean everything removed in a clone.

Recover

• "Recover" was best described in Answer ID 2944 "Tips on Backup and File Recovery" [link] "The primary goal of the Recover command is to retrieve the good parts of the file, resulting in a good working file. Corrupted portions of the file are often eliminated from the recovered file, not fixed. If the recovered file is missing significant portions of your database, one option is to send the database to FileMaker to attempt a more complete recovery of the file" ..... and in Answer ID 4426 "Corrupt / Damaged Files" [link] "Because the recovery process removes structures that may harbor corruption, you may not want to use recovery for routine maintenance"

• The myth that Recover "repairs" damage goes back to FileMaker 2.0. FileMaker 2.0 "Help" did state that recover "creates a repaired copy of a damaged file" ( A quick lunchtime survey at a recent FileMaker Developer Conference showed more than half still thought recover to be a "repair" utility ).

• To help end the myth, FileMaker should rename the "Recover" command, perhaps to "Please open this damaged file, even though I probably shouldn't use it anymore"

• A long lost, and very interesting description of the FileMaker 2.0 Recovery process is reproduced here [link] , from Article 102516 "What Happens During Recovery?" (No longer in the TechInfo Knowledge Base). It is quite possible that Recover was originally intended to be a validation utility. Too bad Recover could never properly report what it found.

• To be fair, Recover does re-index fields, and re-catalog objects in the file (e.g. the script list), so in this respect it does "repair" certain problems

• As reported at DevCon 2006, FM8.5 Recover has a new clean-up step, that detects "stranded" (no longer referenced) library data (often images), and creates a new table, with one container field, and records that point to the stranded data. FileMaker says the appearance of this "Recovered Library" or "Recovered Blob", by itself, is not an indication of corruption, since Library data can get "stranded", even in a "healthy" file, and is now cleaned up (sort of like removing left-over DLL's in Windows).

• Recover's closing dialog is not (and never was) useful, or reliable. It may, or may not report the fact that bytes have been removed (I have seen this happen). We really need a Recover Log to see what was done.

• The sad part is, a Recovered file may be OK. You're just not sure. This was confirmed at DevCon 2008. Most files are Recovered needlessly. Ideally, a "good" file Recovered should end up better, since it is completely rebuilt. The key word is "required". Avoid using a file that required recovery.

• At the DevCon 2008 closing Q&A Session, FileMaker explained that they are looking at ways to make Recover a more useful utility

Index Problems, "Phantom" Records, and Question Marks:

• If an index is scrambled, or contains "extra" entries, FileMaker may "find" a record that does not exist, and display all question marks. The "record" can't be deleted, since it is not real. Just re-index the field in question (below)

• Fields can be forced to re-index, by turning each index off, then performing a find. See Answer ID 5685 "Field May Lose Its Index" [link]

• Unfortunately, Answer ID 5685 also recommends using "Recover" to re-index multiple fields. This is not a good idea.

• Various index & phantom record problems have been around since FM7. They are documented [link], and most have been "fixed". However, I have seen phantom records twice in the current FM9v3.

• If you "Show All Records", and still get question marks, it's more than just an index problem. FileMaker has lost track of your record ID's. Import into a Clone

• If you upgrade from FM7 or 8 to FM9, it would pay to "clean up" everything at least once, by importing into a clone

• You'll only know you have an index problem if someone happens to notice it

Scriptmaker Problems:

• When attempting to move a script to a new position, the script may duplicate itself or disappear, and can not be moved. See Answer ID 6316 "Why am I unable to properly rearrange scripts within ScriptMaker?" [link] There is no solution, other than to delete all scripts from that point down, and recreate them (in FileMaker Advanced, script steps can be copied & pasted). The recreated scripts will have to be reassigned to buttons, etc. Update: FM9 Recover is now supposed to fix this!

Tech Specs:

Make sure you haven't exceeded FileMaker's Technical Specifications, e.g. by defining 5,900 fields in FM5. The file may work, but appear to be corrupted

• Technical Specifications Of FileMaker Pro 5 (Answer ID 1657) [link]

• Technical Specifications Of FileMaker Pro 9 (Answer ID 6586) [link]

Additional Notes:

• Stay calm. Most file problems turn out not to be file "corruption" at all

• Be sure to check all your file references, the most common cause of performance problems

• For info on routine file maintenance, see Answer ID 4426 "How to Avoid the Need for Recovery" [link] It recommends saving a "compressed" (compacted) copy, also defined in Answer ID 656 "Save A Copy As Options" [link] "... only the deleted items from a file are removed when the file is saved". Always try this first!

• Save a Copy As/Compacted is considered the "safer" fix (if it works) and should always be tried before Recover. Important Note: At DevCon 2008, FileMaker's Clay Maeckel confirmed that while Save a Copy As/Compacted does rebuild the file block index, it does not re-index fields.

• The FileMaker Pro Consistency Check is briefly described in Article 6509 (Client) [link] and Article 6603 (Server) [link]. Important Note: The Consistency Check looks only at blocks & pointers. It does not validate structure or indexes.

• If the problem can be isolated to a single object (script, layout, etc.) try re-building that item in the corrupted file

• Please do not confuse the risk of file corruption with overall stability. FileMaker Pro is very stable software. Database Files do not need to crash to become corrupted.

• Corruption is fickle. Files crashed & trashed ( and yes, even Recovered ) may run fine for years, yet a brand new file might become unstable (I have seen this happen)

• Test the file on both Mac & Windows

• ANY file type can get corrupted, FileMaker 7, Excel, or even Outlook Express: http://www.microsoft.com/windows/IE/community/columns/filecorruption.mspx

• Hidden corruption may slow down FileMaker overall, as the engine tries to interpret instructions that no longer make sense. Rebuilding a single damaged file can dramatically improve the performance of your entire solution (I have seen this happen). However, it may be difficult to figure out which file to re-build. With no validation utility to detect damage, finding corruption is mostly a trial and error process.

• If the corruption is obvious, consider yourself lucky

• Periodically open your backups to be sure they run! This simple step is often forgotten.

• If the access privileges are damaged while hosted, a file might close, and never re-open. Very nasty [link]

• "The access privileges in this file have been damaged" is the worst of all possible errors. If no backup exists, this requires a call to Tech Support

• FileMaker's backup strategy fell apart long ago in Answer ID 2943 (FileMaker 5) "Strategies for Protecting Files Whose Structures are as Important as Their Data" [link] by saying that a master clone can be "possibly damaged due to corruption carried over from the original file. This clone needs to be recovered". This is wrong, and misleading. Recover is (unfortunately) not intended for routine file maintenance.

• Whenever possible, revert to your last "good" backup. Avoid using a file that has crashed, or required recovery.

Common Causes of Corruption

• FileMaker Files are stored in 4K blocks. A "pointer" between blocks may get scrambled

• The internal ID of an object, or the catalog of objects in FileMaker may get scrambled

• An index is not properly updated

• An individual bit is flipped, anywhere in the file

• damage that occurs to an image pasted on a layout, or damage that occurs to printer instructions stored in a "print setup" script step. According to FileMaker's Customer Support Services "these are not verified by FileMaker's utilities".

FileMaker 7

• FileMaker added Tech Info Answer ID 5317 "How To Handle A Corrupt FileMaker File" [link]

• and Answer ID 5421 "What to do when your file is corrupt" [link], which pretty much repeats what's already in Answer ID 5317.

• There was no indication from FileMaker that the utilities in FileMaker 7 had in any way been improved

• Although heavily marketed as "uncorruptible", FileMaker 7 was (and is) probably the worst of all versions (FileMaker 8v3 finally got it right).

FileMaker 8 / 8.5

• For file repair options, see Answer ID 5371 "Consistency Check When Opening Files In FileMaker Server 8" [link] and Answer ID 5421 "What to do when your file is corrupt" [link]

• FM8 "Help" states that "Databases that are returning records incorrectly from a find should be fixed by recover ... and ... "The Recover command is intended for data recovery, not file repair". These statements are contradictory.

• Unfortunately, FM8's "Recover" was presented as somehow kinder & gentler, giving new life to the Myth of Recover (see above). This was clarified at DevCon 2006, and FileMaker (again) advised that we should "avoid using a file that required recovery".

FileMaker 9

• The Consistency Check has been added back to the FileMaker 9 Client (for some strange reason, it was removed in FileMaker 7 & 8)

• For file repair options, and info on the (new) Consistency Check, see Answer ID 6509 "The FileMaker Pro 9 Consistency Check" [link] Recover now appears to be "Plan B". However, it is still not clear what recover actually does, or does not do. Be careful.

• If you fail a consistency check, try a save as compacted. NOTE: Since FileMaker 7, 8, & 8.5 Client open files WITHOUT a "consistency check", you can use a previous version to open a problem file, save a copy as "Compacted", and possibly "fix" a file for use in FM9 (and yes, it would be nice if FM9 Client had the ability to skip the consistency check as well, just to "Save Compacted").

• When first migrating your files up to FM9, FileMaker may report "This File is Damaged". Try Save as Compacted, before you run Recover. Most often, Recover is not required.

• With FM Server 9, you can now choose to perform a consistency check on a backup after a scheduled backup completes [link]. Although still not a full validation, it is a welcome improvement. I believe this is currently our best line of defense. Again, the consistency check looks only at blocks & pointers. It does not verify structure, or indexes.

• Important Note: The "startup" consistency check in Server 7,8 & 9 only runs the first time server opens your files, and so is essentially useless. Restarts don't check again, unless server crashes, or the files are opened with FileMaker Client

File Maintenance Options

• Differences Between "File Maintenance" Options and "Save a Copy As" Options are explained in Answer ID 5594 [link]

• At DevCon 2007, FileMaker (reluctantly) confirmed that the File Maintenance options in FileMaker Developer 7 and FileMaker Pro Advanced 8, 8.5 & 9 should never be used. They are no longer seen as beneficial, and may be removed in a future release (it appears moving blocks around is not always a good thing)

What I would like to see:

• A validation utility, to help identify files that are corrupt (not to certify that the file is "OK", just a warning if parts of the file no longer make sense)

• An effort by FileMaker to collect damaged files that run, to see how they get past the consistency check and recover utilities

• A modified "Save as Clone" feature, that will ONLY remove data ( truncate the tables ), and NOT remove "structure" (I have seen this happen)

• A whitepaper on FileMaker's Utilities, written for developers, explaining the functions and limitations of the consistency check, the recover command, save as clone, and save as compressed

• Separate Menu Options for Recover's 5 basic functions:

1. Check File (Read Only)
2. Re-Index
3. Re-Catalog (Rebuild the Script list, Layout List, etc)
4. Remove stranded library objects
5. Rebuild (Remove Damaged)

• Recover must provide (at least) a brief summary of the file's contents, before, and after recovery, e.g. total number of tables, fields, layouts, accounts, value lists, and scripts

Tips on Re-Building a Damaged File:

• Every item in FileMaker is given a "hidden" serial ID number, based on creation order. In order to maintain the links between related files, the ID numbers used in the re-built file must be the same as the original. Use the Design Functions FieldIDs, FieldNames, ScriptIDs, ScriptNames, and ValueListIDs, ValueListNames (in FileMaker 6 and higher) to show the ID's used. The ID's of Relationships are not as important, since they have no external references.

• To preserve the hidden ID's, create all Fields, Scripts, and Value Lists in their original creation order, including previously deleted items. Names of deleted items are not important. Resist the urge to remove "deleted" items in the re-built file, since they may turn out to be in use, if the original ID reference was corrupt (I have seen this happen).

• Avoid importing scripts, or copying layout objects. Unfortunately, there is no documentation to say if this will propagate corruption.

• Avoid importing data directly from the original file. Export to a text file first, as explained above.

• To save typing, print all of your fields and scripts to a PDF file, then copy paste the text definitions into the re-built file. This is particularly useful for long IF statements, and calculations. For this to work, all Field and Relationship names must be the same as the original.

Copyright © Gregory Durniak | info@fileshoppe.com | http://www.fileshoppe.com/

Printer Friendly Version: [link]

---

Posted at Jun 12/2004 11:58 PM:
btw, exporting data to text & reimporting does not always remove data corruption.

you need to open the text export in a good text editor & examine for the presence of low-ASCII "control" characters & upper-ASCII char's as well. Keep low-ascii's that are tabs, end-of-record returns, vertical tabs, and group separators (repeating field delimiters). REMOVE all other low-ASCII's, they cause crashes.

Ilyse Kazar

Thanks! ... this note has been added above ... Greg

---

NOTE: Because of SPAM, this page is locked to new posts. If you have additions, corrections, or comments, please email to info@fileshoppe.com. Also, please let me know if you found this page useful!

---