Where does Gnucash save it backup files when using mysql data source

[AC]

On 2015-07-05 13:42, L. D. James wrote:

> When I was using Peachtree/Pervasive, the option was given to install on
> a server or client.  I chose the server option.  Then I used clients to
> login to and use the data on the server.  I stopped using Peachtree
> because I didn't want to retain the Windows server as a main part of my
> network.  If I could have easily afforded the $1500.00 to install it on
> my Linux server, I would have.  I couldn't justify the expense.  So I
> ran Windows on a virtual machine for Peachtree (for sever years) and in
> the recent months stop using it altogether and have switched to Gnucash.
> 
> I'm saying that to say, I was running it on a Windows server.  That was
> one of the install options that I chose.


You're not really understanding how a database like MySQL works in the
background and that's where we're having difficulty communicating.  It
has nothing to do with how large the database is on disk, it has to do
with how the storage is utilized, protected, and synchronized (I also,
in a previous email, did not specify that my large 400GB backup was a
database, it was just a plain system backup of normal work files...not
the same thing, it was merely a description of how image versus
differential backups work).

The Pervasive database service, even as a server, uses flat files for
its storage system.  This is not how MySQL stores data.  Therefore, the
file locking mechanisms in place in an operating system work fine for
Pervasive databases but do not work at all for a MySQL database.  It
works for Peachtree/Pervasive because they used the way Pervasive
behaves on a low level to achieve the goals of performing backups,
saves, etc.  This isn't the same with Gnucash using something like MySQL
because MySQL does not behave the same way and the behavior can not be
controlled by MySQL unlike the tight integration between Pervasive and
Peachtree.


> The backup feature already exist with the Mysql the way it is.  I use it
> all the time with Gnucash, at this time.  It works flawlessly.  It takes
> a fraction of a second to backup the software.  I have the option of
> backing it up to XML or Sqlite.  In my environment the I can't back it
> up (save as) to Mysql because I have chosen not to give the userID that
> access.

Yes, I do understand it exists as is.  However, what I am saying is the
feature you are enjoying right now ("Save As" on a MySQL backed
database) works in this application because it is only one user at a
time.  This will not work properly, and will be potentially dangerous,
in a multiuser environment.  It doesn't matter that you haven't given
permissions to the user, the process is still the same, the originating
database must be locked prior to this activity.  For a single user, that
isn't a problem, for a multiuser environment it is a big problem.

> Again, the data is stored on a server, on a different machine.  Any of
> the machines in my shop, as well as any of the offsite locations can
> access the accounting system and manually click "Save as" and have the
> complete data on their  local machine.

The location doesn't matter, it's the engine that matters.  MySQL could
be installed locally on the same machine as Gnucash, on a network server
in the closet, or on a cloud server halfway across the world.  The
behavior is the same.

> I don't understand why you're so animate on stopping the "save as" just
> because the data might grow large.  I can't imagine it growing so large
> that it would ever take more than a few seconds to "save as" two years
> of accounting data.  You can save a couple of thousand pages of
> Microsoft pages in a couple of seconds.

Again, this has nothing to do with data size, it's data access,
concurrency, and integrity.  Please ignore any mentions of file size,
they're irrelevant.  For the purposes of my statements, the "save as"
feature is inherently dangerous to a multiuser environment which Gnucash
currently does not support.  Please also note that "multiuser" is
specifically referring to multiple concurrent users.  It is true that,
under the current implementation, multiple unique installations of
Gnucash on different computers could read data from a central MySQL
server.  However, those copies can not be running simultaneously or
there will be data corruption.  It is still a single user program which
happens to support remote data storage using a database engine.

> So again, it's working now.  What I have asked for is already
> implemented.  It is just lacking a feature to specify the directory and
> now and actually add numbers for incrementing to the user configured
> filename.
> 
> At present there multiuser feature isn't available in the mysql. But
> again, the saving (backing up) of the actual data is there and works
> flawlessly.
> 
> You're saying it won't work when multiuser is implemented because the
> data might change.  However, there are api's for SQL to lock the files
> so that the data doesn't change.  This is done all the time with file
> locking when working with any files.  I can't see why it would be so
> complicated for the database to be unavailable for two seconds while the
> "save as" functionality that already works, is happening.


The API to "lock a file" doesn't exist (because there are no files to
lock, file locking is an operating system feature).  There are methods
to lock records, tables, and whole databases but they are different for
each database engine.  I mentioned these already previously
(transactions, mutexes, semaphores, etc.)  If you have ever worked with
locking on databases you would realize that locks are not instantaneous.

Here is an example: If a copy of Gnucash (let's call this GC_A) is
issuing the Save As, this will require a total database lock to ensure
the data is static.  A database lock will first ask any other threads if
the lock can be placed.  If another instance of Gnucash (we'll call this
one GC_B) has a record open, the database lock will fail.  So then
GC_A's choices are to wait until the record lock by GC_B is released,
abort and close without performing the save, or abort and inform the
user that it has failed and then wait for the user to decide what to do
next.

The first two choices will result in software that looks like it had an
unexpected failure (the wait looks like a hang and the abort and close
looks like a crash).  A user may be very confused about what's happening
and this results in a lot of support requests.  So we'll take option
three and pop up a dialog box that says the lock failed, would you like
to try again or give up.  If the user chooses to try again, there is
still no guarantee that the lock will succeed.  The process repeats.
Eventually you reach a point where GC_A finally gets the lock or the
user gives up.

Now, suppose GC_A finally succeeded.  The problem now is that GC_B wants
to make an updated (scheduled transaction happens, a sudden need for a
report, etc.).  GC_B can't complete any of its work because GC_A has the
database locked in its entirety.  So once again, like in the case of
GC_A, GC_B must wait, abort, or inform the user and then, if the user
tries again, there's still no guarantee GC_A is done and the database
lock released.

This situation is exactly why banks and other institutions use known
solutions replication servers, snapshots, views, logging, and scheduled
downtime to perform backups, exports, and the like.  It is a known and
mostly solved problem but it does require experience and software
outside the routine client (Gnucash in this case).  IN the end you can
have your export if you really want it but you should not expect it to
come from inside Gnucash if it is to be safe for the data.  It has to
come from a utility that runs on the outside.  I never said you can't
export your data in general, I only say that it would be a very bad idea
to do it within Gnucash for a multiuser (concurrent) environment.