-
Notifications
You must be signed in to change notification settings - Fork 3.2k
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
Comments
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, |
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:
with:
Allowed us to successfully update the database with the migration. |
Yep. |
One workaround would be to replace the // 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]);"); |
I feel like simply adding What if it's going in the reverse direction and forgets to remove an index it doesn't know about? |
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. |
But I think you're right, we can probably tell that the index won't exist when generating the scripts, and avoid the |
Another workaround is to do the |
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, |
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. |
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, |
- Separate migrations were created as EF Core was erroring out. See dotnet/efcore#7535
I can confirm the workaround (create two migrations) works. |
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. |
Is there any workaround so I can keep the column data. I cannot use approach to drop the column in first migration and create new one in second migration because I need to keep data. |
@dalibor983 This issue should be fixed in version 2.0.0... If you're still seeing it, could you submit a new issue? |
@bricelam I think I made a mistake because I manually created migration It is not matching my entity model classes. |
I was able to repro this in dotnet 2.2.102 'FK_table1_table2_column1' is not a constraint. previous errors: 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 workaround was to add this to my deployment. |
@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. |
Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0] when i run update database i got this error |
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:
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:
When starting the server, I call the following:
This then creates the following upgrade script:
And then the exception:
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
The text was updated successfully, but these errors were encountered: