Logo Home    Page list

Check RootsMagic Database Integrity #pragma #database #integrity

This page applies mainly to RootsMagic 4. RM5 incorporates the SQLite tools Integrity Check, Reindex, and Vacuum under the menu File > Database Tools > Test database integrity | Rebuild indexes | Compact database. However, it is not apparent that the Integrity Check is done automatically on start-up so users would be well advised to do it periodically. Now that there is an integral Integrity Check, we have learned that some modifications to the database using a SQLite manager having a substitute RMNOCASE collation results in index errors. Therefore, users of SQLite queries are warned:
  1. If using RM4, avoid queries that modify fields collated with RMNOCASE.
  2. If using RM5, reindex with the SQLIte manager before running the query; then reindex using RM5 before using it to make other changes to the database or major reports.

Why?

As of RootsMagic 4.0.8.4, there is no built-in mechanism to check the integrity of a RootsMagic database. Some users have encountered SQLite error messages while using RootsMagic, the most terrifying of which is SQLite Error 11 - Database disk image is malformed. It can render the database unusable, or, at best, only parts of it usable. The database has been corrupted in some way and may be undetected for months until some procedure causes the SQLite database engine to attempt to access the corrupted area. Ever since the corruption enters, RootsMagic backups faithfully preserve it. For a real example, read the discussion on the RootsMagic forum in the topic Corrupt Database, Backups & Recovery Therefrom. Had there been a convenient check of the integrity of the database used routinely, the corruption would have been caught much earlier. Corrective action could have been taken sooner and perhaps the cause identified, given a fresh memory of the events preceding. There is no reason why such an integrity check cannot be incorporated in the RootsMagic application on the opening of a database; that's where it should be done. Until the software is upgraded to do so, there are tools that can be used outside RootsMagic 4 to validate the integrity of its SQLite databases.

How?

Jump right in by reading How to query RM4 which just happens to use one of the following SQL commands to carry out an integrity check. Make an integrity check an integral part of opening your database by following the outline in Check RootsMagic Database Integrity on Opening.

The SQLite database engine incorporates two SQL commands that carry out a more or less comprehensive integrity check. From the on-line manual:
This pragma does an integrity check of the entire database. It looks for out-of-order records, missing pages, malformed records, and corrupt indices. If any problems are found, then strings are returned (as multiple rows with a single column per row) which describe the problems. At most integer errors will be reported before the analysis quits. The default value for integer is 100. If no errors are found, a single row with the value "ok" is returned.

The pragma is like integrity_check except that it does not verify that index content matches table content. By skipping the verification of index content, quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same.

These SQL command lines can be run by any SQLite manager against a RootsMagic database opened by the manager with one big exception: indices can only be checked if SQLite can access a collation sequence named RMNOCASE, a name and collation unique to RootsMagic and embedded in the application. Thus, PRAGMA integrity_check will fail unless the SQLite manager has the means to add a collation sequence so named. SharpPlus SQLite Developer (not the free version) is one that can. Of course, RootsMagic should be capable of doing this full test of integrity as it has the RMNOCASE collation.

That leaves the partial test, PRAGMA quick-check, as the one that we can use on RootsMagic database files with any SQLite manager. And this may suffice, because even an 'empty' RootsMagic database is reported to have 13 missing rowid's from index idxSourceTemplateName - 'OK' would never be an output from integrity_check.

Comparison of outputs for a corrupted RootsMagic database

