Windows 7 MySQL 8.0.13 GnuCash 2.6.21 All the above in use, working fine. Decided to upgrade GnuCash to v3.4, so backed up MySQL database. Performed install of GnuCash 3.4. Launched GnuCash; "Loading user data" took several minutes (longer than normal). When the Accounts screen came up, all the accounts were present, but all the account totals were zero, and none of the accounts had any transactions in them! Restored GnuCash 2.6.21, but it too was then showing all accounts with zero totals and no transactions!! Restored the MySQL data from backup - GnuCash 2.6.21 back working OK. Presumably the install of v3.4 alters the database in some way, and in doing so makes it unusable by the program. Any idea what this problem might be?
Someone reported the same problem with Postgresql in Bug 796977. The workaround is to save to XML from GnuCash 2.6, load that in GnuCash 3, and then save to MySQL to generate a new database. To troubleshoot the problem, please load the 2.6-created database in GnuCash 3, starting GnuCash 3 from the command line with --log gnc.backend.dbi=debug. That will capture all SQL queries in the tracefile (https://wiki.gnucash.org/wiki/Tracefile) which you can then examine and compare with your MySQL logs to understand what might be causing everything to be deleted. Then you can tell me, perhaps attaching the relevant part of the Tracefile here. The whole thing would enable someone to reconstruct your GnuCash file so you probably want to reveal only excerpts.
Many thanks, John I followed your workaround and v3.4 seems to be OK now. I troubleshot as you described above, and I believe I have found the reason for the failure. For some reason (which I don't know) my db backup and presumably the mysql db had a "_back" version of every table. On opening the 2.6 db for the first time with 3.4 each table had a sequence of SQL statements as below, eg for the transactions table: CREATE TABLE transactions_merge AS SELECT * FROM transactions UNION SELECT * FROM transactions_back; DROP TABLE transactions; ALTER TABLE transactions_merge RENAME TO transactions; DROP TABLE transactions_back Later a new version of the transactions table is created as transactions_new: CREATE TABLE transactions_new(guid varchar(32) PRIMARY KEY NOT NULL, currency_guid varchar(32) NOT NULL, num varchar(2048) CHARACTER SET utf8 NOT NULL, post_date DATETIME NULL DEFAULT '1970-01-01 00:00:00', enter_date DATETIME NULL DEFAULT '1970-01-01 00:00:00', description varchar(2048) CHARACTER SET utf8) An error is then encountered upon executing: INSERT INTO transactions_new SELECT * FROM transactions as a duplicate entry is detected on the Primary Key, and transactions_new therefore has no rows! The subsequent SQL statements: DROP TABLE transactions; ALTER TABLE transactions_new RENAME TO transactions; then result in no rows on the transactions table - hence my missing data. In summary, the merging of the transactions and transactions_back tables has resulted in a duplicate primary key which is detected on populating the new transactions table, causing all transaction data to be lost. I don't know where the "_back" tables came from or when they were created, or what their specific purpose was, or for that matter why the 3.4 installation was merging them. I hope the foregoing might be helpful, but I am very relieved that I haven't actually lost anything! Regards, Brendan Rogers
The _back tables are the result of an interrupted "safe save", where GnuCash rewrites the whole database in a supposedly recoverable way. When GnuCash loads a database and finds those tables it tries to combine the contents of e.g. transactions and transactions_back. That's the first query. The second query is a bugfix: The MySQL tables were originally created with the TIMESTAMP data type, but it is limited to 1 January 1970-19 January 2038. That won't do for GnuCash: 2038 is only 20 years away and 30-year house loans are common in some places, so we need to convert the MySQL database to DATETIME, which has a much larger date range. That's the second query. I think this reveals two problems in the GnuCash backend: First, we're obviously ignoring errors and pressing on with potentially destructive operations and second that the UNION operation can create duplicate primary keys; presumably other parts of the record are different so that the full records are distinct even if the column sets are not. I'm reopening this because even though your immediate problem is fixed we need to correct those issues in the code.
Thanks for the explanations.
