Upgrading to Archer 6.3 P1 fails with foreign key constraint errors
In certain conditions, the upgrade to Archer 6.3 P1 will fail with the following foreign key constraint exception in the FrameworkInstall log file:
Set log directory E:\Program Files\RSA Archer\
Updating .Net Framework Registry Key
Loading database script resources
SetupUtils.Database.SqlException: An error occurred at step 1 of script 6.3.00000.1057.sql (length=468051, max=0) ---> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblXModulesAdminUsers_tblUser". The conflict occurred in database "ArcherDev", table "dbo.tblUser", column 'user_id'.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblXWorkspaceUsers_tblUser". The conflict occurred in database "ArcherDev", table "dbo.tblUser", column 'user_id'.
The statement has been terminated.
The statement has been terminated.
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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at SetupUtils.Database.ScriptExecutor.ExecuteCommand(String scriptName, String cmdText, Int32 step)
— End of inner exception stack trace —
at Archer.Installer.Pages.PerformInstallationPage.InstallDatabase(Operation dbOp, String connectionString, String instanceName, Boolean allowSkipOnError)
at Archer.Installer.Pages.PerformInstallationPage.OnWizardNext(WizardPageEventArgs e)
This defect is logged as ARCHER-44807 and will be fixed in future release. Unfortunately, there is no workaround available at this time. If you encounter this defect, please open an Archer Support Case requesting your company be added to the defect.
To determine if upgrading your Archer Instance to 6.3 P1 will fail, run the following SQL command against the Instance database:
select * from tblUser where user_username = 'sysadmin'
- If the user_id column shows a value of 2 in the SQL results, upgrading will be fine.
- If the user_id column has a value other than 2, the upgrade will fail.
If the upgrade fails, you will need to restore the database from SQL Backup and revert the Archer servers back to the version before.
- Community Thread
- Forum Thread
- RSA Archer
- RSA Archer Suite
I wanted to add to this discussion that the engineering team (CE) is in the process of delivering a hotfix to resolve this issue. We hope to have this available this week (Dec 19th). Until which time, if you are planning on upgrading to 6.3, or later releases, please ensure that you follow our documented best practices (backup your DB), in addition to the instructions in Jeff's notice (run select * from tblUser where user_username = 'sysadmin') to determine if you will experience this issue. We expect that the majority of Archer customers will experience no issues in upgrading. Regardless, this simple query will allow you to determine your susceptibility immediately.