Semi-Sorted A database can either be sorted, unsorted or semi-sorted. The first two are pretty obvious, but it is less clear how a database becomes semi-sorted. A database becomes semi-sorted when a new record is added to a currently sorted database and the option to keep records sorted is unchecked. It can also become semi-sorted if a record is edited -- but only if one of the fields used in the sort is edited. Deleting a record will not make a database semi-sorted.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!!