One of our administrators encountered the following stack track trace when filtering for document type ‘variable set’. The error appears to be the same as the one reported in July 2017 (‘Bug report: Error when filtering on library variable sets in audit log’):
Octopus v2018.10.2
Error while executing SQL command in transaction ‘http://devopsdeploy.corp.lpl.com/api/events?from=2019-01-22T00%3A00%3A00-08%3A00&to=2019-02-21T23%3A59%3A59-08%3A00&documentTypes=variableset 5664b2f6b762464c9e36440bce63ffa7’: Invalid length parameter passed to the LEFT or SUBSTRING function. The command being executed was: SELECT COUNT(*) FROM dbo.[Event] WHERE ([Occurred] >= @from) AND ([Occurred] < @to) AND ([Id] in (SELECT er.EventId from EventRelatedDocument er where SUBSTRING(er.RelatedDocumentId, 1, (CHARINDEX(’-’, er.RelatedDocumentId)-1)) in (@documenttypes_0))) System.Exception at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable1 commandTimeoutSeconds) at Nevermore.QueryBuilder2.Count() at Nevermore.QueryBuilder2.ToList(Int32 skip, Int32 take, Int32& totalResults) at Octopus.Server.Web.Api.Actions.ListEventsResponder.ExecuteRegistered() at Octopus.Server.Web.Infrastructure.Api.Responder1.Respond(TDescriptor options, NancyContext context) at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2) at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object x) at Nancy.Routing.Route.<>c__DisplayClass4.<Wrap>b__3(Object parameters, CancellationToken context) --Inner Exception-- SQL Error 537 - Invalid length parameter passed to the LEFT or SUBSTRING function. System.Data.SqlClient.SqlException 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.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass11_0.<ExecuteScalarWithRetry>b__0() at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func1 func) at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable`1 commandTimeoutSeconds)
Thanks for getting in touch! I’m terribly sorry you’re hitting this unexpected issue. Unfortunately I’m unable to reproduce this same behavior by filtering by variable set. Are there any other filters applied when hitting this error? Are there any limitations on the permissions for your administrators? If so, could you send us an export of this user’s permissions? (You can get this in the web portal under Configuration > Test Permissions, selecting the user and Export.)
I look forward to hearing back and getting to the bottom of this one.
Thanks for the quick response. There are no other filters applied. I’ve attached a permissions export for my user. I’m part of the administrators group (as well as a few others). Thanks again!
Thanks for following up and providing that permissions export. Unfortunately I’m still unable to reproduce this behavior in 2018.10.2 after mimicking this user’s permissions and scoping of them. Are any of your other users with different permissions experiencing this same issue? If so, would you be able to provide another export from this user? I’ll keep investigating, but I’d like to attempt to reproduce this in any other way I possibly can.
Thanks for following up with that additional information. I think your comment that filtering by any document type is giving this error really helped in narrowing down the issue. I suspect you’ve hit the following issue which was raised a while back. This should have since been fixed with an upgrade script prior to the version you’re currently running, but may be popping up here somehow.
This issue is caused by corrupted data produced as a result of a bug fix from further back, . We’ve proposed a manual solution around this - would you be willing to follow these steps to see if it resolves this problem for you as we continue investigating?
Backup your database
Run this script to get a list of events that will be deleted. Make sure you are happy for those events to be deleted.
Select * from dbo.Event
Where Category = 'Modified' AND Id IN (Select er.EventId from EventRelatedDocument er where CHARINDEX('-', er.RelatedDocumentId) = 0)
Run this script to actually delete the events.
Delete from dbo.Event
Where Category = 'Modified' AND Id IN (Select er.EventId from EventRelatedDocument er where CHARINDEX('-', er.RelatedDocumentId) = 0)
