Oracle 9.2.0.4 connection pooling problem

  • Thread starter Thread starter Paul Speranza
  • Start date Start date
P

Paul Speranza

Hi all,

I am having a problem with connection pooling. My connection string looks
like this:

Data Source=hipqqint;Persist Security Info=True;Pooling=true;Min
Pool Size=3;Max Pool Size=50;Connection Lifetime=60

If I run an insert command - by the way I am testing this in NUnit - it
works fine. However, if I run the insert command as soon as the first one
come back I get this error:

Hip.Sales.UnitTester.SecurityFixture.CreateToken :
System.NullReferenceException : Object reference not set to an instance of
an object.

at System.Data.OracleClient.DBObjectPool.GetObject(Object
owningObject, Boolean& isInTransaction)
at
System.Data.OracleClient.OracleConnectionPoolManager.GetPooledConnection(Str
ing encryptedConnectionString, OracleConnectionString options,
OracleConnection owningObject, Boolean& isInTransaction)
at
System.Data.OracleClient.OracleConnection.OpenInternal(OracleConnectionStrin
g parsedConnectionString, Object transact)
at System.Data.OracleClient.OracleConnection.Open()
at Hip.Sales.DataAccess.OracleHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText,
OracleParameter[]
commandParameters) in
C:\HIPDev\HipStudio\VERSION_1_2\DataAccess\OracleHelper.cs:line 174
at Hip.Sales.MarketProminence.Authentication.CreateWebToken(String
payload) in C:\HIPDev\HipStudio\VERSION_1_2
\MarketProminence\Security\Authentication.cs:line 40
at Hip.Sales.UnitTester.SecurityFixture.CreateToken() in
C:\HIPDev\HipStudio\VERSION_1_2\UnitTester\SecurityFixture.cs:line 44

My thinking is that there may be an issue in the MS OracleClient. If I take
off the pooling attributes so it looks like this:

Data Source=hipqqint;Persist Security Info=True;

everything is fine.

Our DBA was watching the database while I ran the tests and he got no
changes when the error occurred so that makes me think even more that it is
the MS driver.

Can anyone help?

..Net 1.1
VS .Net 2003
Oracle 9.2.0.4
Windows XP all patches

Thanks,
Paul Speranza
 
Hi Paul,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you are getting an
NullReferenceException when executing an insert command using Oracle
provider for .NET. If there is any misunderstanding, please feel free to
let me know.

I'm not quite sure with the meaning of "if I run the insert command as soon
as the first one". Could you explain what's "the first one"? From the stack
trace, the problem occurs when ADO.NET is trying to get a connection from
the connection pool. Please try to use "Pooling=false" in your connection
string to turn off connection pooling.

If the problem still persists, could you please let me see your code so
that I can deliver my assistance more quickly? Thanks!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Not sure if this is related but....

(CSLA is Rocky Lhotka's framework for Business Objects.)
http://www.lhotka.net/ArticleIndex.aspx?area=CSLA .NET
===================================================================
Rocky stated:

Due to the way nunit works, if you are using CSLA security you must be sure
to log in at the top of each test and log out at the bottom. If you are
still logged in when the test method completes the thread will return to
nunit with the CSLA security context.

Since nunit is running in a separate appdomain from your code, your
BusinessPrincipal will be serialized back to the nunit appdomain. That would
be fine, except that nunit doesn't have access to CSLA.dll, so it can't
deserialize the object and so it fails.

The easiest thing is to do your nunit tests using Windows security, since
then there's no issue. If you are using CSLA security (or other custom types
of security) then you need to be aware of this issue.
--
Joe Fallon




Paul Speranza said:
Hi all,

I am having a problem with connection pooling. My connection string looks
like this:

Data Source=hipqqint;Persist Security Info=True;Pooling=true;Min
Pool Size=3;Max Pool Size=50;Connection Lifetime=60

If I run an insert command - by the way I am testing this in NUnit - it
works fine. However, if I run the insert command as soon as the first one
come back I get this error:

Hip.Sales.UnitTester.SecurityFixture.CreateToken :
System.NullReferenceException : Object reference not set to an instance of
an object.

at System.Data.OracleClient.DBObjectPool.GetObject(Object
owningObject, Boolean& isInTransaction)
at
System.Data.OracleClient.OracleConnectionPoolManager.GetPooledConnection(Str
ing encryptedConnectionString, OracleConnectionString options,
OracleConnection owningObject, Boolean& isInTransaction)
at
System.Data.OracleClient.OracleConnection.OpenInternal(OracleConnectionStrin
g parsedConnectionString, Object transact)
at System.Data.OracleClient.OracleConnection.Open()
at Hip.Sales.DataAccess.OracleHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText,
OracleParameter[]
commandParameters) in
C:\HIPDev\HipStudio\VERSION_1_2\DataAccess\OracleHelper.cs:line 174
at Hip.Sales.MarketProminence.Authentication.CreateWebToken(String
payload) in C:\HIPDev\HipStudio\VERSION_1_2
\MarketProminence\Security\Authentication.cs:line 40
at Hip.Sales.UnitTester.SecurityFixture.CreateToken() in
C:\HIPDev\HipStudio\VERSION_1_2\UnitTester\SecurityFixture.cs:line 44

My thinking is that there may be an issue in the MS OracleClient. If I take
off the pooling attributes so it looks like this:

