Data Import/Export peculiarities

[cjhowe]

I've recently been working on a mysql to postgres migration and it's
been quite bumpy.  I'm going to set aside some time to improve some of
the import/export webtools items and wanted to know if anyone else had
come across additional annoyances that I can be on the lookout to
address.  Here are two that I'd like to specifically address.

1. Case sensitive fk integrity check
2. Data write/load order for hierarchy fk integrity (parent*Id -> *Id)


I think 1 is generally only an issue when going from a mysql (which
seems a bit more relaxed) to a stricter (postgres) database.  I'm sure
there are settings.
I think 2 can be addressed pretty well (of course not 100% fool proof)
if the output file is written in the right order.

If anyone has some more annoyances that I can keep an eye out for when
I go to work on this or any thoughts to add on these  two, I would
appreciate it.  

,Chris

[Adrian Crum]

I was planning on working on that component too. I'd like to get rid of the JSPs
and eliminate the frames in the entity reference section.


Chris Howe wrote:

> I've recently been working on a mysql to postgres migration and it's
> been quite bumpy.  I'm going to set aside some time to improve some of
> the import/export webtools items and wanted to know if anyone else had
> come across additional annoyances that I can be on the lookout to
> address.  Here are two that I'd like to specifically address.
>
> 1. Case sensitive fk integrity check
> 2. Data write/load order for hierarchy fk integrity (parent*Id -> *Id)
>
>
> I think 1 is generally only an issue when going from a mysql (which
> seems a bit more relaxed) to a stricter (postgres) database.  I'm sure
> there are settings.
> I think 2 can be addressed pretty well (of course not 100% fool proof)
> if the output file is written in the right order.
>
> If anyone has some more annoyances that I can keep an eye out for when
> I go to work on this or any thoughts to add on these  two, I would
> appreciate it.  
>
> ,Chris
>

[David E Jones]

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

[David E Jones]

On Mar 1, 2007, at 2:15 PM, Adrian Crum wrote:

> I was planning on working on that component too. I'd like to get  
> rid of the JSPs and eliminate the frames in the entity reference  
> section.

It would be great to get those using the screen widget and such.

For the entity ref pages, why eliminate the frames? How else would we  
accomplish what it does?

-David

[Adrian Crum]

David E. Jones wrote:

>
> On Mar 1, 2007, at 2:15 PM, Adrian Crum wrote:
>
>> I was planning on working on that component too. I'd like to get  rid
>> of the JSPs and eliminate the frames in the entity reference  section.
>
>
> It would be great to get those using the screen widget and such.
>
> For the entity ref pages, why eliminate the frames? How else would we  
> accomplish what it does?
>
> -David

I was picturing a tree widget in the left column. As you expand the tree, the
right column "zooms in" on the corresponding entities.

[cjhowe]

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

[Eric Crawford]

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

[cjhowe]

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

[Daniel Kunkel]

Chris...

Another way I've thought about doing this is to "distructively widdle
down" a directory import.

The idea is to load as many records as possible into the database, and
delete them from the import directory as they are imported. The system
already does this on a file level, but I was thinking that doing it on a
record level would take care of all your issues, and leave someone with
a much smaller mess to clean up with the "hopefully few" records that
are left that have problems.

Optionally going another step... It would be great if it even could
handle "circular references." An implementation scheme that might work
would do it by working backwards, moving each single record that causes
a problem to a new directory to be re-attempted the next iteration.
Hopefully, when you the right combination of problem records are
removed, the circular reference will be accepted in one lump commit.

Daniel


On Thu, 2007-03-01 at 14:23 -0800, Chris Howe 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
> >
> >
>

--
Daniel

*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
Have a GREAT Day!

Daniel Kunkel           [hidden email]
BioWaves, LLC           http://www.BioWaves.com
14150 NE 20th St. Suite F1
Bellevue, WA 98007
800-734-3588    425-895-0050
http://www.Apartment-Pets.com  http://www.Illusion-Optical.com
http://www.Card-Offer.com      http://www.RackWine.com
http://www.JokesBlonde.com     http://www.Brain-Fun.com 
*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-

