Oracle testing issue, looks like bug: Separate connections to same DB

[Shai Berger]

Hi all,

I'm seeing a problem with testing on Oracle, in a setting where there are two
defined databases that reference the same connection parameters (this is done
to enable operations in two parallel, separate transactions); the 'other'
database is not treated as a test database. This means that, during tests,
operations on it are performed on the 'production' database (this is all in a
development environment, thankfully).

Has anyone else run into this?

Django==1.4.3

Thanks,
Shai.

[Jani Tiainen]

I assume that you're referring to test setup instructions on
https://code.djangoproject.com/wiki/OracleTestSetup page?

You should see that there is defined TEST_USER, TEST_TBLSPACE and
TEST_TBLSPACE_TMP variables to different names since those are ones used
when running tests.

Django uses main user just to connect database and to create test users.
Main user needs enough permissions (I've usually set that to DBA to make
life easier) to enable creation of test user and needed tablespaces.

You can though have two different users there and even two different
database instances if you do have those. Then there is no need to
provide those custom values.

--
Jani Tiainen

- Well planned is half done and a half done has been sufficient before...

[Shai Berger]

Hi Jani and all,

On Thursday 27 December 2012 13:28:23 Jani Tiainen wrote:
> 27.12.2012 13:08, Shai Berger kirjoitti:
> > Hi all,
> >
> > I'm seeing a problem with testing on Oracle, in a setting where there are
> > two defined databases that reference the same connection parameters
> > (this is done to enable operations in two parallel, separate
> > transactions); the 'other' database is not treated as a test database.
> > This means that, during tests, operations on it are performed on the
> > 'production' database (this is all in a development environment,
> > thankfully).
> >
> > Has anyone else run into this?
> >
> > Django==1.4.3
> >
> > Thanks,
> >
> > Shai.
>
> I assume that you're referring to test setup instructions on
> https://code.djangoproject.com/wiki/OracleTestSetup page?
>

Yes and no: I am referring to a variation on this theme.

> You should see that there is defined TEST_USER, TEST_TBLSPACE and
> TEST_TBLSPACE_TMP variables to different names since those are ones used
> when running tests.
>

In my case, these values *should* be equal. I am not trying to run the Django
test suite, but tests for my own applications, and my applications rely on
having two separate connections to the same database.

> Django uses main user just to [...]

I understand pretty well how the django test framework uses its connections.
I'm pointing out a case where it isn't doing it correctly.

To clarify: I am defining my databases by something like this in settings:

DATABASES = {
'defailt' : ... # some full definition
}

DATABASES['auditlog'] = DATABASES['default'].copy()

and I even add this, which should help

DATABASES['auditlog']['TEST_MIRROR'] = 'default'

but when I run the tests, and print out the configuration from the test runner,
it looks like this:

