DeleteMessageFailure fails

[jalchr]

Sometimes ... I get the following error. Why it is "inserting" on a delete operation ?

2017-12-02 13:25:24,757 [36] ERROR Archive.Video.Importer.Actors.FileHandler - [DeleteMessagesFailure]: shutting down !
System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_Metadata'. Cannot insert duplicate key in object 'dbo.Metadata'. The duplicate key value is (\\nas20\d$\archive\incoming\aptn\cctv025151_russia-scochinese premier.xml, 1).
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Akka.Persistence.Sql.Common.Journal.AbstractQueryExecutor.<DeleteBatchAsync>d__49.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Akka.Persistence.Sql.Common.Journal.SqlJournal.<DeleteMessagesToAsync>d__49.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Akka.Util.Internal.AtomicState.<CallThrough>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Akka.Util.Internal.AtomicState.<CallThrough>d__8.MoveNext()
ClientConnectionId:18d2994b-b099-430e-adc4-4bf5c5ce5d08
Error Number:2627,State:1,Class:14

[Horusiath]

When you're deleting an event journal history, there is a risk of loosing the last value of SequenceNr, which could potentially break a continuous history of your events. If this happens (usually when you clear event journal up to the current event), journal will store the last known SequenceNr for target persistence Id in a separate Metadata table.

This looks like a problem with SQL statement for that case, which should be insert or update, but (probably) is only insert right now.

[hirzraimund]

I had the same problem and you are right, the existing update statement does only an insert. The easies solution which works on any SQL database is to delete the entries in metadata with the same where clause used for deleting the journal entries. This fix is implemented in my pull request.

[Aaronontheweb]

closed via #3468