"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



FileMaker 20 Video Tutorials



Become a patron of this FREE web site!


Recent Blogs:

Currency Formatting
Currency Formatting

Gathering Portals
Gathering Portals

Multiple Choice Picker
Multiple Choice Picker

Popups and Pickers
Popups and Pickers

Window Locking
Window Locking


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


The Philosophy of FileMaker recommends PCI!


Quick Tip:

Multiple Inspectors
Maybe you figured this out already but if you haven't, it will open a whole new world for the Inspector. If you click the icon for the Inspector in the Status Toolbar, it toggles the Inspector palette open and closed. If you use the menu item called "New Inspector" under the View menu, you can open as many Inspector palettes as you desire. I am guessing three will be enough to display all three tabs of the Inspector. Now you will be able to modify any area of the Inspector without switching tabs. And, the Inspector toggle button will show and hide all of the Inspectors.



Beginner, Intermediate and Advanced Video Training


Fun Stuff:

Jaymo
David Knight (aka Speedy) started calling me JoMo years ago, because of my initials, and it kinda stuck. Over the years, it's evolved to the current Jaymo but you can call me anything as long as you are friendly. Other nicknames include Hoss, n2 and Wiz.



Fireside FileMaker Podcast







RSS Feed
File Freedom
Level:
Version: FileMaker 16
Category: General
Tuesday, April 24, 2018
In my two decade career, I've only seen corrupt files while working in technical support. I was in technical support for five long years but even then it was not a common occurrence. Rarely have I encountered a corrupt file while working with a client or in my own personal FileMaker solutions. With good file management practices, your FileMaker file can live a long and happy life, free of corruption. I'm not saying corruption doesn't happen, it's just rare. Still you need to be prepared if the FileMaker God's choose your file for repair. This article will explain how corruption occurs, how to avoid it and how to fix it if needed.

File Freedom

I decided to write this article for several reasons. For starters, there is a lot of confusion about file corruption out there. In addition, I was recently involved in a debate on a forum about how network latency can potentially damage files. There was a lot of disagreement and, to be honest, nobody knows exactly how corruption occurs, even if they say they do. Hopefully, this article will provide some good information on the topic but I always welcome comments. Much of the information in this article stems from discussions with other FileMaker developers over the years, common misconceptions regarding the purpose of the Recover feature, articles from the FileMaker.Com web page, Steven H. Blackwell and various other electronic resources.

Steven Blackwell and I are best of friends but in two different camps of philosophy. He never works on a live system and always uses the separation model so he can minimize the amount he has to import from a old version to an new version of a solution. Steven says one of the most common methods for file corruption is unexpected closure of files and working on live systems. While I agree with him on the former, it is on the latter we debate. While I won't create a new system on a server, I will work on the inevitable changes and additions that occur on a live system. I've been doing it for decades without a hitch. My reasoning is FileMaker allows you to develop on hosted files. With this approach, I can use a more efficient single file development model and also avoid importing. I think we both have good points. Read on for a longer discussion of my point of view.

Avoiding Corruption
Before we discuss how to properly recover a file, let's talk about the best way to avoid a corrupt file.
"To avoid issues with file recovery, avoid doing the things that cause file corruption."

- Jon Thatcher, Retired Senior Software Architect at FileMaker, Inc.
What Jon means is that you should follow best practices regarding your FileMaker Server so it never crashes. The number one reason why FileMaker files become corrupt is because of the host crashing. This also occurs when a FileMaker file is single-user and your computer crashes. However, we will be talking mostly about best practices for servers since they are easier to control than a personal machine.

File Freedom

