Re: Re: [dbunit-user] DB cleanup after exceptions? How?

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Hello Manuel (ant thanks for all the
follow-ups!  I appreciate it).

Yes, my problem occurs even if there are no
exceptions.
The issue is with DB constraints (FKs) and
with the order of DB operations, esp. DELETE
in tearDown().

I identified 3 scenarios.
In all cases the DB already has some data,
but the one in <dataset> does not collide
with it - sequence IDs, etc. are different,
so the existing data is not the problem for
me now.  Because of this existing data, I
don't want to use DELETE_ALL anywhere, which
also rules out CLEAN_INSERT, since that is
really a DELETE_ALL followed by an INSERT.

I created the simplest possible scenario
where I have 2 tables, A and B, one row
each, in my <dataset>.   Table B has a FK
into table A.
In my <dataset> I list tables in this order:
A, B
For these 2 tables constraints are such that
INSERTs need to be in order: A, B, and
DELETEs need to be done in the reverse
order: B, A.

-- Scenario 1: setUp(): INSERT, tearDown():
DELETE
 - setUp(): works, no problems
    (but does not work if test is run twice,
because tearDown() fails and doesn't clean up)
 - tearDown(): fails, because it looks like
DELETE tries to delete rows from tables in
the same order they are listed in <dataset>.
 This results in:
ORA-02292: integrity constraint
(WGEN.B_A_SID_FK) violated - child record found

-- Scenario 2: setUp(): REFRESH, tearDown():
DELETE
 - setUp(): works, no problem.
 - tearDown(): fails, for the same reason as
Scenario 1 (bad delete order)

-- Scenario 3: setUp(): REFRESH, tearDown():
NONE
 - setUp(): works, no problem
 - tearDown(): works, because it does
nothing, but I would prefer to clean up
after the test.

I _think_ I would like to have Scenario 2
working: REFRESH on setUp() and DELETE on
tearDown().  But how do I tell DbUnit to
perform a DELETE in a reverse order?
Do I need another XML file that has the same
tables used for setUp(), but in the reverse
order?

Thank you!
Otis

---- On Wed, 26 Mar 2003,
man...@oz...
(man...@oz...) wrote:

> I don't think that the batch statement bug
is related to your issue. I 
> have some questions regarding your problem...
> 
> 1. Is your problem occurs ONLY after you
get an Exception in your test? 
> 
> -
> 
> 2. Is your tested code inserts data in
tables not present in your XML 
> dataset? Or similarly, a different dataset
used in another test class can 
> have inserted data in tables not present
in your current dataset. 
> 
> If yes, then this is likely to be the
cause of your problem. I had this 
> problem myself and the symptoms were very
similar to yours. The solution I 
> have used are explained in my previous reply.
> 
> - 
> 
> 3. Is your tables sequence is defined
correctly in your dataset? I assume 
> yes but I'm asking just in case...
> 
> From
http://www.dbunit.org/bestpractices.html#tableseq:
> "To prevent problems with foreign key
[constraints], tables must be 
> sequenced appropriately in [your] dataset.
[DbUnit] executes INSERT, 
> UPDATE and REFRESH operations using the
[dataset] sequence while DELETE 
> and DELETE_ALL are executed in reverse order."
> 
> Manuel
> 
> --------------- On 03/25/2003 07:19:40 PM
dbunit-user-admin wrote: 
> ---------------
> >Hello Manuel and Dirk,
> >
> >Yes, I have tried using CLEAN_INSERT.  Maybe
> >it is the complexity of relationships in our
> >DB, but it does not work.
> >Apparently CLEAN_ALL runs DELETE_ALL (like
> >you said) on one of the parent tables, but
> >the child table constraint causes an error:
> >
> >testExecute Error error occurred during
> >batching: ORA-02292: integrity constraint
> >(WGEN.INST_SYSADMIN_INST_SID_FK2)
> >violated - child record found
> >java.sql.BatchUpdateException: error
> >occurred during batching: ORA-02292:
integrity
> >constraint
> >(WGEN.INST_SYSADMIN_INST_SID_FK2) violated -
> >child record found
> >at
>
>oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:521)
> >at
>
>oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:573)
> >at
>
>oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:5995)
> >at
>
>org.dbunit.database.statement.BatchStatement.executeBatch(BatchStatement.java:46
> >)
> >at
>
>org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:77)
> >at
>
>org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:67)
> >at
>
>org.dbunit.DatabaseTestCase.executeOperation(DatabaseTestCase.java:84)
> >at
>
>org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:101)
> >at
>
>net.wgen.unittest.DbTestCase.setUp(DbTestCase.java:108)
> >at
>
>net.wgen.unittest.DbTestCaseWrapper.setUp(DbTestCaseWrapper.java:29)
> >at
>
>net.wgen.ampng.action.LoginActionTest.setUp(LoginActionTest.java:76)
> >
> >
> >Recenly I saw some mentions of batch update
> >fixes being added to the CVS version.  I
> >have DbUnit 1.5.
> >Should I be getting DbUnit from CVS?  Could
> >that fix be related to this error above?
> >The above stack trace shows Batch Update
> >operations, too.
> >
> >Also note that the above stack trace shows
> >that the error comes from setUp() method
calls.
> >
> >I have now tried changing from CLEAN_INSERT
> >to REFRESH.
> >I still get an error, although a different
> >one this time:
> >
> >testExecute Error ORA-02292: integrity
> >constraint (WGEN.AMP_USER_SUJET_SID_FK)
violated
> >- child record found
> >java.sql.BatchUpdateException: ORA-02292:
> >integrity constraint
> >(WGEN.AMP_USER_SUJET_SID_FK) violated -
> >child record found
> >at
>
>oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:441)
> >at
> >
>
>oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.
> >java:336
> >4)
> >at
> >
>
>org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchS
> >tatement
> >.java:77)
> >at
>
>org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:
> >140)
> >at
>
>org.dbunit.DatabaseTestCase.executeOperation(DatabaseTestCase.java:84)
> >at
>
>org.dbunit.DatabaseTestCase.tearDown(DatabaseTestCase.java:108)
> >at
>
>net.wgen.unittest.DbTestCase.tearDown(DbTestCase.java:115)
> >at
>
>net.wgen.unittest.DbTestCaseWrapper.tearDown(DbTestCaseWrapper.java:35)
> >at
>
>net.wgen.ampng.action.LoginActionTest.tearDown(LoginActionTest.java:85)
> >
> >
> >Note how this stack trace now shows
> >tearDown() method calls, not setUp() calls.
> >Why would that be?  I only switched from
> >CLEAN_INSERT to REFRESH.
> >
> >Any help would be very appreciated.
> >
> >Thanks,
> >Otis
> >
> >
> >
> >---- On Tue, 25 Mar 2003,
> >man...@oz...
> >(man...@oz...) wrote:
> >
> >> Hi Otis,
> >>
> >> I'm wondering what operation you are using
> >to setup your tests? Like Dirk
> >> wrote, CLEAN_INSERT should be sufficient
> >in most situations.
> >>
> >> This is important to notice that
> >CLEAN_INSERT and DELETE_ALL perform a
> >> "DELETE *" for all tables present in your
> >dataset and not for all tables
> >> in the database. So if your tests inserts
> >rows in a table not present in
> >> the dataset, this table is not cleaned and
> >can result to the problems you
> >> have.
> >>
> >> In my current project environment, I do
> >not use CLEAN_INSERT in tests
> >> setup. I use DELETE_ALL and INSERT
> >operations with two different datasets.
> >>
> >>
> >> I first execute DELETE_ALL using my
> >database DTD as the dataset (same for
> >> all test classes). Then the INSERT is
> >executed using an XML dataset
> >> specific to the current test class. This
> >way all tables present in my DTD
> >> are cleared before the INSERT. Several
> >database tables are excluded from
> >> my DTD, principally configuration tables
> >that do not change (and should
> >> not) during my tests.
> >>
> >> You can use the FilteredDataSet class to
> >dynamically exclude some tables
> >> from a decorated dataset. In the CVS
> >version, you can even use two new
> >> filtering strategies, IncludeTableFilter
> >and ExcludeTableFilter, which
> >> support wildcards.
> >>
> >> I hope this helps,
> >>
> >> Manuel
> >>
> >> --------------- On 03/24/2003 05:35:28 PM
> >dbunit-user-admin wrote:
> >> ---------------
> >> >Hello,
> >> >
> >> >I am using DbUnit 1.5 and have a question
> >> >about cleaning up the test data from the
> >> >database when unexpected errors are
> >encountered.
> >> >
> >> >I have noticed that when DbUnit-based unit
> >> >tests fail/break/exit because of
things like
> >> >NullPointerException, the DB gets left
in an
> >> >inconsistent state.  This makes re-running
> >> >test very hard/impossible to run
because of
> >> >various DB constraints (PK already exists,
> >> >for example) - a manual DB cleanup becomes
> >> >necessary.
> >> >
> >> >I imagine this is a common scenario
and I'm
> >> >wondering how other DbUnit users
handle it?
> >> >
> >> >For instance, I just run one unit test
that
> >> >broke with the following error, which
left a
> >> >number of rows in the DB:
> >> >
> >> >[junit] java.lang.NullPointerException
> >> >[junit]        at
> >>
>
>>org.apache.struts.action.ActionServlet.initModuleConfig(ActionServlet.java:941)
> >> >
> >> >A bit about our setup, if it matters:
> >> >
> >> >Our unit tests are really tests for Struts
> >> >Actions.
> >> >
> >> >We have a base class called
ActionTestCase.
> >> >ActionTestCase inserits MockStrutsTestCase
> >> >(a class from Struts
> >> >Test Case package).
> >> >ActionTestCase also creates an instance of
> >> >DbTestCase, a class
> >> >that we wrote, which inherits DbUnit's
> >> >DatabaseTestCase.
> >> >
> >> >In our DbTestCase class we override
> >> >DatabaseTestCase's getTearDownOperation()
> >> >method:
> >> >
> >> >protected DatabaseOperation
> >> >getTearDownOperation()
> >> >throws Exception
> >> >{
> >> >return DatabaseOperation.DELETE_ALL;
> >> >}
> >> >
> >> >
> >> >I read the documents on DbUnit.org, and it
> >> >looks like this DELETE_ALL
DatabaseOperation
> >> >should make sure everything is torn down
> >> >from the DB.
> >> >Is this really true?
> >> >
> >> >Is it possible that this operation is not
> >> >being triggered when unexpected
errors, such
> >> >as NullPointerException occur?
> >> >
> >> >How do others handle this?
> >> >Do you have a gigant try/catch somewhere
> >> >that catches any Exception and forces
> >> >DELETE_ALL ...somehow?
> >> >
> >> >
> >> >Thank you,
> >> >Otis
> >> >
> >> >
> >>
>
>>________________________________________________
> >> >Get your own "800" number
> >> >Voicemail, fax, email, and a lot more
> >> >http://www.ureach.com/reg/tag
> >> >
> >> >
> >>
>
>>-------------------------------------------------------
> >> >This sf.net email is sponsored
by:ThinkGeek
> >> >Welcome to geek heaven.
> >> >http://thinkgeek.com/sf
> >>
>
>>_______________________________________________
> >> >dbunit-user mailing list
> >> >dbu...@li...
> >>
>
>>https://lists.sourceforge.net/lists/listinfo/dbunit-user
> 
> 
> 
> 
> <br><font>I don't
think that the batch statement bug is
related to your
> issue. I have some questions regarding
your problem...</font>
> <br>
> <br><font>1. Is
your problem occurs ONLY after you get an
Exception in your
> test? </font>
> <br>
> <br><font>-</font>
> <br>
> <br><font>2. Is
your tested code inserts data in tables not
present in your
> XML dataset? Or similarly, a different
dataset used in another test class can have
inserted data in
> tables not present in your current
dataset. </font>
> <br>
> <br><font>If yes,
then this is likely to be the cause of your
problem. I had
> this problem myself and the symptoms were
very similar to yours. The solution I have
used are
> explained in my previous reply.</font>
> <br>
> <br><font>- </font>
> <br>
> <br><font>3. Is
your tables sequence is defined correctly in
your dataset? I
> assume yes but I'm asking just in
case...</font>
> <br>
> <br><font>From
http://www.dbunit.org/bestpractices.html#tableseq:</font>
> <br><font>"To
prevent problems with foreign key
[constraints], tables
> must be sequenced appropriately in [your]
dataset. [DbUnit] executes INSERT, UPDATE
and REFRESH
> operations using the [dataset] sequence
while DELETE and DELETE_ALL are executed in
reverse
> order."</font>
> <br>
> <br><font>Manuel<br>
> <br>
> --------------- On 03/25/2003 07:19:40 PM
dbunit-user-admin wrote: ---------------<br>
> >Hello Manuel and Dirk,<br>
> ><br>
> >Yes, I have tried using CLEAN_INSERT.
 Maybe<br>
