Jump to content


Photo

Corrupt Database, Backups & Recovery Therefrom


  • Please log in to reply
28 replies to this topic

#1 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 26 May 2010 - 08:05 PM

The preamble to this can be found under the topic Corrupt Backup, started by Emilito in the Discussion forum. Emil encountered the 'horror of horrors' error message SQLite Error 11 - database image is malformed, not only with his working database but with every restore of a backup stretching back to January. GEDCOM exports were but 6% complete until the earliest backup was restored but its import was fraught with errors. He was unable to get any help from RootsMagic Support nor was I - I know the details because I have been intensively working on his problem over the past 5 days.

The good news is that, using SQLite et al, I was able to recover something like 99.9% of his 9559 person database (pending his visual inspection that it looks right) to a fresh database. All the persons came across - what I have identified (so far) as lost are 2 or 3 links to image files (out of some 2900) and 8 or so events (out of some 15000). I suspect there could be more but intimate knowledge of the data is required in order to detect it (Emil's eyes needed for that job).

The bad news is:
  • We don't know how or when the database was corrupted.
  • The error message only shows up when a RM4 process causes the SQLite database engine to access an area of the database that is corrupted. Corruption can exist undetected over months of database building and editing.
  • The RM4 backup procedure does not use SQLite; it is merely a file compression utility that faithfully preserves database corruption.
  • Running File > Properties is not a sufficient test for corruption as that procedure does not use all database tables and indexes.
  • Running a full GEDCOM export of everyone in the database is a more relevant test for corruption. If it does not complete, the database is certainly corrupted.
  • Unfortunately, a complete GEDCOM export by itself does not assure that it will be imported without corruption. That can only be determined by importing it to a new database and inspecting and exercising the latter in a variety of ways.
  • Regular GEDCOM export could be an alternative backup procedure that would exercise the database in the most critical areas, were it not so inconvenient and were the import not so susceptible to corruption.
  • RootsMagic Support appears not to have one or more of tools, skills and inclination to attempt database recovery.
SQLite itself has a Database Integrity check routine. I do not know how thorough it is. SQLite also can batch reindex - that would seem to be a pretty thorough access of all table records with consequent rebuilding of the indexes. Then there is SQLite Vacuum, which compacts a database. It would be my wish that these functions be brought out to the RootsMagic 4 user interface, perhaps under the Files menu, as they should provide early indication of database corruption, provided they are used, and maybe some corrective capability.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#2 mapleleaf

mapleleaf

    Advanced Member

  • Members
  • PipPipPip
  • 558 posts

Posted 27 May 2010 - 02:21 AM

This also "fills me with terror"! A lot of info gets entered into my database every day. To have a corruption existing undetected over months of database building and editing is a horrible thought! All the backups I've made are only faithfully preserving database corruption?! Eee gads!

How do you even know what parts of the database are corrupt? It sounds like we should be daily running a full GEDCOM export of everyone in the database. I've never done this and have always done daily backups - they're so quick and easy to do and takes only a few seconds.

I've just run a full GEDCOM which took over 3 minutes. Comparing both Properties screens, the gedcom has way less information. People and Families count are the same; but I noted these differences:

Database: ------ Gedcom:
Events 79,114 ------ 57,178
Places 3538 ------ 3322
Citations 99,549 ------ 71,363
Multimedia items 671 ------ 658
MM links 667 ------ 658
Addresses 652 ------ 634

Is this worrisome? :o

Regarding Step 6, "inspecting and exercising the latter in a variety of ways"- where does one begin inspecting?

I would hope that the RootsMagic Support people get on this right away. This problem is way more important than any other wish list item or problem I've seen.

An early indication of database corruption is a must!
~ Debbie

#3 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 27 May 2010 - 06:23 AM

How do you even know what parts of the database are corrupt?

At my level of expertise, only indirectly from the way RootsMagic behaves and from a variety of SQLite queries. Maybe there are SQLite super-sleuths who can dig down deeper.

It sounds like we should be daily running a full GEDCOM export of everyone in the database.

Periodically, yes, along with an import to a new database and inspection to complete the test. It's a question of how high an insurance premium you want to pay for what level of risk. SQLite is acknowledged to be a pretty bullet-proof database engine, probably orders-of-magnitude more robust than Xbase which was used in earlier RM and Family Origins.

I've just run a full GEDCOM which took over 3 minutes. Comparing both Properties screens, the gedcom has way less information. People and Families count are the same; but I noted these differences:

Database: ------ Gedcom:
Events 79,114 ------ 57,178
Places 3538 ------ 3322
Citations 99,549 ------ 71,363
Multimedia items 671 ------ 658
MM links 667 ------ 658
Addresses 652 ------ 634

Is this worrisome? :o

Maybe not. If you have been doing lots of merging, I don't think RM4 cleans up after itself very well.

Regarding Step 6, "inspecting and exercising the latter in a variety of ways"- where does one begin inspecting?

Not sure. Large scale list and narrative reports, scroll through the whole People view, sidebar Index to look for anomalies, or triggering of error messages.

I would hope that the RootsMagic Support people get on this right away. This problem is way more important than any other wish list item or problem I've seen.

An early indication of database corruption is a must!

Check!

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#4 kbens0n

kbens0n

    Advanced Member

  • Members
  • PipPipPip
  • 3459 posts

Posted 27 May 2010 - 06:35 AM

So what was damaged in his database? Out-of-order records, missing pages, malformed records, corrupt indices or what?

---
--- "GENEALOGY, n. An account of one's descent from an ancestor who did not particularly care to trace his own." - Ambrose Bierce
--- "The trouble ain't what people don't know, it's what they know that ain't so." - Josh Billings
---Ô¿Ô---
K e V i N


#5 bienia

bienia

    Advanced Member

  • Members
  • PipPipPip
  • 784 posts

Posted 27 May 2010 - 06:54 AM

Periodically, yes, along with an import to a new database and inspection to complete the test. It's a question of how high an insurance premium you want to pay for what level of risk. SQLite is acknowledged to be a pretty bullet-proof database engine, probably orders-of-magnitude more robust than Xbase which was used in earlier RM and Family Origins.

CAUTION: There is still a major problem with GEDCOM Export IF you have user-defined roles. The GEDCOM Export does not include any of the roles or role sentence templates associated with any fact types (the global definitions created through Lists, Fact Types...) or any customized role sentences (although it does include shared role names assigned to an individual.)

We are caught between a roack and a hard place!
-----------
Bill Bienia

RootsMagic Tips sheets: www.CobblestoneLegacies.com/resources.htm

#6 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 27 May 2010 - 07:52 AM

So what was damaged in his database? Out-of-order records, missing pages, malformed records, corrupt indices or what?

An SQLite database integrity check reported:
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
That did not mean a whole lot to me and I have no means of inspecting pages and cells. I did find that the MultimediaTable had a duplicate of row 2285 where original row 800 should have been (a Primary key violation) and that the EventTable was broken near the 10% mark with an unbridgeable gap of 8 records. Those two findings may account for only 4 lines from the integrity check; the balance may never be accounted for - some of it perhaps in the indices.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#7 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 27 May 2010 - 10:01 PM

I have added a page to the SQLiteToolsForRootsMagic4 Wiki on the Why and How of Checking RootsMagic Database Integrity.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#8 mapleleaf

mapleleaf

    Advanced Member

  • Members
  • PipPipPip
  • 558 posts

Posted 28 May 2010 - 02:54 AM

I have added a page to the SQLiteToolsForRootsMagic4 Wiki on the Why and How of Checking RootsMagic Database Integrity.


Sadly, I don't understand how to use the SQLiteTools (the SQLite manual is beyond my comprehension), and I'll have to wait for the day when a built-in mechanism is added to check the integrity of a RootsMagic database upon startup. Maybe Bruce could pop in here and let us know if that's in the plans.

Would the RM error message "SQLite Error 11 - Database image is malformed" be considered a bug that should be reported by submitting a ticket? I hope RootsMagic is following this and the original thread about Emil Signes' plight. Was his database fixed so he can use it again?

If the error were caught in time, then restoring a backup would be the solution, wouldn't it?
~ Debbie

#9 kbens0n

kbens0n

    Advanced Member

  • Members
  • PipPipPip
  • 3459 posts

Posted 28 May 2010 - 04:02 AM

Would the RM error message "SQLite Error 11 - Database image is malformed" be considered a bug that should be reported by submitting a ticket?

Nope,it's just an informative error message. By the way, this corruption problem is not a problem originating in RootsMagic. Most likely Emilito experienced a lockup of the program's process, his operating system or a loss of power and things crashed in the midst of program operations, thusly preventing a database write to complete.

---
--- "GENEALOGY, n. An account of one's descent from an ancestor who did not particularly care to trace his own." - Ambrose Bierce
--- "The trouble ain't what people don't know, it's what they know that ain't so." - Josh Billings
---Ô¿Ô---
K e V i N


#10 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 28 May 2010 - 06:26 AM

If the error were caught in time, then restoring a backup would be the solution, wouldn't it?

I think so. If RootsMagic ran the integrity check every time a database was opened, or closed, or when a backup was made, then the last backup (or the penultimate one) should be a database that passed the check. On restoring from a backup, the integrity check would confirm the database as OK.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#11 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 28 May 2010 - 06:35 AM

By rhe way, this corruption problem is not a problem originating in RootsMagic. Most likely Emilito experienced a lockup of the program's process, his operating system or a loss of power and things crashed in the midst of program operations, thusly preventing a database write to complete.

I agree, with the exception that even a new, 'empty' RootsMagic database fails the full integrity_check with 13 missing RowIDs from an index on SourceTemplate Names. That does not give rise to Error 11 and may have no consequences but leaves me feeling a tiny bit uneasy. I think I reported this to Support months ago.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#12 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 28 May 2010 - 01:40 PM

Sadly, I don't understand how to use the SQLiteTools (the SQLite manual is beyond my comprehension), and I'll have to wait for the day when a built-in mechanism is added to check the integrity of a RootsMagic database upon startup. Maybe Bruce could pop in here and let us know if that's in the plans.

There's a new page How to Query RM4 using the integrity check as an example that I'm sure will give you the confidence to proceed with checking your own database (make a backup first) until the built-in checker is released.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#13 mapleleaf

mapleleaf

    Advanced Member

  • Members
  • PipPipPip
  • 558 posts

Posted 28 May 2010 - 02:02 PM

There's a new page How to Query RM4 using the integrity check as an example that I'm sure will give you the confidence to proceed with checking your own database (make a backup first) until the built-in checker is released.


Wow, thanks! Those were easy directions and I did it! Woo hoo! My results were "ok"! :P

The only thing I didn't figure out was where to find this: "You may also want to be extra safe by checking the box Open as read-only."
~ Debbie

#14 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 28 May 2010 - 05:30 PM

Congratulations, Debbie! It wasn't hard at all, was it. Sorry I took so long writing a How-to. Maybe you will find some of the other queries to be useful to you.
The Open as rread-only checkbox is at the bottom of the File > Open Database dialog window.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#15 mapleleaf

mapleleaf

    Advanced Member

  • Members
  • PipPipPip
  • 558 posts

Posted 29 May 2010 - 12:11 AM

Maybe you will find some of the other queries to be useful to you.

Tom, I did want to try the other queries, so I went here. Then I got stuck on what to do next. I assume I should click RM4_Queries.sql, then type something into the box of SQLiteSpy, but what? For example, I typed this:
CREATE VIEW PlacesDetails AS;
then clicked Execute > Execute SQL (or F9) and a box said: SQLite Error 1 - near ";":syntax error.

I also tried copying everything at this page RM4_Queries.sql into Notepad, saving it with an .sql extension. Then I did File > Open SQL and opened the file I just made. I clicked into SQL command line, did F9 and still got the syntax error message.

What could I be doing wrong?

The Open as read-only checkbox is at the bottom of the File > Open Database dialog window.

I'm still not seeing "read-only" in the Open window. At the bottom is File name, the button for SQLite3 Database (*.db3;*.d and Any File (*.*), Open button, Cancel button. On the Open button is a View previous versions choice.


Now the subject is changed from the original corrupt database problem. Maybe I should have started a new topic. . .
~ Debbie

#16 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 29 May 2010 - 07:01 AM

Now the subject is changed from the original corrupt database problem. Maybe I should have started a new topic. . .

We can continue this on the Wiki if you subscribe/join or privately; I prefer the former as the discussion can be viewed and joined by others. Until the RootsMagic Forum administrators see fit to create a SQLite sub-forum (as they did for nFS), we try to avoid irritating those with no interest in SQLite by keeping detailed discussion of queries off the forum.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#17 mapleleaf

mapleleaf

    Advanced Member

  • Members
  • PipPipPip
  • 558 posts

Posted 29 May 2010 - 08:26 AM

We can continue this on the Wiki if you subscribe/join or privately; I prefer the former as the discussion can be viewed and joined by others. Until the RootsMagic Forum administrators see fit to create a SQLite sub-forum (as they did for nFS), we try to avoid irritating those with no interest in SQLite by keeping detailed discussion of queries off the forum.


lol, I wouldn't want to irritate anyone ;) See you over there. . .
~ Debbie

#18 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6254 posts

Posted 29 May 2010 - 08:38 PM

The SQLite Tools For RootsMagic 4 wikispace now has a few pages on database integrity:

  • Check RootsMagic Database Integrity on Opening: this setup checks and reports the integrity then opens the database in RootsMagic. Use it every time you want to work on your database, for assurance.
  • Check RootsMagic Database Integrity: describes Why we should and How we can, using a command integrated in the SQLite library, even the one used in RootsMagic (if only it were user-accessible ;) ).
  • How to Query RM4: Beginners' guide to querying a RootsMagic database using a GUI SQLite manager - SQLiteSpy; the example shows how to run the quick integrity check.
  • RMGC Tables Row Count: This query could be useful in identifying which tables are corrupted after a failed integrity check, especially if a reference snapshot has been taken immediately after the last pass (hmm, return to 1 and revise...).

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#19 J P

J P

    Advanced Member

  • Members
  • PipPipPip
  • 302 posts

Posted 01 June 2010 - 10:09 AM

I have just discovered that there are problems running RM4 under Parallels on the Mac AND sharing the folders containing the RM data between the Windows and Mac sides.

These problems seem to have been introduced with the current level of Parallels 5.0.9344 which was introduced in mid-March. However there is a downloadable fix - see http://kb.parallels.com/en/8296.

In this case it does not explain why all the backups going even further back were discovered to be corrupt but in my case this fix seems to be a very good fit. See http://forums.rootsm...e-is-malformed/ for more information.

#20 kbens0n

kbens0n

    Advanced Member

  • Members
  • PipPipPip
  • 3459 posts

Posted 09 June 2010 - 05:02 PM

These problems seem to have been introduced with the current level of Parallels 5.0.9344 which was introduced in mid-March. However there is a downloadable fix - see http://kb.parallels.com/en/8296.

Above link is failing because the trailing period is being included in the URL. Here's a working link:

http://kb.parrallels.com/en/8296

---
--- "GENEALOGY, n. An account of one's descent from an ancestor who did not particularly care to trace his own." - Ambrose Bierce
--- "The trouble ain't what people don't know, it's what they know that ain't so." - Josh Billings
---Ô¿Ô---
K e V i N