2024-04-11 12:51:32,412 [18] Orchard.Exceptions.DefaultExceptionPolicy - Tenant - An unexpected exception was caught [http://localhost/OrchardLocal/path...]
NHibernate.Exceptions.GenericADOException: could not execute query
[ SELECT count(*) as y0_ FROM Orchard_Framework_ContentItemVersionRecord this_ WITH(NOLOCK) inner join Orchard_Framework_ContentItemRecord contentite1_ WITH(NOLOCK) on this_.ContentItemRecord_id=contentite1_.Id inner join Orchard_Taxonomies_TermsPartRecord termspartr2_ WITH(NOLOCK) on contentite1_.Id=termspartr2_.Id WHERE termspartr2_.Id in (SELECT this_0_.Id as y0_ FROM Orchard_Taxonomies_TermsPartRecord this_0_ WITH(NOLOCK) inner join Orchard_Taxonomies_TermContentItem tr1_ WITH(NOLOCK) on this_0_.Id=tr1_.TermsPartRecord_id left outer join Orchard_Taxonomies_TermPartRecord termrecord2_ on tr1_.TermRecord_id=termrecord2_.Id WHERE exists(select 1 from Orchard_Taxonomies_TermContentItem where WITH(NOLOCK) this_0_.Id=TermsPartRecord_id) and (termrecord2_.Id = @p0 or termrecord2_.Path like @p1)) and this_.Published = @p2 ]
Name:cp0 - Value:8835 Name:cp1 - Value:/8835/% Name:cp2 - Value:True
[SQL: SELECT count(*) as y0_ FROM Orchard_Framework_ContentItemVersionRecord this_ WITH(NOLOCK) inner join Orchard_Framework_ContentItemRecord contentite1_ WITH(NOLOCK) on this_.ContentItemRecord_id=contentite1_.Id inner join Orchard_Taxonomies_TermsPartRecord termspartr2_ WITH(NOLOCK) on contentite1_.Id=termspartr2_.Id WHERE termspartr2_.Id in (SELECT this_0_.Id as y0_ FROM Orchard_Taxonomies_TermsPartRecord this_0_ WITH(NOLOCK) inner join Orchard_Taxonomies_TermContentItem tr1_ WITH(NOLOCK) on this_0_.Id=tr1_.TermsPartRecord_id left outer join Orchard_Taxonomies_TermPartRecord termrecord2_ on tr1_.TermRecord_id=termrecord2_.Id WHERE exists(select 1 from Orchard_Taxonomies_TermContentItem where WITH(NOLOCK) this_0_.Id=TermsPartRecord_id) and (termrecord2_.Id = @p0 or termrecord2_.Path like @p1)) and this_.Published = @p2] ---> System.Data.SqlClient.SqlException: Sintassi non corretta in prossimità della parola chiave 'WITH'.
Sintassi non corretta in prossimità della parola chiave 'with'. Se l'istruzione è un'espressione di tabella comune, una clausola xmlnamespaces o una clausola context per il rilevamento delle modifiche, l'istruzione precedente deve terminare con un punto e virgola (;).
Impossibile preparare le istruzioni.
in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
in System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
in System.Data.SqlClient.SqlDataReader.get_MetaData()
in System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
in System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
in System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
in NHibernate.AdoNet.AbstractBatcher.DoExecuteReader(DbCommand cmd)
in NHibernate.AdoNet.AbstractBatcher.ExecuteReader(DbCommand cmd)
in NHibernate.Loader.Loader.GetResultSet(DbCommand st, QueryParameters queryParameters, ISessionImplementor session, IResultTransformer forcedResultTransformer)
in NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
in NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
in NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
--- Fine della traccia dello stack dell'eccezione interna ---
in NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
in NHibernate.Loader.Criteria.CriteriaLoaderExtensions.LoadAllToList[T](IList`1 loaders, ISessionImplementor session)
in NHibernate.Impl.SessionImpl.List[T](CriteriaImpl criteria)
in NHibernate.Impl.CriteriaImpl.List[T]()
in NHibernate.Impl.CriteriaImpl.UniqueResult[T]()
in Orchard.ContentManagement.DefaultContentQuery.Count() in D:\Laser.Orchard\Laser.Orchard\Orchard\src\Orchard\ContentManagement\DefaultContentQuery.cs:riga 215
in Orchard.ContentManagement.DefaultContentQuery.ContentQuery`1.Orchard.ContentManagement.IContentQuery<T>.Count() in D:\Laser.Orchard\Laser.Orchard\Orchard\src\Orchard\ContentManagement\DefaultContentQuery.cs:riga 312
in Orchard.Taxonomies.Services.TaxonomyService.GetContentItemsCount(TermPart term, String fieldName)
in Orchard.Taxonomies.Services.TermCountProcessor.ProcessTerm(TermPart termPart, ICollection`1 processedTermPartRecordIds)
in Orchard.Taxonomies.Services.TermCountProcessor.Process(IEnumerable`1 termPartRecordIds)
in Orchard.Events.DelegateHelper.<>c__DisplayClass14_0`2.<BuildAction>b__0(Object target, Object[] p) in D:\Laser.Orchard\Laser.Orchard\Orchard\src\Orchard\Events\DelegateHelper.cs:riga 116
in Orchard.Events.DefaultOrchardEventBus.TryInvokeMethod(IEventHandler eventHandler, Type interfaceType, String messageName, String interfaceName, String methodName, IDictionary`2 arguments, IEnumerable& returnValue) in D:\Laser.Orchard\Laser.Orchard\Orchard\src\Orchard\Events\DefaultOrchardEventBus.cs:riga 84
in Orchard.Events.DefaultOrchardEventBus.TryInvoke(IEventHandler eventHandler, String messageName, String interfaceName, String methodName, IDictionary`2 arguments, IEnumerable& returnValue) in D:\Laser.Orchard\Laser.Orchard\Orchard\src\Orchard\Events\DefaultOrchardEventBus.cs:riga 70
in Orchard.Events.DefaultOrchardEventBus.TryNotifyHandler(IEventHandler eventHandler, String messageName, String interfaceName, String methodName, IDictionary`2 eventData, IEnumerable& returnValue) in D:\Laser.Orchard\Laser.Orchard\Orchard\src\Orchard\Events\DefaultOrchardEventBus.cs:riga 53
(Incorrect syntax near the keyword 'with'. If the statement is a common table expression, an xmlnamespaces clause, or a change-tracking context clause, the preceding statement must end with a semicolon (;).
Unable to prepare instructions)
The query produced is the following:
SELECT count(*) as y0_
FROM Orchard_FrameworkContentItemVersionRecord this WITH(NOLOCK)
inner join Orchard_FrameworkContentItemRecord contentite1 WITH(NOLOCK) on this_.ContentItemRecordid=contentite1.Id
inner join Orchard_TaxonomiesTermsPartRecord termspartr2 WITH(NOLOCK) on contentite1.Id=termspartr2.Id
WHERE termspartr2_.Id in
(SELECT this0.Id as y0_ FROM Orchard_Taxonomies_TermsPartRecord this0 WITH(NOLOCK)
inner join Orchard_TaxonomiesTermContentItem tr1 WITH(NOLOCK) on this0.Id=tr1_.TermsPartRecord_id
left outer join Orchard_TaxonomiesTermPartRecord termrecord2 on tr1_.TermRecordid=termrecord2.Id
WHERE exists(select 1 from Orchard_Taxonomies_TermContentItem where WITH(NOLOCK) this0.Id=TermsPartRecordid) and (termrecord2.Id = @p0 or termrecord2.Path like @p1)) and this.Published = @p2
The issue is that, inside the "exists" subquery, after the table name there is no table alias so the interceptor uses the keywork "where" as the alias and adds the WITH(NOLOCK) clause after it, causing the syntax error.
We had some timeouts in certain peculiar scenarios (involving custom forms + workflows) when reading data from the table Orchard_Taxonomies_TermsPartRecord and timeouts were caused by NHibernate locking that table. For this reason, I added the proper keys to my config files in order to let autofac take care of the tables I don't want to be locked (ref: https://github.com/OrchardCMS/Orchard/blob/441c6da145baccce8f357dbc2c3956c3bb9e99ea/src/Orchard/Data/Providers/DefaultNoLockTableProvider.cs#L10). Two tables have been added to the Sites.Tenant.Config:
The following exception is thrown:
(Incorrect syntax near the keyword 'with'. If the statement is a common table expression, an xmlnamespaces clause, or a change-tracking context clause, the preceding statement must end with a semicolon (;). Unable to prepare instructions)
The query produced is the following:
SELECT count(*) as y0_ FROM Orchard_FrameworkContentItemVersionRecord this WITH(NOLOCK) inner join Orchard_FrameworkContentItemRecord contentite1 WITH(NOLOCK) on this_.ContentItemRecordid=contentite1.Id inner join Orchard_TaxonomiesTermsPartRecord termspartr2 WITH(NOLOCK) on contentite1.Id=termspartr2.Id WHERE termspartr2_.Id in (SELECT this0.Id as y0_ FROM Orchard_Taxonomies_TermsPartRecord this0 WITH(NOLOCK) inner join Orchard_TaxonomiesTermContentItem tr1 WITH(NOLOCK) on this0.Id=tr1_.TermsPartRecord_id left outer join Orchard_TaxonomiesTermPartRecord termrecord2 on tr1_.TermRecordid=termrecord2.Id WHERE exists(select 1 from Orchard_Taxonomies_TermContentItem where WITH(NOLOCK) this0.Id=TermsPartRecordid) and (termrecord2.Id = @p0 or termrecord2.Path like @p1)) and this.Published = @p2
The issue is that, inside the "exists" subquery, after the table name there is no table alias so the interceptor uses the keywork "where" as the alias and adds the WITH(NOLOCK) clause after it, causing the syntax error.