Primary Key constraint error in realtime sync.

[fvanderhaegen]

Hi,

I'm using workloadtools to create a realtime workload to my target system but after a while the AnalysisConsumer that is running on my target system stops working due to following error:

Unhandled Exception: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PKInterval6ED7FE58BB0D4680'. Cannot insert duplicate key in object 'run1.Intervals'. The duplicate key value is (641559678). The statement has been terminated. 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.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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at WorkloadTools.Consumer.Analysis.WorkloadAnalyzer.CreateInterval(SqlConnection conn, SqlTransaction tran, DateTime intervalTime) in C:\GitHub\WorkloadTools\WorkloadTools\Consumer\Analysis\WorkloadAnalyzer.cs:line 788 at WorkloadTools.Consumer.Analysis.WorkloadAnalyzer.WriteToServer(DateTime intervalTime) in C:\GitHub\WorkloadTools\WorkloadTools\Consumer\Analysis\WorkloadAnalyzer.cs:line 407 at WorkloadTools.Consumer.Analysis.AnalysisConsumer.Dispose(Boolean disposing) in C:\GitHub\WorkloadTools\WorkloadTools\Consumer\Analysis\AnalysisConsumer.cs:line 48 at WorkloadTools.WorkloadController.Dispose() in C:\GitHub\WorkloadTools\WorkloadTools\WorkloadController.cs:line 110 at System.Console.ControlCDelegate(Object data) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch()

I'm analyzing my workload when the sync runs with workloadviewer and the PowerBi Report. This is the config of my AnalysisConsumer: `{ "Controller": {

    "Listener":
    {
        "__type": "ExtendedEventsWorkloadListener",
        "ConnectionInfo":
        {
            "ServerName": "target",
            "DatabaseName": "master"
        },
        "DatabaseFilter": "db1",
    "TimeoutMinutes": 690
    },

    "Consumers":
    [
        {
            "__type": "AnalysisConsumer",
            "ConnectionInfo":
            {
                "ServerName": "sqltest-1",
                "DatabaseName": "Workload_Test",
                "SchemaName": "run1"
            },
            "UploadIntervalSeconds": 60
        }
    ]
}

}`

[fvanderhaegen]

I've enabled RCSI on my Workload_Test database and for the moment the error hasn't occured yet. It's running for about an hour now.

[fvanderhaegen]

About 5 hours have passed and the error still didn't occur. It seems that RCSI solved the problem. Maybe the use of workloadviewer or the PowerBI Report caused that certain operations were blocked and for some reason the PK got generated twice. This is just a guess, I didn't find any evidence of that in the source code.

[spaghettidba]

Sorry for the inconvenience. I'm glad it seems sorted now.