Speculation: Users' Lost Sessions from the SQLite Perspective

#1 · 2024-11-30, 11:43 pm

Quote from thejerrybryan on 2024-11-30, 11:43 pm
As you all are surely aware, there have been persistent reports from RM10 users that they sometimes lose entire sessions. The new data was there when they shut RM down and and wasn't there when they restarted RM.
Bruce posted a couple of days ago on RM's Facebook forum that this can happen if during your session you run a Relationship Chart and that a fix will be forthcoming. This got me to thinking what kind of bug in the Relationship Chart code could possibly cause this kind of problem. So the speculation begins.
The only thing I can think of is that if you issue a BEGIN TRANSACTION and never close the transaction with COMMIT TRANSACTION before shutting down your app, then I think the transaction is lost. Is that correct? If so, what if somewhere in RM a BEGIN TRANSACTION is issued and there is a bug that the corresponding COMMIT TRANACTION is never issued? Would that do it? Here's where my lack of knowledge of SQLite lets me down. As far as I know, SQLite doesn't supported nested transactions. So if you do a BEGIN, forget to do the COMMIT, then do a BEGIN and COMMIT pair correctly, does the COMMIT take care of both BEGINs? I don't know. That's as far as my speculations go in that direction. But I'm thinking that an open BEGIN is treating everything the user does for hours as a single transaction for which there is never a closing COMMIT.
As far as the Relationship Calculator code bug goes, the only thing I can think of is that RM is using a temporary table in the the database to assist with the relationship calculations. If so, perhaps there is a BEGIN TRANSACTION for the temporary table and there fails to be an END TRANACTION for the temporary table. But a BEGIN TRANSACTION is not really "for the temporary" table. It's for any subsequent RM commands as well until there is a COMMIT, or a ROLLBACK of course. So my speculation is that that's the nature of the bug.
I very much welcome any contrary speculation that is wiser and more informed than my own.

As you all are surely aware, there have been persistent reports from RM10 users that they sometimes lose entire sessions. The new data was there when they shut RM down and and wasn't there when they restarted RM.

Bruce posted a couple of days ago on RM's Facebook forum that this can happen if during your session you run a Relationship Chart and that a fix will be forthcoming. This got me to thinking what kind of bug in the Relationship Chart code could possibly cause this kind of problem. So the speculation begins.

The only thing I can think of is that if you issue a BEGIN TRANSACTION and never close the transaction with COMMIT TRANSACTION before shutting down your app, then I think the transaction is lost. Is that correct? If so, what if somewhere in RM a BEGIN TRANSACTION is issued and there is a bug that the corresponding COMMIT TRANACTION is never issued? Would that do it? Here's where my lack of knowledge of SQLite lets me down. As far as I know, SQLite doesn't supported nested transactions. So if you do a BEGIN, forget to do the COMMIT, then do a BEGIN and COMMIT pair correctly, does the COMMIT take care of both BEGINs? I don't know. That's as far as my speculations go in that direction. But I'm thinking that an open BEGIN is treating everything the user does for hours as a single transaction for which there is never a closing COMMIT.

As far as the Relationship Calculator code bug goes, the only thing I can think of is that RM is using a temporary table in the the database to assist with the relationship calculations. If so, perhaps there is a BEGIN TRANSACTION for the temporary table and there fails to be an END TRANACTION for the temporary table. But a BEGIN TRANSACTION is not really "for the temporary" table. It's for any subsequent RM commands as well until there is a COMMIT, or a ROLLBACK of course. So my speculation is that that's the nature of the bug.

I very much welcome any contrary speculation that is wiser and more informed than my own.

Tom Holden has reacted to this post.

#2 · 2024-12-01, 7:04 am

I've not yet seen Bruce's post but based on what you've postulated I'll add this.

SQLite does not allow a second transaction to begin while one is open. No nesting.
A transaction lock is database wide, not just on a subset of tables.
I don't know how the program could allow ongoing transactions on the database if one is still in progress.
Could the Relationship Chart be creating a temporary (in-memory) copy of the database that the variable for the database name is changed to and is not changed back to the original on exiting that window? That could allow all subsequent transactions to be done on the temp database, outside of the original database that won't be accessed again until RM closes and reopens the file. The temporary database is lost on closing the session.