> >it is the complexity of relationships in
our<br>
> >DB, but it does not work.<br>
> >Apparently CLEAN_ALL runs DELETE_ALL
(like<br>
> >you said) on one of the parent tables,
but<br>
> >the child table constraint causes an
error:<br>
> ><br>
> >testExecute Error error occurred during<br>
> >batching: ORA-02292: integrity constraint<br>
> >(WGEN.INST_SYSADMIN_INST_SID_FK2)<br>
> >violated - child record found<br>
> >java.sql.BatchUpdateException: error<br>
> >occurred during batching: ORA-02292:
integrity<br>
> >constraint<br>
> >(WGEN.INST_SYSADMIN_INST_SID_FK2)
violated -<br>
> >child record found<br>
> >at<br>
>
>oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:521)<br>
> >at<br>
>
>oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:573)<br>
> >at<br>
>
>oracle.jdbc.driver.OracleStatement.executeBatch(OracleStatement.java:5995)<br>
> >at<br>
>
>org.dbunit.database.statement.BatchStatement.executeBatch(BatchStatement.java:46<br>
> >)<br>
> >at<br>
>
>org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:77)<br>
> >at<br>
>
>org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:67)<br>
> >at<br>
>
>org.dbunit.DatabaseTestCase.executeOperation(DatabaseTestCase.java:84)<br>
> >at<br>
>
>org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:101)<br>
> >at<br>
>
>net.wgen.unittest.DbTestCase.setUp(DbTestCase.java:108)<br>
> >at<br>
>
>net.wgen.unittest.DbTestCaseWrapper.setUp(DbTestCaseWrapper.java:29)<br>
> >at<br>
>
>net.wgen.ampng.action.LoginActionTest.setUp(LoginActionTest.java:76)<br>
> ><br>
> ><br>
> >Recenly I saw some mentions of batch
update<br>
> >fixes being added to the CVS version.  I<br>
> >have DbUnit 1.5.<br>
> >Should I be getting DbUnit from CVS?
 Could<br>
