Incorrectly thrown Sql Exception and weird behaviour of a quite simple query in OpenAccess 2013Q3 +

[Alexander Turlov]

Apologies if this is the wrong place. After upgrading from OpenAccess 2012Q3 to 2013Q3 we started seeing this unexplainable problem.

We execute the following LINQ which used to work with 2012Q3 and not working with 2013Q3 against the same data:

var data = from datum in db.Data.WithOption(new QueryOptions {CommandTimeout = 100})
         where datum.FileId == 1814592013533314000
         select datum;

The runtime throws an exception:

System.InvalidOperationException: An exception occured during the execution of 'Extent<StickerYouModel.AssetDatum>().Where(datum => (datum.FileId == 1814592013533314000
))'. Failure: Error reading field Model.Datum.data from ResultSet: System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
   at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at OpenAccessRuntime.Data.VariableLengthAnsiStringConverter.Read(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
   at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at OpenAccessRuntime.Data.VariableLengthAnsiStringConverter.Read(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)
See InnerException for more details.
Complete Expression:
.Call System.Linq.Queryable.Where(
    .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[Model.Datum]>(Extent<Model.Datum>()),
    '(.Lambda #Lambda1<System.Func`2[Model.Datum,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[Model.Datum,System.Boolean]>(Model.Datum $datum) {
    $datum.FileId == 1814592013533314000
L
}
 ---> Telerik.OpenAccess.Exceptions.DataStoreException: Error reading field StickerYouModel.AssetDatum.data from ResultSet: System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
   at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at OpenAccessRuntime.Data.VariableLengthAnsiStringConverter.Read(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
   at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at OpenAccessRuntime.Data.VariableLengthAnsiStringConverter.Read(DataHolder& data)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol) ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
   at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ReadColumnData()
   at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at OpenAccessRuntime.Data.VariableLengthAnsiStringConverter.Read(ref DataHolder data)
   at OpenAccessRuntime.Relational.RelationalGenericState.copyPass1Fields(ResultSet rs, FetchGroupField[] fetchGroups, Int32 firstCol)
 --- End of inner exception stack trace ---
    at OpenAccessRuntime.ExceptionWrapper.Throw()
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x, Boolean needsRollback)
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.getObjectById(Object oid, Boolean validate)
   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.GetObjectByKey(ObjectKey key)
   at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.GetObjectByKey(ObjectKey key)
   at OpenAccessRuntime.ObjectScope.GetObjectByKey(ObjectKey key)
   at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type resultType, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
   at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
 --- End of inner exception stack trace ---
    at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
   at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQueryMulti(Expression expr, ExecutionSettings settings, Object[] grpVals, Boolean checkOid, FetchStrategyBuilder strategyBuilder, QueryOptions options)
   at Telerik.OpenAccess.Query.Piece`1.ExecuteMultiple()
   at Telerik.OpenAccess.Query.Piece`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()

If modify a query as following:

long [] ids = new [] {1814592013533314000};
var data = from datum in db.Data.WithOption(new QueryOptions {CommandTimeout = 100})
           where ids.Contains(datum.FileId)
           select datum;

then it works. CommandTimeout options still required because the record contains a huge TEXT field which is really large and takes longer than 30 sec to retrieve (not a concern for this post).

If I execute both SQL statements produced by LINQ in Management Studio they succeed both and there is no difference in execution plan.
To emphasize: this problem did not happen with older version of OpenAccess and there is no meaningful explanation why it happens now. Appreciate any comments and ideas. 

[Thomas]

Hi Alexander,

this is a bit strange, especially because the Contains approach should lead to a slightly more complex SQL. The execution plan might be the same, however. As you noted, the loading of the huge text field might take more than 30 seconds; I wonder why this should be the case (most likely a single column value from a single row, right?). Maybe you are running out of memory? Would not be a stream being more appropriate?
Still, I'm wondering what could cause such a difference in behavior.
Do you use shorter command timeouts in other places? You can alter the command timeout globally too via the RuntimeConfiguration.CommandTimeout setting.

Regards,
Thomas
Telerik