[GNC] Exiting Gnucash Lock file

John Ralls jralls at ceridwen.us
Fri Jan 21 16:44:46 EST 2022


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> 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> 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> >
>> 
>> To: Thomas Forrester <tlforrester at gmail.com <mailto:tlforrester at gmail.com>
>>> 
>> 
>> Cc: Peter <pandem at iinet.net.au <mailto:pandem at iinet.net.au> >,
>> gnucash-user
>> 
>>                <gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org
>>> 
>>> , 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> >
>> 
>> 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> > 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> > 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> > 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>
>> 
>>>> 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>
>> 
>>>> To update your subscription preferences or to unsubscribe:
>> 
>>>> [4]https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> 
>>>> If you are using Nabble or Gmane, please see [5]
>> 
>>>> 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=ieee.org at gnucash.org
>> 
>>>>  2. mailto:gnucash-user at gnucash.org
>> 
>>>>  3. mailto:gnucash-user at gnucash.org
>> 
>>>>  4. https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> 
>>>>  5. https://wiki.gnucash.org/wiki/Mailing_Lists
>> 
>>>> _______________________________________________
>> 
>>>> 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
>> 
>>>> 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.
>> 
>>>> 
>> 
>>> _______________________________________________
>> 
>>> 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
>> 
>>> 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.
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> ------------------------------
>> 
>> 
>> 
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> 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.
>> 
> 
> 
> -- 
> Tom
> Thomas L. Forrester
> 3211 Patty Lane
> Middleton, WI 53562-1652 USA
> 608-831-0769
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> 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.



More information about the gnucash-user mailing list