PRAGMA integrity_check;
PRAGMA quick_check;
On tree page 10429 cell 0: 2nd reference to page 16050
On tree page 4494 cell 112: 2nd reference to page 16050
On tree page 4494 cell 112: Child page depth differs
On tree page 4494 cell 113: Child page depth differs
On tree page 16048 cell 0: Rowid 2285 out of order (max larger than parent max of 800)
On tree page 11057 cell 3: Rowid 801 out of order (min less than parent min of 2285)
On tree page 10892 cell 0: 2nd reference to page 16050
On tree page 11617 cell 67: 2nd reference to page 16051
On tree page 11617 cell 67: Child page depth differs
On tree page 11617 cell 68: Child page depth differs
On tree page 11548 cell 0: 2nd reference to page 16045
On tree page 15783 cell 1: 2nd reference to page 16045
On tree page 12446 cell 53: 2nd reference to page 16048
On tree page 12446 cell 53: Child page depth differs
On tree page 12446 cell 54: Child page depth differs
Page 11154 is never used
Page 11155 is never used
Page 15429 is never used
Page 15430 is never used
Page 16054 is never used
Page 16055 is never used
Page 16056 is never used
Page 16057 is never used
rowid 55 missing from index idxSourceTemplateName
rowid 172 missing from index idxSourceTemplateName
rowid 190 missing from index idxSourceTemplateName
rowid 241 missing from index idxSourceTemplateName
rowid 296 missing from index idxSourceTemplateName
rowid 297 missing from index idxSourceTemplateName
rowid 326 missing from index idxSourceTemplateName
rowid 333 missing from index idxSourceTemplateName
rowid 345 missing from index idxSourceTemplateName
rowid 401 missing from index idxSourceTemplateName
rowid 21 missing from index idxSourceName
rowid 61 missing from index idxSourceName
rowid 63 missing from index idxSourceName
... cont'd for total of 1026 'rowid ## missing...'
On tree page 10429 cell 0: 2nd reference to page 16050
On tree page 4494 cell 112: 2nd reference to page 16050
On tree page 4494 cell 112: Child page depth differs
On tree page 4494 cell 113: Child page depth differs
On tree page 16048 cell 0: Rowid 2285 out of order (max larger than parent max of 800)
On tree page 11057 cell 3: Rowid 801 out of order (min less than parent min of 2285)
On tree page 10892 cell 0: 2nd reference to page 16050
On tree page 11617 cell 67: 2nd reference to page 16051
On tree page 11617 cell 67: Child page depth differs
On tree page 11617 cell 68: Child page depth differs
On tree page 11548 cell 0: 2nd reference to page 16045
On tree page 15783 cell 1: 2nd reference to page 16045
On tree page 12446 cell 53: 2nd reference to page 16048
On tree page 12446 cell 53: Child page depth differs
On tree page 12446 cell 54: Child page depth differs
Page 11154 is never used
Page 11155 is never used
Page 15429 is never used
Page 15430 is never used
Page 16054 is never used
Page 16055 is never used
Page 16056 is never used
Page 16057 is never used

Discussions

JP-RM

Why checking RM database integrity proved very useful

JP-RM 02 June 2010 14:23:30

In early April 2010 I suffered an "SQLite error 11 - database disk image is malformed". Although never encountered before, all of a sudden practically any addition to my RM data resulted in this error. In addition it then proved impossible to shutdown RM unless I reverted to using the Windows Task Manager.

Given that my data had been built up over several years, the implications of this error were most worrying. What to do?

I had only just upgraded from RM 4.0.7.1 to 4.0.8.0, so this was the most obvious aspect to investigate. So I raised a problem report with RM support, and attempted to describe a sequence of actions that they could use to attempt to recreate the problem.

I soon found that by creating a new empty database and making a few basic data additions, I seemed to be able to recreate the problem at will. However RM support were not able to cause the problem when they attempted recreation.

In parallel, as a result of my appends on the RM forums, I became aware of this website and received offers of use of some of the tools referenced here to investigate my data. At that stage, this just showed that my database was indeed corrupted.

Holidays and other activity then intervened, but I was hoping that RM support would come up with something useful. Unfortunately, nothing was forthcoming even though there were other uses who were starting to report the same (or a similar) problem.

Then RM forum activity started to point to this problem only occurring when RM was used by those running Windows on an Apple Mac. To be more precise, those using Parallels for Mac to enable Windows (XP SP3 in my case) to run at the same time as the native Mac OS X operating system. AND when the RM data was stored in a folder that was accessible from both Windows and Mac applications.

At this stage it was time to investigate more closely. Using the quick_check function of SQLiteSpy, I developed a simple and repeatable sequence of RM actions on a new empty RM database and ran quick_check every time the database changed (identified by the Date Modified in Windows Explorer). This showed exactly when a first database integrity error was caused. I assumed that this would lead in due course to an SQLite error 11 (but did not bother to go so far).

I then changed to using folders on the base XP virtual C-drive (not shared with Mac applications), ran the test sequence again, and the problem did not re-occur.

I also re-installed RM 4.0.7.1 and redid the tests, with exactly the same result. The problem again occurred when using shared folders. So the change to RM 4.0.8.0 - or by then 4.0.8.4 - was not the cause.

At this stage I then went back through my Mac data archive (Time Machine) to check exactly when RM maintenance and Parallels maintenance was applied. Then looking at when I had taken RM backups (I always take at least one per RM usage session on exiting RM) and looking at their file sizes and using the SQL in RMGC_TablesRowCouunt.sql to investigate their restores, I concluded that the error occurred the first time I added any additional data to my database after upgrading Parallels in mid-March (even though it was by then early April).

A quick investigation of the Parallels website then showed that there was indeed a problem with shared folder support in the level of Parallels I had upgraded to in mid-March (5.0.9344). Not only that, a fix was available (http://kb.parallels.com/en/8296) and applying it resulted in the problem disappearing.

To conclude, the problem was caused by using Parallels 5 at level 5.0.9344 and only when sharing the data folders used by RM between Windows and Mac applications.



One additional area that I found useful was the following:

RM backups have a date/time in Windows Explorer of when they were created. However, when a backup is restored, and BEFORE any change is made to the data, the date/time displayed in Windows Explorer will be the last time the data was modified, which MAY be earlier, both in time and date, than when the backup was taken. In my case this proved to be very useful as it showed that the data had not been modified between backups taken on different days. (Some days I just invoke RM to answer email queries, such that I won't actually change the data on that day. RM (well Windows) does not update the last modified date/time in such cases.

RWells1938

Database Coruption

RWells1938 20 June 2011 17:40:56

Has anyone used the sqlite vaccume to clean up a RM4 database? Will this fix problems?

If so what do I need to have in order to do this?

Thanks
Roger

ve3meo
ve3meo 20 June 2011 18:08:30

VACUUM could repair corruption of an index, as would REINDEX. Vacuum also would return file space to the OS for other uses if a database has undergone many changes and deletions.

Unfortunately, VACUUM and REINDEX cannot be done without SQLite having access to all collation sequences used by the db indexes. What's missing from all SQLite managers is the RMNOCASE collation sequence, proprietary and secret to RM4. I have successfully done both using SQLite Developer and renaming one of the Unicode collations but I don't know if that may result in some downstream risk.

We need to keep urging Bruce and RootsMagic to incorporate VACUUM and REINDEX as user options. If they would provide a SQLite user interface within the program, those PRAGMA commands should automatically become accessible.

RWells1938

re: Database Coruption

RWells1938 20 June 2011 18:58:44

If I use a gedcom export and then import the gedcom what should I look for as possible data loss?

Roger

ve3meo
ve3meo 20 June 2011 19:51:21

If the database is corrupted, the export may fail; if it does not fail, I cannot predict if there would be any data loss due to corruption. OTOH, a RM4 GEDCOM export/import or a drag'n'drop is not yet perfectly transparent - there remain some subtle and not-so-subtle losses even if the database file is uncorrupted. Cannot say completely, but, as of 4.1.1.4, white space at the end of Notes is lost and there may be some issues yet with custom sentences, roles, ...

Tom

www.000webhost.com