The variable name '@p1' has already been declared

Posts   
 
    
yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 12-Mar-2019 23:36:12   

With LLBLGen 5.4.2 / Adapter /SQL Server (Azure), I'm seeing the following 2 errors in our logs


2019-03-12 21:58:05,223 [15] ERROR Framework.Server.Database.DataService [(null)] - 


Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: SD.LLBLGen.Pro.ORMSupportClasses
TargetSite: System.String ToQueryText(Boolean)
StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.FieldLikePredicate.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendWhereClause(IPredicate filter, QueryFragments destination, IQuery query)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at TheHub.Entities.DatabaseSpecific.DataAccessAdapter.CreateSelectDQ(QueryParameters queryParameters) in D:\git\TheHub.Live\src\TheHub.Entities\DatabaseSpecific\Extra\DataAccessAdapter.cs:line 160
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass10_0.<FetchEntityCollection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase.<>c__DisplayClass7_0.<Execute>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase.Execute[TResult](Func`1 toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at Framework.Server.Database.DataService.FetchCollection[T](FetchParams fetchParams, String methodName) in D:\git\TheHub.Live\src\common\Framework.Server\Database\DataService.cs:line 48 Inner excpetion, if any, follows End of RenderObject

Then about 2 seconds later

Type: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException
Message: An exception was caught during the execution of a retrieval query: The variable name '@p1' has already been declared. Variable names must be unique within a query batch or stored procedure.
Must declare the scalar variable "@p2".. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Query: 
    Query: SELECT [dbo].[HSE_CLAIM_VIEW].[acknowledged_ind] AS [Acknowledged], [dbo].[HSE_CLAIM_VIEW].[age_days] AS [AgeDays], [dbo].[HSE_CLAIM_VIEW].[analyst] AS [Analyst], [dbo].[HSE_CLAIM_VIEW].[bl_dt] AS [BlDate], [dbo].[HSE_CLAIM_VIEW].[broker_company_nm] AS [BrokerCompanyName], [dbo].[HSE_CLAIM_VIEW].[business_unit_nm] AS [BusinessUnitName], [dbo].[HSE_CLAIM_VIEW].[calculation_key] AS [CalculationKey], [dbo].[HSE_CLAIM_VIEW].[cancelled_ind] AS [Cancelled], [dbo].[HSE_CLAIM_VIEW].[charterparty_dt] AS [CharterpartyDate], [dbo].[HSE_CLAIM_VIEW].[analyst] AS [ClaimCreatedUserId], [dbo].[HSE_CLAIM_VIEW].[claim_dt] AS [ClaimDate], [dbo].[HSE_CLAIM_VIEW].[note_txt] AS [ClaimReference], [dbo].[HSE_CLAIM_VIEW].[claim_status_cd] AS [ClaimStatusCode], [dbo].[HSE_CLAIM_VIEW].[claim_subtype_nm] AS [ClaimSubtypeName], [dbo].[HSE_CLAIM_VIEW].[timebar_claim_gmt_dtm] AS [ClaimTimebarGmtDateTime], [dbo].[HSE_CLAIM_VIEW].[claim_type_txt] AS [ClaimType], [dbo].[HSE_CLAIM_VIEW].[claim_type_cd] AS [ClaimTypeCode], [dbo].[HSE_CLAIM_VIEW].[client_account_key] AS [ClientAccountKey], [dbo].[HSE_CLAIM_VIEW].[client_claim_seq] AS [ClientClaimSeq], [dbo].[HSE_CLAIM_VIEW].[client_prorata_seq] AS [ClientProrataSeq], [dbo].[HSE_CLAIM_VIEW].[cod_dt] AS [CompletionDischargeDateTime], [dbo].[HSE_CLAIM_VIEW].[contract_nm] AS [ContractName], [dbo].[HSE_CLAIM_VIEW].[counter_amt] AS [CounterAmount], [dbo].[HSE_CLAIM_VIEW].[claim_currency_cd] AS [CurrencyCode], [dbo].[HSE_CLAIM_VIEW].[current_amt] AS [CurrentAmount], [dbo].[HSE_CLAIM_VIEW].[division_key] AS [DivisionKey], [dbo].[HSE_CLAIM_VIEW].[division_nm] AS [DivisionName], [dbo].[HSE_CLAIM_VIEW].[entered_utc_dtm] AS [EnteredUtcDateTime], [dbo].[HSE_CLAIM_VIEW].[finished_ind] AS [Finished], [dbo].[HSE_CLAIM_VIEW].[bl_dt] AS [FirstBlLocalDate], [dbo].[HSE_CLAIM_VIEW].[group_nm] AS [GroupName], [dbo].[HSE_CLAIM_VIEW].[hubex_claim_seq] AS [HubexClaimSeq], [dbo].[HSE_CLAIM_VIEW].[initial_amt] AS [InitialAmount], [dbo].[HSE_CLAIM_VIEW].[invoice_dt] AS [InvoiceDate], [dbo].[HSE_CLAIM_VIEW].[invoice_number_txt] AS [InvoiceNumber], [dbo].[HSE_CLAIM_VIEW].[issued_received_nm] AS [IssuedReceivedName], [dbo].[HSE_CLAIM_VIEW].[claim_key] AS [Key], [dbo].[HSE_CLAIM_VIEW].[last_update_gmt_dtm] AS [LastUpdateGmtDateTime], [dbo].[HSE_CLAIM_VIEW].[note_txt] AS [Note], [dbo].[HSE_CLAIM_VIEW].[other_side_company_key] AS [OtherSideCompanyKey], [dbo].[HSE_CLAIM_VIEW].[other_side_company_nm] AS [OtherSideCompanyName], [dbo].[HSE_CLAIM_VIEW].[outstanding_days] AS [OutstandingDays], [dbo].[HSE_CLAIM_VIEW].[portfolio_txt] AS [Portfolio], [dbo].[HSE_CLAIM_VIEW].[provisional_amt] AS [ProvisionalAmount], [dbo].[HSE_CLAIM_VIEW].[report_dt] AS [ReportDate], [dbo].[HSE_CLAIM_VIEW].[settled_dt] AS [SettledDate], [dbo].[HSE_CLAIM_VIEW].[ship_key] AS [ShipKey], [dbo].[HSE_CLAIM_VIEW].[ship_nm] AS [ShipName], [dbo].[HSE_CLAIM_VIEW].[tanker_barge] AS [TankerBarge], [dbo].[HSE_CLAIM_VIEW].[this_side_company_key] AS [ThisSideCompanyKey], [dbo].[HSE_CLAIM_VIEW].[this_side_company_nm] AS [ThisSideCompanyName], [dbo].[HSE_CLAIM_VIEW].[timebar_claim_gmt_dtm] AS [TimebarClaimGmtDateTime], [dbo].[HSE_CLAIM_VIEW].[trade_number_txt] AS [TradeNumber], [dbo].[HSE_CLAIM_VIEW].[trade_type_nm] AS [TradeTypeName], [dbo].[HSE_CLAIM_VIEW].[voyage_key] AS [VoyageKey], [dbo].[HSE_CLAIM_VIEW].[voyage_nm] AS [VoyageName] FROM [dbo].[HSE_CLAIM_VIEW] WHERE ( [dbo].[HSE_CLAIM_VIEW].[voyage_nm] LIKE @p1 AND [dbo].[HSE_CLAIM_VIEW].[finished_ind] = @p1 AND [dbo].[HSE_CLAIM_VIEW].[client_account_key] = @p2 AND [dbo].[HSE_CLAIM_VIEW].[cancelled_ind] = @p3 AND [dbo].[HSE_CLAIM_VIEW].[client_account_key] = @p4 AND [dbo].[HSE_CLAIM_VIEW].[cancelled_ind] = @p5)
    Parameter: @p1 : AnsiString. Length: 13. Precision: 0. Scale: 0. Direction: Input. Value: "%VGG19_01256%".
    Parameter: @p1 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.
    Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 34.
    Parameter: @p3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.
    Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 34.
    Parameter: @p5 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Parameters: @p1 = %VGG19_01256%
@p1 = False
@p2 = 34
@p3 = False
@p4 = 34
@p5 = False
Source: SD.LLBLGen.Pro.ORMSupportClasses
TargetSite: System.Data.Common.DbDataReader Execute(System.Data.CommandBehavior)
StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String& failureErrorText)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass10_0.<FetchEntityCollection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase.<>c__DisplayClass7_0.<Execute>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.RecoveryStrategyBase.Execute[TResult](Func`1 toExecute)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)
   at Framework.Server.Database.DataService.FetchCollection[T](FetchParams fetchParams, String methodName) in D:\git\TheHub.Live\src\common\Framework.Server\Database\DataService.cs:line 48



