Everything Else
Understanding (and Fixing) Type 22 Errors in Helix Utility 8.0
Introducing New Error Codes in Helix Utility 8.0

The technote New Error Codes in Helix Utility 8.0 lists the new error codes that are reported by Helix Utility 8.0 and later. These error codes are shown in the Activity Log panel of Helix Utility only when Detailed Logging is turned on, and are always recorded in the Activity Log file for the collection.

This technote specifically addresses the Type 22 error, what it is, how it affects your database, and how to correct it yourself, without having to send it to QSA ToolWorks for repair.

This is a long document full of highly technical information. It may take two or three readings to fully grasp what is described here, but once you do, you will have greater insight into the inner workings of a Helix collection and a better idea regarding what is going on in there.

This technote is broken into three sections: Understanding Type 22 Errors, Using the “Error 22 Fixer” Application, and What the “Error 22 Fixer” Application Does.

Understanding Type 22 Errors
Understanding Internal Record IDs and Master Indexes

Every record in a Helix collection has an Internal Record ID that is invisible to the end user and collection designer. (Users familiar with the Apple events for retrieving and deleting records are already acquainted with these Internal Record IDs.) Each Internal Record ID (abbreviation: ‘RID’) is indexed in a Master Index — which is likewise invisible to all end users. The Master Index is simply an index of RIDs and their locations within the collection. This is how Helix locates a record when it is requested.

RIDLocation(Field Data)

For example, if a relation contains nothing but a ‘name’ field and the first five records added are: {Paul, John, George, Pete, Richard} they are stored — either in open spaces or appended to the end — in the collection and assigned incrementally ascending RIDs in the order in which they are created. Each RID is stored in the Master Index along with the location where the record is stored. It looks something like this:

Note! The ‘field data’ is not stored in the Master Index; it is included in this table to help you see how it relates to the User Index described below.

Understanding User Indexes

By itself, the Master Index can only be used to retrieve records in the order in which they are entered. For additional power, a User Index is created by the collection designer to provide a useful sort order, faster data retrieval for lookups, optimized queries, etc. The structure of a User Index is quite different than a Master Index.

ID(Field Data)

You may expect a User Index to contain a sorted list of the data, but it does not. All that a User Index contains is a sorted list of RIDs. Following up on our example above, a User Index built on the name field looks something like this:

This illustration is grossly simplified — actual User Indexes are encoded for more efficiency — and again: the ‘Field Data’ is not really stored in the index; it is shown to help you understand what is going on.

Since ‘George’ is the first name that appears alphabetically in our list, his RID (3) is the first entry in a User Index built on the “Name” field. John (RID: 2) is next, followed by Paul (RID: 1), Pete (RID: 4), and finally Richard (RID: 5).

If this Index is used — for example — to display a list sorted by name, Helix accesses the User Index and retrieves the RIDs in the correct sort order, then uses the retrieved RIDs in conjunction with the Master Index to discover the location of each record within the collection file, and then, from each location, a record is retrieved, decoded, and displayed for the end user to see.

That, in a nutshell, is an explanation of how indexes work in Helix. In a perfect world, it works as expected and no errors are ever introduced into either the Master Index or the User Index(es).

The Source of Type 22 Errors: Duplicate Record IDs
RIDLocation(Field Data)

The problem that has been observed is that in rare cases, records are being created with a RID that already exists in that relation, creating a Duplicate RID problem. If that occurred in our example, the Master Index might look like this:

Both the second and third entries have the same Internal Record ID — the way Helix works internally, that second “2” can not be retrieved for any function at all.

Because the Master Index is incorrect, Helix is in a fragile state, which may explain some of the reported crashes, due to the confusion Helix has over deleting a specific Record ID only to find that a record with that Record ID still exists. Helix detects a problem and shuts down — as it should — to avoid further damage.

ID(Field Data)

A User Index built on the Name field would now look like this:

Even though George is properly stored in the database — at file location 36 according to the Master Index — that record can never be retrieved and used for anything.

