Database corruption

#1 · 2025-01-22, 5:51 pm

Quote from Richard Otter on 2025-01-22, 5:51 pm
I'd like to make a comment concerning "database corruption".
If one runs the "Check Integrity" database tool from RM or the pragma from the command line, and no issues are found, to the best of my knowledge, the database is sound and all of its data can be retrieved without error using SQL.
However, that doesn't necessarily mean that the data makes sense to RM.
Take a commonly submitted tech support issue, two names marked primary.
RM expects one of the names to be primary, but due to some RM bug, sometimes, more than one name will be marked primary. In this case, it's no big deal, RM just shows both names with the primary check box ticked. It could have been different. RM code could have been written to inspect the flags and then seeing more than one primary, it might crash (abnormal end, data access error etc.)
Another common issue is a Person or Name record with a very large primary key. This causes odd behavior, but the app doesn't crash.
Is it common to discuss 2 type of corruption- database vs data?
Tom's example of reindexing or updating an RMNOCASE indexed data outside of RM and then looking at the data in RM without again reindexing, is an example of database corruption. Check Integrity will show problems. As far as I know, this particular problem will always be repaired by reindexing.
Other problems might happen due to defective storage media, messing with the journal file or a bug in SQLIte. I don't personally know of other real life causes of database corruption.
The other examples of more than one name marked primary and the large primary key are cases of data corruption. The database is fine, but there are logical problems, at least as far as how RM reads the data. This class of problem could be fixed by RM itself (e.g. remove phantom records) or more likely, it requires manual repair with SQL update statements to fix the non-logical data and relationships.
I hope this makes sense. Please point out any errors.
Richard

I'd like to make a comment concerning "database corruption".

If one runs the "Check Integrity" database tool from RM or the pragma from the command line, and no issues are found, to the best of my knowledge, the database is sound and all of its data can be retrieved without error using SQL.
However, that doesn't necessarily mean that the data makes sense to RM.

Take a commonly submitted tech support issue, two names marked primary.
RM expects one of the names to be primary, but due to some RM bug, sometimes, more than one name will be marked primary. In this case, it's no big deal, RM just shows both names with the primary check box ticked. It could have been different. RM code could have been written to inspect the flags and then seeing more than one primary, it might crash (abnormal end, data access error etc.)
Another common issue is a Person or Name record with a very large primary key. This causes odd behavior, but the app doesn't crash.

Is it common to discuss 2 type of corruption- database vs data?

Tom's example of reindexing or updating an RMNOCASE indexed data outside of RM and then looking at the data in RM without again reindexing, is an example of database corruption. Check Integrity will show problems. As far as I know, this particular problem will always be repaired by reindexing.
Other problems might happen due to defective storage media, messing with the journal file or a bug in SQLIte. I don't personally know of other real life causes of database corruption.

The other examples of more than one name marked primary and the large primary key are cases of data corruption. The database is fine, but there are logical problems, at least as far as how RM reads the data. This class of problem could be fixed by RM itself (e.g. remove phantom records) or more likely, it requires manual repair with SQL update statements to fix the non-logical data and relationships.

I hope this makes sense. Please point out any errors.

Richard

Tom Holden has reacted to this post.