> >that fix be related to this error above?<br>
> >The above stack trace shows Batch Update<br>
> >operations, too.<br>
> ><br>
> >Also note that the above stack trace
shows<br>
> >that the error comes from setUp() method
calls.<br>
> ><br>
> >I have now tried changing from
CLEAN_INSERT<br>
> >to REFRESH.<br>
> >I still get an error, although a
different<br>
> >one this time:<br>
> ><br>
> >testExecute Error ORA-02292: integrity<br>
> >constraint (WGEN.AMP_USER_SUJET_SID_FK)
violated<br>
> >- child record found<br>
> >java.sql.BatchUpdateException: ORA-02292:<br>
> >integrity constraint<br>
> >(WGEN.AMP_USER_SUJET_SID_FK) violated -<br>
> >child record found<br>
> >at<br>
>
>oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:441)<br>
> >at<br>
> ><br>
>
>oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.<br>
> >java:336<br>
> >4)<br>
> >at<br>
> ><br>
>
>org.dbunit.database.statement.PreparedBatchStatement.executeBatch(PreparedBatchS<br>
> >tatement<br>
> >.java:77)<br>
> >at<br>
>
>org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:<br>
> >140)<br>
> >at<br>
>
>org.dbunit.DatabaseTestCase.executeOperation(DatabaseTestCase.java:84)<br>
> >at<br>
>
>org.dbunit.DatabaseTestCase.tearDown(DatabaseTestCase.java:108)<br>
> >at<br>
>
>net.wgen.unittest.DbTestCase.tearDown(DbTestCase.java:115)<br>
> >at<br>
>
>net.wgen.unittest.DbTestCaseWrapper.tearDown(DbTestCaseWrapper.java:35)<br>
> >at<br>
>
>net.wgen.ampng.action.LoginActionTest.tearDown(LoginActionTest.java:85)<br>
> ><br>
> ><br>
> >Note how this stack trace now shows<br>
> >tearDown() method calls, not setUp()
calls.<br>
> >Why would that be?  I only switched from<br>
> >CLEAN_INSERT to REFRESH.<br>
> ><br>
> >Any help would be very appreciated.<br>
> ><br>
> >Thanks,<br>
> >Otis<br>
> ><br>
> ><br>
> ><br>
> >---- On Tue, 25 Mar 2003,<br>
> >man...@oz...<br>
> >(man...@oz...) wrote:<br>
> ><br>
> >> Hi Otis,<br>
> >><br>
> >> I'm wondering what operation you are
using<br>
> >to setup your tests? Like Dirk<br>
> >> wrote, CLEAN_INSERT should be
sufficient<br>
> >in most situations.<br>
> >><br>
> >> This is important to notice that<br>
> >CLEAN_INSERT and DELETE_ALL perform a<br>
> >> "DELETE *" for all tables present in
your<br>
> >dataset and not for all tables<br>
> >> in the database. So if your tests
inserts<br>
> >rows in a table not present in<br>
> >> the dataset, this table is not cleaned
and<br>
> >can result to the problems you<br>
> >> have.<br>
> >><br>
> >> In my current project environment, I do<br>
> >not use CLEAN_INSERT in tests<br>
> >> setup. I use DELETE_ALL and INSERT<br>
> >operations with two different datasets.<br>
> >><br>
> >><br>
> >> I first execute DELETE_ALL using my<br>
> >database DTD as the dataset (same for<br>
> >> all test classes). Then the INSERT is<br>
> >executed using an XML dataset<br>
> >> specific to the current test class.
This<br>
> >way all tables present in my DTD<br>
> >> are cleared before the INSERT. Several<br>
> >database tables are excluded from<br>
> >> my DTD, principally configuration
tables<br>
> >that do not change (and should<br>
> >> not) during my tests.<br>
> >><br>
> >> You can use the FilteredDataSet class
to<br>
> >dynamically exclude some tables<br>
> >> from a decorated dataset. In the CVS<br>
> >version, you can even use two new<br>
> >> filtering strategies,
IncludeTableFilter<br>
> >and ExcludeTableFilter, which<br>
> >> support wildcards.<br>
> >><br>
> >> I hope this helps,<br>
> >><br>
> >> Manuel<br>
> >><br>
> >> --------------- On 03/24/2003 05:35:28
PM<br>
> >dbunit-user-admin wrote:<br>
> >> ---------------<br>
> >> >Hello,<br>
> >> ><br>
> >> >I am using DbUnit 1.5 and have a
question<br>
> >> >about cleaning up the test data from
the<br>
> >> >database when unexpected errors are<br>
> >encountered.<br>
> >> ><br>
> >> >I have noticed that when DbUnit-based
unit<br>
> >> >tests fail/break/exit because of
things like<br>
> >> >NullPointerException, the DB gets left
in an<br>
> >> >inconsistent state.  This makes
re-running<br>
> >> >test very hard/impossible to run
because of<br>
> >> >various DB constraints (PK already
exists,<br>
> >> >for example) - a manual DB cleanup
becomes<br>
> >> >necessary.<br>
> >> ><br>
> >> >I imagine this is a common scenario
and I'm<br>
> >> >wondering how other DbUnit users
handle it?<br>
> >> ><br>
> >> >For instance, I just run one unit test
that<br>
> >> >broke with the following error, which
left a<br>
> >> >number of rows in the DB:<br>
> >> ><br>
> >> >[junit] java.lang.NullPointerException<br>
> >> >[junit]        at<br>
> >><br>
>
>>org.apache.struts.action.ActionServlet.initModuleConfig(ActionServlet.java:941)<br>
> >> ><br>
> >> >A bit about our setup, if it matters:<br>
> >> ><br>
> >> >Our unit tests are really tests for
Struts<br>
> >> >Actions.<br>
> >> ><br>
> >> >We have a base class called
ActionTestCase.<br>
> >> >ActionTestCase inserits
MockStrutsTestCase<br>
> >> >(a class from Struts<br>
> >> >Test Case package).<br>
> >> >ActionTestCase also creates an
instance of<br>
> >> >DbTestCase, a class<br>
> >> >that we wrote, which inherits DbUnit's<br>
> >> >DatabaseTestCase.<br>
> >> ><br>
> >> >In our DbTestCase class we override<br>
> >> >DatabaseTestCase's
getTearDownOperation()<br>
> >> >method:<br>
> >> ><br>
> >> >protected DatabaseOperation<br>
> >> >getTearDownOperation()<br>
> >> >throws Exception<br>
> >> >{<br>
> >> >return DatabaseOperation.DELETE_ALL;<br>
> >> >}<br>
> >> ><br>
> >> ><br>
> >> >I read the documents on DbUnit.org,
and it<br>
> >> >looks like this DELETE_ALL
DatabaseOperation<br>
> >> >should make sure everything is torn
down<br>
> >> >from the DB.<br>
> >> >Is this really true?<br>
> >> ><br>
> >> >Is it possible that this operation is
not<br>
> >> >being triggered when unexpected
errors, such<br>
> >> >as NullPointerException occur?<br>
> >> ><br>
> >> >How do others handle this?<br>
> >> >Do you have a gigant try/catch
somewhere<br>
> >> >that catches any Exception and forces<br>
> >> >DELETE_ALL ...somehow?<br>
> >> ><br>
> >> ><br>
> >> >Thank you,<br>
> >> >Otis<br>
> >> ><br>
> >> ><br>
> >><br>
>
>>________________________________________________<br>
> >> >Get your own "800" number<br>
> >> >Voicemail, fax, email, and a lot more<br>
> >> >http://www.ureach.com/reg/tag<br>
> >> ><br>
> >> ><br>
> >><br>
>
>>-------------------------------------------------------<br>
> >> >This sf.net email is sponsored
by:ThinkGeek<br>
> >> >Welcome to geek heaven.<br>
> >> >http://thinkgeek.com/sf<br>
> >><br>
>
>>_______________________________________________<br>
> >> >dbunit-user mailing list<br>
> >> >dbu...@li...<br>
> >><br>
>
>>https://lists.sourceforge.net/lists/listinfo/dbunit-user<br>
> </font>
> 
> 

________________________________________________
Get your own "800" number
Voicemail, fax, email, and a lot more
http://www.ureach.com/reg/tag

Re: Re: [dbunit-user] DB cleanup after exceptions? How?

Automated tests with databases: prep, verify, restore tables

Re: Re: [dbunit-user] DB cleanup after exceptions? How?