BUG while reading from the store provider's data reader

[Species8372]

Hello,

currently we have a severe problem in our production environment.

We execute a bunch of queries similar to the following query, within the same context,
but at some time during the day, sometimes after minutes, sometimes after hours, the following code produces the error:

//Value-Holder Class:
public class DataDto
{
public String LETZTERISIKOKL { get; set; }
//.....other fields
}


//Code with error:
using (DdOlExtended context = new DdOlExtended())
{

//a bunch of other queries like the following....

String query = "select max(dedetail.risikoklasseid),0 AS LETZTERISIKOKL from auskunft, deoutexec, dedetail, antrag
where auskunft.sysauskunft = deoutexec.sysauskunft
and dedetail.sysdeoutexec=deoutexec.sysdeoutexec and antrag.sysid = auskunft.sysid
and auskunft.sysauskunfttyp = 3 and auskunft.statusnum = 0 and antrag.syskd in ( 1,2,3 )
and antrag.sysid =
(SELECT MAX(sysid) FROM antrag
WHERE antrag.syskd IN ( 1,2,3 )
and attribut='Vertrag aktiviert'
AND adatum =
(SELECT MAX(antrag.adatum)
FROM antrag
WHERE antrag.syskd IN ( 1,2,3 ) ) )";

var result = context.ExecuteStoreQuery(query, null).FirstOrDefault(); //this is where the error happens!

}