The basic approach is to run a lean mean serving machine. Don't add anything to the server that isn't needed by FileMaker Server. In other words, don't add anything cause FileMaker will install everything it needs. In fact, you should remove stuff. Don't run any other applications on the server like an Email Server, Accounting Software or any other software but FileMaker Server. I'm not saying FileMaker Server can't coexist with other applications on the same server but if FileMaker is your life blood, you want to limit any chance of crashing. For example, I had a client running FileMaker Server and some accounting software on the same server for years. One day they installed an update to the accounting software and FileMaker never ran on that machine again. Luckily, there were no crashes but it could have been much worse... much worse.

It's also important to disable unnecessary system components and services including file sharing, power saving, virus scanners and screen savers. All these features can cause slow performance and possibly crashing of your system software. For more detailed information on what not to do, see Knowledge Base article #000024447 at the filemaker.com web site:

General Performance Tips for FileMaker Server for Macintosh

General Performance Tips for FileMaker Server for Windows

What's the Purpose of the Recover Feature?
The purpose of this article is, ultimately, to prevent damaged files. People think Recovery can fix a corrupt file that can't be opened. Recovery is actually designed to open a file at all costs. It might delete fields, layouts, tables or any corrupt item in a file so it can be opened. It can even fix some corrupt items but it's not a magic wand that fixes your file. Once a file is recovered, the idea is to import the data into a clone of a backup, not to continue using it. Continuing to use a recovered file might work fine in the case of the client below but it's not a risk I'm willing to take. BTW, I inherited the file in the screen shot.

File Freedom

The FileMaker, Inc. web site has a Knowledge Base article listing File Management Best Practices. There are six items listed as causes for file corruption which I would like to cover to gain a better understanding of the issue.

1) Catastrophic Hardware Failure
Any hardware issue causing your computer to lose power and unexpectedly quit a FileMaker solution can damage your file. Ninety-Nine times out of One-Hundred, your file will be fine. It's when that failure occurs during a save process that you are most likely to have an issue. This occurs on FileMaker Server when transferring data from the cache to the hard disk. When you are developing a solution in single-user mode, exiting Manage Database, moving from Layout to another mode and saving a script are some of the most common save routines that, if interrupted, could cause file corruption. Even interrupted data entry changes can cause corruption. It kinda comes down to luck.

File Freedom

NOTE: Data entry is saved during idle time but not more than every five seconds.

Like I said, I don't see corrupt files very often. The reason is the FileMaker development team has guarded against this problem to best avoid corruption. FileMaker has been in development at FileMaker, Inc. for nearly three decades so they've had plenty of time to figure out how to protect FileMaker data. For example, changes to Manage Database are only written when exiting instead of writing those changes as they are made. That means you just lose the most recent changes instead of corrupting your file. It's virtually fail safe, unless FileMaker happens to crash at the very moment of exiting.

2) Unexpected Loss of Power
This is really the same issue as hardware failure except your computer doesn't need repair. Power outages while working on a FileMaker file have happened to me quite a few times over the years but has never resulted in a corrupt file. It's not a bad idea to invest in a UPS (Uninterruptible Power Supply), especially if you live in an area with frequent power outages. Battery backup systems are less than $200.00 these days and can easily pay for themselves with one power outage. Also, consider the occasional blown fuse and kicked power cord.

File Freedom

3) Clients disconnecting without proper termination of their activities
I'm not sure exactly what this means but my best guess is it's a reference to schema changes across a network and not data entry. Yes, interrupting data entry saves to the server could cause corruption but FileMaker Server waits for the entire packet. Better to lose the entire entry than write bad data. Of course, this process isn't perfect, as discussed below, but simply disconnecting a guest doing data entry doesn't seem to be enough to cause corruption. Even if bad data does make it to the server, validation occurs before it is written. There are so many checks and balances for common scenarios like this that something more out of the ordinary seems more likely the culprit of corruption as will be discussed below.

