Unable to convert gnucash to mysql after upgrading to 16.04

[Colin Law]

On 1 May 2016 at 13:55, Colin Law <clanlaw at gmail.com> wrote:
> On 1 May 2016 at 13:49, Colin Law <clanlaw at gmail.com> wrote:
>> On 1 May 2016 at 12:00, Abdd <andrea.beddoni at gmail.com> wrote:
>>> I am a long time gnucash user and after upgrading to ubuntu 16.04 I found
>>> myself with a problem when trying to convert the xml version of the database
>>> to the mysql version.
>>>
>>> I have originally set up the process of transforming the xml gnucash
>>> database into a mysql database following the steps highlighted in  Online
>>> Budget with Gnucash + MySQL + SSH
>>> <http://linuxaria.com/howto/online-budget-with-gnucash-mysql-ssh>   or many
>>> other similar posts.
>>>
>>> The process has been working seamlessly for a while but after upgrading to
>>> 16.04 from 15.10 it stopped working. In particular now when I try to save to
>>> the mysql version I am asked for the Keyring password. After providing the
>>> password and hitting save I receive the following message `The server at URL
>>> mysql://gnucash@localhost/gnucash experienced an error or encountered bad or
>>> corrupt data.` I have also tried to repeat the process creating a test
>>> database from a new gnucash xml file but with the same result.
>>>
>>> I have been unable to diagnose the problem or find a suitable solution
>>> posted somewhere else.
>>
>> I have just discovered this myself, gnucash 2.6.12 on Ubuntu 16.04.  I
>> have been using the beta version of 16.04 and it has been ok, but
>> something has happened recently to make it stop working.
>>
>> Looking at the mysql log it writes some of the database before
>> failing.  I am just about to generate a gnucash trace file to see if
>> it provides more information.
>>
>> I believe that there was recently a major upgrade of mysql-server on
>> 16.04. I am not sure but I suspect it went up to mysql 5.  I am
>> wondering whether there is an incompatibility between gnucash and
>> mysql 5.
>
> Attache is the tracefile.  It starts
> * 12:27:08  CRIT <gnc.backend.dbi> [mysql_error_fn()] DBI error: 1067:
> Invalid default value for 'date'
> * 12:27:08  CRIT <gnc.backend.dbi> [conn_create_table()] Error in
> dbi_result_free() result
> * 12:27:08  CRIT <gnc.backend.dbi> [mysql_error_fn()] DBI error: 1067:
> Invalid default value for 'post_date'
> * 12:27:08  CRIT <gnc.backend.dbi> [conn_create_table()] Error in
> dbi_result_free() result
>
> and goes downhill from there.

I have submitted a bug report with tracefile and mysql log
https://bugzilla.gnome.org/show_bug.cgi?id=765872

Colin