Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrations: AlterColumn tries to drop non-existing index on SQL Server #7535

Closed
ivanut opened this issue Feb 2, 2017 · 19 comments
Closed

Migrations: AlterColumn tries to drop non-existing index on SQL Server #7535

ivanut opened this issue Feb 2, 2017 · 19 comments
Assignees
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-bug
Milestone

Comments

@ivanut
Copy link

ivanut commented Feb 2, 2017

Hi,

I have the exact same problem as described in #7396, when upgrading from "IdentityServer4.EntityFramework": "1.0.0-rc1-update1" to "IdentityServer4.EntityFramework": "1.0.0". I already have a database built on the RC version, and I am now trying to upgrade to version 1.0.0. This is a context defined by the IdentityServer team, so I am just doing the upgrade. I want to do the migrations as part of my upgrade, ideally as part of the server startup.

The problem I face lies in PersistedGrantDbContext. The snapshot from the RC version is:

    [DbContext(typeof(PersistedGrantDbContext))]
    partial class PersistedGrantDbContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
            modelBuilder
                .HasAnnotation("ProductVersion", "1.0.0-rtm-21431")
                .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

            modelBuilder.Entity("IdentityServer4.EntityFramework.Entities.PersistedGrant", b =>
                {
                    b.Property<string>("Key");
                    b.Property<string>("Type");
                    b.Property<string>("ClientId")
                        .IsRequired()
                        .HasAnnotation("MaxLength", 200);
                    b.Property<DateTime>("CreationTime");
                    b.Property<string>("Data")
                        .IsRequired();
                    b.Property<DateTime>("Expiration");
                    b.Property<string>("SubjectId");
                    b.HasKey("Key", "Type");
                    b.ToTable("PersistedGrants");
                });
        }
    }

Please note that there is no HasIndex here. I then upgrade to version 1.0.0, and create a migration. The generated migration is then:

public partial class PersistedGrantDbUpgradeTo100Migration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<string>(
                name: "SubjectId",
                table: "PersistedGrants",
                maxLength: 200,
                nullable: true);

            migrationBuilder.AlterColumn<DateTime>(
                name: "Expiration",
                table: "PersistedGrants",
                nullable: true);

            migrationBuilder.AlterColumn<string>(
                name: "Type",
                table: "PersistedGrants",
                maxLength: 50,
                nullable: false);

            migrationBuilder.AlterColumn<string>(
                name: "Key",
                table: "PersistedGrants",
                maxLength: 200,
                nullable: false);

            migrationBuilder.CreateIndex(
                name: "IX_PersistedGrants_SubjectId",
                table: "PersistedGrants",
                column: "SubjectId");

            migrationBuilder.CreateIndex(
                name: "IX_PersistedGrants_SubjectId_ClientId",
                table: "PersistedGrants",
                columns: new[] { "SubjectId", "ClientId" });

            migrationBuilder.CreateIndex(
                name: "IX_PersistedGrants_SubjectId_ClientId_Type",
                table: "PersistedGrants",
                columns: new[] { "SubjectId", "ClientId", "Type" });
        }
...

When starting the server, I call the following:

		private void initializeDatabase(IApplicationBuilder app)
		{
			using (var scope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
			{
				scope.ServiceProvider.GetRequiredService<PersistedGrantDbContext>().Database.Migrate();
...

This then creates the following upgrade script:

DROP INDEX [IX_PersistedGrants_SubjectId] ON [PersistedGrants];
DROP INDEX [IX_PersistedGrants_SubjectId_ClientId] ON [PersistedGrants];
DROP INDEX [IX_PersistedGrants_SubjectId_ClientId_Type] ON [PersistedGrants];
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'PersistedGrants') AND [c].[name] = N'SubjectId');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [PersistedGrants] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [PersistedGrants] ALTER COLUMN [SubjectId] nvarchar(200);
CREATE INDEX [IX_PersistedGrants_SubjectId] ON [PersistedGrants] ([SubjectId]);
CREATE INDEX [IX_PersistedGrants_SubjectId_ClientId] ON [PersistedGrants] ([SubjectId], [ClientId]);
CREATE INDEX [IX_PersistedGrants_SubjectId_ClientId_Type] ON [PersistedGrants] ([SubjectId], [ClientId], [Type]);

And then the exception:

Unhandled Exception: System.Data.SqlClient.SqlException: Cannot drop the index 'PersistedGrants.IX_PersistedGrants_SubjectId', because it does not exist or you do not have permission.
Cannot drop the index 'PersistedGrants.IX_PersistedGrants_SubjectId_ClientId', because it does not exist or you do not have permission.
Cannot drop the index 'PersistedGrants.IX_PersistedGrants_SubjectId_ClientId_Type', because it does not exist or you do not have permission.
Warning! The maximum key length is 900 bytes. The index 'IX_PersistedGrants_SubjectId_ClientId_Type' has maximum length of 1700 bytes. For some combination of large values, the insert/update operation will fail.
   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, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 64 bit
IDE: Visual Studio 2015 Update 3

Best regards,
Ivan Uthus

@ivanut
Copy link
Author

ivanut commented Feb 2, 2017

Hi,

One more comment here. As part of the upgrade, I also upgraded from .NET core 1.0 to .NET core 1.1, and also from ef core 1.0 to ef.core 1.1. So the initial migration based on the RC version was .NET core 1.0.

Best regards,
Ivan Uthus

@phire
Copy link

phire commented Feb 13, 2017

I noticed the same thing happening when changing the type of a field from an non-indexable type to an indexable type. We were not migrating from .Net core 1.0. Both the InitialCreate and new migrations were created with the same version of Entity framework (1.1.0-rtm-22752).

It appear the AlterColumn was mistakenly assuming the old field already had an index.

Replacing the code:

migrationBuilder.AlterColumn<Guid>(
       name: "StageId",
       table: "VacancyApplications",
       nullable: false,
       oldClrType: typeof(string),
       oldNullable: true);

with:

migrationBuilder.DropColumn(
       name: "StageId",
       table: "VacancyApplications");

 migrationBuilder.AddColumn<Guid>(
       name: "StageId",
       table: "VacancyApplications",
            nullable: false);

Allowed us to successfully update the database with the migration.

@bricelam
Copy link
Contributor

bricelam commented Feb 13, 2017

Yep. AlterColumn on SQL Server rebuilds the indexes based on the current migration's backing model. Looks like there are cases where those indexes may not exist. We should update it to generate an IF statement to drop them only if they exist. and only generate the CREATE INDEX statements if they won't be created later in the migration.

@bricelam
Copy link
Contributor

bricelam commented Feb 13, 2017

One workaround would be to replace the AlterColumn call in the migration with the right Sql. Be careful not to use parameters named @varX since this could cause bad scripts to be generated.

// UNDONE: Migrations generates bad DROP/CREATE INDEX statements
//migrationBuilder.AlterColumn<string>(
//    name: "SubjectId",
//    table: "PersistedGrants",
//    maxLength: 200,
//    nullable: true);
migrationBuilder.Sql(@"
    --DROP INDEX [IX_PersistedGrants_SubjectId] ON [PersistedGrants];
    --DROP INDEX [IX_PersistedGrants_SubjectId_ClientId] ON [PersistedGrants];
    --DROP INDEX [IX_PersistedGrants_SubjectId_ClientId_Type] ON [PersistedGrants];
    DECLARE @default1 sysname;
    SELECT @default1 = [d].[name]
    FROM [sys].[default_constraints] [d]
    INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
    WHERE ([d].[parent_object_id] = OBJECT_ID(N'PersistedGrants') AND [c].[name] = N'SubjectId');
    IF @default1 IS NOT NULL EXEC(N'ALTER TABLE [PersistedGrants] DROP CONSTRAINT [' + @default1 + '];');
    ALTER TABLE [PersistedGrants] ALTER COLUMN [SubjectId] nvarchar(200);
    --CREATE INDEX [IX_PersistedGrants_SubjectId] ON [PersistedGrants] ([SubjectId]);
    --CREATE INDEX [IX_PersistedGrants_SubjectId_ClientId] ON [PersistedGrants] ([SubjectId], [ClientId]);
    --CREATE INDEX [IX_PersistedGrants_SubjectId_ClientId_Type] ON [PersistedGrants] ([SubjectId], [ClientId], [Type]);");

@bricelam bricelam changed the title Migrations is trying to drop a non existing index SQL Server: AlterColumn is trying to drop a non existing index Feb 13, 2017
@bricelam bricelam changed the title SQL Server: AlterColumn is trying to drop a non existing index SQL Server Migrations: AlterColumn is trying to drop a non existing index Feb 13, 2017
@phire
Copy link

phire commented Feb 13, 2017

I feel like simply adding IF statements to the generated SQL is a hacky workaround.

What if it's going in the reverse direction and forgets to remove an index it doesn't know about?

@bricelam
Copy link
Contributor

Don't worry, I'll think through the scenarios and write tests when actually fixing the issue. I'm just jotting down my thoughts right now.

@bricelam
Copy link
Contributor

But I think you're right, we can probably tell that the index won't exist when generating the scripts, and avoid the CREATE INDEX statement altogether.

@bricelam bricelam changed the title SQL Server Migrations: AlterColumn is trying to drop a non existing index SQL Server Migrations: AlterColumn tries to drop non-existing index Feb 27, 2017
@bricelam
Copy link
Contributor

Another workaround is to do the AlterColumn in it's own migration before the CreateIndex.

@bricelam bricelam added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Feb 28, 2017
@ivanut
Copy link
Author

ivanut commented Mar 9, 2017

Hi,

I tested the workaround of moving the create indexes to a new migration, and that did not work out for me. The final solution was to drop and recreate the columns and dependent indexes. That did the trick.

Best regards,
Ivan Uthus

@bricelam
Copy link
Contributor

bricelam commented Mar 9, 2017

By "its own migration", I mean you would have to rollback the changes and do two Add-Migration calls. The first to alter the column, the next to do the rest.

@ivanut
Copy link
Author

ivanut commented Mar 9, 2017

Hi,

I think I did exactly that. The DB was never upgraded (because of the failing migration). I added a new migration (which was then empty) and moved the index statements from the Up and Down methods o my old migration to the new migration, but migrating the database still failed with the same error. I do not have a repro of this, but my point is that the safest workaround is to use DropColumn and AddColumn. I my case there was no data in the table, but that workaround might get more complex if you want to keep the data.

Best regards,
Ivan Uthus

@ajcvickers ajcvickers changed the title SQL Server Migrations: AlterColumn tries to drop non-existing index Migrations: AlterColumn tries to drop non-existing index on SQL Server May 9, 2017
@divega divega added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. and removed closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. labels May 10, 2017
indiwiz pushed a commit to indiwiz/AccountManager that referenced this issue Jun 1, 2017
- Separate migrations were created as EF Core was erroring out. See dotnet/efcore#7535
@BennyM
Copy link

BennyM commented Jul 14, 2017

I can confirm the workaround (create two migrations) works.

@BlueMarmalade
Copy link

BlueMarmalade commented Oct 8, 2017

Another "workaround", if you can afford it, is to delete all migrations in the app and all tables in the db. then do a new migration and update.

@zd-dalibor
Copy link

Is there any workaround so I can keep the column data.
I using core ef 2.0 and I try to create two separate migrations. In first I drop the column index and in second I alter the column. Second migration try to recreate index dropped in first migration :-(.

I cannot use approach to drop the column in first migration and create new one in second migration because I need to keep data.

@bricelam
Copy link
Contributor

@dalibor983 This issue should be fixed in version 2.0.0... If you're still seeing it, could you submit a new issue?

@zd-dalibor
Copy link

@bricelam I think I made a mistake because I manually created migration It is not matching my entity model classes.

@maddadder
Copy link

maddadder commented Jan 28, 2019

I was able to repro this in dotnet 2.2.102

'FK_table1_table2_column1' is not a constraint.
Could not drop constraint. See previous errors.

previous errors:
ALTER TABLE [table1] DROP CONSTRAINT [FK_table1_table2_column1];
System.Data.SqlClient.SqlException (0x80131904): 'FK_table1_table2_column1' is not a constraint.

after some more attempts, I got this. Note: This db used to be an EF 6.0 db

Cannot drop the index 'table1.IX_table1_column1', because it does not exist or you do not have permission.
The index 'IX_column1' is dependent on column 'column1'.
ALTER TABLE ALTER COLUMN column1 failed because one or more objects access this column.

The workaround was to add this to my deployment.
migrationBuilder.DropIndex("IX_column1", "table1" );
migrationBuilder.CreateIndex(
name: "IX_table1_column1",
table: "table1",
column: "column1");

@ajcvickers
Copy link
Contributor

@charlierlee This issue is closed as fixed. If you are still seeing an error, then please create a new issue and include a small, runnable project/solution or complete code listing that demonstrates the error.

@amrrizk95
Copy link

Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
User profile is available. Using 'C:\Users\amr\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.0.1-rtm-125 initialized 'SqlServerContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'__EFMigrationsHistory');
Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'__EFMigrationsHistory');
Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20200413121436_addPriceListDetail03'.
Applying migration '20200413121436_addPriceListDetail03'.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [H_PriceListDetail] DROP CONSTRAINT [FK_H_PriceListDetail_IC_UOM_UomId];
System.Data.SqlClient.SqlException (0x80131904): 'FK_H_PriceListDetail_IC_UOM_UomId' is not a constraint.
Could not drop constraint. See previous errors.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)
ClientConnectionId:b8532f1a-b151-4f3d-af08-4620fb074fe3
Error Number:3728,State:1,Class:16
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [H_PriceListDetail] DROP CONSTRAINT [FK_H_PriceListDetail_IC_UOM_UomId];
System.Data.SqlClient.SqlException (0x80131904): 'FK_H_PriceListDetail_IC_UOM_UomId' is not a constraint.
Could not drop constraint. See previous errors.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:b8532f1a-b151-4f3d-af08-4620fb074fe3
Error Number:3728,State:1,Class:16
'FK_H_PriceListDetail_IC_UOM_UomId' is not a constraint.
Could not drop constraint. See previous errors.

when i run update database i got this error

@ajcvickers ajcvickers modified the milestones: 2.0.0-preview1, 2.0.0 Oct 15, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-bug
Projects
None yet
Development

No branches or pull requests