Re: gnucash and mysql: Incorrect datetime value for column 'post_date'

[Maf. King]

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'

[Joerg Diederich]

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'

[Joerg Diederich]

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'

[Maf. King]

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'

[Joerg Diederich]

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'

[John Ralls]

> 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'

[Colin Law]

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.