4) Writing to a Bad Spot on a Hard Drive
Bad sectors on a hard drive are fairly uncommon but can happen. Modern operating systems attempt to identify bad sectors and avoid them. There are also utilities that will flag bad sectors as unusable. While I'm developing a FileMaker solution, I try to make a backup every couple of hours. It's a good excuse to take a break anyhow. Some developers like to make a backup after each major programming hurdle. The point is to make backups. If a file problem occurs during the development process, it's best to bite the bullet and revert to a backup. You'll lose some work but you don't want to introduce corruption into a file. Corruption isn't always apparent and can rear it's ugly head weeks, month or years down the road.

File Freedom

5) Software and Hardware Conflicts
While hardware conflicts are fairly uncommon (especially on a Macintosh), software conflicts were half the calls in my technical support tenure. That's why FileMaker, Inc. recommends dedicating a machine to running FileMaker Server. While software may work together today, a simple update can create a conflict. If your server crashes, there is always the potential for damage to occur. While it's easy to limit what goes on with a server machine, it's another story with a personal device. If you will be developing FileMaker solutions professionally, resist the temptation to have the extension that makes Oscar the Grouch pop out of your trash can.

SIDE TIP: Steven Blackwell cites power failure and misconfiguration as the top reasons why files become corrupt on a server. Power failure can be minimized using an uninterruptible power supply (UPS). Misconfiguration includes turning on file sharing, using an automatic virus scanner, time machine, automated patch installer and long list of other items that can cause a software conflict and crash your server.

File Freedom

6) Network Latency
This issue is probably the most misunderstood possible cause of file corruption. Network latency is defined as how long it takes a packet of data to travel from one point to another. While a slow network does not necessarily lead to problems with a FileMaker file, dropped packets can. You want all your information to get from point A to point B or corruption could occur. FileMaker engineers have worked tirelessly for decades minimizing the impact of networks on file corruption. While we all have to live with data entry across networks, schema changes on a live system are really the biggest concern. While I don't think an entire FileMaker solution should be developed on a live FileMaker System, adding a field or table here and there is not gonna to cause any higher chance of corruption than adding it locally. Just make sure you aren't working over a DSL connection and you should be fine. The alternative is taking down an entire system just to add one field or working on a copy and importing the current data. Both cause disruption of service and possible late night developer hours.

File Freedom

I've been modifying client solutions over an internet connection for a decade and never once encountered an issue. I do this knowing I have a reliable fiber optic internet connection with downloads and uploads of 100 megabytes. Why do I do this when I could grab a copy of the database and work on it locally. Simply put, it just works. Like I said, I've had no problems in ten years. Besides, FileMaker, Inc. introduced the ability to make live schema changes way back in FileMaker 5.0v3 because they approve of this method for modifying FileMaker files. On top of this, my clients demand this type of service. The alternative is to work on a copy and either have down time or require importing of data. If they just want me to add a report, the best approach is to make the changes while the file is live.

Backups
To counteract file corruption, your biggest weapon is backups. As mentioned above, make backups throughout the development cycle. You should have scheduled FileMaker Server backups at least once a day once the solution is deployed. I like to have RAID or at least two hard drives on a server. Live files and the system software go on one hard drive and backups go to another. This redundancy prevents the complete loss of the entire solution. In addition, have a third party automated backup system backup the FileMaker Server backups. Never backup live databases with anything other than FileMaker Server or the backups will likely be corrupt in addition to possibly corrupting your live databases. I also recommend turning on progressive backups in FileMaker Server if a lot of data entry occurs in your solution.

File Freedom

Protecting your Data Outside Corruption
I rarely need to use backups but when they are needed, they are invaluable. Like I said, I've never encountered a corrupt file outside of technical support. What backups are useful for, more commonly, are developer or user mistakes. The biggie is deleting all the records in the found set. This happens most often on insecure systems when users don't read warning messages and think they are deleting a single record. I usually prevent users from deleting records, leaving it to administrators or at least a button driven script. Of course, I can't leave developers out of the equation. I've had my fair share of brain farts. If I'm going to work on a live system and am doing something more than just adding a layout, I usually make a backup before I start.