StackTrace:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> Devart.Data.Oracle.OracleException: Network error:: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at Devart.Data.Oracle.cy.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cy.a(Byte& A_0)
at Devart.Data.Oracle.cu.b(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.bo.c()
at Devart.Data.Oracle.z.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
--- End of inner exception stack trace ---
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at Product.Common.DAO.Auskunft.AggregationDao.MyExecuteQuery[T](DdOlExtended context, String queryString, String paramName, String kdDoubletten, String queryCfgId) in Product\Common\DAO\Auskunft\AggregationDao.cs:line 1076
at Product.Common.DAO.Auskunft.AggregationDao.GetOLDatenBySysAntrag(Int64 auskunftId, Int64 sysAntragId) in Product\Common\DAO\Auskunft\AggregationDao.cs:line 169

System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> Devart.Data.Oracle.OracleException: Network error:: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at Devart.Data.Oracle.cy.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cy.a(Byte& A_0)
at Devart.Data.Oracle.cu.b(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.bo.c()
at Devart.Data.Oracle.z.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
--- End of inner exception stack trace ---
at Product.Common.DAO.Auskunft.AggregationDao.GetOLDatenBySysAntrag(Int64 auskunftId, Int64 sysAntragId) in Product\Common\DAO\Auskunft\AggregationDao.cs:line 317
at Product.Common.BO.Auskunft.AggregationBo.callByValues(Int64 sysAuskunft) in Product\Common\BO\Auskunft\AggregationBo.cs:line 94
at Product.Service.AuskunftService.doAuskunft(Int64 sysAuskunft) in Product\Service\AuskunftService.svc.cs:line 340

Devart.Data.Oracle.OracleException (0x80004005): Network error:: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at Devart.Data.Oracle.cy.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cy.a(Byte& A_0)
at Devart.Data.Oracle.cu.b(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.bo.c()
at Devart.Data.Oracle.z.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I am not able to reproduce the error, but it always happens at the above query. Executing the query with SqlDeveloper on the database delivers either null or some long value. The
duration of the query is below one second, while our database timeout is at 30 seconds.

Connection-Properties:
Direct=True
DBConnectionTimeout=30
DBMinPoolSize=10
DBMaxPoolSize=100
DBStatementCacheSize=5
DBValidateConnection=false
Devart-Driver-Version: 6.70.293
Oracle-Version: 11.1

Field fetched from query:
RISIKOKLASSEID NUMBER(5)


I tried to reproduce the error, but with no success.
In the code producing the error
var result = context.ExecuteStoreQuery(query, null).FirstOrDefault();
the error happens at the call "FirstOrDefault()", as the stacktrace shows.

So i tried to disconnect the database after context.ExecuteStoreQuery(query, null) and before FirstOrDefault().
The resulting Stacktrace was:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> Devart.Data.Oracle.OracleException: Network error:: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at Devart.Data.Oracle.cy.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cy.a(Byte& A_0)
at Devart.Data.Oracle.cu.b(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.cf.l()
at Devart.Data.Oracle.bo.c()
at Devart.Data.Oracle.z.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
--- End of inner exception stack trace ---
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at Product.Common.DAO.Auskunft.AggregationDao.MyExecuteQuery[T](DdOlExtended context, String queryString, String paramName, String kdDoubletten, String queryCfgId) in ProductProduct.Common\DAO\Auskunft\AggregationDao.cs:Zeile 1322.
at Product.Common.DAO.Auskunft.AggregationDao.GetOLDatenBySysAntrag(Int64 auskunftId, Int64 sysAntragId) in ProductProduct.Common\DAO\Auskunft\AggregationDao.cs:Zeile 170.

System.Data.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> Devart.Data.Oracle.OracleException: Network error:: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at Devart.Data.Oracle.cy.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cy.a(Byte& A_0)
at Devart.Data.Oracle.cu.b(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.cf.l()
at Devart.Data.Oracle.bo.c()
at Devart.Data.Oracle.z.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
--- End of inner exception stack trace ---
at Product.Common.DAO.Auskunft.AggregationDao.GetOLDatenBySysAntrag(Int64 auskunftId, Int64 sysAntragId) in ProductProduct.Common\DAO\Auskunft\AggregationDao.cs:Zeile 316.
at Product.Common.BO.Auskunft.AggregationBo.callByValues(Int64 sysAuskunft) in ProductProduct.Common\BO\Auskunft\AggregationBo.cs:Zeile 94.
at Product.Common.BO.Auskunft.SF.AggregationCallByValues.doAuskunft(Int64 sysAuskunft) in ProductProduct.Common\BO\Auskunft\SF\AggregationCallByValues.cs:Zeile 29.
at Product.Service.AuskunftService.doAuskunft(Int64 sysAuskunft) in ProductProduct.Service\AuskunftService.svc.cs:Zeile 338.

Devart.Data.Oracle.OracleException (0x80004005): Network error:: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at Devart.Data.Oracle.cy.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cy.a(Byte& A_0)
at Devart.Data.Oracle.cu.b(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.cf.l()
at Devart.Data.Oracle.bo.c()
at Devart.Data.Oracle.z.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at System.Data.Common.Internal.Materialization.Shaper`1.StoreRead()
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The only thing that gives a hint to the real error is:

The manual timeout has a Stacktrace with:
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.cf.l()
at Devart.Data.Oracle.bo.c()

while the error in our production-environment shows:
at Devart.Data.Oracle.cu.i()
at Devart.Data.Oracle.bo.c()

The call to Devart.Data.Oracle.cf.l() is missing in our production environment, perhaps that can help to find the error?
Although the error says "An error occurred while reading from the store provider's data reader." i dont believe this is the reason. The query executes fast (with optimal execution plan) below one second, the db is available and all other queries called within the same context before
return fast results. Other threads doing the same task at the same time work.

I hope you can provide a quick solution for us.



Thank you,

Markus Brüderl

[Species8372]

The SQL query i posted above has a little copy/paste error, it is:

Code: Select all

select max(dedetail.risikoklasseid) AS LETZTERISIKOKL from auskunft, deoutexec, dedetail, antrag
where auskunft.sysauskunft = deoutexec.sysauskunft
and dedetail.sysdeoutexec=deoutexec.sysdeoutexec and antrag.sysid = auskunft.sysid
and auskunft.sysauskunfttyp = 3 and auskunft.statusnum = 0 and antrag.syskd in ( 1,2,3 )
and antrag.sysid =
(SELECT MAX(sysid) FROM antrag
WHERE antrag.syskd IN ( 1,2,3 )
and attribut='Vertrag aktiviert'
AND adatum =
(SELECT MAX(antrag.adatum)
FROM antrag
WHERE antrag.syskd IN ( 1,2,3 ) ) )

The 1,2,3 is just an example for the IN-Expression, there are usually 1 - 10 entries , substituted directly in the query (as String, not Parameter-Binding!).

Other things that may be the reason of the error:
* our Data-Holder Class has a Field of Type String, while the Query returns a long
* The Query may return null (no result-row!) or a number

[Shalex]

As we understood, the problem occurs intermittently and only in your production environment (it works fine in development environment). Is this correct?
1. Try setting the CommandTimeout property of your ObjectContext instance to 0 (zero).
2. Do your production and development enveronments work with the same instance of Oracle server (to exclude the possible server's problems)? Can this be the network problem (unstable connection)?
3. You are working in the Direct mode, aren't you? Have you tried the OCI mode (via Oracle client)? Does it work without this problem?
4. Try using the "Validate Connection=true;" connection string option for testing purposes.