If the person who created that record immediately tried to retrieve it, they would not find it, and would most likely re-enter it. This error might also happen in conjunction with a crash, so re-entering the record after the database is restarted would not seem abnormal at all. But as you can now see, the error is embedded in the collection, with no way to correct it other than deleting all of the data in the affected relation, thereby resetting the Master Index (and all User Indexes) to ‘empty’ indexes with no RIDs at all.

Type 22 Errors and Helix Utility 7.0.4 (and Earlier)

Helix Utility 7.0.4 and earlier do not check for the presence of duplicate RIDs, and therefore do not report this as an unrepairable problem. Those versions simply detect that the User Indexes do not have the correct number of records and schedules them to be rebuilt.

The presence of this error can be detected while using Helix Utility 7.0.4 as follows: Use Helix Utility 7.0.4 to ‘Repair’ the collection, so it can schedule affected User Indexes for rebuilding. Then open the collection with Helix RADE, Server, or Engine, and wait for the indexes to be rebuilt. When that is done, immediately save and close the collection. Immediately repair it (again) with the same version of Helix Utility. If Helix Utility immediately schedules the same indexes for rebuilding, your collection has duplicate RIDs. Turn on Detailed Logging to see which indexes are being flagged as invalid to confirm that it is the same ones being invalidated repeatedly.

A Final Note on Understanding Type 22 Errors

As noted above, the Type 22 error is an old problem that was not detected in prior versions of Helix Utility. It is perfectly logical that Helix Utility 7.0.4 would report a collection as having no problems, while Helix Utility 8.0 reports Type 22 errors. Please don’t think (as some have done in the past) that Helix Utility 8.0 is ‘breaking’ your collection! In truth, it is reporting problems that already existed. Before the collection had problems that were un-knowable and that caused unexpected behavior. Now you can know about this problem and take steps (as described below) to fix it.

The “Error 22 Fixer” App

If you followed the short tutorial on Master Indexes and internal Record IDs, the reason a full export/delete/import of a relation works should now be clear: exporting data as text divorces it from the RIDs, deleting resets the Master Index to “no entries,” and importing creates “new” records with new RIDs, eliminating the duplicates.

For relations with thousands (or millions!) of records, however, this is impractical, as it could take hours. To resolve this problem, we have created a utility program that may enable end-user repair of such a problem. Using the power of AppleScript, the “Error 22 Fixer” application uses Helix RADE and Helix Utility to locate and eliminate records with illegal Record IDs.

The app automates most of the process by using Helix RADE to delete every record that has a problematic Record ID and then using Helix Utility to confirm the fix and repair issues that arise during the fixing process.

The Importance of Following Instructions

For those who have Type 22 errors in a collection and simply want to fix them, we have some disappointing news for you: fixing Type 22 errors requires some user assistance. If you do not understand what the application does and what you must do to complete the repair one day you may discover that the collection has lost data because you failed to complete the repair by following the instructions.

In fact, here are the instructions as seen when the app is launched:

This application uses Helix RADE (7.0 or 8.0) and Helix Utility 8.0 to check a collection for Type 22 errors. (This is a new error detected by Helix Utility 8.0, which is why it is required.) If you have not yet opened your collection in Helix RADE Design Mode, do so now, before proceeding.

Repairing Type 22 errors requires that data be deleted from affected relations. A report is created listing each affected relation and the affected RecordsIDs. Each RecordID will have two records associated with it: a ‘Visible Record’ that you can currently see on views within Helix, and a ‘Hidden Record’ that exists in Helix, but can not be seen with Helix no matter what you do. (It is these hidden records that cause the index rebuilding every time Helix Utility is used to repair the collection.)

Both of the records associated with each RecordID must be deleted to repair the collection. It is your responsibility to re-enter one, or both, as desired. You will most likely want to re-enter the Visible Record, but we do not make that assumption. It is our position that you should actually look at your data to determine which records ought to be re-entered.

There are two repair methods, labeled ‘Quick’ and ‘Slow’ — the quick method should work for most repairs, but if Helix RADE hangs or crashes during the repair process, try the slow method instead.

If you are using Helix RADE 8.0, the repair will go much faster, as a new feature provides ability to bypass index rebuilding, which is really pointless until all of the errors have been fixed.

As the app runs, a report is written to a text file containing information about the Type 22 errors found. At the end of the process, this file is automatically opened, where this text is prominently displayed:

Warning! Type 22 errors have been found in this collection. Repairing this error required that data be deleted from the affected relations. Below is a list of each affected relation and the RecordsIDs that contained errors. Each RecordID has two records associated with it: a ‘Visible Record’ that you could see on views within Helix, and a ‘Hidden Record’ that existed, but could not be seen within Helix no matter what you tried. BOTH records associated with each RecordID have been deleted, and it is your responsibility to re-enter one or both, as desired. You most likely want to re-enter the Visible Record, but we do not make that assumption. It is our position that you should actually look at your data and determine which records ought to be re-entered.

As both of these messages indicate, simply running the “Error 22 Fixer” app does not fully resolve the issue. You must also apply your own understanding of your database in order to restore the valid data that is removed in order to complete the fix. Got it? OK, here we go…

Using the “Error 22 Fixer” App

The first step (obviously) is to Download the “Error 22 Fixer” app by clicking here. The download is a simple zip archive, which you may have to unzip manually to extract the app. Once you have the app on your computer, you can delete the zip archive.

The first time you launch the “Error 22 Fixer” app, macOS should ask you to confirm that this is indeed an application and ask you to confirm that you want to open it. Depending on your version of macOS and your security settings, the dialog may simply tell you the app wasn’t downloaded from Apple’s App Store and refuse to open it. In that case, dismiss the dialog and then right-click (or control-click) on the app and choose Open from the contextual menu; this will open a slightly different dialog that does provide an option to proceed with the app launch.

Lastly, we hope this goes without saying but… please make a backup of your collection before using the “Error 22 Fixer” app. If something goes wrong — and there is a definite likelihood that something can go wrong — you will want an ‘untouched’ copy to fall back upon. The “Error 22 Fixer” app does not make backups!

Step by Step…

  1. Open (a copy of) the affected collection with Helix RADE 7.0.4 or 8.0 (8.0 preferred) and switch into Design Mode (if necessary).
  2. Launch Helix Utility 8.0. This is most likely unnecessary, but it ensures that the “Error 22 Fixer” app will be able to find and access it.
  3. Launch Error 22 Fixer and review the Read Me information, then click Proceed.
  4. A dialog appears asking whether to try a Quick Repair or Slow Repair. If you have already run the “Error 22 Fixer” app with this collection and encountered an error (or crash), try the Slow Repair, otherwise choose the Quick Repair option.
  5. If the user from which you logged into Design Mode has a password, a dialog asks you to enter that password again. This is so the “Error 22 Fixer” app can repeatedly open the collection in Design Mode without requiring user intervention. Don’t worry: your password is stored only in memory during the time when the app is running. When you quit the “Error 22 Fixer” app, it is forgotten.
  6. Once Design Mode access is confirmed, a dialog appears asking you to name and save the report that is generated by the “Error 22 Fixer” app. We recommend putting it in the same folder as the collection itself, to keep the two tightly associated.

    At this point the process is automated so that no action is required — for a while — by the user. You will see Helix Utility checking the collection and Helix RADE automatically creating quick entry views in relations where type 22 errors were reported. A progress thermometer (see at right) provides feedback on the process, but since it is part of the “Error 22 Fixer” app, it may be hidden behind Helix windows. Feel free to bring the “Error 22 Fixer” app back to the foreground to monitor the thermometer. The best thing to do is to re-arrange the Helix windows to keep the thermometer visible at all times, while keeping Helix RADE in front.

  7. When all affected records have been deleted, the text file (named “Fix Report for …” by default) opens automatically, using the default plain text editor for your Mac. (We recommend BBEdit) This file contains the data that was found in the records with duplicate RecordIDs and deleted from your database.

    Because of the nature of the repair, every record in this report — both the visible and hidden records — have been deleted from the collection. (If you look in a pre-repaired version of this collection, you will find that records marked “visible” can be found, but ones marked “hidden” can not.)

  8. It is critical to the integrity of your data that you review this file and re-enter the records you wish to preserve. We do not automatically re-create this data because you may have already discovered that a record that had been entered was mysteriously missing and then re-entered that data, resulting in two identical records, albeit one that is hidden from view. And so, in order to ensure the maximum data integrity possible, we delete all affected records, leaving you with the responsibility to determine which ones may have been re-entered at some point, and to re-enter them yourself.

    To assist in re-entering the data, Design Mode is left showing only windows for the relations that had data removed. You can use an existing view (or create a Quick Entry view — create a view and open it with no template specified) to re-enter your data.

  9. Once you have successfully re-entered your data, the collection is “fully repaired.” Use the “Error 22 Fixer” app once again to Verify that there are no leftover errors. (If there are, they should be addressed in this second pass.) Note! when you run the “Error 22 Fixer” app a second time, be sure to change the default name of the new report, or it will replace the original report and the data will be overwritten and lost.

With the repair completed, you can go back to using Helix normally. But do note that if you are using a version of Helix prior to Helix 8.0, there is a possibility that type 22 errors will occur again. If you’ve seen type 22 errors in the past, we strongly recommend upgrading to Helix 8.0 to avoid a recurrence of them.

What The “Error 22 Fixer” App Does

The rest of this technote is incomplete, and somewhat incoherent, as it is still a work in progress.

A detailed explanation of the app is found below. Although it is not completely necessary, we do encourage you to read further in order to understand exactly what the “Error 22 Fixer” app is doing as it attempts to repair your collection.

How does the Clean Up app work? It starts by examining the detailed error report from Helix Utility for type 22 errors. The loop…

  1. Helix Utility 8.0 checks the collection and returns a list of the type 22 errors found. If no type 22 errors are found, a dialog congratulates you on this fact and the application exits.
  2. Working in Design Mode, the app creates a simple entry view in each relation where type 22 errors were reported, and

After the duplicates are identified, the app uses RADE to create a simple view in the affected relation, and it uses that view to retrieve the records one by one using the AE retrieve function (used by AppleScript and all third party solutions such as Qilan, Heliport, etc.). This is important because the AE retrieve can also return the internal RecordID of the record. The app retrieves the records one by one, examining the RecordID of each one. When it finds a record with an RecordID that matches one in the duplicates list, it writes the contents of that record to the report. This is the ‘visible’ record because this is the one that could be displayed by Helix all along.

Next the Clean Up app deletes that record. Why? Because until that record is deleted, the other record with the same RecordID — which we refer to as the ‘hidden’ record — remains inaccessible to Helix (and to you). With the ‘visble’ records deleted, the app can then find the next instance of a record with that same RecordID. When it is located, that record’s contents are also written to the report, labelled as the ‘hidden’ record (even though now it is actually visible) that could never be displayed — or used — by Helix before.

Since that record could never be displayed — or used — by Helix before, leaving it in place would result in ‘mysteriously changing’ data, as the formerly visible record is suddenly replaced by the formerly hidden/now visible one. Deleting it is the only way to ensure that your users do not draw the conclusion that somebody (or something) is modifying records wrongly.

Once that record is deleted, there are zero records with that old RecordID, so there is no longer a duplicate for that RecordID. And once all of the duplicate RecordIDs are addressed in this manner, the technical problem is resolved. But your data has now been altered in significant ways, and there is still work to be done.

It could be argued that, after resolving the duplicates, that the app should silently re-add the data that was visible all along, making the process fully automatic. We did consider doing that, but in the end we chose not to for two reasons: 1) validations could prevent it from working and 2) we decided that if you were running the app, you were in a better position to determine exactly which records you do and do not wish to preserve. Yes, that makes more work for you, but it ensures that the data is accurate according to your standards.

It is our speculation that those ‘hidden’ records had already been re-entered by the users who originally created them and then discovered that they were missing, but that is not 100% certain, so the best thing we can do is show you the data and let you apply your special knowledge of your database.

Because the Master Index is incorrect, Helix is in a fragile state, which may explain some of the crashes. Most certainly it explains the crashes that occur when running the app. The “Slow Repair” option stops after every step and uses Helix Utility to correct the internal errors that arise due to the confusion Helix has over deleting a specific RecordID only to find that a record with that same RecordID still in existence. Helix detects a problem and shuts down (as it should) to avoid further damage.

Type 22 Errors for Record ID Zero