Not Seeing a Newly Committed Record in MySQL from Grails Test_Case

[Steph Thompson]

In a Grails test environment I run an integration test that commits a
record to MySQL 5. I can retrieve the record in the test_case but
when I run a query in a console window against the DB, the record does
not show. The latter is outside the test-case and just my attempt to
see the record via independent means to prove my test_case is setup/
functioning correctly.

The test_case passes, but I lack confidence in it if I can't
independently "open the box" of MySQL in
a console window or in AquaData Studio just to prove to myself the
newly saved record is there.

Does this sound familiar to anyone? Below are more details of what I
am doing:

A domain object of type Servicer (i.e. company name address, etc.) I
create it and then save to
MySQL via a test_case. The MySQL DB is initialized to hold 14
records prior to the test_case.
The DB init is done by a script run in AquaData Studio. The operative
code for the save and retrieve
is listed below.
********************************************************************************************************************
myServ.save(flush:true) // flush to get it to commit to the db
def dbNum = myServ.ident() // ident returns the DB record # for newly
saved record
def dbServ = Servicer.get(dbNum) // fetch the last record stored to
a new object.

assertEquals ( perform string compares between MyServ and dbServ )
// these all SUCCEED
********************************************************************************************************************

After the test_case passes, in a console window I run a Select * from
Servicer table and
only the 14 original records are displayed. The newest one (#15) is
not listed.

I run the test again and the latest record increments to #16. I run
an SQL script to drop the table, reinsert the original 14 records then
run my test again, and the grails test_case record now becomes #15
again.

This tells me the record is in the MySQL database, but it won't show
when I run an SQL query directly against the DB from a console
window.

When I run the grails app and use the UI I still only see the
original 14 records. No 15th record will display.

I use AquaData Studio to initialize the DB table with 14 records.
After a "test run" the 15th record does NOT show up when I run a Query
in AquaData Studio either.

Any ideas?

config stuff:
-----------------
WinXP SP 2
MySQL vers 5.0.27
AquaData Studio free vers 4.7.2
Grails vers 1.0.1

Steph T

[Mike Hugo]

Grails wraps each test inside a transaction, so any changes made
inside your test method are rolledback at the end of the test method
to keep a clean database state.

Mike

[Robert Fischer]

This is to be construed as a feature.

~~ Robert.

[Jesse O'Neill-Oine]

For me, this is one of the best features ever!  I love not having to worry about what I insert, update, delete or whatever.  I never have a test that fails for mysterious reasons only to find out that the DB was jacked.

Jesse

--
----------------------------------------------------------
Jesse O'Neill-Oine // je...@refactr.com
Refactr LLC // http://refactr.com
mobile // 612-670-5037
----------------------------------------------------------

[Ted Naleid]

Test cases happen in a transaction that is rolled back after the test
is run (that's why you can repeatedly run a test without it failing or
impacting other tests).

I'm not positive, but in your test class you might be able to set

def transactional = false

To turn that off (I know that works on services, which are
transactional by default, but might not work on integration tests).

I normally just use my domain objects to inspect to see if what I've
done is correct.

def f = new Foo(name: "bar")
f.save()
assert Foo.findByName("bar").name == "bar"

Either that or take my setup code and put it in a grails console and
check the database to see if it works.

Also remember that the default environment for a test needs to be set
up in your DataSource.groovy to point at your mysql connection as it's
different from

HTH,
Ted

On May 6, 2008, at 9:39 AM, Steph Thompson wrote:

[Hamlet D'Arcy]

I vote it a feature too.

This is called "Transaction Rollback Teardown"
http://xunitpatterns.com/Transaction%20Rollback%20Teardown.html

To quote Dr. Phil: "If you can name it, you can tame it".

--
Hamlet D'Arcy
haml...@gmail.com

[Steph Thompson]

Wow - thanks for the replies.
I had not thought of the Transaction feature. In the service
object there is a statement to set = true or false but none of the
books I have seen mention that I can turn it off or on anywhere else.
(in my test_case for example).

Question: If the transaction rolls back the events of the
test_case,
then why does the DB not decrement the record ID in the table as
well?
Without any other changes, if I run the test_case a second time the
statement

def dbNum = myServ.ident()

is assigned a value of "16". And it continues incrementing until I
"drop table; create table; Insert Into..." to reinstantiate the DB.
Only then does the test_case return to the Record_ID of 15
(next after the initial 14 records I insert).

Because I am learning Grails, I want to prove to myself the DB is
actually
updating for the new record. So....

1. I will try *** def transactional = false *** in setUp() or in my
testCase() methods

to see if it works.

2. I might try a retrieve_all_records after the save() just to see if
it
returns 15 records.

3. Does anyone know where in the Grails Source the transaction/
rollback instructions
reside so I can see it?

Thanks,
Steph T

> > ***************************************************************************­*****************************************

> > > myServ.save(flush:true)               // flush to get it to commit to
> > the db
> > > def dbNum = myServ.ident()            // ident returns the DB record #
> > for newly
> > > saved record
> > > def  dbServ = Servicer.get(dbNum)     // fetch the last record stored to
> > > a new object.
>
> > > assertEquals (  perform string compares between MyServ and dbServ )
> > >       // these all SUCCEED
>

> > ***************************************************************************­*****************************************

> hamlet...@gmail.com- Hide quoted text -
>
> - Show quoted text -

[Ted Naleid]

Whoops, I never finished that sentence. I was just saying that the
test data source needs to be set to mysql if that's where you're
looking. It uses a different data source ("test") from development
mode. Even if test commits were persistent, you'd never find your
data changes unless you change the test environment data source to
point to a real mysql database.

-Ted

[Steph Thompson]

Ted:

A configuration detail I failed to mention: I have the
ConfigSource.groovy
file set to three different instances of MySQL labeled PPWS1_Dev,
_Test, and_Prod.

I call the tests via c:\> grails test test-app

which sets the environment to test and then runs the app against the
_Test DB which has the 14 records initialized.

I can run the app using the Grails default UI, create a record and
save it
top the _Dev DB and have it persisted such that I can see it via an
SQL SELECT
statement from the console. I just tried that with the test DB, I see
the new record
via an SQL SELECT in the console.

I think I have the config set up to meet your suggestion, ne c'est
pas?

Steph

> > different from- Hide quoted text -

[Ted Naleid]

Yep, it sounds like you've got it set up right.  It was just something I wanted to note as some people don't realize (or forget) that the data source is different in each environment and that the test environment is different from the development environment.

Regarding your previous question about the id values incrementing, even though he transaction is rolled back, that's normal behavior.  The IDs that are generated by the database aren't part of the transaction and because of that, contiguous id values aren't guaranteed.  This is a known "bug"/"feature" in mysql and I'd be surprised if other systems didn't suffer from it as well.  Most of the time, contiguous ID's aren't a requirement as no real value is supposed to be attached to the id (other than it being unique).

There's an item on the mysql bug website about this: http://bugs.mysql.com/bug.php?id=6714

-Ted

[Steph Thompson]

Ted et alia:

Good point about the record_ID. "Just so its unique"
seems to be the main criteria and it appears that
MySQL won't reuse the record_ID unless the table has been recreated.

Anyway, Just had a chance to try the suggestion of
****************************************
Boolean transactional = false;
****************************************
(A) when set in the individual test_case the results were
negative. No change to the table.
HOWEVER.....
(B) set the statement in the setUp() routine of the testSuite
now I see the record actually residing in the table via a
SELECT * from the console.

So, Success! I just wanted to see that my test assumptions were
correct
for inserting and retrieving records. Now I can.

I also learned something about transactions in the Grails test
environment.

Thanks everyone for your help.

Steph

> >> - Show quoted text -- Hide quoted text -

[Ted Naleid]

A little bit of follow up (as we actually ran into a need to turn off transactions in integration tests today to test some hibernate stuff).  Setting

static transactional = false  // not a non-static Boolean as you have below

In your unit test will turn off implicit transactions and allow you to control them within your integration test.  This is touched on by Graeme on a comment to this JIRA ticket: http://jira.codehaus.org/browse/GRAILS-2358

Sounds like you have a solution that works for you Steph, but I thought that someone else might find this useful in the future.

-Ted

[Steph Thompson]

Thanks Ted.

I will change the statement to static. I just lifted the
original statement out of the Graham Defin Grails chapter on
Services.

I have used the statement twice just to see the actual results in the
DB.
> for the save record
> for a change to a field in an existing record.

This all stems from the occassional obsession of wanting to see under
the hood
even when the framework is actually "doin' ya a favor".


Steph T