Akka.Persistence.Sql.Common: DeleteMessages when journal is empty causes duplicate key SQL exception

[Aaronontheweb]

Version: Akka.NET v1.3.11

Use case: when using an AtLeastOnceDeliveryActor, I'm not using the journal at all - but I do save snapshots of my AtLeastOnceDelivery state regularly.

When I receive a SaveSnapshotSuccess message, I call:

Command<SaveSnapshotSuccess>(saved =>
            {
                var seqNo = saved.Metadata.SequenceNr;
                DeleteMessages(seqNo);
                DeleteSnapshots(new SnapshotSelectionCriteria(seqNo,
                    saved.Metadata.Timestamp.AddMilliseconds(-1))); // delete all but the most current snapshot
            });

Out of a force of habit from my other actors that use Akka.Persistence.

The DeleteMessages call in this case causes a SqlException to be raised in the Metadata table used by the Akka.Persistence.Sql.Common driver, due to this line in the driver:

akka.net/src/contrib/persistence/Akka.Persistence.Sql.Common/Journal/QueryExecutor.cs

Lines 617 to 629 in 666811c

	if (highestSeqNr <= toSequenceNr)
	{
	using (var updateCommand = GetCommand(connection, UpdateSequenceNrSql))
	{
	updateCommand.Transaction = tx;
	AddParameter(updateCommand, "@PersistenceId", DbType.String, persistenceId);
	AddParameter(updateCommand, "@SequenceNr", DbType.Int64, highestSeqNr);
	await updateCommand.ExecuteNonQueryAsync(cancellationToken);
	tx.Commit();
	}
	}

Since the sequence numbers are both 0, the driver tries to insert another (persistenceId, 0) record into the Metadata table even though one already exists.

I'll send a repo along for this shortly.

[ismaelhamed]

GH-3468

[Aaronontheweb]

@ismaelhamed good to know - I'll go back and review that PR again

[Aaronontheweb]

closed via #3468