Data in flat xml file loaded out of order?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Data in flat xml file loaded out of order?

Eric Schwarzenbach
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

John Hurst-2
This is weird, and not what I expect. 

I work with Oracle and have used DbUnit to load a 10,000-line flat XML file with ordering and FK dependencies as you described. We've loaded this thousands of times without any issue. For that matter I have used the same pattern with PostgreSQL too, albeit not nearly as much.

You are definitely doing this with a single operation, a single file? It sounds like you are.

I believe it's possible to get confusing, timing-related results when using connection pooling and inadvertently using different connections for quick successive operations. But again that doesn't sound like what you are doing.

Sorry that I can not be of more help.

Regards

John Hurst



On Sat, Jan 25, 2014 at 10:08 AM, Eric Schwarzenbach <[hidden email]> wrote:
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

Eric Schwarzenbach
Thanks for your reply John, even if you don't have an answer. You've at least confirmed that it shouldn't be happening.

Yup single connection, no pooling. I am actually loading more than one flat xml file (on this same connection, in a single transaction) but the two records in question are in the same file.

I have used this home grown tool that uses DbUnit to load such sets of data for years without a problem like this (things that have changed occasionally over time, have been updates to the database version, the jdbc driver version, the dbunit version.), and also, for years, loaded similar data sets via an ant task that calls dbunit, so I too am suprised by this behavior. Even in the set of files I'm loading now there are thousands of such dependencies between records that are not encountering any such error.

Could the jdbc driver be causing a behavior like this, by using threads or perhaps trying to optimize statements?

Thanks,

Eric

On 1/24/2014 8:10 PM, John Hurst wrote:
This is weird, and not what I expect. 

I work with Oracle and have used DbUnit to load a 10,000-line flat XML file with ordering and FK dependencies as you described. We've loaded this thousands of times without any issue. For that matter I have used the same pattern with PostgreSQL too, albeit not nearly as much.

You are definitely doing this with a single operation, a single file? It sounds like you are.

I believe it's possible to get confusing, timing-related results when using connection pooling and inadvertently using different connections for quick successive operations. But again that doesn't sound like what you are doing.

Sorry that I can not be of more help.

Regards

John Hurst



On Sat, Jan 25, 2014 at 10:08 AM, Eric Schwarzenbach <[hidden email]> wrote:
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

Eric Schwarzenbach
Ok I've just run into this again with another dataset. I turned on logging on the database server, and it is clearly NOT receiving insert statements in the order that corresponds to the order of records in the xml file. It seems to ordering them according to the table. Why would it do this? I am not using DatabaseSequenceFilter...or ANY filter.

On 1/25/2014 3:46 PM, Eric Schwarzenbach wrote:
Thanks for your reply John, even if you don't have an answer. You've at least confirmed that it shouldn't be happening.

Yup single connection, no pooling. I am actually loading more than one flat xml file (on this same connection, in a single transaction) but the two records in question are in the same file.

I have used this home grown tool that uses DbUnit to load such sets of data for years without a problem like this (things that have changed occasionally over time, have been updates to the database version, the jdbc driver version, the dbunit version.), and also, for years, loaded similar data sets via an ant task that calls dbunit, so I too am suprised by this behavior. Even in the set of files I'm loading now there are thousands of such dependencies between records that are not encountering any such error.

Could the jdbc driver be causing a behavior like this, by using threads or perhaps trying to optimize statements?

Thanks,

Eric

On 1/24/2014 8:10 PM, John Hurst wrote:
This is weird, and not what I expect. 

I work with Oracle and have used DbUnit to load a 10,000-line flat XML file with ordering and FK dependencies as you described. We've loaded this thousands of times without any issue. For that matter I have used the same pattern with PostgreSQL too, albeit not nearly as much.

You are definitely doing this with a single operation, a single file? It sounds like you are.

I believe it's possible to get confusing, timing-related results when using connection pooling and inadvertently using different connections for quick successive operations. But again that doesn't sound like what you are doing.

Sorry that I can not be of more help.

Regards

John Hurst



On Sat, Jan 25, 2014 at 10:08 AM, Eric Schwarzenbach <[hidden email]> wrote:
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

Eric Schwarzenbach
So it looks to me like this is what is going on:

