Damaged Files & Hidden Corruption in FileMaker Pro

[gdurniak]

For detailed info on file corruption , see:

New York FileMaker Developers Group ... Serious Concerns

http://masdevelopment.com:3455/1/57

[Tom Donaldson]

As far as I can tell there is no way to define schema in a text file and import it, or export schema to a text file.

This seemed like a minor inconvenience to me until I experienced a recent database corruption that has trashed table definitions as well as data. Unfortunately the problem was not immediately obvious, and I did not discover it right away. I now have quite a bit of work to do to recover no matter what direction I take.

I don't mind reloading data (I have dumped records as backup), but I sure do mind having to manually re-enter schema. Most database products I have used in the past have permitted loading of schema from text files. Filemaker: this would be a great feature!

At this moment I am considering switching to a more "traditional" SQL database product for safety sake.

[dkemme]

Is this any less likely in FM7?

[Fenton]

A client of mine recently lost a version 7 file. He was being a complete idiot. There was a power outage, on a cheap computer, and he didn't have a UPS. I can't remember exactly what he was doing at the time, I believe printing. The file would open, the structure appeared to be fine, but there was no data. FileMaker wouldn't Recover the file. He was on XP, so I said send me the file. I recovered it fine on Mac OS X; but it didn't matter, 'cause there still was no data. I find that even more disturbing than losing some of the structure. As I said, he was being an idiot (which he'd be the 1st to admit); has anyone else had experience with a badly crashed version 7 file? Did you Recover any data?

[cjaeger]

Does it have to be a text file?

To export structure:

Save as Clone

To export Data

Export as FileMaker. Or save a (compressed) copy of it.

Can't be too hard to remeber, does it?

I always use a developer starter file which clones the structure of the dev project at opening and before closing.

-----

response to Fenton:

Yes, I had severe crashes, but when in single user mode, none of them lead to data loss except for the very last records entered. This may be different when in Multi-user or running FileMaker on Windows XP. MacOS X with journaled Filesystem turned on prevents data loss quite evectively.

As for FileMaker Server: It has a backup feature and FileMaker, Inc, knows why. If you don't use it, loose it!

I can still remember those Days when every single FileMaker 3 database should have a backup-starter script when in multi-user mode. Though not stricly necessary, it is good practice to run such a script at least once a day, just in case ...

I definitely have it while developing to guard against my own stupidity.

[gdurniak]

Christian,

You missed the point of my article (see link above). Yes, you can "Save as Clone", but there is no way to be sure that the Clone is OK. It can be corrupted.

Tom (above) points out that SQL Databases get around this by allowing you to export file structure as a text file, that can be edited by hand. A SQL utility is provided to then build a NEW database file, from scratch, with no corruption, using the text file instructions.

If you think about it, a FileMaker file is just a long set of instructions, followed by data

From your response, my guess is you haven't seen hidden corruption in your files. When you do, this will all make sense.

[gdurniak]

Doug,

You ask, is corruption less likely in FM7?

FileMaker did improve FM7, so that design changes are not committed until you're "done". Therefore, for example, FM7 files are ( in theory ) less likely to get corrupted if you crash while in scriptmaker, or in layout mode.

[QuinTech]

