ALTER TABLE ALTER COLUMN Quantity failed (...) when upgrading to 9.24.1

Vote:
 

Hi,

after upgrading packages and codebase to version 9.24.1, and after deploy to testenvironment is finished, I try to access <testserver>/EPiServer, but I get this error:

Server Error in '/' Application.
The index 'IX1_LineItem9F210' is dependent on column 'Quantity'.
ALTER TABLE ALTER COLUMN Quantity failed because one or more objects access this column.

Stack trace:

[SqlException (0x80131904): The index 'IX1_LineItem9F210' is dependent on column 'Quantity'.
ALTER TABLE ALTER COLUMN Quantity failed because one or more objects access this column.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +277
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +765
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4239
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1217
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +420
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +1146
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +1628
   EPiServer.Data.SchemaUpdates.Internal.<>c__DisplayClass5_0.<ExecuteScript>b__0() +134

[DataException: Failed to update database during execution of statement '-- end of alter table CatalogEntryRelation
-- begin alter table LineItem
ALTER TABLE LineItem ALTER COLUMN [Quantity]                    decimal (38, 9)            NOT NULL;
']
   EPiServer.Data.SchemaUpdates.Internal.<>c__DisplayClass5_0.<ExecuteScript>b__0() +310
   EPiServer.Data.Providers.<>c__DisplayClass30_0`1.<ExecuteTransaction>b__0() +195
   EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScript(IDatabaseHandler databaseHandler, StreamReader stream) +297
   EPiServer.Data.SchemaUpdates.Internal.<>c__DisplayClass2_0.<ExecuteScripts>b__0() +271
   EPiServer.Data.Providers.<>c__DisplayClass29_0.<ExecuteTransaction>b__0() +18
   EPiServer.Data.Providers.<>c__DisplayClass30_0`1.<ExecuteTransaction>b__0() +195
   EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute(Func`1 method) +68
   EPiServer.Data.SchemaUpdates.Internal.ScriptExecutorImplementation.ExecuteEmbeddedZippedScripts(String connectionString, Assembly assembly, String resourcePath) +615
   EPiServer.Data.SchemaUpdates.Internal.DatabaseSchemaManagerImplementation.EnsureDatabaseSchema(ConnectionStringsSection connectionStrings, Boolean automaticSchemaUpdatesEnabled, Boolean automaticSchemaCreationEnabled) +1751
   EPiServer.Data.DataInitialization.Initialize(InitializationEngine context) +31
   EPiServer.Framework.Initialization.Internal.ModuleNode.Execute(Action a, String key) +58
   EPiServer.Framework.Initialization.InitializationEngine.InitializeModules() +356

[InitializationException: Initialize action failed for Initialize on class EPiServer.Data.DataInitialization, EPiServer.Data, Version=9.12.5.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7]
   EPiServer.Framework.Initialization.InitializationEngine.InitializeModules() +885
   EPiServer.Framework.Initialization.InitializationEngine.ExecuteTransition(Boolean continueTransitions) +156
   EPiServer.Framework.Initialization.InitializationModule.EngineExecute(HostType hostType, Action`1 engineAction) +746
   EPiServer.Framework.Initialization.InitializationModule.FrameworkInitialization(HostType hostType) +187
   EPiServer.Global..ctor() +86
   ASP.global_asax..ctor() +9

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck) +0
   System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark) +194
   System.Activator.CreateInstance(Type type, Boolean nonPublic) +100
   System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark) +1485
   System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) +298
   System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture) +26
   System.Web.HttpRuntime.CreateNonPublicInstance(Type type, Object[] args) +80
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +182
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +264

[HttpException (0x80004005): Exception has been thrown by the target of an invocation.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +532
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +114
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +606

I get this error before getting to the "Run migrations" page.

What might be the problem here? I guess a solution might be to delete the named index and then create it afterwards, but this doesn't seem quite right. I'm deploying to an on-prem environment with IIS and SQL Server. Please do let me know if I need to provide any other details.

#249126
Feb 24, 2021 9:31
Vote:
 
IX1_LineItem9F210

is a custom index (i.e. added by you/your colleagues), so yes you would need to drop it before hand, then recreate it.

We try to automate as much as possible, but some time it's best that manual thing remains manual :)

#249138
Feb 24, 2021 11:24
Vote:
 

Thanks - that solved it! I dropped the index, and repeated the steps for all the other affected indexes. 

I used "Script index as" in SSMS to create scripts so that I can easily recreate the indexes after successfully upgrading.

#249140
Feb 24, 2021 12:14
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.