They may not be related, but is there a known issue with 5.4.2 where the parameters can be corrupted like this?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Mar-2019 07:21:07   

What is the code that triggers such error? Did this happen after an upgrade/migrate to a newer LLBLGen or .net version? or Is this a new code?

David Elizondo | LLBLGen Support Team
yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 13-Mar-2019 15:11:30   

Our code looks like:


        public EntityCollectionBase2<T> FetchCollection<T>(FetchParams fetchParams, [CallerMemberName] string methodName = "caller not supplied") where T : EntityBase2
        {
            try
            {
                using(var tsw = new TransactionScopeWrapperBase(methodName))
                {
                    var col = EntityCollectionFactory.Create<T>();
                    if(fetchParams.Timeout > 0) tsw.DataAccessAdapter.CommandTimeOut = fetchParams.Timeout;
                    tsw.DataAccessAdapter.FetchEntityCollection(col, fetchParams.RelationPredicateBucket, fetchParams.MaxItemsToReturn, fetchParams.SortClause, fetchParams.PrefetchPath);
                    tsw.Complete();
                    return col;
                }
            }
            catch(Exception e)
            {
                if(Log.IsErrorEnabled) Log.Error("", e);
                throw;
            }
        }

We've been on 5.4.2 for around 6 months, before that 5.2.2. I wonder if its the recovery strategy and when it retries it is adding/overwriting the existing parameters. We use this recovery strategy:


    public class AzureRecoveryStrategy : SqlAzureRecoveryStrategy
    {
        static readonly ILog Log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        protected override bool IsTransientException(Exception toCheck)
        {
            var txEx = toCheck as TransactionException;
            if (txEx?.InnerException is TransactionPromotionException)
            {
                Log.WarnFormat("transient error, will retry {0}", ExceptionHelper.GetErrorText(txEx));
                return true; // this can happen when opening the connection
            }

            // exclude deadlocks
            var toCheckAsSqlException = toCheck as SqlException;
            if (toCheckAsSqlException == null)
            {
                return false;
            }
            if ((from SqlError error in toCheckAsSqlException.Errors select error.Number).Any(number => number == 1205))
            {
                return false;
            }
            var isTransient = base.IsTransientException(toCheck);
            if(isTransient)
            {
                Log.WarnFormat("transient error, base indicates will retry {0}", ExceptionHelper.GetErrorText(toCheck));
            }
            return isTransient;
        }
    }

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Mar-2019 19:24:28   

