[GNC] Upgrade to 3.x Save As (sqlite3) failing with data corruption error

[Geert Janssens]

Op maandag 25 juni 2018 13:20:10 CEST schreef Steve Alex:
> > On Jun 25, 2018, at 3:40 AM, Geert Janssens <geert.gnucash at kobaltwit.be>
> > wrote:> 
> > Op maandag 25 juni 2018 01:38:35 CEST schreef salex:
> >> I've been trying to set up a procedure where two people can access our
> >> XML
> >> file located on a file server and of course ran into problems and know
> >> there are other possible problem (file marked open if you loose the
> >> network - yes I know its single user)
> >> 
> >> My tests last week seemed to work, but I've been cautious. There may be
> >> potential problems in that I'm on a Mac and the other user will be on
> >> Windows 10.  Then the file server is a debian server.
> >> 
> >> I noticed that the Windows version 3.x and I was using 2.6.4.  Also
> >> noticed
> >> that the windows version had Postgres option. I download the new version
> >> using brew cask wanting to try postgres for my report system,
> >> 
> >> I've been using a procedure for years that now seems broken.
> >> 
> >> I update the main book on a Mac (using xml). A few times a month I do a
> >> "Save As" and save off an sqlite3 version in another folder inside my
> >> GnuCash folder. I immediately open the xml version and quit.
> >> 
> >> I then run a bash script to backup the folder(rsync) to the debian
> >> server.
> >> 
> >> The sqlite3 file is used by a Rails server to produce reports I've
> >> developed.
> >> 
> >> I figured we'd use a modified procedure in that the updates would use the
> >> XML version on the debian server (maybe a script to check dates or see if
> >> file is open). I'd also put in a cron task to backup the folder to
> >> another
> >> offsite server.
> >> 
> >> Since I was on the server, when I did my "Save As", it was in the same
> >> file
> >> system. I alway just did the save as over the existing version since its
> >> just a read only file ( just confirm overwrite message)
> >> 
> >> When I did that with 3.x, after confirming overwrite, I'd get an error
> >> message saying it could complete because of data corruption.
> > 
> > This sounds like https://bugzilla.gnome.org/show_bug.cgi?id=789594
> > If you remove the existing sqlite file before running a Save As, do you
> > still get the error message about data corruption ?
> 
> I just saw that in the release 3.2 message, then got this reply.
> 
> I don’t think so. I think I set a different file name (or different folder)
> and there was no error. I think thats when I discovered my report system
> broke - most likely by the date format change mentioned below.
> >> Don't know if this is 3.x problem or not.  Switched back to 2.6 and it
> >> worked, even working on the server version.
> >> 
> >> Before I switched back to 2.6, I did the save as on my mac and copied the
> >> file the the server, I haven't verified this but my report system broke.
> >> I
> >> got no errors but a simple checkbook register had a balance but do ledger
> >> lines.
> > 
> > We had to make some changes to the database schema that may affect your
> > direct access to the book.
> > 
> > A few that come to mind:
> > - the field format to store dates has changed (affecting many tables)
> 
> That is most likely it.
> 
> What did dates change to? Just another version of a timestamp (and not
> YYYYMMDDHHMM ? I have lots of code (functions) that is basically taking a
> ruby date and doing something like; def self.month_transactions(date)
>       month = Vfwcash.yyyymm(date) # just date parser that returns string in
> format YYYYMM trans = Tran.where('transactions.post_date BETWEEN ? and
> ?',month+"00",month+"32").order(:post_date,:num) end

I believe the change was to
YYYY-MM-DD HH:MM:SS
Though I'm not sure. I haven't done this part and I'm not really using the SQL 
backends.

Regards,

Geert