GNOME Bugzilla – Bug 784623
GNUCash does not work with sql backend, I can open in read only mode
Last modified: 2018-06-29 23:57:50 UTC
Created attachment 355023 [details] screenshot with error message. I've just upgraded from 2.5.15. to 2.6.17. I am using MySQL 5.7 as SQL backend. The database opened only in read only mode. If I tried to write I got an error message. History: When 2.6.16 issued, I upgraded once from 2.6.15, but there was a problem: sometimes the frontend lost the connection with database and I got a "unable save to database" error when I tried to commit a transaction. Sometimes worked, sometimes not. That is why I reverted back to 2.6.15. When 2.6.17 Issued, I've tried the upgrade again. I can open my data, but I can't write it. I get "Unable save to database" error on every transaction (see the attached file). (In the previous version it happened regularly (1 transaction of 10), this version does not work at all. Then I reverted back to 2.6.15. this version is working. Operation system: Win 8.1 Mysql: 5.7 Gnucash language: Hungarian.
Please attach a trace file [1] from a session in which you had the error. [1] https://wiki.gnucash.org/wiki/Tracefile
Created attachment 355026 [details] trace files from 2017 Send the track files as requested from year 2017 Thank you in advance for your help! G.
First, I didn't ask for all of your tracefiles for the whole year, I asked for *exactly one*, the one that exhibits the problem. Second, don't use obscure single-platform archivers here. An RAR is completely useless to me. If the tracefile is large (and it shouldn't be, since you couldn't edit the database), archive it with a standard cross-platform archiver like zip.
Created attachment 355034 [details] today trace files Dear Sir, I send all of today's log. It is not one, just because I do not perfect in this software debugging, so today, while I am trying to resolve this problem I've done many attempts. And, there is many - different size log files created by the system, and I do not know what do you exactly looking for. While I previously attached the years's logs (it not so many, it was 13 files) because the previous version was created errors as well this version created. I thought it may help. But now, I attached only today's scripts. I cannot filter it more. I am not a linux developer. If RAR is useless for you (it is generally a common format for general users), please inform the users by the upload script that it is an unacceptable format, if possible. I am very thankful of your help, and I would like kindly ask to you, if it be possible, please be a little more polite. I hope that I can contribute to the perfecting of this great software. LG.
Created attachment 355040 [details] Log with the error I am on linux and could open the rar file with no problems. John is on OS X. However, I extracted one trace file from the list which clearly shows the error. It seems gnucash attempts to set an invalid time stamp. I have no idea why though.
Some additional information (if it can help): I used the gnucash on my laptop used as a desktop machine. No timezone changes, no clock manual setting. I've tried to restart the computer after this bug was detected. It is not worked. Then, I installed the 2.6.15 - it worked, without restart. So, I think it can't be a windows problem. An other idea: The hungarian date format is special, similar to the ISO format: yyyy.mm.dd. (to me, sometimes it can cause problems in windows, visual basic)
Comment on attachment 355040 [details] Log with the error The problem appears to be that in MySQL we specify that slots.timespec_val is TIMESTAMP and that MySQL 5.7 specifies that TIMESTAMP has a range beginning at 1970-01-01 00:00:01. The code in question is trying to store a 0 timespec value, which is a special value in MySQL, 0000-00-00 00:00:00. Reading those in caused a crash reported on the mailing list, so I fixed it to save 1970-01-01 00:00:00 instead. GnuCash's internal date system can operate from 01-01-01 (1400-01-01 in master). Testing with MySQL 5.5 worked fine: It seems that version had an undocumented "feature" that made the actual range 1970-01-01 00:00:00 and it automatically converted earlier date-times to 0000-00-00 00:00:00. MySQL 5.7 is apparently not so liberal. Even worse, the MySQL TIMSTAMP type has a maximum date of 2038-01-19 03:14:07. We'll need to do a schema change to make all of the date-time fields DATETIME (range 1000-01-01 00:00:00 to 9999-01-01 23:59:59.
I'm having a problem with MySQL 5.6 where gnucash is trying to store 1970/01/01 00:00:00 and MySQL is rejecting it as an invalid value causing the "Unable to save database" if I tried to change or add any transaction. * 11:58:57 CRIT <gnc.backend.dbi> [mysql_error_fn()] DBI error: 1292: Incorrect datetime value: '19700101000000' for co lumn 'reconcile_date' at row 1 Interestingly I received the first save error this week after several years of use. Daylight savings kicked in this week which may have something to do with it. I didn't check the trace before upgrading to Gnucash 2.6.18. As mentioned the TIMESTAMP starts at 1970-01-01 00:00:01 so 1970-01-01 00:00:00 would be invalid right? Even if I try entering 1970-01-01 00:00:01 manually it rejects. Could this be because it is converting from my timezone (+11) to UTC and falling out of the range? Is this part of the same problem?
Sure sounds like it's the same. Until the schema is fixed (which won't be in 2.6.x) I guess versions of MySQL later than 5.5 are not supported, sorry. The manual entry part might be your timezone if you're doing it in GnuCash rather than mysql. Try setting it to 1970-01-01 21:59:00; that will be the same as 1970-01-01 10:59:00Z. That what GnuCash uses for all posted-date times.
Hi All Latest version 2.6.18 same issue. Thanks a lot.
Thanks for the responses. I'm unable to enter anything in GnuCash (manual or otherwise) as if I try and add a new transaction (with a current timestamp) or modify an existing transaction it throws the "Unable to save the database" error and the trace logs show GnuCash is trying to enter '19700101000000' into a timestamp field somewhere each time. I've checked MySQL and it appears the earliest date I can submit is 1970/01/01 10:00:01. I believe this is because my timezone is +10 (ignoring daylight savings) and MySQL converts from local time to UTC which would bring it to 1970/01/01 00:00:01, the documented start range value. What I don't understand is why GnuCash is trying to insert this value. Is this something you did John to fix the problem (you mention a crash report above)? It seems the all zero special value is accepted by MySQL. It would seem '19700101000000' is a documented invalid value but would probably work if your timezone is in the negatives. Is it possible to get it to use 19700101000000 + <TZ>*10000 + 1? This would ensure it gets stored as 19700101000001 UTC which is valid. My appologies if I'm confusing the issue. I don't know what any of these particular TIMESTAMP fields are for or why this value is being stored. It appears to me though GnuCash is doing the wrong thing. I'm no developer though.
*** Bug 788593 has been marked as a duplicate of this bug. ***
I have done some research and found the bug revealed due to a change in mysql. It's a bit confusing how they deal with zero date values from version to version: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date Anyway as a workaround deleting STRICT_TRANS_TABLES from sql_mode in my.cnf can be used to avoid the problem. Here is what I use so far to be able to save to gnucash database: sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION I am using: MySQL Version: 5.7.19 (mysql-community-server) GnuCash Version: 2.6.17 Fedora Kernel: 4.13.4-200.fc26.x86_64
It looks like Johns "fix" is still in the current stable GnuCash. GnuCash is still trying to insert the 1970-01-01 timestamp and as a result anyone in a positive timezone will end up with this database save error due to the invalid timestamp value. For those in the unlucky hemisphere, you can work around this bug by setting your timezone to a negative one (I chose -10 Hawaii) and reboot your system. You can then use GnuCash again. The only catch is some of your dates might be slightly off due to the timezone difference (and your system clock will be wrong). To the developers (who are probably in the lucky hemisphere): The MySQL timestamp value takes into account the local timezone. As the actual date stored is UTC time it converts the local time into UTC before storing. If I try and enter the minimum timestamp it will subtract 10 hours (due to my timezone) and cause the UTC value to be outside the range. This is why it is failing for me and I suspect most of the others. It is probably also why half the world hasn't noticed it. You need to set the slots.timespec_val field to something valid for all timezones. Why not just leave it null if it is not need to store a useful timestamp? All zeroes seems to work in my testing (although someone mentioned this had problems). Maybe I should move to Hawaii. It seems like the most practical solution :-)
*** Bug 790441 has been marked as a duplicate of this bug. ***
This bug also still seems to be in the unstable 2.7.1. I was rather hoping it would at least be fixed there.... Any chance that will happen before 2.7 becomes the stable branch?
(In reply to Gary Bilkus from comment #16) > This bug also still seems to be in the unstable 2.7.1. I was rather hoping > it would at least be fixed there.... > > Any chance that will happen before 2.7 becomes the stable branch? Yes, I hope to have a partial fix for both 2.6.19 and 2.7.2 that will at least handle the NULL time64_t/timespec better, and changing the MySQL schema to use DATETIME is a blocker for releasing 2.8.0.
I want to fix this for 2.6.19 but I can't get it to fail. The only way I can reproduce it is to set NO_ZERO_DATE in sql_mode and disable removing it during session initialization in GnuCash. Otherwise the slots table is populated with plenty of timespec_val = 1970-01-01 00:00:00 and no complaints from mysql. Global sql_mode is set to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION I've tried on both Debian 9 with MariaDB 10.1.26, innodb version 5.6.36-82.1 and Fedora 26 with MySQL 5.7.20 community version from the MySQL Yum Repository.
I have just tried it on Ubuntu 17.10 with the default installs of gnucash (2.6.17) and mysql-server (5.7.20) and having created a new accounts file with the wizard, with default settings, I see the problem as described. I am in timezone UTC.
I can also confirm that the workaround suggested in comment #13 works for me, so add to the end of /etc/mysql/my.cnf # added to get gnucash to go with mysql 5.7 [mysqld] sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
I am sure I fixed a bug that appeared with mysql 5.5 (I think) that was to do with a very similar issue, and I suspect there were some comments in there relating to the fact that issue would re-appear later, but I cannot find the bug. I have not used bugzilla much and am not skilled at using the search feature.
I was wrong about the mysql version on the previous issue, it was https://bugzilla.gnome.org/show_bug.cgi?id=765872 Unable to Save As mysql on Ubuntu 16.04 (mysql-server 5.7.12) Has the same issue recurred?
Is the issue that my patch allowed it to work with zero dates, by automatically removing the NO_ZERO_DATES option for the session, but since then gnucash has moved on and the zero dates are no longer written as 0 but as 1970-01-01. If I am right then perhaps that was not such a good idea.
I think I have confirmed that this is caused by a change in gnucash rather than mysql-server as gnucash 2.6.15 runs ok with mysql-server 5.7.20 (on Ubuntu 17.04) whereas gnucash 2.6.17 requires the removal of STRICT_TRANS_TABLES from sql_mode (on Ubuntu 17.10). If the change in gnucash cannot be reverted then a 'fix' might be to remove STRICT_TRANS_TABLES from the mysql session (rather than in my.cnf) in the same way that NO_ZERO_DATE is removed in pull request https://github.com/Gnucash/gnucash/pull/86
I've pushed the schema change I mentioned in comment 7 to unstable, which we're going to release as 2.7.2 this weekend. The really interesting thing is that the change seems to be utterly transparent. With the change applied and the database converted 2.6.18 was (in my admittedly limited testing) still able to read and write to the database. I'm not totally confident that this is a fix because as I said I can't reproduce it so I'd like someone who can to test it: Load or create a database with 2.7.2, shut down, then open it up with 2.6.18 and make sure that you can create and edit transactions. If it works then I can back-port the schema change for 2.6.19.
Sorry, I am not in a situation where I can build and test it at the moment.
OK. Since a simple work-around seems to exist for 2.6, in the absence of testing I'm going to release 2.6.19 with only the change to handling 0000-00-00 dates and consider it fixed in 2.8.
*** Bug 791851 has been marked as a duplicate of this bug. ***
Hi John Ralls I had been upgrade to version 2.6.19 and test. But same issue. So roll-back to 2.6.16. Thanks a lot.
Neko Chang, did you change your my.cnf as outlined in comment 13?
Hi John Ralls I didn't change my my.cnf as comment 13 before. After added it and v2.6.19 work fine :D Thanks your suggest and fix. A question: The bug mark fixed at v2.8. Means remove the workaround after upgraded to v2.8? Thanks a lot.
Yes, you can remove the workaround after upgrading to v2.8.
Hi John Ralls I understood :D Thanks you a lot.
Some quick feedback which may help others using mysql/gnucash on windows. After upgrading gnucash to version 3.1 I was still unable to save to existing gnucash databases. However, opening the database, saving as xml, opening the xml and saving to mysql gave me a new copy of the database which works fine. Gary
Can you run GnuCash from the commandline with --log gnc.backend.dbi=debug and attempt to open one of your pre-3.0 databases, then attach the resulting trace file? If you can find any related errors in the MySQL log that would be helpful please report them as well.
(In reply to John Ralls from comment #32) > Yes, you can remove the workaround after upgrading to v2.8. Hi John Ralls I upgrade Gnucash from 2.6.21 to 3.1. I removed below sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION workaround from MySQL configure before first time start Gnucash 3.1. After start, Gnucash show error dialog "Can't save to database" when create schedule transaction. Of course, schedule transaction didn't save. PS: The error dialog approximation of meaning, my machine speak Taiwanese :p Before second start Gnucash, I added "--log gnc.backend.dbi=debug" to debug and generated message from below transaction copy test start to finish as below. epopen@epopen[~]% gnucash --log gnc.backend.dbi=debug Found Finance::Quote version 1.47 Gtk-Message: GtkDialog mapped without a transient parent. This is discouraged. epopen@epopen[~]% Only 2 line message. In MySQL log, haven't log about this problem.(Log level: MySQL 5.7 default). Different first time started. I copy a transaction, error dialog show TWICE, but transaction save successful. Copy other transaction, same above. Because error exist, I rollback MySQL workaround. Thanks, Neko.
Neko, Can you check the schema of the transaction table and make sure that GnuCash 3.1 upgraded the TIMESTAMP columns to DATETIME?
(In reply to John Ralls from comment #37) > Neko, > > Can you check the schema of the transaction table and make sure that GnuCash > 3.1 upgraded the TIMESTAMP columns to DATETIME? Hi John Ralls Thanks your reply. But going to work, so can't check right now. Can you provide reference check method for me before check?
use <database>; show columns in transactions;
Hi John Ralls My schema below.. [gnucash]> show columns in transactions; +---------------+---------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------------------+-------+ | guid | varchar(32) | NO | PRI | NULL | | | currency_guid | varchar(32) | NO | | NULL | | | num | varchar(2048) | NO | | NULL | | | post_date | datetime | YES | | 1970-01-01 00:00:00 | | | enter_date | datetime | YES | | 1970-01-01 00:00:00 | | | description | varchar(2048) | YES | | NULL | | +---------------+---------------+------+-----+---------------------+-------+ 6 rows in set (0.50 sec) Time default is 00:00:00, not 00:00:00, it mean won't upgrade? Thanks a lot.
Hi John Ralls I checked other database (never access with Gnucash 3.1,(pre-3.0)). Old schema transactions below. +---------------+---------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------------------+-------+ | guid | varchar(32) | NO | PRI | NULL | | | currency_guid | varchar(32) | NO | | NULL | | | num | varchar(2048) | NO | | NULL | | | post_date | timestamp | YES | MUL | 0000-00-00 00:00:00 | | | enter_date | timestamp | YES | | 0000-00-00 00:00:00 | | | description | varchar(2048) | YES | | NULL | | +---------------+---------------+------+-----+---------------------+-------+ Remove "sql_mode" workaround. Drop and create database and Gnucash 3.1 saved, new schema transactions below. +---------------+---------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------------------+-------+ | guid | varchar(32) | NO | PRI | NULL | | | currency_guid | varchar(32) | NO | | NULL | | | num | varchar(2048) | NO | | NULL | | | post_date | datetime | YES | MUL | 1970-01-01 00:00:00 | | | enter_date | datetime | YES | | 1970-01-01 00:00:00 | | | description | varchar(2048) | YES | | NULL | | +---------------+---------------+------+-----+---------------------+-------+ Close and reopen Gnucash, create new transaction and save work file. The new schema same comment #40, look like upgraded successful. But still error dialog "Can't save to database", other issue exist look like. Thanks a lot.
GnuCash bug tracking has moved to a new Bugzilla host. The new URL for this bug is https://bugs.gnucash.org/show_bug.cgi?id=784623. Please continue processing the bug there and please update any external references or bookmarks.