Dbunit is ordering the insert / update statements according to the tables...so all the inserts to table a will go together, and all the inserts to table b go together, etc. It decides whether the a's or the b's should come first by which it sees the first instance of in the file...so of the first record in the file is an a, it will do all the inserts to a before all the inserts to b.

This probably works fine in most cases. But not all cases. Given my a and b example of my first message in this thread, where b has a fk to a, suppose the first record in my flat xml file is a b record which has a FK to an a row which I expect to already be in the database. This will cause it to insert all my b's before all my a's causing me to get FK errors even if I'm careful to put every a record before any b record with a FK to it.



On 1/29/2014 7:35 PM, Eric Schwarzenbach wrote:
Ok I've just run into this again with another dataset. I turned on logging on the database server, and it is clearly NOT receiving insert statements in the order that corresponds to the order of records in the xml file. It seems to ordering them according to the table. Why would it do this? I am not using DatabaseSequenceFilter...or ANY filter.

On 1/25/2014 3:46 PM, Eric Schwarzenbach wrote:
Thanks for your reply John, even if you don't have an answer. You've at least confirmed that it shouldn't be happening.

Yup single connection, no pooling. I am actually loading more than one flat xml file (on this same connection, in a single transaction) but the two records in question are in the same file.

I have used this home grown tool that uses DbUnit to load such sets of data for years without a problem like this (things that have changed occasionally over time, have been updates to the database version, the jdbc driver version, the dbunit version.), and also, for years, loaded similar data sets via an ant task that calls dbunit, so I too am suprised by this behavior. Even in the set of files I'm loading now there are thousands of such dependencies between records that are not encountering any such error.

Could the jdbc driver be causing a behavior like this, by using threads or perhaps trying to optimize statements?

Thanks,

Eric

On 1/24/2014 8:10 PM, John Hurst wrote:
This is weird, and not what I expect. 

I work with Oracle and have used DbUnit to load a 10,000-line flat XML file with ordering and FK dependencies as you described. We've loaded this thousands of times without any issue. For that matter I have used the same pattern with PostgreSQL too, albeit not nearly as much.

You are definitely doing this with a single operation, a single file? It sounds like you are.

I believe it's possible to get confusing, timing-related results when using connection pooling and inadvertently using different connections for quick successive operations. But again that doesn't sound like what you are doing.

Sorry that I can not be of more help.

Regards

John Hurst



On Sat, Jan 25, 2014 at 10:08 AM, Eric Schwarzenbach <[hidden email]> wrote:
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable 
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

Jeff Jensen-2
Yes, this is how dbUnit works - it inserts them in the order of the file.  Its delete operation removes them in the opposite order.
I don't understand any cases this does not work for; well, except cyclical... 
In your example, placing A records before the B records in the dbUnit data file will not cause a FK dependency problem of B to A.


On Wed, Jan 29, 2014 at 7:02 PM, Eric Schwarzenbach <[hidden email]> wrote:
So it looks to me like this is what is going on:

Dbunit is ordering the insert / update statements according to the tables...so all the inserts to table a will go together, and all the inserts to table b go together, etc. It decides whether the a's or the b's should come first by which it sees the first instance of in the file...so of the first record in the file is an a, it will do all the inserts to a before all the inserts to b.

This probably works fine in most cases. But not all cases. Given my a and b example of my first message in this thread, where b has a fk to a, suppose the first record in my flat xml file is a b record which has a FK to an a row which I expect to already be in the database. This will cause it to insert all my b's before all my a's causing me to get FK errors even if I'm careful to put every a record before any b record with a FK to it.




On 1/29/2014 7:35 PM, Eric Schwarzenbach wrote:
Ok I've just run into this again with another dataset. I turned on logging on the database server, and it is clearly NOT receiving insert statements in the order that corresponds to the order of records in the xml file. It seems to ordering them according to the table. Why would it do this? I am not using DatabaseSequenceFilter...or ANY filter.

On 1/25/2014 3:46 PM, Eric Schwarzenbach wrote:
Thanks for your reply John, even if you don't have an answer. You've at least confirmed that it shouldn't be happening.

Yup single connection, no pooling. I am actually loading more than one flat xml file (on this same connection, in a single transaction) but the two records in question are in the same file.