I remember at the FM7 road show, one of their developers stopped just short of saying we would never have to recover a file again. Of course, he said it was still possible, but that in all their testing they had never had to recover (or maybe it was only 1 time in 1000, or something very rare... i don't remember which).

Point is, FM7 should be far, far less likely to need recovered, according to FMInc.

Jerry

[Hurican]

It gets interesting though, when in 6 there is latent but as of yet... unnoticed corruption, and you try to convert it to 7. Then you can get unexpected results, and the file will most likely have to be recovered and compressed pre-conversion.

The best thing to do though, is to export to text file, save a clone, and import into the clone. Once the OS gets ahold of the text, all extraneous artifacts are removed.

[eJK]

Fenton -

I've experienced a pretty bad crash in FM7 that seemed to lead to data loss; fortunately, I was able to get everything recovered, but it was definitely one of those 'oh sh!' moments.

After a long day of hacking I was doing some cleanup in Define Database. I went back to Browse mode, and all of a sudden, all of my layouts were gone, as was all of my data! I went back to the Tables view in Define Database - all of my Tables were there, but they all read '0 fields defined, 0 records'. Uh oh! Not good. Not knowing what else to do, I closed the file, which was being served off FM Server 7. I went to check the server logs, nothing out of the ordinary. Backups occur in the middle of the night, so I was looking at an entire days work, lost.

I sadly went to another computer to load up one of my backups, and in disbelief tried one last time to figure out what was wrong with my original. I did an 'Open Remote', and viola! The file was back to normal with all Tables and Data intact. Still can't figure out what went wrong or what I did to fix it... seemingly nothing out of the ordinary on either side. Glad everything turned out cool, but I was definitely sweating it out for a good half hour.

Anyone else have a near-death experience like this?

[gdurniak]

Note:

As of 3/1/2005, FileMaker's web site is under construction, so many of the Tech Info Articles I list have been moved, or re-moved. I hope to update the links, once the web site is back in order.

[gdurniak]

Note:

As of 3/1/2005, FileMaker's web site is under construction, so many of the Tech Info Articles I list have been moved, or re-moved. I hope to update the links, once the web site is back in order.

[gdurniak]

Note:

As of 3/1/2005, FileMaker's web site is under construction, so many of the Tech Info Articles I list have been moved, or re-moved. I hope to update the links, once the web site is back in order.

[Fridolin]

I have had some experience with corrupt files when still on FM5. Corruption mostly occured when doing changes in layouts. Recently rebuilt a large sollution in FM7 using separate files for data and UI and then provoced it by chrashing the server several times. I had to replace just one the UI files. I really think separation of data makes it a lot safer and easier to clean up when (not if) the ... hits the fan.

Regards / Fridolin

[Fridolin]

I have had some experience with corrupt files when still on FM5. Corruption mostly occured when doing changes in layouts. Recently rebuilt a large sollution in FM7 using separate files for data and UI and then provoced it by chrashing the server several times. I had to replace just one the UI files. I really think separation of data makes it a lot safer and easier to clean up when (not if) the ... hits the fan.

Regards / Fridolin

[Fridolin]

I have had some experience with corrupt files when still on FM5. Corruption mostly occured when doing changes in layouts. Recently rebuilt a large sollution in FM7 using separate files for data and UI and then provoced it by chrashing the server several times. I had to replace just one the UI files. I really think separation of data makes it a lot safer and easier to clean up when (not if) the ... hits the fan.

Regards / Fridolin

[xochi]

I've had two bad crashes using FM7, so this "never have to recover a file again" statement appears to be utter nonsense.

1. Using FM 7.0v2 on a locally opened file, I created a looping script to create a million records. After about 230,000 records, filemaker crashed. The database file was not openable, and in fact it was so corrupted that using the "Recover" command crashed FileMaker (both 7.0v2 and 7.0v3). I subsequently upgraded to 7.0v3, repeated my test, and it was able to create 500,000 records w/o a crash.

2. Using FM Server 7.0v2, I was editing field definitions from a FM Developer 7.0v3 client. The field I was defined as follows:

Calculated field, unstored : gMaxIDNumber = Max(Self-relation::ID) where self-relation was a self-relationship using the cross-product operator.

Then, I went into Define Fields, and turned on "Global" storage for the field. Upon closing the file dialog on FM Client, FM Client put up a dialog saying "Updating field definitions..." and FMServer went to 100% CPU usage and stayed there. 4 Hours later, it was still in this state, so I force-quit both FM Server and FM Client. The file was damaged, but did appear to recover OK.

So, moral of my story: Frequent backups, with as much time between backups as work you can afford to lose.

I haven't yet tried #2 with FileMaker Server 7.0v3, I wonder if they fixed this bug.

[xochi]

I've had two bad crashes using FM7, so this "never have to recover a file again" statement appears to be utter nonsense.

1. Using FM 7.0v2 on a locally opened file, I created a looping script to create a million records. After about 230,000 records, filemaker crashed. The database file was not openable, and in fact it was so corrupted that using the "Recover" command crashed FileMaker (both 7.0v2 and 7.0v3). I subsequently upgraded to 7.0v3, repeated my test, and it was able to create 500,000 records w/o a crash.

2. Using FM Server 7.0v2, I was editing field definitions from a FM Developer 7.0v3 client. The field I was defined as follows:

Calculated field, unstored : gMaxIDNumber = Max(Self-relation::ID) where self-relation was a self-relationship using the cross-product operator.

Then, I went into Define Fields, and turned on "Global" storage for the field. Upon closing the file dialog on FM Client, FM Client put up a dialog saying "Updating field definitions..." and FMServer went to 100% CPU usage and stayed there. 4 Hours later, it was still in this state, so I force-quit both FM Server and FM Client. The file was damaged, but did appear to recover OK.

So, moral of my story: Frequent backups, with as much time between backups as work you can afford to lose.

I haven't yet tried #2 with FileMaker Server 7.0v3, I wonder if they fixed this bug.

[xochi]

I've had two bad crashes using FM7, so this "never have to recover a file again" statement appears to be utter nonsense.

1. Using FM 7.0v2 on a locally opened file, I created a looping script to create a million records. After about 230,000 records, filemaker crashed. The database file was not openable, and in fact it was so corrupted that using the "Recover" command crashed FileMaker (both 7.0v2 and 7.0v3). I subsequently upgraded to 7.0v3, repeated my test, and it was able to create 500,000 records w/o a crash.

2. Using FM Server 7.0v2, I was editing field definitions from a FM Developer 7.0v3 client. The field I was defined as follows:

Calculated field, unstored : gMaxIDNumber = Max(Self-relation::ID) where self-relation was a self-relationship using the cross-product operator.

Then, I went into Define Fields, and turned on "Global" storage for the field. Upon closing the file dialog on FM Client, FM Client put up a dialog saying "Updating field definitions..." and FMServer went to 100% CPU usage and stayed there. 4 Hours later, it was still in this state, so I force-quit both FM Server and FM Client. The file was damaged, but did appear to recover OK.

So, moral of my story: Frequent backups, with as much time between backups as work you can afford to lose.

I haven't yet tried #2 with FileMaker Server 7.0v3, I wonder if they fixed this bug.

[gdurniak]

On May 5, 2005, FileMaker rolled out its new Knowledge Base, which renumbered all Tech Info Articles, and changed their URL's

This is unfortunate, since Tech Info Articles are referenced everywhere

The links in this article have been fixed

[T-Square]

Fenton--

Way back when, you asked whether anyone else had data loss in FM7, and whether people were able to recover. Well, I had a similar client problem--machine was left on overnight with the monitor turned off (so they could keep their Blackberry synched). Two year old decides to play with the keyboard, and next morning there's no data in the file--except in certain reports.

After three days of trying every trick I could imagine, I was able to recover the missing data by going to the report that still displayed data and exporting all the records I found there to a new data file. From there I was able to locate the data corruption (several records without required fields), delete, and re-import into a new (perhaps clean) copy of the datafile.

I hope that helps someone out there. Now, if we could get the data schema export/import thing.

David

[jackfeuer]

I've recently discovered what appears to be a corruption problem within the schema in a major development project.

What happens is this: In a Go To Related Record (GTRR) operation, "ghost" records appear; that is, non-existent, empty records. In the FM sidebar, the found count exceeds the total record count. For example, when executing a GTRR, I'll discover 49 "found" records out of a total 48 records in the table.

The extra "record" is viewable (although all its fields are empty, including the relationship fields). I can also "delete" it through FM delete functions. However, when I do another GTRR, another ghost record appears.

This error is associated with just one table. Any GTRRs that go to that table exhibit the problem. GTRRs to other tables work fine.

I only recently noticed the error, but tracking back through daily backups, I can find the day in which the problem appears. One day the GTRR works fine for this table, the next day it does not.

Something else of interest: The day the problem first appears coincides with a day that the app crashed and needed to be recovered. Note that I did NOT use the recovered file. I went back a day, got the backup, and re-did my work for the fatal day. So, even though the prior day's version of the app works fine, the corruption must have been latent in that version.

It's now two weeks later, and I've got a lot of work to recreate. But even more troubling, I'm worried about how far back I need to go to ensure that the latent corruption isn't present. My best guess is that I'll go back several more days to the point where the troubled table was first added.

If anybody has seen something similar (these "ghost" records in GTRRs), I'd be interested in hearing about the experience -- especially if you found a solution and/or determined that the issue was something other than a corrupt schema.

And yes, I add my vote that I'd like the ability to export the schema as text that I could reimport.

[gdurniak]

jackfeur,

when you have "49 "found" records out of a total 48 records in the table", you may have bad data, or a bad index. I have seen this happen.

try exporting all records to a text file, then import back into a clone. This may be enough to fix the problem.

otherwise, you may have to go back quite a few backups, to find a "clean" one.

[xochi]

One tip I discovered: I had a file that was corrupted so badly that when I tried to use the Recover file command in FM7, it would crash FM7.

However, I'm also using FM7 Developer. On a whim, I first tried the File Maintenance commands (compress/optimize) on the corrupted file. To my surprise, the worked on the file, after which I was able to Recover the file successfully.

Now, I'm sure this is not an ideal situation -- it's very possible that the Compress/Optimize step simply discarded some data. But, in an emergency, when you had to recover as much data / layouts / scripts as possible, it could prove useful.

[Vaughan]

Read this TechInfo article recently posted:

Answer ID: 5317

How To Handle A Corrupt FileMaker File

[xochi]

One additional tip (use at your own risk, your mileage may vary, etc.):

I've encountered at least one case where a corrupt file could not be recovered in FM7, but would recover in FM8 Advanced. Now, whether this file had the dreaded 'hidden corruption' I can't say, but I was at least able to open it an get the data out.

So, if you have a badly corrupted file, i'd suggest you try FM8 Advanced for the recovery process.

[Miguels]

Where I work we have incountered some serious issues..

1. We have had one file in particular have ghost scripts. This I believe happened when a developer copied and pasted a script from another file. What the developer believes happened was he copied a script,close the file then pasted the it into the new file. We(all in house developers)believe this occured because the file was close. However, the script could not be opened if you printed it.. it contained nothing in the script. You could not open it or delete.

2. We also encountered script that when we tried to move in order to organize, would assume the name of the script of the order where we placed it.

both of these issues were corrected with a recovery.

3. We have had a server continue to host several files which by the way were not backing up because they were corrupted. The only reason we found out about the corruption was because the server had to shut down and restarted for something else. Once the server started back up it did a consistency check. Needless to say the file would not open as it came back as damaged.

We tracked this back to 2 weeks earlier. Thankfully this was a conversion file in process and not in production... Luckily we also had a backup.

After speaking with Filemaker they informed us that on any conversion from Fm6 to Fm7 to fm8 that they suggest you do a complete recovery as stated in the white pages. This included compacting, recovery, exporting records(as a .merg file), cloning recovered file, then reimporting data. They even told us that corruption can be passed from files upgraded as early as 4.. and suggest we may even really consider a complete rewrite instead of conversion. Go Figure.

Anyways thought it was interesting enough to disucuss. Oh by the way, here's a cavieat.. you can't obviously this nothing new, export container fields. You can only get the data from the previous version's container and paste back into the new. So what about corruption in this data?..

[gdurniak]

Miguels,

You say "After speaking with Filemaker ... they told us that corruption can be passed from files upgraded as early as 4 ... and suggested we consider a complete rewrite instead of conversion"

Thanks for confirming that

and yes, there is no easy way to "clean" the contents of container fields