All Scheduled Transactions Disappear

[John Ralls]

> On Dec 26, 2016, at 11:18 AM, aeneas <receiver at gowdygroup.net> wrote:
> 
> I've had some success with the recovery operation.  Using the most recent
> backup of the DB I was able to drop the tables and rename the foo_back
> tables as replacements and open the the resulting DB.  At present I'm unable
> to open the new DB using the Linux version of GnuCash that is installed on
> the same computer as the MySQL server which is actually the MariaDB that is
> supplied with XAMPP.  I haven't had much success troubleshooting this
> problem.  While I do think the user permissions are correctly specified I
> haven't figured out how to verify that fact.  The message displayed by
> GnuCash is pretty general meaning not much help for troubleshooting. 
> However, remote access using the Windows version of GnuCash is working. 
> While this DB is definitely better than what I had when I first noticed a
> problem, I think I have no way of determining whether or not it is complete.
> 
> I'm only using a limited amount of the GnuCash capability.  Most of the
> tables are empty.  My use primarily involves reconciling bank and credit
> accounts.  This makes me think that one test of completeness will be whether
> or not subsequent account reconciliations work correctly.  Does that make
> sense to you?
> 
> I have no knowledge of using GnuCash from the command line.  Have never done
> it.
> 
> I'm probably missing something here but your explanation of table
> replacement causes me to think that absent some kind of failure the DB
> should never be found with the foo_back tables present.  In this case they
> are not only present but they appear to be persistently present even though
> there was no perceptible indication of any system failure.  My best
> suspicion about what may have gone wrong is a poor Internet connection to
> the DB which was remote.  My Internet service is also asymmetric meaning the
> outgoing service needed for writing to the DB is slow even when working
> properly.  I don't think I'd have ever attempted this if I knew the DB would
> be completely rewritten.
> 
> Your remarks about full save trigger some curiosity.  My expectation of a
> benefit associated with using a DB management system (DBMS) is that
> transactions (maybe updates is a better word) would be committed at the time
> each individual revision is done.  The idea being that you don't risk
> loosing a lot of work because you haven't performed a save for a while.  In
> that, there is no cumulative save operation that needs to be performed that
> potentially affects all or most of the data.  My thinking being that this
> should improve integrity and minimize loss that comes from system
> problems/failures.  Just what is a full save?  How is it triggered?  Is that
> what renaming and then rebuilding all of the tables is about?  While I can
> appreciate how this concept might fit with the idea of developing a system
> that works either with or without a DBMS it is also contrary to the benefits
> desired from using a DBMS.  Is that the price we are paying for being able
> to use either standard files or databases?  This does have me reassessing
> the desirability of using a database.
> 

You can check the reconciled balances on your asset accounts against your last statements to ensure that nothing from before that was lost. That will at least ensure that any problems with your next reconciles will be limited to their periods, and tracking down missing transactions is much easier when you don't have to find the error in some random previous period.

That's correct: Absent some kind of failure the database should have the foo_back tables only during a full save.

To understand a full save better, you must first know that GnuCash reads the whole database into memory and does all of its work on the in-memory objects. With a database backend all operations "mark dirty" the objects they touch, and at the end of the operation the touched objects are "committed", where they pass themselves to the database backend to be written out with an INSERT if it's a new object or an UPDATE if not.

But GnuCash for many years had only a file backend and some authors got lazy and didn't use the mark-and-commit logic, instead relying on just telling the top-level object that it needed to be saved and relying on the fact that a file backend has to save everything. This caused a lot of problems when the database backend was first introduced in 2.4 and I carefully audited the code several times; I think that by 2.6.8 or so all of those cases have been tracked down and fixed.

So for the database backend a full save shouldn't be necessary except when doing a File>Save As..., and in that case there won't be any tables to back up. But there's a lot of code and I was worried that there might be execution paths that could call a save operation without going through the file dialog, so I added the simple backup, save, drop routine soon after I took over development of the SQL backend. As for how it gets triggered, so far I know that File>Save (disabled unless the book is "dirty", which it shouldn't ever be), File>Save As.., and the command-line price retrieval code. Since you don't use that there must be another one somewhere. I'll keep looking.

As for whether it's the price we're paying for multiple backends, I don't think so. I think it's the price we're paying for the current design of the SQL backend which follows a little too closely the design of the XML one. That's in part because of a poorly designed backend abstraction. We do intend to change that abstraction and a future version of GnuCash--not 2.8, there isn't enough time left--will use a database the way it's supposed to be used and at that point we'll start preferring the SQL backend to the XML backend. In the meantime the SQL backend is considered somewhat experimental. I use it for my primary accounts, but with SQLite. The files are kept in an encrypted image that I sync on GoogleDrive for offline backup and to allow me to access it from my laptop when I'm traveling. The fact that it's still file-based saves me the timing and connectivity issues inherent in a remote DB server, and allows me to encrypt my data for cloud storage.

Regards,
John Ralls