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

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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.

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.

Have not tried the Windows version - I don't like to touch those systems if
I can avoid it.

If there is a problem with accessing the same data file from different
system, I just may have tho keep the file on windows and VNC into it.

Steve Alex



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

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

Geert Janssens-4
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 ?

> 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)
- some parts of gnucash now store their slots in flat format instead of
hierarchically. If you're not using the slots in your custom report, this
won't affect you

Regards,

Geert


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

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

salex

> On Jun 25, 2018, at 3:40 AM, Geert Janssens <[hidden email]> 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

> - some parts of gnucash now store their slots in flat format instead of
> hierarchically. If you're not using the slots in your custom report, this
> won’t affect you

Don’t use slots, just Accounts, Splits and Transactions.


>
> Regards,
>
> Geert
>
>

_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

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

Geert Janssens-4
Op maandag 25 juni 2018 13:20:10 CEST schreef Steve Alex:

> > On Jun 25, 2018, at 3:40 AM, Geert Janssens <[hidden email]>
> > 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


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

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

John Ralls-2


> On Jun 25, 2018, at 6:33 AM, Geert Janssens <[hidden email]> wrote:
>
> Op maandag 25 juni 2018 13:20:10 CEST schreef Steve Alex:
>>> On Jun 25, 2018, at 3:40 AM, Geert Janssens <[hidden email]>
>>> 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


_______________________________________________
gnucash-user mailing list
[hidden email]
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.