Backups are great but another great method for protecting your data is FMDataguard. It stores developer specified edits and deletes in a FileMaker table and works seamlessly in the background. Not only that, it can roll back a change in an instant. This is often far better than replacing the entire solution with a backup!

Clone
Having a clone handy allows you to import data into an uncorrupt version of your schema. FileMaker Server even allows clone creation as part of the backup schedule. If corruption occurs, import the data into a clone that has never been crashed and you should be good to go. If you can't open your file then recover it. If it opens then quickly import it into a clone that has no corruption. Never work with a recovered file past import.

File Freedom

SIDE TIP: A clone can only be created on a database that is single-user.

Rebuilding Indexes
Rather than recovering your entire database and importing it into a clone, sometimes all you need is to rebuild an index or two. A corrupt index can show itself in many ways. Basically, anywhere the index is utilized is a potential indicator. A relationship might not be working correctly. Maybe an auto-complete isn't completing. Even a value list can go awry when an index is corrupt. First determine if it is developer error. I've thought a lot of times I had a corrupt index when I had really just made a mistake in my programming.

The FileMaker Recover tool does have an advanced option where you can just rebuild indexes. If you select just this option along with "Copy file blocks as-is" then the invasive recovery will not occur and you can continue you to use the file.

File Freedom

If you believe you have just one or two fields with a corrupt index and don't trust a file that runs through recovery, the easiest solution is to turn off indexing, exit Manage Database, re-enter Manage Database and turn back on indexing. Just to be clear, this means setting indexing to "None" in Storage options for the misbehaving field.

File Freedom

Recovery White Paper
Steven Blackwell has written quite a few white papers on FileMaker technologies but this is probably my favorite. He goes into significant detail about how blocks and headers work, if you really want to understand how a FileMaker file is constructed. It probably won't help you become a better FileMaker developer but it has a high geek factor rating. More practical sections of the white paper cover how to prevent corruption and the real purpose of the Recover feature.

Official Recovery White Paper

This white paper was written for FileMaker 10 but is still valid and relevant to this day. Unfortunately, there is nothing more current from FileMaker, Inc.

File Recovery
If you get caught with your pants down and you can't revert to a backup or recover and import into a clone, FileMaker, Inc. does have a recovery service. This is not a free service and can takes months so use it as a last resort.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

Rick Torchia 04/25/2018
  I am working on an inherited FileMaker solution. Is there a way to tell if it has crashed in the past? Or a way to determine if it has potential damage from a crash? Thanks for the great article.
Response by:   John Mark Osborne 04/27/2018
There is a dirty close flag set in a FileMaker file when it unexpectedly quits so FileMaker can display messages about the occurrence, among other things. However, this data isn't really accessible and won't tell you if there is something wrong with the file. I would recommend performing a consistency check through the Recover interface. You'll see it as an option in the dialog that appears after you choose Recover from the File menu and then single click on a file. Read the log it produces and that should tell you if anything is wrong with the file.
Danny Anctil 04/25/2018
  Hello, John Mark. The only time I had to deal with a corrupt file was a case in which I had to assume FMPro application that was thrown together by a non-IT employee who took it upon himself to play with FMPro and the organization had no policy or or supervision to prevent it. His application eventually became critically important, and then started crashing, so it was handed over to me. First thing I did was lock down access to FMPro development, then started troubleshooting the problem, on a file and application that I had no familiarity with. It took me about 12 hours, non-stop, to identify and isolate one corrupt record, which I deleted, and the problem was resolved. After that I went to work completely rebuilding the files and application. It served to get management of the organization behind me to establish policies that permitted me to maintain exclusive access to FMPro development and recognition by the staff that anything involving data applications should be brought to my attention. After that, application development accelerated and there were no more corruption issues. I miss my days of developing with FMPro!!
Response by:   John Mark Osborne 04/25/2018
Great story Danny! Thanks for sharing!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!