Major issues with Orchard site stability

Topics: Troubleshooting
Sep 21, 2015 at 10:50 AM
Hi, I'm hoping someone can help me with some major stability issues we are having with an Orchard site we have created. We have spent a considerable amount of time developing a site using Orchard, but now be have come to deploy it we are having stability issues that seem to relate to NHibernate and transaction handling. The site can behave normally at first but then we start seeing connection errors (example stack trace below). After some searching we have seen suggestions of altering the connection string with flags such as Enlist=false and MultipleActiveResultSets=true but these do not seem to help. We have also tried writing our own SQL and executing this against the NHibernate session but this also gives errors. Any advice would be very much appreciated as we are struggling to see how to resolve this.

2015-09-21 10:35:04,907 [23] Orchard.ContentManagement.Drivers.Coordinators.ContentPartDriverCoordinator - Default - InvalidOperationException thrown from IContentPartDriver by CompanyNamespace.Drivers.Vehicles.RangePartDriver
http://www.xxxxxxxxxx.co.uk/xxxx-xxxx
System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at CompanyNamespace.Services.Vehicles.VehiclePartSearchService.GetChildVehicleItemsSQL[T](IEnumerable
1 parentIds, VehicleLevel requiredLevel)
at CompanyNamespace.Drivers.Vehicles.RangePartDriver.GetBodystyles(IContent part)
at CompanyNamespace.Drivers.Vehicles.RangePartDriver.<>c__DisplayClass9.<Display>b__7()
at Orchard.ContentManagement.Drivers.ContentPartDriver1.<>c__DisplayClass15.<ContentShape>b__14(BuildShapeContext ctx) in c:\Builds\3\Shared Components\CompanyNamespace\Sources\Shared Components\src\Orchard\ContentManagement\Drivers\ContentPartDriver.cs:line 196
at Orchard.ContentManagement.Drivers.ContentPartDriver
1.<>c__DisplayClass1b.<ContentShapeImplementation>b__1a(BuildShapeContext ctx) in c:\Builds\3\Shared Components\CompanyNamespace\Sources\Shared Components\src\Orchard\ContentManagement\Drivers\ContentPartDriver.cs:line 205
at Orchard.ContentManagement.Drivers.ContentShapeResult.ApplyImplementation(BuildShapeContext context, String displayType) in c:\Builds\3\Shared Components\CompanyNamespace\Sources\Shared Components\src\Orchard\ContentManagement\Drivers\ContentShapeResult.cs:line 45
at Orchard.ContentManagement.Drivers.ContentShapeResult.Apply(BuildDisplayContext context) in c:\Builds\3\Shared Components\CompanyNamespace\Sources\Shared Components\src\Orchard\ContentManagement\Drivers\ContentShapeResult.cs:line 21
at Orchard.ContentManagement.Drivers.Coordinators.ContentPartDriverCoordinator.<>c__DisplayClassa.<BuildDisplay>b__9(IContentPartDriver driver) in c:\Builds\3\Shared Components\CompanyNamespace\Sources\Shared Components\src\Orchard\ContentManagement\Drivers\Coordinators\ContentPartDriverCoordinator.cs:line 49
at Orchard.InvokeExtensions.Invoke[TEvents](IEnumerable1 events, Action1 dispatch, ILogger logger) in c:\Builds\3\Shared Components\CompanyNamespace\Sources\Shared Components\src\Orchard\InvokeExtensions.cs:line 17
Developer
Sep 22, 2015 at 12:03 PM
Are you accessing using a DB connection managed by yourself? Looking at the stack trace, this does not seem to have anything to do with Orchard itself. Perhaps you could show us the code in your VehiclePartSearchService.GetChildVehicleItemsSQL method?
Sep 22, 2015 at 1:40 PM
Originally we were using the session locator as follows:
        var session = this.sessionLocator.For(typeof(VehiclePartRecord));
        using (var cmd = session.Connection.CreateCommand())
        {
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;

            session.Transaction.Enlist(cmd);

            using (SqlDataReader reader = (SqlDataReader)cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }

                reader.Close();
            }
        }
It appears this was causing the errors however, because now we have changed it to the following the situation has improved:
        var connection = new SqlConnection(connectionString);
        using (var cmd = new SqlCommand(query, connection))
        {
            cmd.CommandType = CommandType.Text;
            connection.Open();
            using (SqlDataReader reader = (SqlDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetInt32(0));
                }

                reader.Close();
            }
        }

It seems that every new request is not starting a new NHibernate session, I thought this would be standard behaviour? Does session locator try to do something clever? I would just like it to give me a new session/connection/transaction each time to avoid any multi-threading issues.
Coordinator
Sep 22, 2015 at 5:06 PM
Every request is actually creating a new Session, but this you don't have to care, you just care about the connection, which is pooled by .NET, but you don't need to care either.

Which version of Orchard are you using? What are you trying to do with the Orchard database that can't be done with Records ?
Ping me on skype if you are still blocked. Same username.
Sep 25, 2015 at 4:37 PM
Thanks for your reply, we are using the latest version of Orchard. We were originally just doing standard content manager queries (no SQL of our own) but the only way we have managed to resolve these issues is by writing our own SQL, which is strange. I can't really investigate any further now we have it working as this is for a client and we don't have any more money from them. However I have noticed another strange error, is this something we need to worry about?

2015-09-25 12:39:30,023 [28] Orchard.Alias.Implementation.Updater.AliasHolderUpdater - Default - Exception during Alias refresh
http://xxx.xxxxx.xx.xx/xxx-x-xxxx
NHibernate.Exceptions.GenericADOException: could not execute query
[ select aliasrecor0_.Id as Id16_, aliasrecor0_.Path as Path16_, aliasrecor0_.RouteValues as RouteVal3_16_, aliasrecor0_.Source as Source16_, aliasrecor0_.Action_id as Action5_16_ from Orchard_Alias_AliasRecord aliasrecor0_ where aliasrecor0_aliasrecor0_.Id>@p0 order by aliasrecor0_.Id asc ]
Name:p1 - Value:404326
[SQL: select aliasrecor0_.Id as Id16_, aliasrecor0_.Path as Path16_, aliasrecor0_.RouteValues as RouteVal3_16_, aliasrecor0_.Source as Source16_, aliasrecor0_.Action_id as Action5_16_ from Orchard_Alias_AliasRecord aliasrecor0_ where aliasrecor0_aliasrecor0_.Id>@p0 order by aliasrecor0_.Id asc] ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)
at NHibernate.Loader.Loader.ListUsingQueryCache(ISessionImplementor session, QueryParameters queryParameters, ISet
1 querySpaces, IType[] resultTypes)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet1 querySpaces, IType[] resultTypes)
at NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)
at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters)
at NHibernate.Impl.AbstractQueryImpl2.List()
at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery)
at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
at Remotion.Linq.QueryableBase
1.GetEnumerator()
at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext()
at System.Collections.Generic.List
1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable
1 source)
at Orchard.Alias.Implementation.Storage.AliasStorage.List(Expression`1 predicate)
at Orchard.Alias.Implementation.Updater.AliasHolderUpdater.Refresh()
Coordinator
Sep 27, 2015 at 8:23 PM
You should be able to use HQL for your queries, using the NH session, but if it worked for you using custom connections then fine.

For the exception, this is something that should only happen if you have enabled the Alias Updater feature which is only useful in a farm. So if you are not running on a farm then you can disable it. And about the actual issue, this can be fixed in you use ReadUncommitted in the /config/Hostcomponents file. we have also identified and fixed an issue in 1.9.2 that might be related, but this is not yet confirmed.