MigrationManager is returning an error upgrading 4.3 to 4.40.3

Error is: There is already an object named 'AddressAttribute' in the database.


Application '/LM/W3SVC/2/ROOT' with physical root 'C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Presentation\Nop.Web\' hit unexpected managed exception, exception code = '0xe0434352'. First 30KB characters of captured stdout and stderr logs:
info: FluentMigrator.Runner.MigrationRunner[1000]
      637160666562551771: SchemaMigration migrating
info: FluentMigrator.Runner.Processors.SqlServer.SqlServer2016Processor[1004]
      Beginning Transaction
info: FluentMigrator.Runner.Processors.SqlServer.SqlServer2016Processor[1003]
      BEGIN TRANSACTION
info: FluentMigrator.Runner.MigrationRunner[1004]
      CreateTable AddressAttribute
info: FluentMigrator.Runner.Processors.SqlServer.SqlServer2016Processor[1003]
      CREATE TABLE [dbo].[AddressAttribute] ([Id] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(400) NOT NULL, [IsRequired] BIT NOT NULL, [AttributeControlTypeId] INT NOT NULL, [DisplayOrder] INT NOT NULL, CONSTRAINT [PK_AddressAttribute] PRIMARY KEY ([Id]))
fail: FluentMigrator.Runner.MigrationRunner[0]
      An error occured executing the following sql:
      CREATE TABLE [dbo].[AddressAttribute] ([Id] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(400) NOT NULL, [IsRequired] BIT NOT NULL, [AttributeControlTypeId] INT NOT NULL, [DisplayOrder] INT NOT NULL, CONSTRAINT [PK_AddressAttribute] PRIMARY KEY ([Id]))
      The error was There is already an object named 'AddressAttribute' in the database.
      
      System.Exception: An error occured executing the following sql:
      CREATE TABLE [dbo].[AddressAttribute] ([Id] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(400) NOT NULL, [IsRequired] BIT NOT NULL, [AttributeControlTypeId] INT NOT NULL, [DisplayOrder] INT NOT NULL, CONSTRAINT [PK_AddressAttribute] PRIMARY KEY ([Id]))
      The error was There is already an object named 'AddressAttribute' in the database.
      
       ---> System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'AddressAttribute' in the database.
         at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at System.Data.SqlClient.SqlInternalConnection.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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
         at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
         at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)
      ClientConnectionId:4e13a115-6dd6-4b7f-a2b3-436cf443550b
      Error Number:2714,State:6,Class:16
         --- End of inner exception stack trace ---
         at FluentMigrator.Runner.Processors.ProcessorBase.ReThrowWithSql(Exception ex, String sql)
         at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)
         at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.Process(String sql)
         at FluentMigrator.Runner.Processors.ProcessorBase.Process(CreateTableExpression expression)
         at FluentMigrator.Expressions.CreateTableExpression.ExecuteWith(IMigrationProcessor processor)
         at FluentMigrator.Runner.MigrationRunner.<>c__DisplayClass80_0.<ExecuteExpressions>b__1()
         at FluentMigrator.Runner.StopWatch.Time(Action action)
         at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions(ICollection`1 expressions)
info: FluentMigrator.Runner.Processors.SqlServer.SqlServer2016Processor[1004]
      Rolling back transaction
info: FluentMigrator.Runner.Processors.SqlServer.SqlServer2016Processor[1003]
      ROLLBACK TRANSACTION
crit: Microsoft.AspNetCore.Hosting.Diagnostics[6]
      Application startup exception
      System.Exception: An error occured executing the following sql:
      CREATE TABLE [dbo].[AddressAttribute] ([Id] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(400) NOT NULL, [IsRequired] BIT NOT NULL, [AttributeControlTypeId] INT NOT NULL, [DisplayOrder] INT NOT NULL, CONSTRAINT [PK_AddressAttribute] PRIMARY KEY ([Id]))
      The error was There is already an object named 'AddressAttribute' in the database.
      
       ---> System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'AddressAttribute' in the database.
         at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at System.Data.SqlClient.SqlInternalConnection.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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
         at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
         at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)
      ClientConnectionId:4e13a115-6dd6-4b7f-a2b3-436cf443550b
      Error Number:2714,State:6,Class:16
         --- End of inner exception stack trace ---
         at FluentMigrator.Runner.Processors.ProcessorBase.ReThrowWithSql(Exception ex, String sql)
         at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)
         at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.Process(String sql)
         at FluentMigrator.Runner.Processors.ProcessorBase.Process(CreateTableExpression expression)
         at FluentMigrator.Expressions.CreateTableExpression.ExecuteWith(IMigrationProcessor processor)
         at FluentMigrator.Runner.MigrationRunner.<>c__DisplayClass80_0.<ExecuteExpressions>b__1()
         at FluentMigrator.Runner.StopWatch.Time(Action action)
         at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions(ICollection`1 expressions)
         at FluentMigrator.Runner.MigrationRunner.ExecuteMigration(IMigration migration, Action`2 getExpressions)
         at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction)
         at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion, Boolean useAutomaticTransactionManagement)
         at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion)
         at Nop.Data.Migrations.MigrationManager.ApplyUpMigrations(Assembly assembly, Boolean isUpdateProcess) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Libraries\Nop.Data\Migrations\MigrationManager.cs:line 193
         at Nop.Web.Framework.Infrastructure.Extensions.ApplicationBuilderExtensions.StartEngine(IApplicationBuilder application) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Presentation\Nop.Web.Framework\Infrastructure\Extensions\ApplicationBuilderExtensions.cs:line 69
         at Nop.Web.Startup.Configure(IApplicationBuilder application) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Presentation\Nop.Web\Startup.cs:line 47
         at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)
         at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.Server.IIS.Core.IISServerSetupFilter.<>c__DisplayClass2_0.<Configure>b__0(IApplicationBuilder app)
         at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
         at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
Unhandled exception. System.Exception: An error occured executing the following sql:
CREATE TABLE [dbo].[AddressAttribute] ([Id] INT NOT NULL IDENTITY(1,1), [Name] NVARCHAR(400) NOT NULL, [IsRequired] BIT NOT NULL, [AttributeControlTypeId] INT NOT NULL, [DisplayOrder] INT NOT NULL, CONSTRAINT [PK_AddressAttribute] PRIMARY KEY ([Id]))
The error was There is already an object named 'AddressAttribute' in the database.

---> System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'AddressAttribute' in the database.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)
ClientConnectionId:4e13a115-6dd6-4b7f-a2b3-436cf443550b
Error Number:2714,State:6,Class:16
   --- End of inner exception stack trace ---
   at FluentMigrator.Runner.Processors.ProcessorBase.ReThrowWithSql(Exception ex, String sql)
   at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.ExecuteNonQuery(String sql)
   at FluentMigrator.Runner.Processors.SqlServer.SqlServerProcessor.Process(String sql)
   at FluentMigrator.Runner.Processors.ProcessorBase.Process(CreateTableExpression expression)
   at FluentMigrator.Expressions.CreateTableExpression.ExecuteWith(IMigrationProcessor processor)
   at FluentMigrator.Runner.MigrationRunner.<>c__DisplayClass80_0.<ExecuteExpressions>b__1()
   at FluentMigrator.Runner.StopWatch.Time(Action action)
   at FluentMigrator.Runner.MigrationRunner.ExecuteExpressions(ICollection`1 expressions)
   at FluentMigrator.Runner.MigrationRunner.ExecuteMigration(IMigration migration, Action`2 getExpressions)
   at FluentMigrator.Runner.MigrationRunner.ApplyMigrationUp(IMigrationInfo migrationInfo, Boolean useTransaction)
   at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion, Boolean useAutomaticTransactionManagement)
   at FluentMigrator.Runner.MigrationRunner.MigrateUp(Int64 targetVersion)
   at Nop.Data.Migrations.MigrationManager.ApplyUpMigrations(Assembly assembly, Boolean isUpdateProcess) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Libraries\Nop.Data\Migrations\MigrationManager.cs:line 193
   at Nop.Web.Framework.Infrastructure.Extensions.ApplicationBuilderExtensions.StartEngine(IApplicationBuilder application) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Presentation\Nop.Web.Framework\Infrastructure\Extensions\ApplicationBuilderExtensions.cs:line 69
   at Nop.Web.Startup.Configure(IApplicationBuilder application) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Presentation\Nop.Web\Startup.cs:line 47
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Server.IIS.Core.IISServerSetupFilter.<>c__DisplayClass2_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Nop.Web.Program.Main(String[] args) in C:\nopCommerce\nopCommerce_4.40.3_SQL\nopCommerce_4.40.3_Source\src\Presentation\Nop.Web\Program.cs:line 29
   at Nop.Web.Program.<Main>(String[] args)

Check the database and see if there is another table called AddressAttribute.

If there is, rename it to AddressAttribute-old for the time being, and see if that works?

select * from MigrationVersionInfo  order by 1

Do you have a record with
Description
Nop.Data base schema

(it should be the very first one)

The MigrationVersionInfo table is empty

Check this https://github.com/nopSolutions/nopCommerce/issues/5580

nortel wrote:

Possibly the same issue but I'm not certain.  The AddressAttribute table is in the 4.3 schema so it would already be present although the table is empty.  The MigrationVersionInfo  table is not in the 4.3 schema so some of the migration is being processed as it has been added.  This is on our test system so I have been running it through VS 2019 without debug which may or may not be a contributing factor to the migration issue.  I will restore the database on the test system with the 4.3 backup and use a copy of 4.40.3 without source and run without VS.

I tried migrating from 4.3 to 4.40.3 again with a clean 4.3 database and without VS and it returned the same error.

On hosting environment (Shared), I think the Sql User Login does not have dbo as preset. While I was doing the migration from 4.3 to 4.40.3, there were two tables in the DB
dbo.MigrationVersionInfo
<sqluser>.MigrationVersionInfo

This is the problem I think. How to fix this on shared hosting? Any ideas

I had the same problem.
and also 2 tables in the DB
- dbo.MigrationVersionInfo
- <sqluser>.MigrationVersionInfo
the first was empty, the second had 62 entries.
Than I copied the records to the first table by
USE [<db name>]
GO
INSERT INTO [dbo].[MigrationVersionInfo]
           ([Version]
           ,[AppliedOn]
           ,[Description])
SELECT TOP (1000) [Version], [AppliedOn], [Description]
FROM [<db name>].[<db user name>].[MigrationVersionInfo]
GO
After that the update to 4.4 worked like expected.
And there are some new entries in dbo.MigrationVersionInfo only.

This is exactly the same problem I was having, on a 32-bit hosted platform. I copied <dbuser>.MigrationVersionInfo to dbo.MigrationVersionInfo, using Thomas's code and the upgrade then proceeded smoothly from v4.30 to v4.40.4.

The developers need to be aware that the current MigrationRunner code is broken for hosted platforms.

Andrew Sidwell
British Human Power Club