Data Import/Export peculiarities

[jonwimp]

Andrew,

In /etc/my.cnf , do this:

[mysqld]
wait_timeout=60

and you should be able to hit the problem within 60 seconds.

Jonathon

Andrew Sykes wrote:

> Chris, Jonathon,
>
> Thanks for the info.
>
> Is there a Jira issue for this? this definitely needs fixed, if someone
> can give thorough instructions for reproducing (without waiting 8 hours)
> and any thoughts on a solution in Jira, I'll try to look at this next
> week.
>
> - Andrew
>
>
> On Fri, 2007-03-02 at 23:17 +0800, Jonathon -- Improov wrote:
>> Chris is right, it's deprecated.
>>
>> I was mistaken, autoReconnect didn't solve my problems. I used attribute "validationQuery". Works
>> even with autoCommit false. Does OFBiz have something similar? Or is it too MySQL-specific?
>>
>> Jonathon
>>
>> Chris Howe wrote:
>>> Autoreconnect was marked deprecated in mysql's Connector/J (jdbc) in
>>> 3.2 and removed in 3.3
>>>
>>> Jonathon is likely using 3.1.14
>>>
>>> I have very little interest or experience in database features and
>>> couldn't tell you if what comes along in Connector/J 5 is worth the
>>> change or necessary or anything else, except to tell you that
>>> Connector/J 5 is recommended to use with MySql 5.  This is another
>>> reason I'm switching to Postgres (at least for the ERP work).  There
>>> seems to be more people that might have an interest/experience in these
>>> details using Postgres around in the OFBiz community, so any issues
>>> will likely be uncovered before my deployment is affected by it :)
>>>
>>> --- Jonathon -- Improov <[hidden email]> wrote:
>>>
>>>> Andrew,
>>>>
>>>> I can confirm 2 things:
>>>>
>>>> 1. I tested "autoReconnect=true" to work in Tomcat deployment (not
>>>> OFBiz); will
>>>>     get "Communication link failure" after timeout (8 hours in my
>>>> setup)
>>>>     otherwise.
>>>>
>>>> 2. I have never had a similar timeout incident with OFBiz; I'm using
>>>>     "autoReconnect=true" and MySQL.
>>>>
>>>> Jonathon
>>>>
>>>> Andrew Sykes wrote:
>>>>> Chris,
>>>>>
>>>>> I thought the timeout issue was resolved by adding the "?
>>>>> autoReconnect=true" to the jdbc-uri?
>>>>>
>>>>> Interested to hear more...
>>>>>
>>>>> - Andrew
>>>>>
>>>>> On Thu, 2007-03-01 at 15:43 -0800, Chris Howe wrote:
>>>>>> Hey Eric,
>>>>>>
>>>>>> The only technical reason so far was the issue with max_timeout.
>>>> A
>>>>>> default installation connection will timeout after 8 hours of
>>>>>> inactivity and may cause some problems with misses after that 8
>>>> hours.
>>>>>> You can change this to up to 24 days which should alleviate some
>>>>>> issues, but I'm not sure how extensive a test I can do to see if
>>>> there
>>>>>> are any repercussions from doing that.  I'm also not sure there's
>>>> much
>>>>>> momentum to address the issue any time soon.  I know I don't have
>>>> any
>>>>>> momentum in learning about it.  Issues that pop up regarding
>>>> Postgres
>>>>>> specifically, I think would garner a bit more attention.
>>>>>>
>>>>>> Licensing issues were the main driving force though.  After
>>>> reading up
>>>>>> a bit there just seems to be quite a bit of uncertainty
>>>> surrounding
>>>>>> MySql licensing most of it can be gleaned by reading:
>>>>>> http://en.wikipedia.org/wiki/MySQL#License_issues
>>>>>> I'd prefer to not worry about what Oracle and SAP are doing to
>>>> each
>>>>>> other.
>>>>>>
>>>>>> Postgres being BSD and originating from University of California
>>>> seems
>>>>>> a bit safer on the legal front.  We've see a lot of opportunity
>>>> using
>>>>>> OFBiz in our industry and may wish to do something in the future
>>>> and
>>>>>> want to expand our knowledge in areas that keep our options open.
>>>>>>
>>>>>> ,Chris
>>>>>>
>>>>>> --- Eric Crawford <[hidden email]> wrote:
>>>>>>
>>>>>>> Chris, just out of curiosity, what made you decide to move from
>>>> mysql
>>>>>>> to postgres?
>>>>>>>
>>>>>>> On 3/1/07, Chris Howe <[hidden email]> wrote:
>>>>>>>> The error is most likely on this side of the keyboard, but the
>>>>>>>> dummy-fks didn't work for me going from mysql to postgres.  Even
>>>>>>> with
>>>>>>>> it ticked, postgres got mad about referential integrity.  I
>>>> didn't
>>>>>>> dig
>>>>>>>> into it any further, that's going to be one of the things I do
>>>> look
>>>>>>>> into when i set aside some time.
>>>>>>>>
>>>>>>>> I'm just thinking abstractly, wouldn't something like the
>>>> following
>>>>>>>> work for writing to the correct order
>>>>>>>>
>>>>>>>> Start with a HashSet
>>>>>>>>
>>>>>>>> Get Record
>>>>>>>> If has parent
>>>>>>>>  get parent
>>>>>>>>  Is parent in Hashset?
>>>>>>>>  yes->write record
>>>>>>>>  no-> does parent have parent?
>>>>>>>>  ..etc
>>>>>>>> If does not have parent
>>>>>>>>  write record
>>>>>>>>
>>>>>>>>
>>>>>>>> --- "David E. Jones" <[hidden email]> wrote:
>>>>>>>>
>>>>>>>>> On Mar 1, 2007, at 1:57 PM, Chris Howe wrote:
>>>>>>>>>
>>>>>>>>>> 2. Data write/load order for hierarchy fk integrity (parent*Id
>>>>>>> ->
>>>>>>>>> *Id)
>>>>>>>>>
>>>>>>>>>> I think 2 can be addressed pretty well (of course not 100%
>>>> fool
>>>>>>>>> proof)
>>>>>>>>>> if the output file is written in the right order.
>>>>>>>>> This is actually not possible to do, ie sorting a graph with
>>>>>>> loops is
>>>>>>>>> NP-hard.
>>>>>>>>>
>>>>>>>>> That is why we have the dummy-fks thing, which of course should
>>>>>>> ONLY
>>>>>>>>> be used for a case like this where you are sure that there are
>>>> no
>>>>>>> bad
>>>>>>>>> fk records.
>>>>>>>>>
>>>>>>>>> -David
>>>>>>>>>
>>>>>>>>>
>>>

[jonwimp]

Andrew,

Did you get the chance to reproduce the database connection error? Should I use PostgreSQL instead?

There could be a similar, if not same, issue at
http://www.nabble.com/forum/ViewPost.jtp?post=5998171&framed=y . Closed 26 Aug 06 because error
was assumed to be non-reproducible.

If someone will post an issue about this again (old issue not in JIRA anymore), I'll reproduce the
error. Just hit it again this morning.

I thought this issue is critical. But it might only be a problem with MySQL. Seems like everyone
else here is using PostgreSQL? There seems to be a number of errors that can go unnoticed if using
PostgreSQL; I've fixed some.

Jonathon

Jonathon -- Improov wrote:

> Andrew,
>
> In /etc/my.cnf , do this:
>
> [mysqld]
> wait_timeout=60
>
> and you should be able to hit the problem within 60 seconds.
>
> Jonathon
>
> Andrew Sykes wrote:
>> Chris, Jonathon,
>>
>> Thanks for the info.
>>
>> Is there a Jira issue for this? this definitely needs fixed, if someone
>> can give thorough instructions for reproducing (without waiting 8 hours)
>> and any thoughts on a solution in Jira, I'll try to look at this next
>> week.
>>
>> - Andrew
>>
>>
>> On Fri, 2007-03-02 at 23:17 +0800, Jonathon -- Improov wrote:
>>> Chris is right, it's deprecated.
>>>
>>> I was mistaken, autoReconnect didn't solve my problems. I used
>>> attribute "validationQuery". Works even with autoCommit false. Does
>>> OFBiz have something similar? Or is it too MySQL-specific?
>>>
>>> Jonathon
>>>
>>> Chris Howe wrote:
>>>> Autoreconnect was marked deprecated in mysql's Connector/J (jdbc) in
>>>> 3.2 and removed in 3.3
>>>>
>>>> Jonathon is likely using 3.1.14
>>>>
>>>> I have very little interest or experience in database features and
>>>> couldn't tell you if what comes along in Connector/J 5 is worth the
>>>> change or necessary or anything else, except to tell you that
>>>> Connector/J 5 is recommended to use with MySql 5.  This is another
>>>> reason I'm switching to Postgres (at least for the ERP work).  There
>>>> seems to be more people that might have an interest/experience in these
>>>> details using Postgres around in the OFBiz community, so any issues
>>>> will likely be uncovered before my deployment is affected by it :)
>>>>
>>>> --- Jonathon -- Improov <[hidden email]> wrote:
>>>>
>>>>> Andrew,
>>>>>
>>>>> I can confirm 2 things:
>>>>>
>>>>> 1. I tested "autoReconnect=true" to work in Tomcat deployment (not
>>>>> OFBiz); will
>>>>>     get "Communication link failure" after timeout (8 hours in my
>>>>> setup)
>>>>>     otherwise.
>>>>>
>>>>> 2. I have never had a similar timeout incident with OFBiz; I'm using
>>>>>     "autoReconnect=true" and MySQL.
>>>>>
>>>>> Jonathon
>>>>>
>>>>> Andrew Sykes wrote:
>>>>>> Chris,
>>>>>>
>>>>>> I thought the timeout issue was resolved by adding the "?
>>>>>> autoReconnect=true" to the jdbc-uri?
>>>>>>
>>>>>> Interested to hear more...
>>>>>>
>>>>>> - Andrew
>>>>>>
>>>>>> On Thu, 2007-03-01 at 15:43 -0800, Chris Howe wrote:
>>>>>>> Hey Eric,
>>>>>>>
>>>>>>> The only technical reason so far was the issue with max_timeout.
>>>>> A
>>>>>>> default installation connection will timeout after 8 hours of
>>>>>>> inactivity and may cause some problems with misses after that 8
>>>>> hours.
>>>>>>> You can change this to up to 24 days which should alleviate some
>>>>>>> issues, but I'm not sure how extensive a test I can do to see if
>>>>> there
>>>>>>> are any repercussions from doing that.  I'm also not sure there's
>>>>> much
>>>>>>> momentum to address the issue any time soon.  I know I don't have
>>>>> any
>>>>>>> momentum in learning about it.  Issues that pop up regarding
>>>>> Postgres
>>>>>>> specifically, I think would garner a bit more attention.
>>>>>>>
>>>>>>> Licensing issues were the main driving force though.  After
>>>>> reading up
>>>>>>> a bit there just seems to be quite a bit of uncertainty
>>>>> surrounding
>>>>>>> MySql licensing most of it can be gleaned by reading:
>>>>>>> http://en.wikipedia.org/wiki/MySQL#License_issues
>>>>>>> I'd prefer to not worry about what Oracle and SAP are doing to
>>>>> each
>>>>>>> other.
>>>>>>>
>>>>>>> Postgres being BSD and originating from University of California
>>>>> seems
>>>>>>> a bit safer on the legal front.  We've see a lot of opportunity
>>>>> using
>>>>>>> OFBiz in our industry and may wish to do something in the future
>>>>> and
>>>>>>> want to expand our knowledge in areas that keep our options open.
>>>>>>>
>>>>>>> ,Chris
>>>>>>>
>>>>>>> --- Eric Crawford <[hidden email]> wrote:
>>>>>>>
>>>>>>>> Chris, just out of curiosity, what made you decide to move from
>>>>> mysql
>>>>>>>> to postgres?
>>>>>>>>
>>>>>>>> On 3/1/07, Chris Howe <[hidden email]> wrote:
>>>>>>>>> The error is most likely on this side of the keyboard, but the
>>>>>>>>> dummy-fks didn't work for me going from mysql to postgres.  Even
>>>>>>>> with
>>>>>>>>> it ticked, postgres got mad about referential integrity.  I
>>>>> didn't
>>>>>>>> dig
>>>>>>>>> into it any further, that's going to be one of the things I do
>>>>> look
>>>>>>>>> into when i set aside some time.
>>>>>>>>>
>>>>>>>>> I'm just thinking abstractly, wouldn't something like the
>>>>> following
>>>>>>>>> work for writing to the correct order
>>>>>>>>>
>>>>>>>>> Start with a HashSet
>>>>>>>>>
>>>>>>>>> Get Record
>>>>>>>>> If has parent
>>>>>>>>>  get parent
>>>>>>>>>  Is parent in Hashset?
>>>>>>>>>  yes->write record
>>>>>>>>>  no-> does parent have parent?
>>>>>>>>>  ..etc
>>>>>>>>> If does not have parent
>>>>>>>>>  write record
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --- "David E. Jones" <[hidden email]> wrote:
>>>>>>>>>
>>>>>>>>>> On Mar 1, 2007, at 1:57 PM, Chris Howe wrote:
>>>>>>>>>>
>>>>>>>>>>> 2. Data write/load order for hierarchy fk integrity (parent*Id
>>>>>>>> ->
>>>>>>>>>> *Id)
>>>>>>>>>>
>>>>>>>>>>> I think 2 can be addressed pretty well (of course not 100%
>>>>> fool
>>>>>>>>>> proof)
>>>>>>>>>>> if the output file is written in the right order.
>>>>>>>>>> This is actually not possible to do, ie sorting a graph with
>>>>>>>> loops is
>>>>>>>>>> NP-hard.
>>>>>>>>>>
>>>>>>>>>> That is why we have the dummy-fks thing, which of course should
>>>>>>>> ONLY
>>>>>>>>>> be used for a case like this where you are sure that there are
>>>>> no
>>>>>>>> bad
>>>>>>>>>> fk records.
>>>>>>>>>>
>>>>>>>>>> -David
>>>>>>>>>>
>>>>>>>>>>
>>>>
>
>

