Database Reliability- Part 4 (Dec 14)

The past few posts have described many ways for databases to go bad. All those potential problems give us plenty to think about, as we write code for our new accounting and estimating software. As a bonus, there are two more risks that we still haven’t covered.

The first problem is relatively minor: database files can easily expand to enormous size. FileMaker used to suffer from that. Things start to go south when a data record changes, and gets bigger. For example, when you add a new Material Purchase in Goldenseal, 3 accounts and 3 record arrays add a reference to it. That adds 4 bytes to their size, which makes them too big for their old spots within the file. It’s easiest to move them to the end of the file, but that leaves 6 empty gaps in the middle. Make that shift enough times, and the file will grow huge, even though it is mostly empty.

The NeoAccess database actually had a very good solution. It kept a list of empty spaces, and used it to save records in the most suitable gap, as close to the front of the file as possible. Unfortunately, their implementation was buggy, and too hard to fix. In 2002 we rewrote it using the same basic design, but with understandable code. It has worked well ever since. Goldenseal Pro uses the same approach, but with a few improvements.

The second problem is extremely serious: it’s relatively easy for a database to accidentally write data on top of other data. This is usually called “buffer overflow”, and it’s a general issue that all software faces. Overflows are the cause of most security holes that you read about in the news. They cause most data corruption, and the most serious of crashes.

Overflows are so dangerous that Goldenseal Pro has five different ways to prevent them.

  1. The Gap Manager that prevents file bloat, also knows where it’s safe to add records, without risk of damaging something else.
  2. After finding a gap, we next check the Sector Manager, which has a list of every record stored in that part of the file. The Manager looks on both sides of the gap, and makes doubly-sure there is no overlap with existing records.
  3. When Goldenseal accounting software saves records, it adds up the amount of data it writes, and makes sure the total matches exactly with the expected size. The same sanity-check happens again when it reads records from disk.
  4. Each record has about 20 bytes of “safety tags” at the beginning and end. When reading each record, we test the tags to make sure they are still OK.  If there ever is a bug that causes a record to overlap something else, it’s not hard to do forensics and discover the culprit. After we added the safety tags back in 2005, we soon tracked down the last remaining bug that was overwriting data.
  5. Within each record, there are additional safety tags for data that is most likely to cause a buffer overflow. If data is corrupted, the goal is to learn about it as quickly as possible. That way we can abort instead of damaging something else.

Most of these safety features are already present in current versions of Goldenseal. However, we are expanding and enhancing them for Goldenseal Pro. Disk storage is now so cheap that it makes sense to add an extra few megabytes, if it creates a more reliable database.


Meanwhile, back to Graphic Interface land. Since finishing the database work, our staff has focused on more advanced interface features. That includes breakdown tables, printing, “more info” boxes and the Find commands.

We also have started to simplify our accounting a bit. Goldenseal currently has 5 classes of bank accounts and transactions that are almost identical: for cash, checking, credit cards, loans, and savings. For Pro, they will all merge into one. Bank transactions will still look the same, so the only change is how you navigate to them.  Investment and escrow accounts are also very similar, but we haven’t decided yet whether to merge them, or keep them separate.

For Goldenseal estimating and accounting records, we currently have two types of breakdowns: category and item. There isn’t much difference between them, and they always have confused new users. Goldenseal Pro will merge them.

The tricky part for both these changes is converting existing data to the new format. Fortunately, a couple of previous updates made similar class changes, so we can reuse that code.

Dennis Kolva
Programming Director

Author: Dennis Kolva

Programming Director for Turtle Creek Software. Design & planning of accounting and estimating software.