[cjhowe]

Daniel,

I like it.  In addition maybe have an option to write the good records
to one file and the bad records to another...Follow Andrew's recent
counting pattern and retry both files with the good ones first. I'll
probably put some time away for it next weekend.


--- Daniel Kunkel <[hidden email]> wrote:

> Chris...
>
> Another way I've thought about doing this is to "distructively widdle
> down" a directory import.
>
> The idea is to load as many records as possible into the database,
> and
> delete them from the import directory as they are imported. The
> system
> already does this on a file level, but I was thinking that doing it
> on a
> record level would take care of all your issues, and leave someone
> with
> a much smaller mess to clean up with the "hopefully few" records that
> are left that have problems.
>
> Optionally going another step... It would be great if it even could
> handle "circular references." An implementation scheme that might
> work
> would do it by working backwards, moving each single record that
> causes
> a problem to a new directory to be re-attempted the next iteration.
> Hopefully, when you the right combination of problem records are
> removed, the circular reference will be accepted in one lump commit.
>
> Daniel
>
>
> On Thu, 2007-03-01 at 14:23 -0800, Chris Howe 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
> > >
> > >
> >
> --
> Daniel
>
> *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> Have a GREAT Day!
>
> Daniel Kunkel           [hidden email]
> BioWaves, LLC           http://www.BioWaves.com
> 14150 NE 20th St. Suite F1
> Bellevue, WA 98007
> 800-734-3588    425-895-0050
> http://www.Apartment-Pets.com  http://www.Illusion-Optical.com
> http://www.Card-Offer.com      http://www.RackWine.com
> http://www.JokesBlonde.com     http://www.Brain-Fun.com 
> *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
>
>

[Daniel Kunkel]

Thanks. Great.

Just to be sure I've communicated what I had in mind clearly, I'll say I
didn't think you'd ever retry the good records...  as they shouldn't
need to be retried because they've made it into the database. However,
Saving the good records somewhere  is a good idea since someone is bound
to get bit from a "disappearing" import. Perhaps the best solution is to
write them to a new file, *.xml.done as each record is successfully
imported. Anyway, I think you got the main jist...  widdle down the
import so it's easy to find and fix those nagging leftover imports that
fail.

Daniel


On Thu, 2007-03-01 at 20:11 -0800, Chris Howe wrote:

> Daniel,
>
> I like it.  In addition maybe have an option to write the good records
> to one file and the bad records to another...Follow Andrew's recent
> counting pattern and retry both files with the good ones first. I'll
> probably put some time away for it next weekend.
>
>
> --- Daniel Kunkel <[hidden email]> wrote:
>
> > Chris...
> >
> > Another way I've thought about doing this is to "distructively widdle
> > down" a directory import.
> >
> > The idea is to load as many records as possible into the database,
> > and
> > delete them from the import directory as they are imported. The
> > system
> > already does this on a file level, but I was thinking that doing it
> > on a
> > record level would take care of all your issues, and leave someone
> > with
> > a much smaller mess to clean up with the "hopefully few" records that
> > are left that have problems.
> >
> > Optionally going another step... It would be great if it even could
> > handle "circular references." An implementation scheme that might
> > work
> > would do it by working backwards, moving each single record that
> > causes
> > a problem to a new directory to be re-attempted the next iteration.
> > Hopefully, when you the right combination of problem records are
> > removed, the circular reference will be accepted in one lump commit.
> >
> > Daniel
> >
> >
> > On Thu, 2007-03-01 at 14:23 -0800, Chris Howe 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
> > > >
> > > >
> > >
> > --
> > Daniel
> >
> > *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> > Have a GREAT Day!
> >
> > Daniel Kunkel           [hidden email]
> > BioWaves, LLC           http://www.BioWaves.com
> > 14150 NE 20th St. Suite F1
> > Bellevue, WA 98007
> > 800-734-3588    425-895-0050
> > http://www.Apartment-Pets.com  http://www.Illusion-Optical.com
> > http://www.Card-Offer.com      http://www.RackWine.com
> > http://www.JokesBlonde.com     http://www.Brain-Fun.com 
> > *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> >
> >
>

