GNOME Bugzilla – Bug 794936
3.0 does not open previously saved sqlite3 files properly - corrupted business data
Last modified: 2018-06-30 00:06:53 UTC
Note, this is using the sqlite backend, I'll test to see if this makes a difference. I will also test editing the data to see if it sticks or fixes anything. When I open an invoice or bill, the quantity, price (and discount) are very large numbers, usually expressed as a sum with a fraction—e.g., All quantities on any bill or invoice show as 7,406,985,222,262 + 6/19 All prices on any invoices show as 4,691,090,640766 + 2/15, BUT All prices on any bills show as 70,366,359,611,492.00 and on invoices, even where no previous discount existed, discounts now show as either: 1,211,813,243 + 30211/58607 1,211,646,312 + 42092/58075 1,211,479,427 + 53051/58083 (it turns out, the discounts are all different, but every invoice has them even if they originally did not) And of as a likely consequence, I can't print the invoice/bill without getting: "Report error An error occurred while running the report." Note also, the Total & Subtotal at the top of the invoice/bill window are both zero, and the subtotal column in the line item detail is blank. (not sure if that's a separate bug or a consequence of flubbed numbers in the other fields)
I tested opening an XML version of the file and all data is as it should be. It seems the sqlite version is not read properly, I couldn't tell if it was corrupted due to the issue of date conversions making the file unreadable by 2.16.19. (I'll try to view the data with an sqlite viewer to verify) Editing the quantities, prices and discounts 'fixes' the problem, but I don't think anyone wants to export pdf copies of every bill and invoice only to re-enter all the numeric data all over again. I guess it's back to XML till this gets resolved.
Note, I viewed the sqlite file and in the entries table, all seems well with prices and quantities save one of my bills has both i_price/denom & b_price/denom entries. The b series is correct, and not only is the i series supposed to be 0 & 1 respectively (based on the other entries) but it's way weird. The i_price_num shows as "140732839401464" and the i_price_denom as "2" (which is likely where the 70,366,359,611,492.00 comes from) This bill also shows a discount which should not exist as i_discount_num with the same large number and the i_discount_denom as "1046258" for the first line item and "1046274" for the second line item. It seems at least this one bill is corrupted, but I don't understand how that would negatively impact all invoices and bills even those previous to this one. I've tried Check & Repair > All, but this does not resolve the situation. I'll next attempt to zero out the offending bill (since I can't delete it) and see if that doesn't clear things up. (though I'm not sure how to properly since it seems to include invoice prices when it is in fact a bill) Is this a rare case for direct editing? (on a copy of course)
More weirdness. So I tried to delete the line items on the offending invoice. It would only let me delete one. So I zeroed the quantity and price on the remaining item and closed the bill (unposted) I then tried to view other bills/invoices to see if there was any change. (none) I exited and reloaded and tried to view other bills/invoices again. (still no change) Then I thought about adding a blank line item so I could delete the one it wouldn't let me delete before. Now I can't unpost the invoice at all. (but it is not posted and has no posted date) When I click the Unpost button, nothing happens. Also oddly, the line item I zeroed out once again has erroneous data in it. I then tried to open an XML version of the file and delete those lines in the bill. Now I can't delete either the dummy line I entered, or the original offending line. The bill shows properly as unposted, but I can't do anything else with it. I can't even ADD a line now or edit the lines that are there. So I opted to try to edit bill using the XML version of the file with 2.6.19 - success. I was able to delete both original lines on the bill after unposting. I then opened this version with 3.0 and saved in sqlite format, reopened the file, and still the absurdly large numbers appear in both invoices and bills. So it wasn't that one bill causing havoc. (and I checked carefully over the entries table - there are no others with this issue)
Changing the component from Business to Backend-SQL since everything works fine in XML.
Can you make a small SQLite3 database that demonstrates the problem?
I'll see if I can duplicate this with a test file. I have a meeting shortly, so It'll be later this evening or tomorrow.
Created attachment 370542 [details] business test sqlite3 database Sorry I'm just getting back to this. Here's a test db I created with 3.0. It seems this isn't just a translation issue from 2.6.19. I created this first as XML, then saved as sqlite3 and re-opened to find, once again, very large numbers in the respective fields. (my original issue WAS sqlite-sqlite but just changing versions) So it's more like 3.0 seems to not open a previously saved sqlite3 file properly. There is one bill and one invoice. (each #1) I left them open as well as left open the AR and AP registers but I'm not sure if that's a local setting or something that carries over with the file. I'm also going to attach the XML version for reference.
Created attachment 370543 [details] business test sqlite3 db xml original
Interesting. The values are stored correctly in SQL.
Yes, interesting. At least it is reassuring for data integrity.
*** Bug 794982 has been marked as a duplicate of this bug. ***
Jo Wetzig reported on bug 794982: > >Please open your SQLite3 file with sqlite3 and do the following query: > > SELECT guid, i_price_num, i_price_denom, i_discount_num, i_discount_denom > >FROM entries; > > Not all of them ;((( > > "a091a44e7259b2b2fba207e14be6f2ea" "2000000" "1000000" "0" "1" > "4da6ba68f004f65ce22ae2f4f738bb22" "30000000" "1000000" "20000000" "1000000" > "9d42cb293dc1952bdb99446ed708c0c8" "30000000" "1000000" "20000000" "1000000" > "8a5029c38e8255ef275c02063a87f5ea" "1000000" "1000000" "0" "1" > "7d5225ed198029c094e8a69f86f1eee2" "30000000" "1000000" "20000000" "1000000" > "5ec50f6e6dc459952e2e04b01b9c27cd" "30000000" "1000000" "20000000" "1000000" > "301c90831e1af9e65bdaca358dbad40a" "30000000" "1000000" "0" "1" > "fafd8b9b573e40b5224122eb1cc816e0" "30000000" "1000000" "20000000" "1000000" > "5dddbd8746644f2d1512c22fc1848b4b" "140734710354216". "2" "140734710354216" > "176630" > "017c6cee2948a2c99766b89b7920d812" "30000000" "1000000" "30000000" "1000000" > "38a4e55f8e8403392c8ce62c2558759b" "1000000". "1000000" "10000000" "1000000" > "4a5f2c30af1e4beba463acafdbe86046" "0". "1" "140734611095848" "2" > > (Bold emphasis added by yours truly) > > Cheers > > J W > > On second thought: > > This is how the data looked in the backup version of the SQL file BEFORE > changing to V 3.0, i.e. when accessed with V 2.6.19. > Discrepancies in boldface: > > "a091a44e7259b2b2fba207e14be6f2ea" "12823140" "1000000" "0" "1" > "4da6ba68f004f65ce22ae2f4f738bb22" "30000000" "1000000" "20000000" "1000000" > "9d42cb293dc1952bdb99446ed708c0c8" "30000000" "1000000" "20000000" "1000000" > "8a5029c38e8255ef275c02063a87f5ea" "6994440" "1000000" "0" "1" > "7d5225ed198029c094e8a69f86f1eee2" "30000000" "1000000" "20000000" "1000000" > "5ec50f6e6dc459952e2e04b01b9c27cd" "30000000" "1000000" "20000000" "1000000" > "301c90831e1af9e65bdaca358dbad40a" "30000000" "1000000" "0" "1" > "fafd8b9b573e40b5224122eb1cc816e0" "30000000" "1000000" "20000000" "1000000" > "5dddbd8746644f2d1512c22fc1848b4b" "0" "1" "0" "1" > "017c6cee2948a2c99766b89b7920d812" "30000000" "1000000" "30000000" "1000000" > "38a4e55f8e8403392c8ce62c2558759b" "10724808" "1000000" "0" „1" > > Puzzled ;) > > J W Presumably he edited the invoices causing the bad numbers to be written back to the database while Adrien didn't.
*** Bug 794932 has been marked as a duplicate of this bug. ***
(In reply to John Ralls) > Even though this is 3 items newer, there's more information on bug 794936, > so let's use that for dissecting the problem. > > Tom, please post over there the results of the query > select guid, qty_num, qty_denom, i_price_num, i_price_denom from events; > If you have a backup from 2.6.19 it would be helpful to run it on both. Presumably, you meant: SELECT guid, i_price_num, i_price_denom, i_discount_num, i_discount_denom from ENTRIES; There's nothing of interest to note. v2.6.19: a83f69a3cd6f8d43eaa24af547763898|60000000|1000000|0|1 df98027384f835ee1093163ca46a653e|165300000|1000000|0|1 20c04d1c33d713831d51745c46f9138b|105740000|1000000|0|1 1f4854ade4d17accdd9766e2f1dc21c7|21560000|1000000|0|1 e8a076632284df7b81cf27fffa03e1b8|133790000|1000000|0|1 df6b39133e01200a062ae66ed1c53bbc|443810000|1000000|0|1 ceaf44deb5f098ad71cd779ba808d79f|60000000|1000000|0|1 436014b1cbdf5d6f888e84935d3d973e|60000000|1000000|0|1 8ec83bd63080c299eaea5a2f725c725f|0|1|0|1 7b44f4c9c79fd6cf6f9c0867e2f86627|0|1|0|1 ec25dcbd37f7110675626eeff15bbbdc|0|1|0|1 b16815699ea036e0d4f969dfcbe38baf|0|1|0|1 7c3d41c273232602d3408887f901442d|0|1|0|1 d6c2cb4bc240cfb26a9528e1b68731f2|60000000|1000000|0|1 f27eec7fc4258a0e612b440ab95f060e|0|1|0|1 f416c7dc510bf39881756a2e0991be75|8000000000|1000000|0|1 v3.0: a83f69a3cd6f8d43eaa24af547763898|60000000|1000000|0|1 df98027384f835ee1093163ca46a653e|165300000|1000000|0|1 20c04d1c33d713831d51745c46f9138b|105740000|1000000|0|1 1f4854ade4d17accdd9766e2f1dc21c7|21560000|1000000|0|1 e8a076632284df7b81cf27fffa03e1b8|133790000|1000000|0|1 df6b39133e01200a062ae66ed1c53bbc|443810000|1000000|0|1 ceaf44deb5f098ad71cd779ba808d79f|60000000|1000000|0|1 436014b1cbdf5d6f888e84935d3d973e|60000000|1000000|0|1 8ec83bd63080c299eaea5a2f725c725f|0|1|0|1 7b44f4c9c79fd6cf6f9c0867e2f86627|0|1|0|1 ec25dcbd37f7110675626eeff15bbbdc|0|1|0|1 b16815699ea036e0d4f969dfcbe38baf|0|1|0|1 7c3d41c273232602d3408887f901442d|0|1|0|1 d6c2cb4bc240cfb26a9528e1b68731f2|60000000|1000000|0|1 f27eec7fc4258a0e612b440ab95f060e|0|1|0|1 f416c7dc510bf39881756a2e0991be75|8000000000|1000000|0|1
The following is the same between both versions: sqlite> SELECT guid, i_price_num, i_price_denom, i_discount_num, i_discount_denom from entries; a83f69a3cd6f8d43eaa24af547763898|115630000|1000000|60000000|1000000 df98027384f835ee1093163ca46a653e|1000000|1000000|165300000|1000000 20c04d1c33d713831d51745c46f9138b|1000000|1000000|105740000|1000000 1f4854ade4d17accdd9766e2f1dc21c7|1000000|1000000|21560000|1000000 e8a076632284df7b81cf27fffa03e1b8|1000000|1000000|133790000|1000000 df6b39133e01200a062ae66ed1c53bbc|1000000|1000000|443810000|1000000 ceaf44deb5f098ad71cd779ba808d79f|166250000|1000000|60000000|1000000 436014b1cbdf5d6f888e84935d3d973e|85433300|1000000|60000000|1000000 8ec83bd63080c299eaea5a2f725c725f|1000000|1000000|0|1 7b44f4c9c79fd6cf6f9c0867e2f86627|1000000|1000000|0|1 ec25dcbd37f7110675626eeff15bbbdc|1000000|1000000|0|1 b16815699ea036e0d4f969dfcbe38baf|1000000|1000000|0|1 7c3d41c273232602d3408887f901442d|1000000|1000000|0|1 d6c2cb4bc240cfb26a9528e1b68731f2|143300000|1000000|60000000|1000000 f27eec7fc4258a0e612b440ab95f060e|1000000|1000000|0|1 f416c7dc510bf39881756a2e0991be75|1000000|1000000|8000000000|1000000
(Sorry, that query should have been: sqlite> SELECT guid, quantity_num, quantity_denom, i_price_num, i_price_denom from entries;)
Found it, <CT_NUMERIC>was passing a pointer to functions taking a copy.
Thank you sir!
https://github.com/Gnucash/gnucash/commit/39aecb7610fe9e5241f7ab77e99cf86ed53f36a9 resolved the issue for me. Thanks!
Just tested with 3.1.2 - all figures now look correct. Thanks!
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=794936. Please update any external references or bookmarks.