[GNC] Exiting Gnucash Lock file
john
jralls at ceridwen.us
Fri Jan 21 23:31:32 EST 2022
Nope, because GnuCash uses the DB only as a data store. All of the business logic and referential integrity are inside GnuCash. If you write the database from outside of that you can far too easily unbalance transactions or break critical references.
Regards,
John Ralls
> On Jan 21, 2022, at 3:57 PM, Kalpesh Patel <kalpesh.patel at usa.net> wrote:
>
> Is any tool, perhaps SQLite studio (https://sqlitestudio.pl/about/ <https://sqlitestudio.pl/about/>), supported as the alternate to an GNC API as the Editor that won’t void the warranty?
>
> From: Thomas Forrester <tlforrester at gmail.com>
> Sent: Friday, January 21, 2022 5:03 PM
> To: John Ralls <jralls at ceridwen.us>
> Cc: Kalpesh Patel <kalpesh.patel at usa.net>; gnucash-user <gnucash-user at gnucash.org>
> Subject: Re: [GNC] Exiting Gnucash Lock file
>
> John,
>
> Haha! Yeah, I'm afraid I've voided that warranty a few times, but only to correct reconciliation issues that took me moments to correct in Access, yet seem to cause much hair-pulling and gnashing of teeth with others.
>
> I don't think it would be any performance or storage issue to simply add an auto-number integer field and set as the primary key, though. Doesn't affect your other indices in the least, so I doubt your code even needs to change. But IDK, and I'm sure making the GnuCash SQLite3 db compatible with ODBC connectivity to MS Access is no priority at all. Just wishful thinking on my part.
>
> On Fri, Jan 21, 2022 at 3:45 PM John Ralls <jralls at ceridwen.us <mailto:jralls at ceridwen.us>> wrote:
>> Having the database read-only in Access sounds like a feature: Writing to the GnuCash database using anything except the GnuCash API voids your warranty.
>>
>> Access insisting on a non-text Primary Key an Access bug. All of GnuCash's tables have primary keys already, but they're mostly text representations of GUIDs because SQLite doesn't support 128-bit unsigned ints nor--unlike MySQL and Postgresql--have a UUID field type.
>>
>> Regards,
>> John Ralls
>>
>> > On Jan 21, 2022, at 1:25 PM, Thomas Forrester <tlforrester at gmail.com <mailto:tlforrester at gmail.com>> wrote:
>> >
>> > I have tried to switch to SQLite3 this afternoon, and although I cannot
>> > answer all your questions, I have observed the following:
>> >
>> > 1. Regarding your stability on Windows question, Check Wiki on SQLite.
>> > It's pretty ubiquitous at this point and extraordinarily stable on all
>> > platforms. I don't think you're going to have an issue with stability.
>> >
>> > 2. There are log files when using SQLite3, but I cannot say if they work
>> > as you are asking.
>> >
>> > 3. Using the recommended ODBC driver, I was able quite easily to connect
>> > the GnuCash database to Microsoft Access, but immediately realized there
>> > are insurmountable problems that I have not found with ODBC/Access using a
>> > MySQL backend for GnuCash. Specifically, although the tables link into
>> > Access with the expected number of rows, All column data shows as
>> > #DELETED#. According to more research, I find the following
>> > incompatibility between the way the GnuCash database is designed and what
>> > Access aspects (from stackoverflow):
>> >
>> > [...] Access (Jet) wants a table to have a unique index in order to be able
>> > to insert/update the table if necessary.
>> >
>> > If your SQLite table doesn't have a unique index (or primary key), then
>> > Access will only allow read access to the table -- you can't edit the
>> > table's data in Access, but the data displays fine.
>> >
>> > To make the table updateable you might revise your SQLite code (or using a
>> > SQLite tool) to add an index to the table.
>> >
>> > If your PK/unique index happens to use a TEXT field, that's fine for
>> > SQLite. However, when you link to it in Access, Access will show the
>> > #DELETED# indications.
>> >
>> > The chain of events appears to be:
>> >
>> > Access/Jet notices the unique index, and tries to use it. However, SQLite
>> > TEXT fields are variable length and possibly BLOBs. This apparently doesn't
>> > fulfill Access's requirements for a unique index field, hence the #DELETED#
>> > indication.
>> >
>> > To avoid that problem, the index has to be a SQLite field type that Access
>> > will accept. I don't know the complete list of types that are acceptable,
>> > but INTEGER works.
>> >
>> >
>> > 4. I don't hold any hope of this, but it would be truly outstanding if the
>> > development team considered this issue (#3 above) and worked to resolve
>> > it. It most certainly is an outlier issue and not something germane to
>> > GnuCash itself, in fact it is more akin to assisting someone in shooting
>> > themselves in the foot. But to someone who understands the proper
>> > precautions, it sure would be helpful! (Not sure why ODBC to Access using
>> > MySQL works fine. Different db structures for different dbs???)
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > On Fri, Jan 21, 2022 at 2:48 PM Kalpesh Patel <kalpesh.patel at usa.net <mailto:kalpesh.patel at usa.net>> wrote:
>> >
>> >> Few questions in regards to SQLite3 backend recommendation because
>> >> currently
>> >> XML format for my data is compressed size of 1.82MB so exploring moving to
>> >> it. Not sure if this is considered small sized XML file or not but .
>> >>
>> >>
>> >>
>> >> 1 - SQLite was natively born on UNIX systems with UNIX systems in mind so
>> >> how stable is it for Windows and how well is it supported from the GNC
>> >> perspective?
>> >>
>> >> 2 - Does it still create the log file to be able to replay it like XML
>> >> storage creates?
>> >>
>> >> 3 - How can I roll back transactions (more so related to roll back from
>> >> import errors)? With XML I can take the previously created file and make it
>> >> my current file after renaming it.
>> >>
>> >>
>> >>
>> >> Any other differences to keep in mind other than backup regiment?
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >>
>> >>
>> >> Message: 8
>> >>
>> >> Date: Fri, 21 Jan 2022 08:43:04 -0800
>> >>
>> >> From: john <jralls at ceridwen.us <mailto:jralls at ceridwen.us> <mailto:jralls at ceridwen.us <mailto:jralls at ceridwen.us>> >
>> >>
>> >> To: Thomas Forrester <tlforrester at gmail.com <mailto:tlforrester at gmail.com> <mailto:tlforrester at gmail.com <mailto:tlforrester at gmail.com>>
>> >>>
>> >>
>> >> Cc: Peter <pandem at iinet.net.au <mailto:pandem at iinet.net.au> <mailto:pandem at iinet.net.au <mailto:pandem at iinet.net.au>> >,
>> >> gnucash-user
>> >>
>> >> <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
>> >>>
>> >>> , john.layman at laymanandlayman.com <mailto:john.layman at laymanandlayman.com> <mailto:john.layman at laymanandlayman.com <mailto:john.layman at laymanandlayman.com>>
>> >>
>> >>
>> >> Subject: Re: [GNC] Exiting GnuCash Lock file Vol 226, Issue 57
>> >>
>> >> Message-ID: <E0D29238-0072-40CD-AEA7-8DACECEA3654 at ceridwen.us <mailto:E0D29238-0072-40CD-AEA7-8DACECEA3654 at ceridwen.us>
>> >> <mailto:E0D29238-0072-40CD-AEA7-8DACECEA3654 at ceridwen.us <mailto:E0D29238-0072-40CD-AEA7-8DACECEA3654 at ceridwen.us>> >
>> >>
>> >> Content-Type: text/plain; charset=us-ascii
>> >>
>> >>
>> >>
>> >> Very few users will be able to manage a MySQL database server safely.
>> >> Fortunately GnuCash provides a SQLite3 backend that doesn't require any
>> >> database admin skills. The only caveat is that unlike XML it doesn't write
>> >> a
>> >> fresh file every session so you need to arrange backups on your own; the
>> >> OS's built-in snapshotting facility (rather grandly named Time Machine on
>> >> macOS and simply Windows Backup on Windows) is good for this, just make
>> >> sure
>> >> that your GnuCash data folder is included in its list of things to back up.
>> >>
>> >>
>> >>
>> >> Regards,
>> >>
>> >> John Ralls
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>> On Jan 21, 2022, at 6:45 AM, Thomas Forrester <tlforrester at gmail.com <mailto:tlforrester at gmail.com>
>> >> <mailto:tlforrester at gmail.com <mailto:tlforrester at gmail.com>> > wrote:
>> >>
>> >>>
>> >>
>> >>> Seems like a lot of work. No doubt you're finding interesting
>> >>
>> >>> anomalies, but...
>> >>
>> >>>
>> >>
>> >>> Rather than using xml data files, which have always seemed odd to me
>> >>
>> >>> (a la, this thread), if you use a MySQL database instead, all saves
>> >>
>> >>> are automatic and completely bypass the need for all this testing.
>> >>
>> >>> You still have to save each transaction, or edits to a transaction
>> >>
>> >>> (Enter key), but otherwise you can freely close the program without
>> >>
>> >>> any worries about loosing data, and there is no save prompt on the way
>> >>
>> >>> out. I've run with a MySQL database from the start without any
>> >>
>> >>> problems at all. Just a thought, and I recognize some may have
>> >>
>> >>> reasons not to choose that path, although I'm not sure what they would
>> >> be.
>> >>
>> >>>
>> >>
>> >>> On Thu, Jan 20, 2022, 11:22 PM Peter <pandem at iinet.net.au <mailto:pandem at iinet.net.au>
>> >> <mailto:pandem at iinet.net.au <mailto:pandem at iinet.net.au>> > wrote:
>> >>
>> >>>
>> >>
>> >>>> John,
>> >>
>> >>>> Well yes it did terminate or close.
>> >>
>> >>>> gnucash was closed and was then reopened without any reported issue.
>> >>
>> >>>> Other than it failed to save the changes and delete the lock file.
>> >>
>> >>>> Maybe if you could tell me how to confirm it closed or terminated
>> >>
>> >>>> correctly, I will go and test and see what happens.
>> >>
>> >>>> This issue I noted in Test#2 was actually caused by me not following
>> >> my
>> >>
>> >>>> own test process.
>> >>
>> >>>>
>> >>
>> >>>> On 21/01/2022 04:48, John Layman wrote:
>> >>
>> >>>>
>> >>
>> >>>> The test cases that appear to have failed aren't conclusive, however,
>> >>
>> >>>> without co nfirming that GnuCash had actually terminated, and
>> >>
>> >>>> terminated normally.
>> >>
>> >>>>
>> >>
>> >>>> -----Original Message-----
>> >>
>> >>>> From: gnucash-user [1]<gnucash-user-bounces+john.layman=
>> >>
>> >>>> ieee.org at gnucash.org <mailto:ieee.org at gnucash.org> <mailto:ieee.org at gnucash.org <mailto:ieee.org at gnucash.org>> > On
>> >>
>> >>>> Behalf Of Peter
>> >>
>> >>>> Sent: Thursday, January 20, 2022 9:39 AM
>> >>
>> >>>> To: [2]gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>>
>> >>
>> >>>> Subject: Re: [GNC] Exiting gnucash Lock file Vol 226, Issue 57
>> >>
>> >>>>
>> >>
>> >>>> Here is the testing methods I used to verify the issue I have noted.
>> >>
>> >>>>
>> >>
>> >>>> Selected a known small good gnucash data file.
>> >>
>> >>>> Storage Location: Local D: c: drive is Samsung SSD and D: is Seagate
>> >>
>> >>>> 2TB GNUCAS H Storage type : XML and compressed Operating system:
>> >>
>> >>>> Windows 10 GNUCASH versio n 4.9 The opening method in ALL test was to
>> >>
>> >>>> use the GNUCASH icon on the TASKBAR and then select the recent (test)
>> >>
>> >>>> file If the LOCK file was not deleted it was r emoved using the
>> >>
>> >>>> windows File Explorer
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Test #1: - change, Save Button and then File->Quit
>> >>
>> >>>>
>> >>
>> >>>> Result: This was repeated 5 times each time the same result - Lock file
>> >>
>> >>>> was dele
>> >>
>> >>>> ted and the change was Saved.
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Test #2 - change then File>Quit and save at 15 seconds on timer
>> >>
>> >>>>
>> >>
>> >>>> Result: In test 5 of 5 Lock file was NOT deleted and the change was NOT
>> >>
>> >>>> saved in
>> >>
>> >>>> the data file This test was actual performed more than 5 times but I did
>> >>
>> >>>> note 5
>> >>
>> >>>> of 5 in one contiguous run. There were a few test that asked to save the
>> >>
>> >>>> transa
>> >>
>> >>>> ction prior to the question of save the file I was never able to get it
>> >> to
>> >>
>> >>>> repea
>> >>
>> >>>> t twice in a row.
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Test #3 - change and exit File->Quit wait for the Save dialogue then let
>> >>
>> >>>> it time
>> >>
>> >>>> out
>> >>
>> >>>>
>> >>
>> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in
>> >>
>> >>>> the dat
>> >>
>> >>>> a file
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Test #4 - change and wait for autosave then File->Quit
>> >>
>> >>>>
>> >>
>> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in
>> >>
>> >>>> the dat
>> >>
>> >>>> a file
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Test #5 - change and wait for autosave then use X method
>> >>
>> >>>>
>> >>
>> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in
>> >>
>> >>>> the dat
>> >>
>> >>>> a file
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Test #6 - change then use X method wait for the save dialogue and save
>> >> at
>> >>
>> >>>> 15 sec
>> >>
>> >>>> onds on timer
>> >>
>> >>>>
>> >>
>> >>>> Result: In test 5 of 5 Lock file was NOT deleted and the change was NOT
>> >>
>> >>>> saved in
>> >>
>> >>>> the data file
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >> ----------------------------------------------------------------------------
>> >> ----
>> >>
>> >>>> ---------------------
>> >>
>> >>>>
>> >>
>> >>>> Conclusion:
>> >>
>> >>>>
>> >>
>> >>>> There is an issue in exiting with the SAVE button enabled/Highlighted
>> >> and
>> >>
>> >>>> comply
>> >>
>> >>>> ing with the dialogue to SAVE the Data file.
>> >>
>> >>>> Test#2 and Test#6
>> >>
>> >>>>
>> >>
>> >>>> Four methods of exiting that do delete the LOCK file and SAVE the latest
>> >>
>> >>>> changes
>> >>
>> >>>> A: Waiting to exit after an Autosave then File->Quit
>> >>
>> >>>> B: forcing a Save by using the SAVE button then File->Quit
>> >>
>> >>>> C: Waiting to exit after an Autosave then the X method
>> >>
>> >>>> D: forcing a Save by using the SAVE button then the X method
>> >>
>> >>>>
>> >>
>> >>>> X method works if you do a SAVE using the Save Button or wait till
>> >> after
>> >>
>> >>>> an Au
>> >>
>> >>>> tosave then exit via the X method
>> >>
>> >>>>
>> >>
>> >>>> There is an issue with exiting and relying on gnucash to save the data
>> >> if
>> >>
>> >>>> gnucas
>> >>
>> >>>> h produces the dialogue to SAVE the data then File->Quit or the X method
>> >>
>> >>>> The Sav
>> >>
>> >>>> e does not work and the LOCK file is not deleted.
>> >>
>> >>>>
>> >>
>> >>>> I cannot safely say I do not use the X method as I now have doubts
>> >> about
>> >>
>> >>>> it, I
>> >>
>> >>>> may use it or I may not.
>> >>
>> >>>>
>> >>
>> >>>> Hope this helps someone as now I understand why sometimes it does work
>> >> and
>> >>
>> >>>> does
>> >>
>> >>>> NOT work, there is a bug in it or is it a misinterpreted enhancement.
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>>
>> >>
>> >>>> _______________________________________________
>> >>
>> >>>> gnucash-user mailing list
>> >>
>> >>>> [3]gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>>
>> >>
>> >>>> To update your subscription preferences or to unsubscribe:
>> >>
>> >>>> [4]https://lists.gnucash.org/mailman/listinfo/gnucash-user <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>> >>
>> >>>> If you are using Nabble or Gmane, please see [5]
>> >>
>> >>>> https://wiki.gnucash.org/wiki/Ma <https://wiki.gnucash.org/wiki/Ma>
>> >>
>> >>>> iling_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.
>> >>
>> >>>>
>> >>
>> >>>> References
>> >>
>> >>>>
>> >>
>> >>>> 1. mailto:gnucash-user-bounces+john.layman <mailto:gnucash-user-bounces%2Bjohn.layman>=ieee.org at gnucash.org <mailto:ieee.org at gnucash.org>
>> >>
>> >>>> 2. mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
>> >>
>> >>>> 3. mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
>> >>
>> >>>> 4. https://lists.gnucash.org/mailman/listinfo/gnucash-user <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>> >>
>> >>>> 5. https://wiki.gnucash.org/wiki/Mailing_Lists <https://wiki.gnucash.org/wiki/Mailing_Lists>
>> >>
>> >>>> _______________________________________________
>> >>
>> >>>> gnucash-user mailing list
>> >>
>> >>>> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>>
>> >>
>> >>>> To update your subscription preferences or to unsubscribe:
>> >>
>> >>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>> >>
>> >>>> If you are using Nabble or Gmane, please see
>> >>
>> >>>> https://wiki.gnucash.org/wiki/Mailing_Lists <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.
>> >>
>> >>>>
>> >>
>> >>> _______________________________________________
>> >>
>> >>> gnucash-user mailing list
>> >>
>> >>> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org> <mailto:gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>>
>> >>
>> >>> To update your subscription preferences or to unsubscribe:
>> >>
>> >>> https://lists.gnucash.org/mailman/listinfo/gnucash-user <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>> >>
>> >>> If you are using Nabble or Gmane, please see
>> >> https://wiki.gnucash.org/wiki/Mailing_Lists <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.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >>
>> >>
>> >> _______________________________________________
>> >> gnucash-user mailing list
>> >> gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
>> >> To update your subscription preferences or to unsubscribe:
>> >> https://lists.gnucash.org/mailman/listinfo/gnucash-user <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>> >> If you are using Nabble or Gmane, please see
>> >> https://wiki.gnucash.org/wiki/Mailing_Lists <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.
>> >>
>> >
>> >
>> > --
>> > Tom
>> > Thomas L. Forrester
>> > 3211 Patty Lane
>> > Middleton, WI 53562-1652 USA
>> > 608-831-0769
>> > _______________________________________________
>> > gnucash-user mailing list
>> > gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
>> > To update your subscription preferences or to unsubscribe:
>> > https://lists.gnucash.org/mailman/listinfo/gnucash-user <https://lists.gnucash.org/mailman/listinfo/gnucash-user>
>> > If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists <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.
>>
>
>
>
> --
> Tom
> Thomas L. Forrester
> 3211 Patty Lane
> Middleton, WI 53562-1652 USA
> 608-831-0769
More information about the gnucash-user
mailing list