Five New Optimizely Certifications are Here! Validate your expertise and advance your career with our latest certification exams. Click here to find out more

AI OnAI Off

Initialization Failed when upgrading to Commerce v14

Ionut Pati
Ionut Pati
Vote:
 

Hi there, 

We have upgraded an Optimizely solution to Commerce v14 and Cms v12 but we get the following error when trying to first run the application: 

fail: EPiServer.Framework.Initialization.InitializationEngine[0]
      Initialize action failed for 'Initialize on class EPiServer.Data.DataInitialization, EPiServer.Data, Version=12.22.4.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7'
      System.Data.DataException: Failed to update database during execution of statement '-- TextInRowSize value must first be switched off because Azure do not support TextInRowSize.
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_Membership')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_Membership', 'text in row', 'OFF';
      END
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_Profile')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_Profile', 'text in row', 'OFF';
      END
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_PersonalizationAllUsers')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_PersonalizationAllUsers', 'text in row', 'OFF';
      END
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_PersonalizationPerUser')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_PersonalizationPerUser', 'text in row', 'OFF';
      END
      -- Decrypt credit card data
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'cls_CreditCard') AND
          EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[mdpsp_sys_OpenSymmetricKey]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) AND
          EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[mdpsp_sys_CloseSymmetricKey]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
      BEGIN
          EXEC mdpsp_sys_OpenSymmetricKey
          UPDATE dbo.cls_CreditCard
          SET [CreditCardNumber] = CCD.CardNumber_string,
              [SecurityCode] = CCD.SecurityCode_string
          FROM (SELECT CONVERT(VARCHAR(max), DecryptByKey(cast(N'' AS XML).value('xs:base64Binary(sql:column("CC.CreditCardNumber"))', 'varbinary(max)'))) AS [CardNumber_string],
                       CONVERT(VARCHAR(max), DecryptByKey(cast(N'' AS XML).value('xs:base64Binary(sql:column("CC.SecurityCode"))','varbinary(max)'))) AS [SecurityCode_string]
              FROM cls_CreditCard CC
              WHERE CC.CreditCardNumber is not NULL AND CC.SecurityCode is not NULL) CCD
          EXEC mdpsp_sys_CloseSymmetricKey
      END
      '
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'CreditCardNumber', table 'epicommerce.dbo.cls_CreditCard'; column does not allow nulls. UPDATE fails.
      The statement has been terminated.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
         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 EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass5_0.<ExecuteScript>b__0()
      ClientConnectionId:78601a4a-8ed8-4891-9a1b-f3fbf64a3eac
      Error Number:515,State:2,Class:16
      ClientConnectionId before routing:832723a6-65df-48e5-8169-83e77754d35a
      Routing Destination:c0625fe4952f.tr8300.westus1-a.worker.database.windows.net,11023
         --- End of inner exception stack trace ---
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass5_0.<ExecuteScript>b__0()
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass28_0`1.<ExecuteTransaction>b__0()
         at EPiServer.Data.Providers.Internal.NoRetriesPolicy.Execute[TResult](Func`1 method)
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction[TResult](Func`1 action)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScript(IDatabaseExecutor databaseHandler, StreamReader stream)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass2_0.<ExecuteScripts>b__0()
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass27_0.<ExecuteTransaction>b__0()
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass28_0`1.<ExecuteTransaction>b__0()
         at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction[TResult](Func`1 action)
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction(Action action)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScripts(IDatabaseExecutor databaseHandler, IEnumerable`1 streams)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptExecutorImplementation.ExecuteEmbeddedZippedScripts(String connectionString, Assembly assembly, String resourcePath)
         at EPiServer.Commerce.Internal.SchemaUpdaterBase.Update(ConnectionStringOptions connectionStringOptions)
         at EPiServer.Data.SchemaUpdates.Internal.DatabaseSchemaManagerImplementation.EnsureDatabaseSchema(DataAccessOptions dataAccessOptions)
         at EPiServer.Data.DataInitialization.ValidateDatabaseSchema(InitializationEngine context)
         at EPiServer.Data.DataInitialization.Initialize(InitializationEngine context)
         at EPiServer.Framework.Initialization.Internal.ModuleNode.<>c__DisplayClass4_0.<Initialize>b__0()
         at EPiServer.Framework.Initialization.Internal.ModuleNode.Execute(Action a, String key)
         at EPiServer.Framework.Initialization.Internal.ModuleNode.Initialize(InitializationEngine context)
         at EPiServer.Framework.Initialization.InitializationEngine.InitializeModules()
      System.Data.DataException: Failed to update database during execution of statement '-- TextInRowSize value must first be switched off because Azure do not support TextInRowSize.
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_Membership')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_Membership', 'text in row', 'OFF';
      END
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_Profile')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_Profile', 'text in row', 'OFF';
      END
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'aspnet_PersonalizationAllUsers')
      BEGIN
          EXECUTE sp_tableoption 'aspnet_PersonalizationAllUsers', 'text in row', 'OFF';
      END
      BEGIN
          EXECUTE sp_tableoption 'aspnet_PersonalizationPerUser', 'text in row', 'OFF';
      END
      -- Decrypt credit card data
      IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'cls_CreditCard') AND
          EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[mdpsp_sys_OpenSymmetricKey]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) AND
          EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[mdpsp_sys_CloseSymmetricKey]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
      BEGIN
          EXEC mdpsp_sys_OpenSymmetricKey
          UPDATE dbo.cls_CreditCard
          SET [CreditCardNumber] = CCD.CardNumber_string,
              [SecurityCode] = CCD.SecurityCode_string
          FROM (SELECT CONVERT(VARCHAR(max), DecryptByKey(cast(N'' AS XML).value('xs:base64Binary(sql:column("CC.CreditCardNumber"))', 'varbinary(max)'))) AS [CardNumber_string],
                       CONVERT(VARCHAR(max), DecryptByKey(cast(N'' AS XML).value('xs:base64Binary(sql:column("CC.SecurityCode"))','varbinary(max)'))) AS [SecurityCode_string]
              FROM cls_CreditCard CC
              WHERE CC.CreditCardNumber is not NULL AND CC.SecurityCode is not NULL) CCD
          EXEC mdpsp_sys_CloseSymmetricKey
      END
      '
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'CreditCardNumber', table 'epicommerce.dbo.cls_CreditCard'; column does not allow nulls. UPDATE fails.
      The statement has been terminated.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
         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 EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass5_0.<ExecuteScript>b__0()
      ClientConnectionId:78601a4a-8ed8-4891-9a1b-f3fbf64a3eac
      Error Number:515,State:2,Class:16
      ClientConnectionId before routing:832723a6-65df-48e5-8169-83e77754d35a
      Routing Destination:c0625fe4952f.tr8300.westus1-a.worker.database.windows.net,11023
         --- End of inner exception stack trace ---
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass5_0.<ExecuteScript>b__0()
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass28_0`1.<ExecuteTransaction>b__0()
         at EPiServer.Data.Providers.Internal.NoRetriesPolicy.Execute[TResult](Func`1 method)
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction[TResult](Func`1 action)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScript(IDatabaseExecutor databaseHandler, StreamReader stream)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass2_0.<ExecuteScripts>b__0()
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass27_0.<ExecuteTransaction>b__0()
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass28_0`1.<ExecuteTransaction>b__0()
         at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction[TResult](Func`1 action)
         at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction(Action action)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScripts(IDatabaseExecutor databaseHandler, IEnumerable`1 streams)
         at EPiServer.Data.SchemaUpdates.Internal.ScriptExecutorImplementation.ExecuteEmbeddedZippedScripts(String connectionString, Assembly assembly, String resourcePath)
         at EPiServer.Commerce.Internal.SchemaUpdaterBase.Update(ConnectionStringOptions connectionStringOptions)
         at EPiServer.Data.SchemaUpdates.Internal.DatabaseSchemaManagerImplementation.EnsureDatabaseSchema(DataAccessOptions dataAccessOptions)
         at EPiServer.Data.DataInitialization.ValidateDatabaseSchema(InitializationEngine context)
         at EPiServer.Data.DataInitialization.Initialize(InitializationEngine context)
         at EPiServer.Framework.Initialization.Internal.ModuleNode.<>c__DisplayClass4_0.<Initialize>b__0()
         at EPiServer.Framework.Initialization.Internal.ModuleNode.Execute(Action a, String key)
         at EPiServer.Framework.Initialization.Internal.ModuleNode.Initialize(InitializationEngine context)
         at EPiServer.Framework.Initialization.InitializationEngine.InitializeModules()


The problem seems to be when trying to decrypt the CreditCard data. It returns null. 
Data in the Credit Card table looks somthing like this for those to columns:
CreditCardNumber
HVBECXMQ4QPWE-1111
SecurityCode
0

I am not sure why would it be expected to be encrypted. We plan to delete that data anyhow, but we get this error even before reaching to that step. 
Any ideas? 

Thank you!





#337107
Mar 04, 2025 15:05
Quan Mai
Quan Mai
Vote:
 

The script should not be run. There was a bug but it should be fixed for your version. Please run this script

-- Drop the function if it exists
IF OBJECT_ID('[dbo].[mdpfn_sys_IsAzureCompatible]', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[mdpfn_sys_IsAzureCompatible];
END
GO

-- Recreate the function
CREATE FUNCTION [dbo].[mdpfn_sys_IsAzureCompatible]()
RETURNS BIT
AS
BEGIN
    RETURN 1;
END
GO

#337126
Mar 06, 2025 11:36
Ionut Pati
Ionut Pati
Vote:
 

Hi Quan, 

Unfortunately this still does not solve the issue. 

We asked support to run the script on our prep environment, copied the content to the new app, and redeployed, but we still got the same error. 

We even upgraded to the latest Optimizely version: EPiServer.Framework Version=12.22.5, EPiServer.CMS Version=12.32.4, EPiServer.Commerce Version=14.35.0

#337151
Edited, Mar 10, 2025 12:15
Quan Mai
Quan Mai
Vote:
 

Hmm, was it run against commerce database or cms? It should have been commerce. sorry for not being clear from beginning 

#337152
Mar 10, 2025 13:47
Ionut Pati
Ionut Pati
Vote:
 

It was run against the commerce database. 

#337153
Mar 10, 2025 13:51
Quan Mai
Quan Mai
Vote:
 

Then I'm out of idea. Please reach out to developer support service again and ask to escalate 

#337154
Mar 10, 2025 14: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.