{'DATABASES': {'auditlog': {'ENGINE': 'healarium.utils.oracle_backend',
'HOST': 'oracle',
'NAME': 'orcl',
'OPTIONS': {'threaded': True},
'PASSWORD': 'xxxx',
'PORT': '1521',
'TEST_CHARSET': None,
'TEST_COLLATION': None,
'TEST_MIRROR': 'default',
'TEST_NAME': None,
'TEST_USER': 'test_usr1',
'TIME_ZONE': 'Asia/Jerusalem',
'USER': 'usr1'},
'default': {'ENGINE': 'healarium.utils.oracle_backend',
'HOST': 'oracle',
'NAME': 'orcl',
'OPTIONS': {'threaded': True},
'PASSWORD': 'Im_a_lumberjack',
'PORT': '1521',
'SAVED_PASSWORD': 'xxxx',
'SAVED_USER': 'usr1',
'TEST_CHARSET': None,
'TEST_COLLATION': None,
'TEST_MIRROR': None,
'TEST_NAME': None,
'TEST_USER': 'test_usr1',
'TIME_ZONE': 'Asia/Jerusalem',
'USER': 'test_usr1'},

The thing to notice is how in 'default' there is a 'SAVED_PASSWORD' and
'SAVED_USER', and the 'USER' is equal to the 'TEST_USER', while in 'auditlog'
(the 'other' database connection) these do not hold (the only reason
'auditlog' has a correct 'TEST_USER' entry is that in trying to track this
down, I defined it explicitly for 'default' and it was copied).

Hope the issue is now clearer,

Shai.

[Jani Tiainen]

TEST_MIRROR = 'default' means that instead of creating test database of
"auditlog" using independent database connection Django will reuse
connection to "default". That's how Django makes testing of replication
happen (that's what TEST_MIRROR setting is for).

IOW it doesn't matter what your auditlog contains if you have
TEST_MIRROR set. Django will reuse connection there.

You might get what you want by removing TEST_MIRROR option and setting
TEST_CREATE and TEST_USER_CREATE values as false. And propably defining
that auditlog database test depends on default test database.

[Jani Tiainen]

27.12.2012 14:41, Shai Berger kirjoitti:

Just out of curiosity - what's the rationale to duplicate connection
information? Why it's so important?

[Shai Berger]

On Thursday 27 December 2012 15:11:31 Jani Tiainen wrote:
>
> TEST_MIRROR = 'default' means that instead of creating test database of
> "auditlog" using independent database connection Django will reuse
> connection to "default". That's how Django makes testing of replication
> happen (that's what TEST_MIRROR setting is for).
>

Removing TEST_MIRROR changes nothing (except the printing of databases). If
you were correct, and TEST_MIRROR were the issue, then this would cause the
whole test suite to fail on attempt to create the 'auditlog' test database
(which would already exist); but no attempt is made to create this database.

Also, my tests currently fail because they include some
'model.objects.get(...)' call, which would succeed if the test were run on an
empty database; instead, it fails because it retrieves more than one record.
The number of records returned increases each time I run the test, proving (to
me, at least) that the records are written to a persistent database, not one
that is deleted when the tests end.

[Shai Berger]

On Thursday 27 December 2012 15:16:15 Jani Tiainen wrote:
>
> Just out of curiosity - what's the rationale to duplicate connection
> information? Why it's so important?

As I noted before, the idea is to use the second connection to write things in
a separate transaction. As the name 'auditlog' implies, this is an audit log;
I don't want records written there to be rolled back when a request encounters
an error (one of these records may well include the exception).

[Jani Tiainen]

It might be very much that such a setup you're looking is not possible
for testing.

Though still it doesn't make any sense to duplicate connections to same
database, to same user.

[Jani Tiainen]

I've never done such a setup (we always have separate schemas) so I've
feeling that testing such a setup doesn't work right away.

One option could be create separate test users and add synonyms (by
custom SQL script on auditlog test database creation) for auditlog user
to default test user auditlog tables.

There also exists a oldish ticket:
https://code.djangoproject.com/ticket/14415

That indicates that issue is fixed...

[Shai Berger]

Hi again,

On Thursday 27 December 2012, Jani Tiainen wrote:
> 27.12.2012 15:32, Shai Berger kirjoitti:
> > On Thursday 27 December 2012 15:16:15 Jani Tiainen wrote:
> >> Just out of curiosity - what's the rationale to duplicate connection
> >> information? Why it's so important?
> >
> > As I noted before, the idea is to use the second connection to write
> > things in a separate transaction. As the name 'auditlog' implies, this
> > is an audit log; I don't want records written there to be rolled back
> > when a request encounters an error (one of these records may well
> > include the exception).
>
> I've never done such a setup (we always have separate schemas) so I've
> feeling that testing such a setup doesn't work right away.
>
> One option could be create separate test users and add synonyms (by
> custom SQL script on auditlog test database creation) for auditlog user
> to default test user auditlog tables.
>

That is one ugly workaround, but I guess I'll take it if I can't find anything
else.

> There also exists a oldish ticket:
> https://code.djangoproject.com/ticket/14415
>
> That indicates that issue is fixed...

Yes, I submitted that ticket. Problems there were much worse than the problems
I'm facing now, but it is all around the same issue. And BTW, the behaviour on
1.3.5 is the same as 1.4.3 (not that I expect 1.3 to be fixed for this, just
indicating this is not a 1.4-era regression).

Shai.

[Shai Berger]

Hi again,

On Thursday 27 December 2012 15:50:58 Jani Tiainen wrote:
> There also exists a oldish ticket:
> https://code.djangoproject.com/ticket/14415
>
> That indicates that issue is fixed...

After looking further into this, this is indeed a bug, and indeed separate
from 14415. For one, the problem now is only with Oracle.

The root of the problem is that unlike the other DBMSs, where you can create a
new database for testing, Oracle does not have the concept of separate
databases within the same server. So, to have a clean schema to work in, the
test framework creates a new user (in Oracle there's also a 1-1 correspondence
between users and schemas) when it creates the test database.

This is all done in the backend-specific _create_test_db(). The problem is that
this code only takes care of a single db (naturally). There is generic code
(in DjangoTestSuiteRunner.setup_databases()), that takes care of multiple dbs
-- including same-db-references and mirrors (hereafter, for simplicity, just
"mirrors"). This code isn't aware of the Oracle-specific settings changes. To
handle mirrors, it just updates the database name of the mirror to the one set
by _create_test_db(). In the Oracle case, this is a no-op
(self.connection.settings_dict['NAME'] is not changed); and nobody is left to
update the user, password and tablespace parameters, which the backend does
change.

This means that, not just my somewhat peculiar use case is affected -- the
whole concept of TEST_MIRROR is broken on Oracle.

Can anyone attest differently? Is there a reason not to open a bug?

Also, I intend to fix this by using a custom test-runner, which will invoke a
new backend-specific API to take care of the mirroring; the default behavior
will be the one used today, and I'll be able to override it in (my custom)
Oracle backend.

Any comments about this approach?

Thanks,
Shai.

[Anssi Kääriäinen]

Seems like you are correct - line 321 of django/test/simple.py copies
the NAME, but nothing else between mirrors. This should be handled by
backend.creation method instead. Please open a bug.

- Anssi

[Shai Berger]

On Sunday 30 December 2012 21:54:52 Anssi Kääriäinen wrote:
>
> Seems like you are correct [...]. Please open a bug.
>
For anyone interested that isn't on the new-bugs list, it's
https://code.djangoproject.com/ticket/19542

For anyone else: my apologies for the noise.

Shai.

[Jani Tiainen]

31.12.2012 14:18, Shai Berger kirjoitti:

> On Sunday 30 December 2012 21:54:52 Anssi K��ri�inen wrote:
>>
>> Seems like you are correct [...]. Please open a bug.
>>
> For anyone interested that isn't on the new-bugs list, it's
> https://code.djangoproject.com/ticket/19542
>
> For anyone else: my apologies for the noise.
>
> Shai.
>

Can you please setup simple testcase to ease bugfixing?

[Shai Berger]

On Thursday 03 January 2013, Jani Tiainen wrote:
> 31.12.2012 14:18, Shai Berger kirjoitti:

> > On Sunday 30 December 2012 21:54:52 Anssi Kääriäinen wrote:
> >> Seems like you are correct [...]. Please open a bug.
> >
> > For anyone interested that isn't on the new-bugs list, it's
> > https://code.djangoproject.com/ticket/19542
> >
> > For anyone else: my apologies for the noise.
> >
> > Shai.
>
> Can you please setup simple testcase to ease bugfixing?

(sorry about the delay in responding to this -- mis-managed mail)

The issue here is mostly to do with settings; thus, no testcase could be added
to the Django test suite. There are similar comments from Russel Keith-Magee
quoted on the old bug #14415.

I couldn't reasonably provide a working settings file for Oracle; for debugging
this, you just need to have a file with two connections, and have one marked as
a test mirror, i.e.

DATABASES = {
'default' : {
... whatever
},
'other' : {
... whatever
'TEST_MIRROR' = 'default'
}
}

Test code for this should look like (untested):

from django.contrib.auth.models import Group

class MirrorTest(TransactionTestCase):
def test_method():
gname='unique_and_unnatural'
g1 = Group.objects.create(name=gname) # uses default
transaction.commit(using='default')
g2 = Group.objects.all().using('other').get(name=gname)
self.assertEqual(g1,g2)

The test should verify that 'default' and 'other' are indeed the same db.

Hope this helps,
Shai.