MySQL - how to handle the primary key constraint name mismatch

[kjkrum]

I'm trying EfCore.SchemaCompare for the first time, so this could be me misunderstanding something. But your README.md does suggest you're aware of the potential for this kind of issue.

EfCore.SchemaCompare and all my EF Core packages are version 8.0.1. I'm using Pomelo.EntityFrameworkCore.MySql 8.0.0-beta.2 and MySQL 8.0.34.

Here's a snippet of my migration. Note the name of the primary key constraint.

migrationBuilder.CreateTable(
    name: "Users",
    columns: table => new
    {
        UserId = table.Column<int>(type: "int", nullable: false)
            .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
        Username = table.Column<string>(type: "varchar(255)", nullable: false)
            .Annotation("MySql:CharSet", "utf8mb4"),
        ApiKeyExpirationMinutes = table.Column<int>(type: "int", nullable: true),
        AllowMultipleApiKeys = table.Column<bool>(type: "tinyint(1)", nullable: false),
        Inactive = table.Column<bool>(type: "tinyint(1)", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Users", x => x.UserId);
    })
    .Annotation("MySql:CharSet", "utf8mb4");

After updating the database, here's what HeidiSQL displays as the create code for the Users table. Note the name (or lack thereof) for the primary key constraint.

CREATE TABLE `Users` (
    `UserId` INT(10,0) NOT NULL AUTO_INCREMENT,
    `Username` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `ApiKeyExpirationMinutes` INT(10,0) NULL DEFAULT NULL,
    `AllowMultipleApiKeys` TINYINT(1) NOT NULL,
    `Inactive` TINYINT(1) NOT NULL,
    PRIMARY KEY (`UserId`) USING BTREE,
    UNIQUE INDEX `IX_Users_Username` (`Username`) USING BTREE
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

CompareEfWithDb() returns true and GetAllErrors includes this.

DIFFERENT: User->PrimaryKey 'PK_Users', constraint name. Expected = PK_Users, found = PRIMARY
DIFFERENT: Entity 'User', constraint name. Expected = PK_Users, found = PRIMARY

Edit: I found a workaround. It requires two ignore log patterns.

var compareConfig = new CompareEfSqlConfig();
compareConfig.AddIgnoreCompareLog(new CompareLog(CompareType.Entity, CompareState.Different, null, CompareAttributes.ConstraintName));
compareConfig.AddIgnoreCompareLog(new CompareLog(CompareType.PrimaryKey, CompareState.Different, null, CompareAttributes.ConstraintName));
var comparer = new CompareEfSql(compareConfig);

[JonPSmith]

Hi @kjkrum,

I haven't heard from anyone using MYSQL before. Version 8 is the first version of this library and I am glad it worked for you. You even fixed your own problem using exactly the way I would suggested.

I hope EfCore.SchemaCompare helps you in your project.

[kjkrum]

For the benefit of any other MySQL users who come across this, I narrowed the ignore filters slightly:

compareConfig.AddIgnoreCompareLog(new CompareLog(CompareType.Entity, CompareState.Different, null, CompareAttributes.ConstraintName, null, "PRIMARY"));
compareConfig.AddIgnoreCompareLog(new CompareLog(CompareType.PrimaryKey, CompareState.Different, null, CompareAttributes.ConstraintName, null, "PRIMARY"));

[JonPSmith]

Thanks @kjkrum. That's very useful. I have also updated the issue name so others will find it more easily.