Upgrade v9.2 to v9.3 fails with Database error 2601: Cannot insert duplicate key row in object 'dbo.DialogColumn' with unique index 'QBM_XA2DialogColumn'

Hi.

Thanks so much for your help. Apologies for the delay, I was running more tests. 

Eventually, here are the steps I've followed, they might be useful for other users:

• Restore backup from production
• Configwizard->Enable a restored database. Compilation ends with no errors.
• Ran Designer, updates applied.
• Checked db consistency according to the documentation. Only two errors that could not be automatically corrected, but they were related to PWO pointing to apps that no longer exist. So I skipped that.
  
  
• Installed v93, ran config wizard -> update database. Cleared DialogDBQueue.
• Upgrade ended with no errors this time.

As Markus Weiss-Ehlers pointed out at one point errors might have ocurred on a previous attempt, but I cannot recall having done so. We know how updates work: Should the process fails, recover from backup and retry.

Only thing I can think of: The DB I was trying our first testing was:

• An already dev database that's been thoroughly used for our testings.
• Running on SQLServer dev edition on Linux. In this case, I have to say v93 won't install at all and will fail later on during the upgrade process.

Thanks again!

We have test pipelines running the Linux-based SQL Server image mcr.microsoft.com/mssql/server:2022-latest to host several versions of OneIM including 9.3 without problems.
Several colleagues use the same image to host OneIM databases locally on their workstations, also without problems.

Can you provide any details regarding your installation issues?  (Logs, latest errors and warings from the system journal, ...)

Looking at the Supported Platforms for SQL Server 2022 on Linux it seems that CentOS is not explicitly supported by Microsoft,

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-ver16#supported-platforms

Not explicitly, but it runs like a charm (RedHat) and updates just the same.

Andreas, just as a curiosity ( I dont want this thread to interfere on other user problems) :

During the upgrade process, the installer stops with "AgentService stopped with error. This SqlTransaction has completed; it is no longer usable." and the config wizard shows "the dbqueue processor is not running" .

No errors on the linux side logs , the sqlagent is up and running. 

As of version 9.0, db queue processing no longer relies on the SQL Server Agent.

I don't recall ever seeing "AgentService stopped with error", but it sounds like a transaction died in an unrecoverable way. This should not happen. Can you provide Config Wizard logs of the failed upgrade?

Without knowing your test environment, a few ideas:

You could try adding more resources to your Sql Server.
Are initial installations working?
Do upgrades from fresh 9.2 installations work?

Sure, Andreas. Allow me a few days to come back to you with that info.

Hi,  Andreas Finsterbusch  , 

Seems a resources problem (maybe timeout), yes.

• Initial installations of v92 are working.
• Fresh 9.2 to 9.3 upgrades fail with the same error.

Upgrade process fails with the same error. Here's a trace log:

2025-01-20 10:12:13.4738 INFO (VI.Database.Agent.Startup.VersionCheck 20810cc6 PID:4788 ) : Checking if version of database agent is compatible with the target database... 
2025-01-20 10:12:13.5054 INFO (VI.Database.Agent.Startup.VersionCheck 20810cc6 PID:4788 ) : Database version is 2024.0011.0030.0000 
2025-01-20 10:12:13.5507 INFO (VI.Database.Agent.DbAgent 20810cc6 PID:4788 ) : Agent workers (re)starting... 
2025-01-20 10:12:13.7527 INFO (VI.Database.Agent.DbQueueMain 20810cc6 PID:4788 ) : Maximum bulk: 15000 
2025-01-20 10:12:13.7527 INFO (VI.Database.Agent.DbQueueMain 20810cc6 PID:4788 ) : Overload limit: 200000 
2025-01-20 10:12:13.7527 INFO (VI.Database.Agent.DbQueueMain 20810cc6 PID:4788 ) : Maximum slots per task: 16 
2025-01-20 10:12:13.8193 INFO (VI.Database.Agent.DbQueueMain 20810cc6 PID:4788 ) : 9 slots total available for processing. 
2025-01-20 10:12:13.8193 INFO (VI.Database.Agent.DbQueueMain 20810cc6 PID:4788 ) : Desired maximum slot runtime: 90 seconds. 
2025-01-20 10:12:13.9046 ERROR (VI.Database.Agent.DbQueueMetric 20810cc6 PID:4788 ) : 
merge into QBMDBQueueTaskMetric as t using ( values
('QBM-K-Root',0,NULL,1,0,NULL,NULL,1,0,NULL,0,'QBM_ZDBQueueVoidTask',1,0,0,0,0,NULL)
,('QBM-K-All',1000,NULL,1,0,NULL,NULL,0,0,NULL,0,'QBM_ZDBQueueVoidTask',1,0,0,0,0,NULL)
,('QBM-K-DBPrincipalCreate',1000,NULL,1,1,NULL,NULL,1,0,NULL,0,'QBM_ZDBPrincipalCreate',1,0,0,0,0,NULL)
,('QBM-K-DBQueueCompress',1000,NULL,1,0,NULL,NULL,1,0,NULL,0,'QBM_ZDBQueueCompress',1,0,0,0,0,NULL)
,('QBM-K-QBMConstraintDisable',1000,NULL,1,0,'QBM-K-QBMConstraintEnable2',NULL,1,0,NULL,0,'QBM_ZConstraintDisable',1,0,0,0,0,NULL)
,('QBM-K-DBRoleDefCreate',2000,NULL,1,1,NULL,NULL,1,0,NULL,0,'QBM_ZDBRoleDefCreate',1,0,0,0,1,NULL)
,('QBM-K-SetRowLockOnly',2000,NULL,1,0,NULL,NULL,1,0,NULL,0,'QBM_ZSetRowLockOnly',1,0,0,0,0,NULL)
,('QBM-K-TableReload',2000,NULL,1,1,'QBM-K-QBMConstraintEnable2','QBM-K-QBMConstraintDisable',1,0,NULL,0,'QBM_ZTableReload',1,0,0,0,0,NULL)
,('QBM-K-TableReplace',2000,NULL,1,1,NULL,NULL,0,0,NULL,0,'QBM_ZTableReplace',1,0,0,0,0,NULL)
,('QBM-K-DBRoleDefFill',3000,NULL,1,0,NULL,NULL,0,0,NULL,0,'QBM_ZDBRoleDefFill',1,0,0,0,0,NULL)
,('QBM-K-FillModelStart',3000,NULL,1,0,NULL,'QBM-K-QBMConstraintDisable',1,0,NULL,0,'QBM_ZDBQueueVoidTask',1,0,0,0,0,NULL)

(huge sentence) (...)
; Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - Se ha forzado la interrupción de una conexión existente por el host remoto.)
 ---> System.ComponentModel.Win32Exception (10054): Se ha forzado la interrupción de una conexión existente por el host remoto.
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---

