All Scheduled Transactions Disappear

[John Ralls]

> On Dec 24, 2016, at 6:32 PM, aeneas <receiver at gowdygroup.net> wrote:
> 
> I don't recall anything unusual when opening the databases but I started and
> stopped GnuCash several times after recognizing the problem but didn't do
> anything that would cause a change to the database.  Something I probably
> should mention is that a big reason for choosing to use MySQL is that I can
> use different computers to process the accounts.  This includes a
> combination of laptop and desktops where the desktops include both Windows
> and Linux (Ubuntu) based systems.  I've also experimented with both the
> version of GnuCash obtained from portableapps.com as well as the installed
> version from the GnuCash website.  A definite consequence is that slightly
> different versions of GnuCash have been used to process the same MySQL
> database.  With that said, I have been aware that GnuCash doesn't permit
> simultaneous access to a database and I'm pretty sure that I didn't
> accidentally violate that restriction.
> 
> I've now restored 2 databases to my test system.  The first is the one I
> backed up as soon as I realized that I had a problem.  The other is the one
> which my hosting service restored today which was supposed to have been
> backed up one week prior to experiencing the problem.  There were only about
> a half dozen transactions entered during that time.  I haven't yet opened
> either with GnuCash but have been doing some inspection with phpMyAdmin. 
> Something I have noticed is that the "transactions_back" table in each DB
> have the same number of rows and the most recently entered few rows appear
> to be identical.  The "transactions" table on the most current version of
> the database contains a lot fewer rows than the "transactions_back" table
> but the most recently entered transactions are present.  In that, these are
> the few transactions entered to the older DB version.  This implies that the
> process for creating the new tables inserts the newer data first and the
> older data last.  Does that sound plausible?
> 
> Before trying to undertake your repair suggestion I thought I'd try opening
> these test versions with GnuCash.  Is there any possibility that GnuCash
> will use, or can be made to use, the data from the "foo_back" tables or is
> that a waste of time?  Note:  Now that I can restore either of these
> databases to their current state I can afford to experiment without risking
> further damage.


You'd only have had to "Open Anyway" the first time after GnuCash exited abnormally, and unfortunately the problem seems to have existed before you noticed it: The fact that the restored backup also has the foo_back tables means that you want to look for an earlier backup; that would be the nearest one on or before the latest date-entered in transactions_back (SELECT MAX(enter_date) FROM transactions_back;). 

Full saves with a DB backends shouldn't be a routine occurrence, but it looks (from reading the code) like the command-line add quotes routine (i.e., the code that runs when you do `gnucash --add-price-quotes foo`) does a full save instead of updating. Do you ever do that? There might be a couple more traps like that hidden away, I'll have to search more thoroughly for them.

GnuCash won't look at the foo_back tables, so just do the drop-and-rename thing in  a copy of the dateabase.

GnuCash loads the whole database into memory, converting each table row into its corresponding GnuCash object, at startup. References to objects are stored in hash tables keyed on the string value of their GUID. A full save uses the hash table's "for-each" function to get each object, convert it to a SQL row, and write it to the database. I'd expect that the resulting table would not be in any sort of interesting order. The normal "commit" process will add rows to the table as each is created so they'll be in date-enterred order. Among other things that means that a partially-saved table likely will contain neither the first-entered nor the last-entered transaction from memory, but once you've opened the database and entered new transactions those transactions will be appended to the end of the tables in the order that you entered them.

If GnuCash attempts a full save and is unable to rename the tables to foo_back it will drop all of the foo tables and rename the foo_back to foo. Sadly I wrote that code very naively, because it would be triggered if it found even one foo_back and it appears to me now (with the benefit of several more years experience :-( ) that it would make a serious mess of the database.  That may have been what happened in your case: The first full-save failure didn't do enough (or maybe any) damage for you to notice, but a 
subsequent one encountered the existing foo_back tables and exploded spectacularly.

Regards,
John Ralls