#3 · 2024-12-01, 9:02 am

Found where Bruce replied with reference to a now-known bug caused by Relationship Chart and warning to not use it until the next update fixes it.

https://www.facebook.com/groups/RootsMagicUsers/posts/2688490341338495/

#4 · 2024-12-01, 9:13 am

Quote from thejerrybryan on 2024-12-01, 9:13 am
Copied and pasted from Facebook (it's hard to find):
Paul Stainthorp
I had a very strange experience a couple of days ago (26 November) in which I lost a lot of changes from my RM10 database.
I had made a whole raft of changes over the course of the day, to a number of newspaper sources, created at least three brand-new sources, added sources to individuals' facts, changed colour coding, etc. etc. Probably around 25-30 individual changes in total.
These changes appeared in my database as normal. I then backed up the database (manually) and closed it down.
Then, when I re-opened the database (definitely the same file) the next day (27 Nov.), all of the changes that I'd made were not there.
I tried recreating the file from the backup I'd made, with the same result - all of the changes I'd made the preceding day before backing up, were missing.
This is the first and only time this has happened to me while using RootsMagic. I only upgraded to RM10 from RM9 very recently. I'm not sure that there's anything I would or could have done differently to avoid this from happening. It is still a mystery to me.
Luckily I spotted it the next day and can remember all of (hopefully all of) the individual changes I'd made that day, so I've set myself a number of tasks to re-do the changes one by one.
Very strange. I'm not sure there's any way for me to work out what happened to cause this, but interested to hear if anyone has any thoughts.
Bruce Buzbee
Admin
Top contributor
Paul, did you happen to run / create a relationship chart at some point during your data entry?
Reply
Paul Stainthorp
Author
Bruce Buzbee Hello. Thank you for replying. Yes, I did - I published a number of reports and charts during that session including several relationship charts.
Reply
Bruce Buzbee
Admin
Top contributor
We have discovered an issue with creating a relationship chart (either standalone or in the Publisher) that can lead to something like this. We will be releasing an update with a fix, but it is best for now to not generate the relationship chart.

Copied and pasted from Facebook (it's hard to find):

Paul Stainthorp
I had a very strange experience a couple of days ago (26 November) in which I lost a lot of changes from my RM10 database.
I had made a whole raft of changes over the course of the day, to a number of newspaper sources, created at least three brand-new sources, added sources to individuals' facts, changed colour coding, etc. etc. Probably around 25-30 individual changes in total.
These changes appeared in my database as normal. I then backed up the database (manually) and closed it down.
Then, when I re-opened the database (definitely the same file) the next day (27 Nov.), all of the changes that I'd made were not there.
I tried recreating the file from the backup I'd made, with the same result - all of the changes I'd made the preceding day before backing up, were missing.
This is the first and only time this has happened to me while using RootsMagic. I only upgraded to RM10 from RM9 very recently. I'm not sure that there's anything I would or could have done differently to avoid this from happening. It is still a mystery to me.
Luckily I spotted it the next day and can remember all of (hopefully all of) the individual changes I'd made that day, so I've set myself a number of tasks to re-do the changes one by one.
Very strange. I'm not sure there's any way for me to work out what happened to cause this, but interested to hear if anyone has any thoughts.

Bruce Buzbee
Admin
Top contributor
Paul, did you happen to run / create a relationship chart at some point during your data entry?

Reply
Paul Stainthorp
Author
Bruce Buzbee Hello. Thank you for replying. Yes, I did - I published a number of reports and charts during that session including several relationship charts.

Reply
Bruce Buzbee
Admin
Top contributor
We have discovered an issue with creating a relationship chart (either standalone or in the Publisher) that can lead to something like this. We will be releasing an update with a fix, but it is best for now to not generate the relationship chart.

Tom Holden has reacted to this post.

#5 · 2024-12-01, 9:37 am

Quote from thejerrybryan on 2024-12-01, 9:37 am
Quote from Tom Holden on 2024-12-01, 7:04 am
I've not yet seen Bruce's post but based on what you've postulated I'll add this.
SQLite does not allow a second transaction to begin while one is open. No nesting.
A transaction lock is database wide, not just on a subset of tables.
I don't know how the program could allow ongoing transactions on the database if one is still in progress.
Could the Relationship Chart be creating a temporary (in-memory) copy of the database that the variable for the database name is changed to and is not changed back to the original on exiting that window? That could allow all subsequent transactions to be done on the temp database, outside of the original database that won't be accessed again until RM closes and reopens the file. The temporary database is lost on closing the session.
For the reasons cited in your bullet points #1, #2, and #3, I suspect that your bullet point #4 might be the close to the truth.
On your bullet point #2, I'm aware of the globalness of the lock. In that regard, let me describe as symptom that I have encountered in the last few days.
I have been doing some cleanup of data in SourceTable.ActualText. I have been running an SQLite query to find rows in SourceTable where the cleanup is required. The cleanup is short and simple in concept, but it's variable enough that it's hard to write an SQLite script to do the cleanup. By contrast, the SQLite script to find the rows that need the cleanup is trivial. So I have been doing the cleanup manually in RM itself after finding the rows with SQLite.
I copy the Name field out of my SQLite query and paste it into the search box in the main RM Sources tab to find each source that needs the cleanup. So it's back and forth between RM and SQLiteSpy, easy-peasy, and mind numbingly boring. No big deal.
Because I'm a source splitter, I have exactly one citation in CitationTable for each source in SourceTable. And because I'm a source splitter, I like to replicate SourceTable.ActualText into CitationTable.ActualText. So for years, I have had a very simple SQLite script which copies SourceTable.ActualText into CitationTable.ActualText for any rows where they don't match. It saves me a good bit of data entry time. Now that I'm on RM10, I sometimes discover that my SQLite script will not run because the RM10 database is locked. This will be immediately after I have cleaned up a few rows worth of SourceTable.ActualText as described above. But otherwise, my RM10 database is completely idle.
Why would RM10 be holding a global lock unless it still had a transaction in progress that wasn't yet COMMITed? When this happens, I shut down RM10, run my transaction to copy SourceTable.ActualText into CitationTable.ActualText, and then bring RM10 back up. It works, but it's very disconcerting that RM10 should be holding a lock when it is idle. It's like there is still a transaction in progress. But I have never encountered any data loss in RM10 as other uses have described. The cleanup I'm doing for SourceTable.ActualText in the RM UI is not getting lost and I can see it immediately from an SQLite query.

Quote from Tom Holden on 2024-12-01, 7:04 am
I've not yet seen Bruce's post but based on what you've postulated I'll add this.
SQLite does not allow a second transaction to begin while one is open. No nesting.
A transaction lock is database wide, not just on a subset of tables.
I don't know how the program could allow ongoing transactions on the database if one is still in progress.
Could the Relationship Chart be creating a temporary (in-memory) copy of the database that the variable for the database name is changed to and is not changed back to the original on exiting that window? That could allow all subsequent transactions to be done on the temp database, outside of the original database that won't be accessed again until RM closes and reopens the file. The temporary database is lost on closing the session.

For the reasons cited in your bullet points #1, #2, and #3, I suspect that your bullet point #4 might be the close to the truth.

On your bullet point #2, I'm aware of the globalness of the lock. In that regard, let me describe as symptom that I have encountered in the last few days.

I have been doing some cleanup of data in SourceTable.ActualText. I have been running an SQLite query to find rows in SourceTable where the cleanup is required. The cleanup is short and simple in concept, but it's variable enough that it's hard to write an SQLite script to do the cleanup. By contrast, the SQLite script to find the rows that need the cleanup is trivial. So I have been doing the cleanup manually in RM itself after finding the rows with SQLite.

I copy the Name field out of my SQLite query and paste it into the search box in the main RM Sources tab to find each source that needs the cleanup. So it's back and forth between RM and SQLiteSpy, easy-peasy, and mind numbingly boring. No big deal.

Because I'm a source splitter, I have exactly one citation in CitationTable for each source in SourceTable. And because I'm a source splitter, I like to replicate SourceTable.ActualText into CitationTable.ActualText. So for years, I have had a very simple SQLite script which copies SourceTable.ActualText into CitationTable.ActualText for any rows where they don't match. It saves me a good bit of data entry time. Now that I'm on RM10, I sometimes discover that my SQLite script will not run because the RM10 database is locked. This will be immediately after I have cleaned up a few rows worth of SourceTable.ActualText as described above. But otherwise, my RM10 database is completely idle.

Why would RM10 be holding a global lock unless it still had a transaction in progress that wasn't yet COMMITed? When this happens, I shut down RM10, run my transaction to copy SourceTable.ActualText into CitationTable.ActualText, and then bring RM10 back up. It works, but it's very disconcerting that RM10 should be holding a lock when it is idle. It's like there is still a transaction in progress. But I have never encountered any data loss in RM10 as other uses have described. The cleanup I'm doing for SourceTable.ActualText in the RM UI is not getting lost and I can see it immediately from an SQLite query.

#6 · 2024-12-01, 12:32 pm

I have also wondered about the bug report.
Unfortunately, we really haven't seen a before and after database where we could look not just at the PersonTable but all of the others that my or may not have been rolled back.

Regarding the database locked message- that's a normal part of using SQLite, right? It just means that you have to try your command again after a suitable delay, and repeat. It seems RM tries once, display the message and then locks up.
I haven't tested this, but I would think that the web hints functionality may create transactions in the background. What do you all think about that?

And on another possible cause, I wonder what happens if a cloud storage/backup/sync system locks the journal file, or restores it after RM/sqlite deletes it?

#7 · 2024-12-01, 1:05 pm

I ran this little test and it demonstrates that after doing a Relationship Chart report, RM has left the database in an open transaction with a rollback journal accumulating all the subsequent changes to data. It would seem that the main program does not use an explicit BEGIN TRANSACTION on the database but the Relationship Chart report does and fails to end it which would delete the journal. Thus all subsequent Updates and Inserts in the main program are operating under the BEGIN, data is written to the database but the operations are recorded in the journal because the transaction is still open. There is apparently no COMMIT on closing the database so the journal is not deleted. On the next opening of the database, the SQLite engine detects the undeleted journal which tells it that the transaction failed to complete, winds through the journal to undo the changes in the database to where it was before the Chart was run, and deletes the journal file.

Richard Otter has reacted to this post.

#8 · 2024-12-01, 1:30 pm

Quote from Tom Holden on 2024-12-01, 1:05 pm
There is apparently no COMMIT on closing the database so the journal is not deleted. On the next opening of the database, the SQLite engine detects the undeleted journal which tells it that the transaction failed to complete, winds through the journal to undo the changes in the database to where it was before the Chart was run, and deletes the journal file.

Well I'm a bit confused. On repeating, the journal file was deleted when the database was closed but the changes were indeed gone. I tried deleting the journal file while the database was open but Windows wouldn't allow it as it was in use. I made a copy of the database before closing it and looked for the changes to have been written but they weren't so where were they and would the ops have been journaled? Maybe the database remains in memory until the COMMIT is executed. Because there is no COMMIT and no failure of the COMMIT to complete, nothing is written to the disk database and there is no error to cause the journal to be preserved?

#9 · 2024-12-01, 2:39 pm

I guess the not-closed transaction bug would also confirm that RM generally doesn't start explicit transactions in the course of inserting data.
If it did, the attempt would fail due to the open transaction done by the Relationship Chart report. Agree?

#10 · 2024-12-01, 3:23 pm

Quote from Richard Otter on 2024-12-01, 2:39 pm
... RM generally doesn't start explicit transactions in the course of inserting data.
If it did, the attempt would fail due to the open transaction done by the Relationship Chart report. Agree?

Agreed.

And on your earlier speculation about WebHints, they have no interaction with the database. Nothing about them is stored. There have been wishes for enhancements that would require storage but no fulfillment.