[cjhowe]

It's been my limited experience that if you have a failure in your
datafile, you may need to make adjustments to all your records in the
batch; good records and bad records.  That they should have been
entered as a group.  So, if one record fails, you'll likely still want
the entire datafile to fail, but seperate the failed records so that
you can track down the error(s) quickly.


--- Daniel Kunkel <[hidden email]> wrote:

> Thanks. Great.
>
> Just to be sure I've communicated what I had in mind clearly, I'll
> say I
> didn't think you'd ever retry the good records...  as they shouldn't
> need to be retried because they've made it into the database.
> However,
> Saving the good records somewhere  is a good idea since someone is
> bound
> to get bit from a "disappearing" import. Perhaps the best solution is
> to
> write them to a new file, *.xml.done as each record is successfully
> imported. Anyway, I think you got the main jist...  widdle down the
> import so it's easy to find and fix those nagging leftover imports
> that
> fail.
>
> Daniel
>
>
> On Thu, 2007-03-01 at 20:11 -0800, Chris Howe wrote:
> > Daniel,
> >
> > I like it.  In addition maybe have an option to write the good
> records
> > to one file and the bad records to another...Follow Andrew's recent
> > counting pattern and retry both files with the good ones first.
> I'll
> > probably put some time away for it next weekend.
> >
> >
> > --- Daniel Kunkel <[hidden email]> wrote:
> >
> > > Chris...
> > >
> > > Another way I've thought about doing this is to "distructively
> widdle
> > > down" a directory import.
> > >
> > > The idea is to load as many records as possible into the
> database,
> > > and
> > > delete them from the import directory as they are imported. The
> > > system
> > > already does this on a file level, but I was thinking that doing
> it
> > > on a
> > > record level would take care of all your issues, and leave
> someone
> > > with
> > > a much smaller mess to clean up with the "hopefully few" records
> that
> > > are left that have problems.
> > >
> > > Optionally going another step... It would be great if it even
> could
> > > handle "circular references." An implementation scheme that might
> > > work
> > > would do it by working backwards, moving each single record that
> > > causes
> > > a problem to a new directory to be re-attempted the next
> iteration.
> > > Hopefully, when you the right combination of problem records are
> > > removed, the circular reference will be accepted in one lump
> commit.
> > >
> > > Daniel
> > >
> > >
> > > On Thu, 2007-03-01 at 14:23 -0800, Chris Howe 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
> > > > >
> > > > >
> > > >
> > > --
> > > Daniel
> > >
> > > *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> > > Have a GREAT Day!
> > >
> > > Daniel Kunkel           [hidden email]
> > > BioWaves, LLC           http://www.BioWaves.com
> > > 14150 NE 20th St. Suite F1
> > > Bellevue, WA 98007
> > > 800-734-3588    425-895-0050
> > > http://www.Apartment-Pets.com  http://www.Illusion-Optical.com
> > > http://www.Card-Offer.com      http://www.RackWine.com
> > > http://www.JokesBlonde.com     http://www.Brain-Fun.com 
> > > *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> > >
> > >
> >
>
>

[Jacopo Cappellato]

This is my personal wish list:

1) more detailed error log (in a separate file selectable from ui before
the import?) where the data file, line number in the file containing bad
data is logged

2) add the ability to run the import service (from ui) as an async
service; also the creation of a report log (summary) would help

3) in the datafile definition (grammar) add the attributes to set the
commit strategies for the file: row-level commit, file commit, abort on
commit, continue on commit etc...

Jacopo


Chris Howe wrote:

