Invalid SQL generated when changing the primary key in migration with .AddPrimaryKey(...) #1348
Comments
|
I am able to reproduce this with |
|
@lauxjpn I have tried I have updated the same repo accordingly |
|
The issue now is the following call in your second migrations migrationBuilder.DropIndex(
name: "IX_tasks_workers_TrekkTemplateId",
table: "tasks_workers");This call shouldn't be there, but appears to be generated by the EF Core differ for some inexplicable reason. So as a quick workaround, remove the line from the The following sample code reproduces the issue for MySQL and SQL Server: using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public ICollection<IceCream_IceCreamParlor> IceCreamIceCreamParlors { get; set; }
}
public class IceCreamParlor
{
public int IceCreamParlorId { get; set; }
public ICollection<IceCream_IceCreamParlor> IceCreamIceCreamParlors{ get; set; }
}
public class IceCream_IceCreamParlor
{
#if !SECOND_MIGRATION
public int AccidentalId { get; set; } // <-- Remove for the second migration
#endif
public int IceCreamId { get; set; }
public int IceCreamParlorId { get; set; }
public IceCream IceCream { get; set; }
public IceCreamParlor IceCreamParlor { get; set; }
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
public DbSet<IceCreamParlor> IceCreamParlors { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
// optionsBuilder.UseMySql("server=127.0.0.1;port=3306;user=root;password=;database=Issue1348_02", new MySqlServerVersion(new Version(8, 0, 21)))
optionsBuilder.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1348_02")
.UseLoggerFactory(
LoggerFactory.Create(
configure => configure
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream_IceCreamParlor>(
entity =>
{
#if !SECOND_MIGRATION
entity.HasKey(e => e.AccidentalId); // <-- Remove for second migration
#else
entity.HasKey(e => new {e.IceCreamId, e.IceCreamParlorId}); // <-- Add for second migration
#endif
// WORKAROUND:
// Explicitly defining the index will prevent EF Core from dropping it later.
// entity.HasIndex(e => e.IceCreamId);
// This is never needed, because this index is never being dropped by EF Core.
// entity.HasIndex(e => e.IceCreamParlorId);
entity.HasOne(e => e.IceCream)
.WithMany()
.HasForeignKey(e => e.IceCreamId);
entity.HasOne(e => e.IceCreamParlor)
.WithMany()
.HasForeignKey(e => e.IceCreamParlorId);
});
}
}
internal static class Program
{
private static void Main(string[] args)
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
}
}Run the following PowerShell command from the project directory, to generate the two migrations:
In this sample code, the unexpected call, that drops the index that is unrelated to the primary key, is the following from the protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropPrimaryKey(
name: "PK_IceCream_IceCreamParlor",
table: "IceCream_IceCreamParlor");
//
// This call should not be here:
//
migrationBuilder.DropIndex(
name: "IX_IceCream_IceCreamParlor_IceCreamId",
table: "IceCream_IceCreamParlor");
migrationBuilder.DropColumn(
name: "AccidentalId",
table: "IceCream_IceCreamParlor");
migrationBuilder.AddPrimaryKey(
name: "PK_IceCream_IceCreamParlor",
table: "IceCream_IceCreamParlor",
columns: new[] { "IceCreamId", "IceCreamParlorId" });
}It tries to drop the index of the This does not happen if the index has been explicitly defined in the model. This issue does not seem to have any negative side effects for SQL Server when the generated SQL drops the index, but MySQL is not as forgiving and returns an error if you try to drop an index, that is being used for an existing foreign key relationship. /cc @ajcvickers @bricelam |
lauxjpn
added
Area-EntityFramework
external
type-bug
and removed
type-investigation
labels
|
Can you submit a new issue on dotnet/efcore so we can investigate further? |
|
This provider may need to be responsible for rebuilding the foreign keys (similar to dotnet/efcore#12586), but I want to make sure I fully understand the issue first. |
@bricelam I'll open an issue on the EF Core repo. The issue is, that the index gets dropped in the first place. It is the wrong index (it has nothing to do with the PK that is being dropped). |
|
The migration operation order is not going to change in EF Core, so we will implement a Pomelo specific fix for this. |
|
Are there any news to this issue? |
Steps to reproduce
See code in https://github.com/Mertsch/PomeloPrimaryKeyBug
The issue
The migration step
generates the SQL statement
Exception
Further technical details
MySQL version: 10.4.17-MariaDB - mariadb.org binary distribution
Operating system: Win 10 x64
Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2
Microsoft.AspNetCore.App version: 5.0.4
The text was updated successfully, but these errors were encountered: