GNOME Bugzilla – Bug 583150
Problems opening sqlite3 version of GnuCash file
Last modified: 2018-06-29 22:21:54 UTC
I saved my fairly large GnuCash file as an sqlite3 data base (which produced a 25.8 megabyte file) and tried to open it again. This takes a very long time and produces a number of errors like * 19:32:42 CRIT <gnc.backend.dbi> [sqlite3_error_fn()] DBI error: 1: table lots already exists * 19:32:42 CRIT <gnc.backend.dbi> [conn_create_table()] Error in dbi_result_free() result for 22 different tables. These were followed by more messages like * 19:32:42 WARN <GLib-GObject> value "0" of type `gint' is invalid or out of range for property `fraction' of type `gint' and then hundreds of messages like * 19:32:45 CRIT <gnc.backend.sql> set_slot_type: assertion `pValue != NULL' failed After this there is a very long pause while it loads the price data base, which is large. This is because it is checking each price to see if it is a duplicate of a previous price. This changes an O(n) algorithm to O(n**2) and is a waste of time while loading the data base since there shouldn't be duplicate prices in the DB. I fixed the same problem in the XML backend years ago and the attached patch fixes this in the DBI backend. After the price DB loads I get hundreds more of the 'pValue != NULL' assertions interspersed with a few messages like * 23:22:03 WARN <gnc.gui> [PrintAmountInternal()] Bad numeric. Eventually it loads. One of the pages that opens automatically is an investment portfolio report which isn't correct. Most lines have a zero value for the "Units" column and the total is also zero. A new investment portfolio report I opened has a similar problem. This is with SVN version R18080.
Created attachment 134911 [details] [review] Patch to speed up loading of price DB Don't check for duplicates while loading the price DB from an SQL data base.
You are changing from a compressed text file to an SQL database. Unfortunately, I have also found that the file size increases considerably. Do you have the 'sqlite3' utility installed? It allows you to view data from the sqlite3 file e.g. if your sqlite3 file is my-data.gnc, then 'sqlite3 my-data.gnc' will allow you to access the data. 1) what is the output of 'select * from versions;' ? (enter this to the sqlite3 utility without the ''. 2) what is the output of 'select distinct type from slots;' ?
File size doesn't particularly bother me, my point was just that it's a largish file. The first command gives me: sqlite> select * from versions; lots|2 jobs|1 recurrences|1 invoices|2 prices|2 commodities|1 customers|2 taxtables|2 taxtable_entries|2 orders|1 employees|2 billterms|2 schedxactions|1 transactions|2 splits|2 budgets|1 entries|2 slots|2 vendors|1 books|1 accounts|1 lots|2 jobs|1 recurrences|1 invoices|2 prices|2 commodities|1 customers|2 taxtables|2 taxtable_entries|2 orders|1 employees|2 billterms|2 schedxactions|1 transactions|2 splits|2 budgets|1 entries|2 slots|2 vendors|1 books|1 accounts|1 lots|2 jobs|1 recurrences|1 invoices|2 prices|2 commodities|1 customers|2 taxtables|2 taxtable_entries|2 orders|1 employees|2 billterms|2 schedxactions|1 transactions|2 splits|2 budgets|1 entries|2 slots|2 vendors|1 books|1 accounts|1 lots|2 jobs|1 recurrences|1 invoices|2 prices|2 commodities|1 customers|2 taxtables|2 taxtable_entries|2 orders|1 employees|2 billterms|2 schedxactions|1 transactions|2 splits|2 budgets|1 entries|2 slots|2 vendors|1 books|1 accounts|1 lots|2 jobs|1 recurrences|1 invoices|2 prices|2 commodities|1 customers|2 taxtables|2 taxtable_entries|2 orders|1 employees|2 billterms|2 schedxactions|1 transactions|2 splits|2 budgets|1 entries|2 slots|2 vendors|1 books|1 accounts|1 The second command seems to be slightly wrong (or something else is wrong): sqlite> select distinct type from slots; SQL error: no such column: type
The versions table keeps track of the version for each other table. On startup, gnucash is supposed to read this table. If there is no value for a specific table or the value is 0, the table is created. If the value < the current table version, it is upgraded automatically. If a table is created, the name and version number is written to this versions table. What I see there is multiple lines for each other table, so for some reason, it isn't reading properly or something. This might be the first time the code has been tested on MacOX. I use linux and windows and they work fine. What version of libdbi/libdbi-drivers do you have? BTW, I will merge your price speedup patch.
I'm using version 0.8.3 of libdbi and libdbi-drivers. These were installed using Macports. Since Macports didn't have a port for them, I created one. The libdbi port has been added to SVN, but the libdbi-drivers port hasn't. They had some issues with it which I fixed but they haven't committed it yet. I'll rattle their cage and see why.
Can you use GDB and do some debugging? Unfortunately, I don't see your problems on linux and I don't have access to a mac. If you can, set a breakpoint in routine gnc_sql_init_version_info() and load your file. This routine loads the version info and stores it in a hash table with table name as a key. Then, routine gnc_sql_get_table_version() takes a table name and returns a version number.
Thanks for the pointer, that was enough to find the bug. It turned out to be an endian bug in libdbi. I got hit by it not because I'm running MacOSX, but rather because I'm running on a PowerPC machine. libdbi stores values in a union type and returns (for example) the long long member of the union from a function with a long long value even if the union contains a shorter integer. I submitted a patch for this to the libdbi-devel list and to MacPorts. I'll also attach it here in case anyone else runs into it. With this patch my GnuCash sql file opens with no errors and looks ok. It takes a while to open since I have one report and several registers open, but that's not too surprising. It looks like the GnuCash code was ok all along.
Created attachment 135154 [details] [review] Patch to libdbi to fix endian problem
Created attachment 135223 [details] [review] Corrected patch to libdbi tp fix endian problem The previous version of this patch didn't handle 3 byte integers (yes the libdbi driver API really has 3 byte integers) correctly.
The patch should be added to the win32 gnucash build, at least.
Comment on attachment 135223 [details] [review] Corrected patch to libdbi tp fix endian problem Has this already been sent upstream to libdbi? In any case it indeed should be added to our windows build before compiling libdbi.
I will add it. However, note that this was discovered on a mac because of different endianness, so changing our windows build won't really help fix the problem.
This patch was submitted upstream and was applied to the CVS source in May. It should be in the next version of libdbi released. I don't know when that might be, but the last release was in February 2008 and there has been some recent discussion on the libdbi-devel list about a new release.
Comment on attachment 135223 [details] [review] Corrected patch to libdbi tp fix endian problem Oh well, if this isn't a problem on Windows machines because of the originally expected endianess, we probably don't need this patch in gnucash but just in a new libdbi version to come? Marking as not-longer-necessary ("rejected" here). Or did I get something wrong?
You're right that this is not a bug in GnuCash. The patch I attached is not relevant to GnuCash since it is a libdbi patch. There still hasn't been a new release of libdbi that includes the fix. Since it's now been nearly 2 years since the last release, it seems like there ought to be one soon. In the meantime, if someone installs libdbi using Macports on a Macintosh this bug will be fixed since my patch is applied as part of the Macports build process.
Closing - since it's now fixed in macports and in libdbi source.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=583150. Please update any external references or bookmarks.