> It's been my limited experience that if you have a failure in your
> datafile, you may need to make adjustments to all your records in the
> batch; good records and bad records.  That they should have been
> entered as a group.  So, if one record fails, you'll likely still want
> the entire datafile to fail, but seperate the failed records so that
> you can track down the error(s) quickly.
>
>
> --- Daniel Kunkel <[hidden email]> wrote:
>
>> Thanks. Great.
>>
>> Just to be sure I've communicated what I had in mind clearly, I'll
>> say I
>> didn't think you'd ever retry the good records...  as they shouldn't
>> need to be retried because they've made it into the database.
>> However,
>> Saving the good records somewhere  is a good idea since someone is
>> bound
>> to get bit from a "disappearing" import. Perhaps the best solution is
>> to
>> write them to a new file, *.xml.done as each record is successfully
>> imported. Anyway, I think you got the main jist...  widdle down the
>> import so it's easy to find and fix those nagging leftover imports
>> that
>> fail.
>>
>> Daniel
>>
>>
>> On Thu, 2007-03-01 at 20:11 -0800, Chris Howe wrote:
>>> Daniel,
>>>
>>> I like it.  In addition maybe have an option to write the good
>> records
>>> to one file and the bad records to another...Follow Andrew's recent
>>> counting pattern and retry both files with the good ones first.
>> I'll
>>> probably put some time away for it next weekend.
>>>
>>>
>>> --- Daniel Kunkel <[hidden email]> wrote:
>>>
>>>> Chris...
>>>>
>>>> Another way I've thought about doing this is to "distructively
>> widdle
>>>> down" a directory import.
>>>>
>>>> The idea is to load as many records as possible into the
>> database,
>>>> and
>>>> delete them from the import directory as they are imported. The
>>>> system
>>>> already does this on a file level, but I was thinking that doing
>> it
>>>> on a
>>>> record level would take care of all your issues, and leave
>> someone
>>>> with
>>>> a much smaller mess to clean up with the "hopefully few" records
>> that
>>>> are left that have problems.
>>>>
>>>> Optionally going another step... It would be great if it even
>> could
>>>> handle "circular references." An implementation scheme that might
>>>> work
>>>> would do it by working backwards, moving each single record that
>>>> causes
>>>> a problem to a new directory to be re-attempted the next
>> iteration.
>>>> Hopefully, when you the right combination of problem records are
>>>> removed, the circular reference will be accepted in one lump
>> commit.
>>>> Daniel
>>>>
>>>>
>>>> On Thu, 2007-03-01 at 14:23 -0800, Chris Howe 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
>>>>>>
>>>>>>
>>>> --
>>>> Daniel
>>>>
>>>> *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
>>>> Have a GREAT Day!
>>>>
>>>> Daniel Kunkel           [hidden email]
>>>> BioWaves, LLC           http://www.BioWaves.com
>>>> 14150 NE 20th St. Suite F1
>>>> Bellevue, WA 98007
>>>> 800-734-3588    425-895-0050
>>>> http://www.Apartment-Pets.com  http://www.Illusion-Optical.com
>>>> http://www.Card-Offer.com      http://www.RackWine.com
>>>> http://www.JokesBlonde.com     http://www.Brain-Fun.com 
>>>> *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
>>>>
>>>>
>>

[cjhowe]

Good ideas...
I've started a wiki page at
http://docs.ofbiz.org/x/ZwU
to track some of these ideas...feel free to add your own or correct my
interpretation of your wish.

If some seem doable or someone is actually working on implementing one
or two, naturally, create a JIRA issue around it.


--- Jacopo Cappellato <[hidden email]> wrote:

> This is my personal wish list:
>
> 1) more detailed error log (in a separate file selectable from ui
> before
> the import?) where the data file, line number in the file containing
> bad
> data is logged
>
> 2) add the ability to run the import service (from ui) as an async
> service; also the creation of a report log (summary) would help
>
> 3) in the datafile definition (grammar) add the attributes to set the
>
> commit strategies for the file: row-level commit, file commit, abort
> on
> commit, continue on commit etc...
>
> Jacopo
>
>
> Chris Howe wrote:
> > It's been my limited experience that if you have a failure in your
> > datafile, you may need to make adjustments to all your records in
> the
> > batch; good records and bad records.  That they should have been
> > entered as a group.  So, if one record fails, you'll likely still
> want
> > the entire datafile to fail, but seperate the failed records so
> that
> > you can track down the error(s) quickly.
> >
> >
> > --- Daniel Kunkel <[hidden email]> wrote:
> >
> >> Thanks. Great.
> >>
> >> Just to be sure I've communicated what I had in mind clearly, I'll
> >> say I
> >> didn't think you'd ever retry the good records...  as they
> shouldn't
> >> need to be retried because they've made it into the database.
> >> However,
> >> Saving the good records somewhere  is a good idea since someone is
> >> bound
> >> to get bit from a "disappearing" import. Perhaps the best solution
> is
> >> to
> >> write them to a new file, *.xml.done as each record is
> successfully
> >> imported. Anyway, I think you got the main jist...  widdle down
> the
> >> import so it's easy to find and fix those nagging leftover imports
> >> that
> >> fail.
> >>
> >> Daniel
> >>
> >>
> >> On Thu, 2007-03-01 at 20:11 -0800, Chris Howe wrote:
> >>> Daniel,
> >>>
> >>> I like it.  In addition maybe have an option to write the good
> >> records
> >>> to one file and the bad records to another...Follow Andrew's
> recent
> >>> counting pattern and retry both files with the good ones first.
> >> I'll
> >>> probably put some time away for it next weekend.
> >>>
> >>>
> >>> --- Daniel Kunkel <[hidden email]> wrote:
> >>>
> >>>> Chris...
> >>>>
> >>>> Another way I've thought about doing this is to "distructively
> >> widdle
> >>>> down" a directory import.
> >>>>
> >>>> The idea is to load as many records as possible into the
> >> database,
> >>>> and
> >>>> delete them from the import directory as they are imported. The
> >>>> system
> >>>> already does this on a file level, but I was thinking that doing
> >> it
> >>>> on a
> >>>> record level would take care of all your issues, and leave
> >> someone
> >>>> with
> >>>> a much smaller mess to clean up with the "hopefully few" records
> >> that
> >>>> are left that have problems.
> >>>>
> >>>> Optionally going another step... It would be great if it even
> >> could
> >>>> handle "circular references." An implementation scheme that
> might
> >>>> work
> >>>> would do it by working backwards, moving each single record that
> >>>> causes
> >>>> a problem to a new directory to be re-attempted the next
> >> iteration.
> >>>> Hopefully, when you the right combination of problem records are
> >>>> removed, the circular reference will be accepted in one lump
> >> commit.
> >>>> Daniel
> >>>>
> >>>>
> >>>> On Thu, 2007-03-01 at 14:23 -0800, Chris Howe 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
> >>>>>>
> >>>>>>
> >>>> --
> >>>> Daniel
> >>>>
> >>>>
> *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> >>>> Have a GREAT Day!
> >>>>
> >>>> Daniel Kunkel           [hidden email]
> >>>> BioWaves, LLC           http://www.BioWaves.com
> >>>> 14150 NE 20th St. Suite F1
> >>>> Bellevue, WA 98007
> >>>> 800-734-3588    425-895-0050
> >>>> http://www.Apartment-Pets.com  http://www.Illusion-Optical.com
> >>>> http://www.Card-Offer.com      http://www.RackWine.com
> >>>> http://www.JokesBlonde.com     http://www.Brain-Fun.com 
> >>>>
> *-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-.,,.-*"*-
> >>>>
> >>>>
> >>
>
>
>

[Andrew Sykes]

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

[jonwimp]

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

[cjhowe]

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]

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]

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

[cjhowe]

I have no idea where to start with this.  To lower the timeout in your
my.ini add the line

max_timeout=integer in seconds


if this isn't specified, mysql assumes 28800 (60 seconds * 60 minutes*
8 hours)


--- Andrew Sykes <[hidden email]> 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]>
>

=== message truncated ===