GNOME Bugzilla – Bug 765872
Unable to Save As mysql on Ubuntu 16.04 (mysql-server 5.7.12)
Last modified: 2018-06-29 23:48:54 UTC
Created attachment 327109 [details] Gnucash trace file Recently (possibly following mysql server update) I can no longer Save As mysql. It fails with server experienced an error or encountered bad or corrupt data.
I should have said, mysql-server is version 5.7.12. Also I get the same problem if I build 2.6.12 from source.
Created attachment 327110 [details] MYSQL log
I believe that mysql on the machine was upgraded recently from 5.6 to 5.7, possibly that is relevant.
Further information. I have tried saving (from the machine showing the issue) to a mysql server on another machine, which is running mysql 5.1 and that saves ok.
Does configuring the server to not have the NO_ZERO_DATE setting as explained here http://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field fix the problem?
Yes, ensuring NO_ZERO_DATE is not set fixes the problem. I had discounted that as the question was from four years ago. Obviously I should not have done so. My config did not setup NO_ZERO_DATE explicitly so I assume it is the default. Is that the change that is introduced with mysql 5.7?
You've been around long enough to know the rule: Server administration is out of scope for GnuCash support. I don't know what the defaults are or were, if or when they changed, or who changed them, and I'm not interested in doing that research for you.
Initially I agreed with you John, but having looked more carefully at a description of the changes made in mysql 5.7 [1] I am not entirely sure. If I understand correctly the ability to allow zero dates is deprecated and will be removed at some point. In particular it says "The long term plan is still to have the three affected modes be included in strict SQL mode and to remove them as explicit modes in a future MySQL release. But to restore compatibility in MySQL 5.7 with MySQL 5.6 strict mode and to provide additional time for affected applications to be modified, the following changes were made in MySQL 5.7.8:". If that is the case (and it is entirely possible that I have missed something) then at some point GC will have to be modified to not rely on zero dates. [1] http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes
I think you misunderstood that section, it's about "strict mode". So at some point it may not be possible to run GnuCash in strict mode because one won't be able to turn off NO_ZERO_DATES by itself. It will take some compatibility-breaking changes to deal with not storing NULL in date fields. That means that it can't happen in the 2.6 series, but it's something I'll keep in mind for 2.8.
Did you mean NULL? I thought the problem was a zero date value rather than null. I believe that strict mode is the recommended mode. I don't think there is a way for the DBA to specify sql_mode on a per application basis, so as it stands it is necessary to change the setting for all databases, which is not ideal. It is, however, possible for the application to specify a mode for the session, so a possible solution for the short term is for Gnucash to do that.
It's NULL in C code, which is defined to 0 per the C standard. AFAIK that's also the case for SQL; there's no special "NULL" value that I'm aware of. It seems odd to me that one could override that on a per-session basis: That would mean that a session could write zero dates in one session and it would be valid, but a subsequent session could read and write back that record and get an error. The current situation works for 2.6. If the MySQL folks release a future version where NO_ZERO_DATE is enforced for strict mode then we'll just have to tell people that MySQL isn't supported on GnuCash 2.6.x for that version and later. FWIW neither MariaDB nor Postgresql seem to have this problem.
There is certainly a difference between a null field and 0 in an sql database. See http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html
A workaround is to remove the NO_ZERO_DATE option from the sql options on the server. That is add to the [mysqld] section of /etc/mysql/mysql.conf.d/mysqld.cnf the line sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Note that is a single line. Then restart the server sudo systemctl restart mysql
See Pull Request https://github.com/Gnucash/gnucash/pull/85
For completeness, it's also worth mentioning that removing NO_ZERO_DATE from the sql_mode options will not help in mysql versions 5.7.5 to 5.7.7. This was resolved in 5.7.8, until it will be removed for good from a currently undefined future mysql release. So if someone stumbles on this while using the mentioned mysql versions, their only options are to - switch to another mysql version, or - abandon strict mode. That is until gnucash fixes its db schema at some point (likely for 2.8.x).
See pull request https://github.com/Gnucash/gnucash/pull/86 for the patch that works around around the issue.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=765872. Please update any external references or bookmarks.