[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