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

[John Ralls]

> On Jun 25, 2018, at 6:33 AM, Geert Janssens <geert.gnucash at kobaltwit.be> wrote:
> 
> 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.
> 

That’s correct. SQLite3 stores everything as strings. The old database code wrote YYYYMMDDHHMMSS, the new writes YYYY-MM-DD HH:MM:SS so that it’s consistent with what MySQL and Postgresql return.

Regards,
John Ralls