GNOME Bugzilla – Bug 796369
Notes lost or perhaps just not displaying when using SQLite backend
Last modified: 2018-06-30 00:10:44 UTC
I've just noticed in Version 3.1 that all my Notes have vanished from the register. I use GnuCash with the register set to Basic Ledger and Double Line Mode, so I always see the Description and Notes fields. I've noticed today that all of the Notes fields are blank, including some that I know I've entered. I'm using Version 3.1, Build ID: 3.0-118-gd2ef5fd0f+ (2018-04-28), on Windows 10. Detailed steps: Select an account, go to the bottom new transaction line Enter the date, Description, an account, an amount, and something in the Notes field, and hit Enter See that the Notes appear in the register Close the tab for the register Re-open the account See that the Notes still appear in the register for that transaction Close GnuCash Re-open GnuCash, then re-open the account See that the test note, and all other notes, are gone Adrien Monteleone has also confirmed the bug on the gnucash-user mailing list (on Mac, so it's not Windows specific), and suggested testing with the XML backend. Per his suggestion I saved my file using the XML backend and reopened that file, but the notes have not reappeared. I fear that means the notes are actually lost, but if there's a way to get them back that would be great. Newly created notes using the XML backend are being saved and displayed. (I definitely was on the SQL backend - the program used to close immediately. I'm definitely on the XML backend now - it takes quite a while to save when I close the program.)
Here is the mailing list thread for reference: https://lists.gnucash.org/pipermail/gnucash-user/2018-May/077158.html In my case, the notes are still in the db (confirmed using an sqlite viewer) and don't seem to have disappeared, but are not visible when opening an sqlite version of the file with GnuCash. I have not (yet) tested trying to create a new note using the sqlite backend to see what happens, if anything.
I too have confirmed that all my old notes are still in the most recent sqlite file, including recently ones.
Further testing: after saving as XML and finding the notes still not displayed, I then saved from the XML file back to SQLite and checked with DB Browser - the notes are now missing. I'll go back to using my recent SQLite file.
I just tested the following: 1. Open XML version of the file 2. Observe notes are visible 3. Save as sqlite 4. Quit GnuCash 5. Reopen GnuCash (which opens the sqlite version) 6. Observe no notes are visible from previous transactions 7. Enter a transaction with a note 8. Quit GnuCash 9. Re-open GnuCash (which opens edited sqlite version) 10. Observe note in new transaction is not visible either. 11. Save file as XML 12. Quit GnuCash 13. Re-open GnuCash (which opens XML version) 14. Observe note is still not visible and now, no previous notes are visible either. (but were earlier using XML as noted) A check of the sqlite version of the file at this point shows the notes are still there. So there is no data destruction in the sqlite version of the file. Unfortunately, I can't say the same for the saved XML version from step 11. Viewing the XML file shows that the <slot:key>notes</slot:key> sections are missing from ALL <gnc:transaction> entries. (but they are still there for all <gnc:account> and all <gnc:lot> having type gncInvoice. Note, that saving the sqlite version as XML using 2.6.21 does not cause this loss of data. Since data loss is involved, I'm not sure if 'normal-critical' is correct, but the reporter or one of the devs will have to change it.
I'll confess I haven't looked at the code, but I have the following suspicions: With 2.6.x, saving sqlite as XML writes to the XML what is loaded into memory. It does not write the XML from the on-disk sqlite file. With 3.x, opening an sqlite file does not for some reason, read into memory the slots matching name="notes" for transactions, but does for everything else. (accounts, invoices, vendors, customers, etc.) With 3.x, using sqlite, when posting a new transaction with a note, the transaction is written immediately to the file. (likely the case for all CRUD since there is no need to 'save' the file periodically) Reopening this file however, still does not read those rows into memory. With 3.x, saving an sqlite file to XML occurs the same as with 2.6.x - from memory rather than from the on-disk file. Since the notes for transactions were not read into memory in the first place, they are not written out to the XML file. Or, perhaps I'm misunderstanding how the data is read into memory and the source/process for writing back to disk.
I can confirm not seeing the transaction notes in the register but there are plenty in the slots table. I have also confirmed that the notes belong to expected transactions by comparing ids. sqlite backend, windows nightly from 2018-05-12.
Looks to me like none the transaction slots are getting read. Adrien, in the file created from save as xml do any of the transactions have any slots?
The XML I saved using 2.6.21 has all slots that are expected. The XML saved using 3.1-2 none of the transactions have "notes" slots. (as noted, only accounts and lots-type invoice have slots) I think it does have some slots for transactions that aren't of type "note". I'll have to double check.
After a bit of debugging it looks like a query issue. The immediate cause is that the query for transaction slots ends up as: "SELECT * FROM slots WHERE obj_guid = ((SELECT DISTINCT guid FROM transactions))" Oddly, the double parenthesis select some slots but not all. Even more oddly, if the note is created in 3.1 it isn't loaded but if it's created from the current maint HEAD it is. Still scratching my head on that one.
I've had a similar case where some queries in the Android app would not load some transactions or add their amounts to the balance sum, but loading individual records and reading the value worked. Also, some built-in Android data retrieval methods would not return the full numeric values (I think it was only the first six digits or so). I know this might not help much but just wanted to add some strange experiences with SQLite data reading. The fact that all data is stored as strings probably contributes to that. One thing to check is if this happens *only* with sqlite and not with other SQL backends.
Figured it out. In SQLite3--but not in MySql, haven't checked PgSql but I don't think it matters--"SELECT * FROM slots WHERE obj_guid IN ((SELECT DISTINCT guid FROM transactions))" returns the slots for only the first transaction returned by the subquery. The difference between the two databases was simply down to the order in which the subquery presented its results. Fixed in git for 3.2.
Sweet, Thanks John! Might this subquery problem be what's also causing bug 795702? (albeit at a different place in the code) That's another case of data still existing but not making it into all places of the app it's supposed to. (but does so in some areas)
(In reply to Adrien from comment #12) > Sweet, Thanks John! > > Might this subquery problem be what's also causing bug 795702? (albeit at a > different place in the code) > > That's another case of data still existing but not making it into all places > of the app it's supposed to. (but does so in some areas) Maybe. Some transactions involved in business operations are loaded explicitly by GUID from the GncEntry loader and those use a different subquery that didn't have the error so their slots got loaded correctly. It's possible that the payment transactions aren't loaded that way so their slots aren't pulled in, breaking the link to the GncEntry.
*** Bug 796609 has been marked as a duplicate of this bug. ***
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=796369. Please update any external references or bookmarks.