Bug report: Error when filtering on library variable sets in audit log

Legacy Modelling
1

Hi, I encountered the following stack trace when attempting to apply a filter of Document Type Library Variable Set.

Error while executing SQL command in transaction '<octopusServerURL>/api/events?from=2017-06-27T00%3A00%3A00%2B10%3A00&to=2017-07-27T23%3A59%3A59%2B10%3A00&documentTypes=LibraryVariableSets c431bc91864f4011926002b40d2958a3': 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))) SQL Error 537 - Invalid length parameter passed to the LEFT or SUBSTRING function.
Error while executing SQL command in transaction '<octopusServerURL>/api/events?from=2017-06-27T00%3A00%3A00%2B10%3A00&to=2017-07-27T23%3A59%3A59%2B10%3A00&documentTypes=LibraryVariableSets c431bc91864f4011926002b40d2958a3': 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, CommandParameters args, Nullable`1 commandTimeoutSeconds)
   at Nevermore.QueryBuilder`1.Count()
   at Nevermore.QueryBuilder`1.ToList(Int32 skip, Int32 take, Int32& totalResults)
   at Octopus.Server.Web.Api.Actions.ListEventsResponder.Execute()
   at Octopus.Server.Web.Infrastructure.Api.Responder`1.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, 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.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](Func`1 func)
   at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameters args, Nullable`1 commandTimeoutSeconds)
2

Hi Nikolas,

Thanks for getting in touch! I’m sorry you’re hitting this unexpected and annoying bug. It looks likely that you’re hitting this known issue where audit events could be formatted incorrectly, which breaks the audit query. Here’s the link to the issue which has some additional information.

Which version of Octopus you’re currently running? As the fix script for this was added in 3.14.15, an upgrade to the latest version should fix it, given that you’re running a version prior to it. Would you be able to upgrade and let me know how you go, if applicable?

I hope this helps! Don’t hesitate to reach out with any questions or concerns going forward.

Kind regards,

Kenny