Data Source=hipqqint;Persist Security Info=True;

everything is fine.

Our DBA was watching the database while I ran the tests and he got no
changes when the error occurred so that makes me think even more that it is
the MS driver.

Can anyone help?

.Net 1.1
VS .Net 2003
Oracle 9.2.0.4
Windows XP all patches

Thanks,
Paul Speranza
Click to expand...
 
I think I need to clear up a few things here.

I have a test that inserts a new record. I want to test connection pooling,
so I have inserted them in my connection string.

I run the test once and everything is fine. If I run it again as soon as the
first run is done, I get the error.

If take the connection pooling attributes out of the connection string, I
can keep running the test as each run has finished with no error.
Waiting a minute clears whatever the problem is and I can run the test once,
and if I immediately try it again, I get the error. I am assumming that the
Connection Lifetime attribute is being honored.

Based on the stack trace I am assuming the error is happening in the MS
Oracle Provider, as indicated in the stack trace I passed along.

So Joe, thanks for the input but I don't think that NUnit is the problem
here - yet. All of my tests work every time in NUnit with the pooling
attributes out of the connection string. Since I don't have the actual front
end written yet, NUnit is my launcher, though I am thinking I should write a
web form to call this without NUnit to verify. Is this correct TDD
procedure? (I am really trying to follow the TDD rules, which I believe
requires me to stick with NUnit. I am leaning towrds cheating here. Maybe I
am being to TDD anal.)

Ah, I cheated. I stopped writing this message and created a web form. It
still does it so it is not NUnit :0)


Regards,
Paul Speranza





Paul Speranza said:
Hi all,

I am having a problem with connection pooling. My connection string looks
like this:

Data Source=hipqqint;Persist Security Info=True;Pooling=true;Min
Pool Size=3;Max Pool Size=50;Connection Lifetime=60

If I run an insert command - by the way I am testing this in NUnit - it
works fine. However, if I run the insert command as soon as the first one
come back I get this error:

Hip.Sales.UnitTester.SecurityFixture.CreateToken :
System.NullReferenceException : Object reference not set to an instance of
an object.

at System.Data.OracleClient.DBObjectPool.GetObject(Object
owningObject, Boolean& isInTransaction)
at
System.Data.OracleClient.OracleConnectionPoolManager.GetPooledConnection(Str
ing encryptedConnectionString, OracleConnectionString options,
OracleConnection owningObject, Boolean& isInTransaction)
at
System.Data.OracleClient.OracleConnection.OpenInternal(OracleConnectionStrin
g parsedConnectionString, Object transact)
at System.Data.OracleClient.OracleConnection.Open()
at Hip.Sales.DataAccess.OracleHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText,
OracleParameter[]
commandParameters) in
C:\HIPDev\HipStudio\VERSION_1_2\DataAccess\OracleHelper.cs:line 174
at Hip.Sales.MarketProminence.Authentication.CreateWebToken(String
payload) in C:\HIPDev\HipStudio\VERSION_1_2
\MarketProminence\Security\Authentication.cs:line 40
at Hip.Sales.UnitTester.SecurityFixture.CreateToken() in
C:\HIPDev\HipStudio\VERSION_1_2\UnitTester\SecurityFixture.cs:line 44

My thinking is that there may be an issue in the MS OracleClient. If I take
off the pooling attributes so it looks like this:

Data Source=hipqqint;Persist Security Info=True;

everything is fine.

Our DBA was watching the database while I ran the tests and he got no
changes when the error occurred so that makes me think even more that it is
the MS driver.

Can anyone help?

.Net 1.1
VS .Net 2003
Oracle 9.2.0.4
Windows XP all patches

Thanks,
Paul Speranza
Click to expand...
 
Hi Paul,

Could you please simplify your code by just opening the connection and
execute the insert command to see if the problem can be reproduced? If it
still persists, could you please show us your code? Thanks for your
cooperation.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin,

Its this simple - if I execute any command with the connection pooling
attributes I get the error on the second attempt. If I leave them off it
works.

This fails
Data Source=hipqqint;Persist Security Info=True;Pooling=true;Min Pool
Size=3;Max Pool Size=50;Connection Lifetime=60

This works
Data Source=hipqqint;Persist Security Info=True;
 
Hi Paul,

Since ADO.NET uses connection pooling by default, and "Data
Source=hipqqint;Persist Security Info=True;" works fine, I think the
problem might have something to do with the Min pool size, Max Pool Size
and Connection Lifetime settings. Would you please try the following
connection strings for trouble shooting?

1. "Data Source=hipqqint;Persist Security Info=True;Pooling=true;"
2. "Data Source=hipqqint;Persist Security Info=True;Pooling=true;Max Pool
Size=50;"
3. "Data Source=hipqqint;Persist Security Info=True;Pooling=true;Max Pool
Size=50;Connection Lifetime=60;"
4. "Data Source=hipqqint;Persist Security Info=True;Pooling=true;Max Pool
Size=50;Connection Lifetime=60;Min Pool Size=3;"
5. "Data Source=hipqqint;Persist Security Info=True;Pooling=true;Max Pool
Size=50;Connection Lifetime=60;Min Pool Size=0;"

Please let me know the testing results, so that I can make further researh
on it. Thanks for your cooperation!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Paul,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Paul,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top