Hi CVU ,

to me, it looks like an environment or infrastructure issue. This merge statement is new with 9.3 and quite large, maybe there is a network package size (package truncation) issue?

First of all, thanks Andreas Finsterbusch 

I'm not sure how to check that out, first time I try to handle this sort of errors. I'll dig more and try to find out.

The complete list of errors is:

; Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - Se ha forzado la interrupción de una conexión existente por el host remoto.)
 ---> System.ComponentModel.Win32Exception (10054): Se ha forzado la interrupción de una conexión existente por el host remoto.
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)


2025-01-20 10:48:12.5206 FATAL (VI.Database.Agent.AgentService 103e3db6 PID:5460 ) : Fatal exception occurred. Unable to continue.
This SqlTransaction has completed; it is no longer usable.
2025-01-20 10:48:15.0574 ERROR (Quantum.Migrator.Migrator  ) : Processing step 'QBM CheckForAgentStartDetect (detect if agent is Running)' failed. System.Exception: 2 - Wait command failed.
2025-01-20 10:48:15.0574 INFO (Quantum.Migrator.Migrator  ) : Processing PostInstall steps 
2025-01-20 10:48:15.0574 ERROR (VI.FormBase.ExceptionMgr  ) : AgentService stopped with error. System.Exception: AgentService stopped with error.
 ---> System.AggregateException: One or more errors occurred. (This SqlTransaction has completed; it is no longer usable.)
 ---> System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.

If you still have the database, try to run the full merge statement (from the log) in a sql console (e.g. ssms, azure sql studio) on the database.
If you don't have the database anymore, restore a 9.3 if you have a backup made on a windows sql server.

Good point! 

Tried using SSMS and ran without a complaint:

"

(373 rows affected)

Completion time: 2025-01-20T12:59:24.6767206+01:00"

In the log I can see references to the AgentService. Should I stop the agent service before hand?

Good point! 

Tried using SSMS and ran without a complaint:

"

(373 rows affected)

Completion time: 2025-01-20T12:59:24.6767206+01:00"

In the log I can see references to the AgentService. Should I stop the agent service before hand?

Okay, no network package scrambling due to query size.
Regales, I still believe this to be an infrastructure issue. But I'm running out of ideas, maybe the sql server logs have some useful hints.

In this context "AgentService" refers to the Database Agent Service, a OneIM component. During the installation (and upgrades), it is hosted by the ConfigWizard. If you refer to the Sql Server Agent service, it does not matter. The "AgentService stopped with error" message is an intermediat result of the SQL Server cutting the connection.

No worries, Andreas. 

I've been searching for possible causes, modified the tcp stack in linux with the same results and now the only thing left is to upgrade server hardware. I can recall you pointed out a possible lack of resources. But still the query runs well from the ssms client.  Anyway...

If that fails I'll move to another linux flavor. 

Thanks again!

Andreas Finsterbusch  , fyi, after some tests, install/upgrade v92 to v93 works alright on a msqlserver database running on Ubuntu server (22.04 jammy)