Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
On Tuesday, 17 October 2017 14:24:58 BST Joerg Diederich wrote: > Hi Maf, > > just to inform you. Your assumption was totally correct. After fixing the > date in the xml file I succeeded in importing the data into my mysql > database. Many thanks to you and all others who friendly shared their > thoughts concerning my issue. I'm still wondering a little how I could > encounter a Windows bug in my pure Linux installation. > I could be wrong about it being a windows-only bug! But glad that it got you going on the correct path to a fix. Maf. ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
Hi Maf, just to inform you. Your assumption was totally correct. After fixing the date in the xml file I succeeded in importing the data into my mysql database. Many thanks to you and all others who friendly shared their thoughts concerning my issue. I'm still wondering a little how I could encounter a Windows bug in my pure Linux installation. Joerg On Mon, 16 Oct 2017 22:43:28 +0200 Joerg Diederich wrote: > Hi Maf, > > thank you very much for this hint. I guess it points into the right > direction. I've just checked my xml file and indeed, there is an entry with > such a strange date. I will try to fix it as suggested and look, if I can > save the data into my mysql database. > > Joerg > > > On Mon, 16 Oct 2017 20:46:14 +0100 > "Maf. King" wrote: > > > On Monday, 16 October 2017 17:27:13 BST Fred Bone wrote: > > > > > > > > A date in 1898 (as reported) looks more like a data error. > > > > I know the OP mentioned Linux - but isn't that date something to with a > > (fixed/ > > resolved) windows bug creating transactions from "before time existed" > > Just > > rings a distant bell with me... > > > > Maybe a grep of the xml file for something like the problem timestamp and > > fixing > > the data manually? A postdate won't be visible in the registers anywhere > > AFAIK. > > > > Back up the data file first then back it up again... > > > > 0.02 > > Maf. > > > > > > > > ___ > > gnucash-user mailing list > > gnucash-user@gnucash.org > > https://lists.gnucash.org/mailman/listinfo/gnucash-user > > - > > Please remember to CC this list on all your replies. > > You can do this by using Reply-To-List or Reply-All. > > > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-user > - > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. > ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
Hi Maf, thank you very much for this hint. I guess it points into the right direction. I've just checked my xml file and indeed, there is an entry with such a strange date. I will try to fix it as suggested and look, if I can save the data into my mysql database. Joerg On Mon, 16 Oct 2017 20:46:14 +0100 "Maf. King" wrote: > On Monday, 16 October 2017 17:27:13 BST Fred Bone wrote: > > > > > A date in 1898 (as reported) looks more like a data error. > > I know the OP mentioned Linux - but isn't that date something to with a > (fixed/ > resolved) windows bug creating transactions from "before time existed" Just > rings a distant bell with me... > > Maybe a grep of the xml file for something like the problem timestamp and > fixing > the data manually? A postdate won't be visible in the registers anywhere > AFAIK. > > Back up the data file first then back it up again... > > 0.02 > Maf. > > > > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-user > - > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. > ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
On Monday, 16 October 2017 17:27:13 BST Fred Bone wrote: > > A date in 1898 (as reported) looks more like a data error. I know the OP mentioned Linux - but isn't that date something to with a (fixed/ resolved) windows bug creating transactions from "before time existed" Just rings a distant bell with me... Maybe a grep of the xml file for something like the problem timestamp and fixing the data manually? A postdate won't be visible in the registers anywhere AFAIK. Back up the data file first then back it up again... 0.02 Maf. ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
On 16 October 2017 at 7:27, John Ralls said: > > > > On Oct 16, 2017, at 2:50 AM, joerg_dieder...@freenet.de wrote: > > > > Hello, > > > > I'm trying to convert my existing gnucash account from xml to mysql. I > > created a new mysql database, but when I try to save my data into this > > database with gnucash I get the following error message: > > > > <--- cut here ---> > > mysql error: Incorrect datetime value: '18981201105900' for column > > 'post_date' at row 1 <--- cut here ---> > > > > In another thread I found the recommendation to adjust the sql_mode > > using > > > > sql_mode = > > ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISIO > > N_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > > > > Unfortunately, this doesn't change the behaviour for me. > > > > > > Here are some version information: > > > > Operating System: Linux opensuse LEAP 42.2 > > gnucash: gnucash-2.6.16-3.3.1.x86_64 > > mysql: mariadb-10.0.31-20.7.1.x86_64 > > > > > > Any hint how to get rid off this error is really appreciated. > > We need to change the schema for MySQL/MariaDB to use DATETIME instead of > TIMESTAMP; the latter’s date range is only 1 Jan 1970-15 Jun 2038. > It’s on my to-do list for 2.8. > > For 2.6 you’ll need to use Postgresql instead if you need your books to > run before 1970. A date in 1898 (as reported) looks more like a data error. ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
Hi Colin, thank you very much for the quick response to my post. I dropped the database and let gnucash create it. But I get the same error message again. Joerg On Mon, 16 Oct 2017 14:46:31 +0100 Colin Law wrote: > On 16 October 2017 at 10:50, wrote: > > Hello, > > > > I'm trying to convert my existing gnucash account from xml to mysql. I > > created a new mysql database, but when I try to save my data into this > > database with gnucash I get the following error message: > > Don't manually create the database first. Just use File > Save As and > give it a database name. You can overwrite an existing gnucash > database in this way but don't try to overwrite an existing db unless > it was created by gnucash. I am not guaranteeing that this is the > problem, but try that first. > > > > > Operating System: Linux opensuse LEAP 42.2 > > gnucash: gnucash-2.6.16-3.3.1.x86_64 > > mysql: mariadb-10.0.31-20.7.1.x86_64 > > I don't know whether anyone has tried it with mariadb, but it should > be ok as maria is supposed to be a dropin replacement for mysql. > > Colin > ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
> On Oct 16, 2017, at 2:50 AM, joerg_dieder...@freenet.de wrote: > > Hello, > > I'm trying to convert my existing gnucash account from xml to mysql. I > created a new mysql database, but when I try to save my data into this > database with gnucash I get the following error message: > > <--- cut here ---> > mysql error: Incorrect datetime value: '18981201105900' for column > 'post_date' at row 1 > <--- cut here ---> > > In another thread I found the recommendation to adjust the sql_mode using > > 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 > > Unfortunately, this doesn't change the behaviour for me. > > > Here are some version information: > > Operating System: Linux opensuse LEAP 42.2 > gnucash: gnucash-2.6.16-3.3.1.x86_64 > mysql: mariadb-10.0.31-20.7.1.x86_64 > > > Any hint how to get rid off this error is really appreciated. We need to change the schema for MySQL/MariaDB to use DATETIME instead of TIMESTAMP; the latter’s date range is only 1 Jan 1970-15 Jun 2038. It’s on my to-do list for 2.8. For 2.6 you’ll need to use Postgresql instead if you need your books to run before 1970. Regards, John Ralls ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: gnucash and mysql: Incorrect datetime value for column 'post_date'
On 16 October 2017 at 10:50, wrote: > Hello, > > I'm trying to convert my existing gnucash account from xml to mysql. I > created a new mysql database, but when I try to save my data into this > database with gnucash I get the following error message: Don't manually create the database first. Just use File > Save As and give it a database name. You can overwrite an existing gnucash database in this way but don't try to overwrite an existing db unless it was created by gnucash. I am not guaranteeing that this is the problem, but try that first. > > Operating System: Linux opensuse LEAP 42.2 > gnucash: gnucash-2.6.16-3.3.1.x86_64 > mysql: mariadb-10.0.31-20.7.1.x86_64 I don't know whether anyone has tried it with mariadb, but it should be ok as maria is supposed to be a dropin replacement for mysql. Colin ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
gnucash and mysql: Incorrect datetime value for column 'post_date'
Hello, I'm trying to convert my existing gnucash account from xml to mysql. I created a new mysql database, but when I try to save my data into this database with gnucash I get the following error message: <--- cut here ---> mysql error: Incorrect datetime value: '18981201105900' for column 'post_date' at row 1 <--- cut here ---> In another thread I found the recommendation to adjust the sql_mode using 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 Unfortunately, this doesn't change the behaviour for me. Here are some version information: Operating System: Linux opensuse LEAP 42.2 gnucash: gnucash-2.6.16-3.3.1.x86_64 mysql: mariadb-10.0.31-20.7.1.x86_64 Any hint how to get rid off this error is really appreciated. Regards, Joerg Mit TravelXtra profitieren Sie von 5% Rückvergütung auf den Reisepreis – bekannte dt. Reiseanbieter und ein umfangreiches Reiseangebot wie im Reisebüro! https://email.freenetde/reisen/index.html ___ gnucash-user mailing list gnucash-user@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.