I have used this home grown tool that uses DbUnit to load such sets of data for years without a problem like this (things that have changed occasionally over time, have been updates to the database version, the jdbc driver version, the dbunit version.), and also, for years, loaded similar data sets via an ant task that calls dbunit, so I too am suprised by this behavior. Even in the set of files I'm loading now there are thousands of such dependencies between records that are not encountering any such error.

Could the jdbc driver be causing a behavior like this, by using threads or perhaps trying to optimize statements?

Thanks,

Eric

On 1/24/2014 8:10 PM, John Hurst wrote:
This is weird, and not what I expect. 

I work with Oracle and have used DbUnit to load a 10,000-line flat XML file with ordering and FK dependencies as you described. We've loaded this thousands of times without any issue. For that matter I have used the same pattern with PostgreSQL too, albeit not nearly as much.

You are definitely doing this with a single operation, a single file? It sounds like you are.

I believe it's possible to get confusing, timing-related results when using connection pooling and inadvertently using different connections for quick successive operations. But again that doesn't sound like what you are doing.

Sorry that I can not be of more help.

Regards

John Hurst



On Sat, Jan 25, 2014 at 10:08 AM, Eric Schwarzenbach <[hidden email]> wrote:
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable 
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

John Hurst-2
I'm really embarrassed that I didn't spot this before. Yes, that is standard behaviour, as Jeff described. 

Your data are loaded in file order if they are like this:

<a .../>
<a .../>
<a .../>
<b .../>
<b .../>
<b .../>

They are not loaded in file order when they are like this:

<a .../>
<b .../>
<a .../>
<b .../>
<a .../>
<b .../>

Rather, all data for table 'a' are loaded first, then all data for table 'b'. 

I have actually hit this myself in the past when I wanted to group related data and have parents placed with their children in the data file. 

I cannot remember exactly why DbUnit does not simply insert in file order (and delete in reverse file order).

It hasn't been a problem for me for a long time though because these days I provide data for each specific test case and so have very small data sets, such that FK issues are rare and easily solved.

Sorry not to be a better help.

John Hurst
Wellington, New Zealand



On Thu, Jan 30, 2014 at 2:03 PM, Jeff Jensen <[hidden email]> wrote:
Yes, this is how dbUnit works - it inserts them in the order of the file.  Its delete operation removes them in the opposite order.
I don't understand any cases this does not work for; well, except cyclical... 
In your example, placing A records before the B records in the dbUnit data file will not cause a FK dependency problem of B to A.


On Wed, Jan 29, 2014 at 7:02 PM, Eric Schwarzenbach <[hidden email]> wrote:
So it looks to me like this is what is going on:

Dbunit is ordering the insert / update statements according to the tables...so all the inserts to table a will go together, and all the inserts to table b go together, etc. It decides whether the a's or the b's should come first by which it sees the first instance of in the file...so of the first record in the file is an a, it will do all the inserts to a before all the inserts to b.

This probably works fine in most cases. But not all cases. Given my a and b example of my first message in this thread, where b has a fk to a, suppose the first record in my flat xml file is a b record which has a FK to an a row which I expect to already be in the database. This will cause it to insert all my b's before all my a's causing me to get FK errors even if I'm careful to put every a record before any b record with a FK to it.




On 1/29/2014 7:35 PM, Eric Schwarzenbach wrote:
Ok I've just run into this again with another dataset. I turned on logging on the database server, and it is clearly NOT receiving insert statements in the order that corresponds to the order of records in the xml file. It seems to ordering them according to the table. Why would it do this? I am not using DatabaseSequenceFilter...or ANY filter.

On 1/25/2014 3:46 PM, Eric Schwarzenbach wrote:
Thanks for your reply John, even if you don't have an answer. You've at least confirmed that it shouldn't be happening.

Yup single connection, no pooling. I am actually loading more than one flat xml file (on this same connection, in a single transaction) but the two records in question are in the same file.

I have used this home grown tool that uses DbUnit to load such sets of data for years without a problem like this (things that have changed occasionally over time, have been updates to the database version, the jdbc driver version, the dbunit version.), and also, for years, loaded similar data sets via an ant task that calls dbunit, so I too am suprised by this behavior. Even in the set of files I'm loading now there are thousands of such dependencies between records that are not encountering any such error.