This has not been reported before, and I can't think of something that may cause it.

Are you using threading by any chance? Can you manage to intentionally reproduce it?

I don't believe upgrading would help, but you are welcomed to try the latest version, and see if it would make any difference.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Mar-2019 11:33:20   

Might be re-entry of the adapter, with recovery a re-entry on the adapter instance might corrupt things, but it's cumbersome to reproduce these.

the NRE on the FieldLikePredicate is also suspicious. Do you know the input on the like predicate perhaps or why it causes the null reference exception? I can't see a reason at first glance where the exception might have occurred.

Frans Bouma | Lead developer LLBLGen Pro
yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 14-Mar-2019 14:25:16   

So far I've been unsuccessful to repro. You can force a NRE with this:


            var bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Clear();
            var filter = ((EntityField2)TFields.C % ("%" + null + "%")); // this not really relevant
            bucket.PredicateExpression.Add(filter);
            ((FieldLikePredicate)filter).PersistenceInfo = null;
            filter.DatabaseSpecificCreator = new SqlServerSpecificCreator();
            Console.WriteLine(filter.ToQueryText(false));

Setting the PersistenceInfo to null is contrived and that doesn't happen in our code, but maybe there is a path where this can happen. I'll continue to try to force it another way.

yowl
User
Posts: 266
Joined: 11-Feb-2008
# Posted on: 14-Mar-2019 15:02:21   

I give up. I'll improve our logging and hopefully next time I'll be able to tell if the NRE occurs after a retry which would help repro.

I'll close this thread as I'm not going to make any progress unless this happens again.