Failed to process subscription

kesavananbu.edu · 18 February 2020 08:28

I have created a new subscription with Email but I’m not receiving any email from the server.
Then after looking into Subscription diagnostics found that this error.

I have tried all frequency
Frequency => 1m & 1hr and 1 day

Failed to process subscription ‘Notify Team Manual Intervention Raised’. Error while executing SQL command in transaction ‘ProcessSubscriptions’: Incorrect syntax near ‘forgroups’.
Incorrect syntax near ‘)’.
The command being executed was:
IF EXISTS(SELECT *
FROM dbo.[Event]
WHERE ((([SpaceId] = ‘Spaces-1’) OR ([SpaceId] IS NULL)))
AND ([UserId] in (@userids_0))
AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where er.RelatedDocumentId in (@regardinganyprojects_0, @regardinganyprojects_1)))
AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where er.RelatedDocumentId in (@regardinganyprojects_0, @regardinganyprojects_1)forgroups))
AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where er.RelatedDocumentId in (@regardinganyenvironments_0)))
AND ([Category] in (@eventcategories_0))
AND ([AutoId] > @fromautoid)
AND ([AutoId] <= @toautoid))
SELECT @true_0
ELSE
SELECT @false_1
System.Exception: Error while executing SQL command in transaction ‘ProcessSubscriptions’: Incorrect syntax near ‘forgroups’.
Incorrect syntax near ‘)’.
The command being executed was:
IF EXISTS(SELECT *
FROM dbo.[Event]
WHERE ((([SpaceId] = ‘Spaces-1’) OR ([SpaceId] IS NULL)))
AND ([UserId] in (@userids_0))
AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where er.RelatedDocumentId in (@regardinganyprojects_0, @regardinganyprojects_1)))
AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where er.RelatedDocumentId in (@regardinganyprojects_0, @regardinganyprojects_1)forgroups))
AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where er.RelatedDocumentId in (@regardinganyenvironments_0)))
AND ([Category] in (@eventcategories_0))
AND ([AutoId] > @fromautoid)
AND ([AutoId] <= @toautoid))
SELECT @true_0
ELSE
SELECT @false_1 —> System.Data.SqlClient.SqlException: Incorrect syntax near ‘forgroups’.
Incorrect syntax near ‘)’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass11_0.b__0()
at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func1 func) at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable1 commandTimeout)
— End of inner exception stack trace —
at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable1 commandTimeout) at Nevermore.QueryBuilder2.Any()
at Octopus.Server.Schedules.SubscriptionsProcessor.IsWorkRequiredForSubscriptions(IRelationalTransaction transaction, IEnumerable`1 subscriptions, DateTimeOffset processingDate, Int64 upToEventAutoId) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Server\Schedules\SubscriptionsProcessor.cs:line 69
Octopus.Server version 2019.9.12 (2019.9.12+Branch.tags-2019.9.12.Sha.e1cf1796f5c4739226ef4cd9c8da27141fe9b46a)

!

paul.calvert · 18 February 2020 10:52

Hi @kesavananbu.edu,

Thanks for getting in touch!

Are you able to share a screenshot or information on how the subscription was configured?

Regards,
Paul

kesavananbu.edu · 18 February 2020 12:09

Hi @paul.calvert I have attached the Error and Configuration Screenshot. Please look into that.

MarkSiedle · 19 February 2020 02:53

Hi,

This was a known issue regarding the combination of both project and project-group filtering on the subscription for your version of Octopus.

This was fixed in 2019.10.6. Are you in a position where you can upgrade?

Another workaround is to only use either the project filter or the project group filter, but not both.

Hope this helps. Let me know how you go.

Thanks
Mark

kesavananbu.edu · 19 February 2020 05:08

Thanks @MarkSiedle , Now this error is gone. But only thing I’m not getting any email for the Manual Intervention event Triggered in the Project Group.

@MarkSiedle @paul.calvert
Now the log =>
No notifications need to be sent at this time (upToEventAutoId:76473).

paul.calvert · 19 February 2020 09:39

Hi @kesavananbu.edu,

Just to clarify, you’ve completed the upgrade the 2019.10.6 and have the subscription targeted at a project group and a single project?

You can are getting emails for manual interventions on the single project but not for other projects within the project group?

Regards,
Paul

kesavananbu.edu · 19 February 2020 10:17

Hi @paul.calvert, I’ve not completed the upgrade instead I have followed the workaround you have provided.

I have created a subscription for Deployment Related events and Manual Interruption Event for a single project as well as I tried for the project group. No email’s received even I made a deployment conditional failure even though not received any subscription email event for Deployment Failed also.

The log :
No notifications need to be sent at this time (upToEventAutoId:76473).

@paul.calvert @MarkSiedle

paul.calvert · 19 February 2020 10:28

My test environment is using the same version as you are, I’ve setup the following subscription:

Is that comparable to yours?

I am seeing this trigger as expected when initiating a deployment with a manual intervention step.

Out of curiosity, if you got to the Tasks tab and filter on Task Type: Process Subscriptions do you see any results?
e.g.

kesavananbu.edu · 19 February 2020 10:44

It looks like similar subscription. But I don’t receive any email still now.

Once I select the Process subscription not showing any task here.
@paul.calvert @MarkSiedle

paul.calvert · 19 February 2020 10:53

Ok, I want to rule out anything obvious, so apologies if these questions seem basic.

Are those projects definitely within the project group that the subscription is linked to?

Were those releases and deployments created after you made the changes to the subscription? If not, can you try a new deployment and see if that triggers.

kesavananbu.edu · 19 February 2020 11:26

Hi @paul.calvert , Finally I got under subscription log “Notifications need to be sent for subscriptions. This work will now be added to the Octopus task queue to complete (upToEventAutoId:76661).” But email not received may be issue with SMTP. When the email will be triggered by Octopus ?

Thank you,

paul.calvert · 19 February 2020 11:36

The email should be fairly immediate, you should see a Process Subscriptions task created in the Tasks section and this will provide details on whether the email was sent, or if there were issues.

kesavananbu.edu · 19 February 2020 11:39

Yeah Finally I got it. Thanks for your valuable time.

paul.calvert · 19 February 2020 11:57

Not a problem, glad we got there in the end

kesavananbu.edu · 19 February 2020 12:40

Hi @paul.calvert But I’m getting email only once when it get deploy completed. I need email for awaiting manual intervention. I made filter under select event categories => Manual Intervention Interrupted. Subscription processing.

paul.calvert · 19 February 2020 13:16

If you use the Event Group: Deployment Events, then the email will collect all relevant events since the last time it ran and include all of them in the email.
e.g.

If you use the Event Category: Manual Intervention Interruption Raised, then you should only get the single event in the email.
e.g.

However, if there are multiple projects within the project group with interruptions then it will list all of them in the email.

If you’re wanting to use this function in order to notify the correct people when a deployment requires manual intervention, a better option may be to add a Notification step to your deployment process to run just before the manual intervention step.
Doing it this way will allow a more timely notification rather than having to wait for the subscription to run, and avoids an email with multiple interventions listed that may cause some to get missed.

kesavananbu.edu · 19 February 2020 13:19

Thanks @paul.calvert