[Andrew Sykes]

Jonathon,

No I never did get around to this one, hopefully I will eventually. Or
even better, someone will beat me to it! ;-)

- Andrew

On Wed, 2007-03-21 at 11:10 +0800, Jonathon -- Improov wrote:

> Andrew,
>
> Did you get the chance to reproduce the database connection error? Should I use PostgreSQL instead?
>
> There could be a similar, if not same, issue at
> http://www.nabble.com/forum/ViewPost.jtp?post=5998171&framed=y . Closed 26 Aug 06 because error
> was assumed to be non-reproducible.
>
> If someone will post an issue about this again (old issue not in JIRA anymore), I'll reproduce the
> error. Just hit it again this morning.
>
> I thought this issue is critical. But it might only be a problem with MySQL. Seems like everyone
> else here is using PostgreSQL? There seems to be a number of errors that can go unnoticed if using
> PostgreSQL; I've fixed some.
>
> Jonathon
>
> Jonathon -- Improov wrote:
> > Andrew,
> >
> > In /etc/my.cnf , do this:
> >
> > [mysqld]
> > wait_timeout=60
> >
> > and you should be able to hit the problem within 60 seconds.
> >
> > Jonathon
> >
> > Andrew Sykes wrote:
> >> Chris, Jonathon,
> >>
> >> Thanks for the info.
> >>
> >> Is there a Jira issue for this? this definitely needs fixed, if someone
> >> can give thorough instructions for reproducing (without waiting 8 hours)
> >> and any thoughts on a solution in Jira, I'll try to look at this next
> >> week.
> >>
> >> - Andrew
> >>
> >>
> >> On Fri, 2007-03-02 at 23:17 +0800, Jonathon -- Improov wrote:
> >>> Chris is right, it's deprecated.
> >>>
> >>> I was mistaken, autoReconnect didn't solve my problems. I used
> >>> attribute "validationQuery". Works even with autoCommit false. Does
> >>> OFBiz have something similar? Or is it too MySQL-specific?
> >>>
> >>> Jonathon
> >>>
> >>> Chris Howe wrote:
> >>>> Autoreconnect was marked deprecated in mysql's Connector/J (jdbc) in
> >>>> 3.2 and removed in 3.3
> >>>>
> >>>> Jonathon is likely using 3.1.14
> >>>>
> >>>> I have very little interest or experience in database features and
> >>>> couldn't tell you if what comes along in Connector/J 5 is worth the
> >>>> change or necessary or anything else, except to tell you that
> >>>> Connector/J 5 is recommended to use with MySql 5.  This is another
> >>>> reason I'm switching to Postgres (at least for the ERP work).  There
> >>>> seems to be more people that might have an interest/experience in these
> >>>> details using Postgres around in the OFBiz community, so any issues
> >>>> will likely be uncovered before my deployment is affected by it :)
> >>>>
> >>>> --- Jonathon -- Improov <[hidden email]> wrote:
> >>>>
> >>>>> Andrew,
> >>>>>
> >>>>> I can confirm 2 things:
> >>>>>
> >>>>> 1. I tested "autoReconnect=true" to work in Tomcat deployment (not
> >>>>> OFBiz); will
> >>>>>     get "Communication link failure" after timeout (8 hours in my
> >>>>> setup)
> >>>>>     otherwise.
> >>>>>
> >>>>> 2. I have never had a similar timeout incident with OFBiz; I'm using
> >>>>>     "autoReconnect=true" and MySQL.
> >>>>>
> >>>>> Jonathon
> >>>>>
> >>>>> Andrew Sykes wrote:
> >>>>>> Chris,
> >>>>>>
> >>>>>> I thought the timeout issue was resolved by adding the "?
> >>>>>> autoReconnect=true" to the jdbc-uri?
> >>>>>>
> >>>>>> Interested to hear more...
> >>>>>>
> >>>>>> - Andrew
> >>>>>>
> >>>>>> On Thu, 2007-03-01 at 15:43 -0800, Chris Howe wrote:
> >>>>>>> Hey Eric,
> >>>>>>>
> >>>>>>> The only technical reason so far was the issue with max_timeout.
> >>>>> A
> >>>>>>> default installation connection will timeout after 8 hours of
> >>>>>>> inactivity and may cause some problems with misses after that 8
> >>>>> hours.
> >>>>>>> You can change this to up to 24 days which should alleviate some
> >>>>>>> issues, but I'm not sure how extensive a test I can do to see if
> >>>>> there
> >>>>>>> are any repercussions from doing that.  I'm also not sure there's
> >>>>> much
> >>>>>>> momentum to address the issue any time soon.  I know I don't have
> >>>>> any
> >>>>>>> momentum in learning about it.  Issues that pop up regarding
> >>>>> Postgres
> >>>>>>> specifically, I think would garner a bit more attention.
> >>>>>>>
> >>>>>>> Licensing issues were the main driving force though.  After
> >>>>> reading up
> >>>>>>> a bit there just seems to be quite a bit of uncertainty
> >>>>> surrounding
> >>>>>>> MySql licensing most of it can be gleaned by reading:
> >>>>>>> http://en.wikipedia.org/wiki/MySQL#License_issues
> >>>>>>> I'd prefer to not worry about what Oracle and SAP are doing to
> >>>>> each
> >>>>>>> other.
> >>>>>>>
> >>>>>>> Postgres being BSD and originating from University of California
> >>>>> seems
> >>>>>>> a bit safer on the legal front.  We've see a lot of opportunity
> >>>>> using
> >>>>>>> OFBiz in our industry and may wish to do something in the future
> >>>>> and
> >>>>>>> want to expand our knowledge in areas that keep our options open.
> >>>>>>>
> >>>>>>> ,Chris
> >>>>>>>
> >>>>>>> --- Eric Crawford <[hidden email]> wrote:
> >>>>>>>
> >>>>>>>> Chris, just out of curiosity, what made you decide to move from
> >>>>> mysql
> >>>>>>>> to postgres?
> >>>>>>>>
> >>>>>>>> On 3/1/07, Chris Howe <[hidden email]> wrote:
> >>>>>>>>> The error is most likely on this side of the keyboard, but the
> >>>>>>>>> dummy-fks didn't work for me going from mysql to postgres.  Even
> >>>>>>>> with
> >>>>>>>>> it ticked, postgres got mad about referential integrity.  I
> >>>>> didn't
> >>>>>>>> dig
> >>>>>>>>> into it any further, that's going to be one of the things I do
> >>>>> look
> >>>>>>>>> into when i set aside some time.
> >>>>>>>>>
> >>>>>>>>> I'm just thinking abstractly, wouldn't something like the
> >>>>> following
> >>>>>>>>> work for writing to the correct order
> >>>>>>>>>
> >>>>>>>>> Start with a HashSet
> >>>>>>>>>
> >>>>>>>>> Get Record
> >>>>>>>>> If has parent
> >>>>>>>>>  get parent
> >>>>>>>>>  Is parent in Hashset?
> >>>>>>>>>  yes->write record
> >>>>>>>>>  no-> does parent have parent?
> >>>>>>>>>  ..etc
> >>>>>>>>> If does not have parent
> >>>>>>>>>  write record
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>> --- "David E. Jones" <[hidden email]> wrote:
> >>>>>>>>>
> >>>>>>>>>> On Mar 1, 2007, at 1:57 PM, Chris Howe wrote:
> >>>>>>>>>>
> >>>>>>>>>>> 2. Data write/load order for hierarchy fk integrity (parent*Id
> >>>>>>>> ->
> >>>>>>>>>> *Id)
> >>>>>>>>>>
> >>>>>>>>>>> I think 2 can be addressed pretty well (of course not 100%
> >>>>> fool
> >>>>>>>>>> proof)
> >>>>>>>>>>> if the output file is written in the right order.
> >>>>>>>>>> This is actually not possible to do, ie sorting a graph with
> >>>>>>>> loops is
> >>>>>>>>>> NP-hard.
> >>>>>>>>>>
> >>>>>>>>>> That is why we have the dummy-fks thing, which of course should
> >>>>>>>> ONLY
> >>>>>>>>>> be used for a case like this where you are sure that there are
> >>>>> no
> >>>>>>>> bad
> >>>>>>>>>> fk records.
> >>>>>>>>>>
> >>>>>>>>>> -David
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>
> >
> >
>

--
Kind Regards
Andrew Sykes <[hidden email]>
Sykes Development Ltd
http://www.sykesdevelopment.com