Could the jdbc driver be causing a behavior like this, by using threads or perhaps trying to optimize statements?

Thanks,

Eric

On 1/24/2014 8:10 PM, John Hurst wrote:
This is weird, and not what I expect. 

I work with Oracle and have used DbUnit to load a 10,000-line flat XML file with ordering and FK dependencies as you described. We've loaded this thousands of times without any issue. For that matter I have used the same pattern with PostgreSQL too, albeit not nearly as much.

You are definitely doing this with a single operation, a single file? It sounds like you are.

I believe it's possible to get confusing, timing-related results when using connection pooling and inadvertently using different connections for quick successive operations. But again that doesn't sound like what you are doing.

Sorry that I can not be of more help.

Regards

John Hurst



On Sat, Jan 25, 2014 at 10:08 AM, Eric Schwarzenbach <[hidden email]> wrote:
I'm running into something rather odd. I'm using FlatXmlDataSet to load
data to a PostgreSQL database, with batching off, using DBUnit 2.4.9 via
the Java API. And I'm getting an error that suggests that (in just one
case) the data elements are being loaded out of order.

I always have the records in the file in order of foreign key
dependency, and always include all the columns. For most of my data this
is working fine. But there is one case where it doesn;t. The data is like

<a a_id="x" name="blah"/>

<b b_id="y" a_id="a" />

where a_id in b is a FK to a.a_id

(simplified and the names changed to protect the innocent)

And I'm getting an error like:

ERROR: insert or update on table "b"
  violates foreign key constraint "foo"
   Detail: Key (a_id)=(x) is not present in table "a".


I've been over the data a million times and I can't make sense of this,
unless for some reason the a record isn't getting sent to the db before
the b record. If I remove the b record, the error goes away and I can
check the database and verify the presence of the a record it depends
on. I can then use dbunit to load that b record. I've also found if I
move the a record much higher in this xml file (it's 100 some odd lines
long) then the b record loads with no error. (I'm always loading to a
clean db when I test this.)

So it's as if the a record and a record were being loaded by different
threads and due to the vagueries of timing the b were loading before the
a, despite the order. Does that make any sense? Does db unit attempt to
use multiple threads? (Or might the jdbc driver?)

Is there any rational explanation for this behavior?

Thanks,

Eric

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable 
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk


_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user




--
Life is interfering with my game

------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Data in flat xml file loaded out of order?

Eric Schwarzenbach
Thanks for the confirmation. Now that I know I can work around it.

I would just like to point out to any maintainers that read this list
that the documentation is simply wrong then. It says

"DbUnit inserts and updates rows in the order they are found in your
dataset; deletes on the other hand are done in reverse order."

> On 1/30/2014 1:20 AM, John Hurst wrote:
>> I'm really embarrassed that I didn't spot this before. Yes, that is
>> standard behaviour, as Jeff described.
>>
>> Your data are loaded in file order if they are like this:
>>
>> <a .../>
>> <a .../>
>> <a .../>
>> <b .../>
>> <b .../>
>> <b .../>
>>
>> They are not loaded in file order when they are like this:
>>
>> <a .../>
>> <b .../>
>> <a .../>
>> <b .../>
>> <a .../>
>> <b .../>
>>
>> Rather, all data for table 'a' are loaded first, then all data for
>> table 'b'.
>>
>> I have actually hit this myself in the past when I wanted to group
>> related data and have parents placed with their children in the data
>> file.
>>
>> I cannot remember exactly why DbUnit does not simply insert in file
>> order (and delete in reverse file order).
>>
>> It hasn't been a problem for me for a long time though because these
>> days I provide data for each specific test case and so have very
>> small data sets, such that FK issues are rare and easily solved.
>>
>> Sorry not to be a better help.
>>
>> John Hurst
>> Wellington, New Zealand
>>


------------------------------------------------------------------------------
WatchGuard Dimension instantly turns raw network data into actionable
security intelligence. It gives you real-time visual feedback on key
security issues and trends.  Skip the complicated setup - simply import
a virtual appliance and go from zero to informed in seconds.
http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user