.Net Core 8 Entity Framework (MySQL) DROP PROCEDURE IF EXISTS `MYSQL_BEFORE_DROP_PRIMARY_KEY` Error

Question

I'm created a migration to add a few tables and update one table (add one column). The database is MySQL 8.0.31 and the EF provider is Oracle's MySql.EntityFrameworkCore

The migration was completed sucessfully, but when I run dotnet ef database update I get this error which I can't find online.

For context, this is a brand new project with only two previous migrations.

Error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP PROCEDURE IF EXISTS `MYSQL_BEFORE_DROP_PRIMARY_KEY`;
        CREATE PROCEDURE `MYSQL_BEFORE_DROP_PRIMARY_KEY`(IN `SCHEMA_NAME_ARGUMENT` VARCHAR(255), IN `TABLE_NAME_ARGUMENT` VARCHAR(255))
        BEGIN
          DECLARE HAS_AUTO_INCREMENT_ID TINYINT(1);
          DECLARE PRIMARY_KEY_COLUMN_NAME VARCHAR(255);
          DECLARE PRIMARY_KEY_TYPE VARCHAR(255);
          DECLARE SQL_EXP VARCHAR(1000);
          SELECT COUNT(*)
          INTO HAS_AUTO_INCREMENT_ID
          FROM `information_schema`.`COLUMNS`
          WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
            AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
            AND `Extra` = 'auto_increment'
            AND `COLUMN_KEY` = 'PRI'
            LIMIT 1;
          IF HAS_AUTO_INCREMENT_ID THEN
          SELECT `COLUMN_TYPE`
            INTO PRIMARY_KEY_TYPE
            FROM `information_schema`.`COLUMNS`
            WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
            AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
            AND `COLUMN_KEY` = 'PRI'
            LIMIT 1;
          SELECT `COLUMN_NAME`
            INTO PRIMARY_KEY_COLUMN_NAME
            FROM `information_schema`.`COLUMNS`
            WHERE `TABLE_SCHEMA` = (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA()))
            AND `TABLE_NAME` = TABLE_NAME_ARGUMENT
            AND `COLUMN_KEY` = 'PRI'
            LIMIT 1;
          SET SQL_EXP = CONCAT('ALTER TABLE `', (SELECT IFNULL(SCHEMA_NAME_ARGUMENT, SCHEMA())), '`.`', TABLE_NAME_ARGUMENT, '` MODIFY COLUMN `', PRIMARY_KEY_COLUMN_NAME, '` ', PRIMARY_KEY_TYPE, ' NOT NULL;');
          SET @SQL_EXP = SQL_EXP;
          PREPARE SQL_EXP_EXECUTE FROM @SQL_EXP;
          EXECUTE SQL_EXP_EXECUTE;
          DEALLOCATE PREPARE SQL_EXP_EXECUTE;
          END IF;
        END;
Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution.
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@SQL_EXP' must be defined.
   at MySql.Data.MySqlClient.Statement.SerializeParameterAsync(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex, Boolean execAsync)
   at MySql.Data.MySqlClient.Statement.InternalBindParametersAsync(String sql, MySqlParameterCollection parameters, MySqlPacket packet, Boolean execAsync)
   at MySql.Data.MySqlClient.Statement.BindParametersAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.Statement.ExecuteAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.PreparableStatement.ExecuteAsync(Boolean execAsync)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(Boolean execAsync, CancellationToken cancellationToken)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.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)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Fatal error encountered during command execution.

How do I fix this issue?

Answer 1

I also have been encontering this, after updating my project from version 6.0 to 9.0 of MySql.EntityFrameworkCore, I began encountering issues when applying database migrations.

The root cause was a new locking mechanism introduced in recent versions of the MySQL connector. This change interferes with the default migration workflow, as reported in the EF Core GitHub issue: Issue #33731.

---

Workaround: Custom Implementation for MySQL

Since an official fix for this issue has not been released yet, I implemented a custom solution by reimplementing the HistoryRepository service for MySQL. The following steps outline the changes I made:

1. Custom History Repository:
Create a new class named MySqlHistoryRepository that implements the functionality of Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.

This class manages the migration history specifically for MySQL, bypassing the problems caused by the new locking mechanism.

2. Custom Migration Database Lock:
To handle the locking mechanism, add another class called MySqlMigrationDatabaseLock. This class implements Microsoft.EntityFrameworkCore.Migrations.IMigrationsDatabaseLock and controls the lock behavior during migrations.

3. Service Replacement in Database Context Setup
Finally, modify the setup of your database context to replace the default IHistoryRepository service with the custom MySqlHistoryRepository.

This is done in the dependency injection configuration (typically within the ConfigureServices method in the startup file), ensuring that our implementation is used during the migration process.

You can check the files used below.

---

MySqlHistoryRepository.cs

/// <inheritdoc />
public class MySqlHistoryRepository : HistoryRepository
{
    #region Variables
    private static readonly TimeSpan _retryDelay = TimeSpan.FromSeconds(1);

    /// <inheritdoc />
    protected override string ExistsSql
        => CreateExistsSql(TableName);

    /// <inheritdoc />
    public override LockReleaseBehavior LockReleaseBehavior => LockReleaseBehavior.Explicit;

    /// <inheritdoc />
    protected virtual string LockTableName { get; } = "__EFMigrationsLock";
    #endregion

    #region Constructors
    /// <summary>
    /// Initializes a new instance of the <see cref="MySqlHistoryRepository"/> class.
    /// </summary>
    /// <param name="dependencies">Parameter object containing dependencies for this service.</param>
    public MySqlHistoryRepository(HistoryRepositoryDependencies dependencies)
        : base(dependencies)
    {
    }
    #endregion

    #region Public methods
    /// <inheritdoc />
    public override IMigrationsDatabaseLock AcquireDatabaseLock()
    {
        Dependencies.MigrationsLogger.AcquiringMigrationLock();

        if (!InterpretExistsResult(
            Dependencies.RawSqlCommandBuilder.Build(CreateExistsSql(LockTableName))
                .ExecuteScalar(CreateRelationalCommandParameters())))
        {
            CreateLockTableCommand().ExecuteNonQuery(CreateRelationalCommandParameters());
        }

        TimeSpan retryDelay = _retryDelay;
        while (true)
        {
            MySqlMigrationDatabaseLock dbLock = CreateMigrationDatabaseLock();
            object? insertCount = CreateInsertLockCommand(DateTimeOffset.UtcNow)
                .ExecuteScalar(CreateRelationalCommandParameters());
            if ((long)insertCount! == 1)
            {
                return dbLock;
            }

            Thread.Sleep(retryDelay);
            if (retryDelay < TimeSpan.FromMinutes(1))
            {
                retryDelay = retryDelay.Add(retryDelay);
            }
        }
    }

    /// <inheritdoc />
    public override async Task<IMigrationsDatabaseLock> AcquireDatabaseLockAsync(
        CancellationToken cancellationToken = default)
    {
        Dependencies.MigrationsLogger.AcquiringMigrationLock();

        if (!InterpretExistsResult(
            await Dependencies.RawSqlCommandBuilder.Build(CreateExistsSql(LockTableName))
                .ExecuteScalarAsync(CreateRelationalCommandParameters(), cancellationToken).ConfigureAwait(false)))
        {
            await CreateLockTableCommand().ExecuteNonQueryAsync(CreateRelationalCommandParameters(), cancellationToken)
                .ConfigureAwait(false);
        }

        TimeSpan retryDelay = _retryDelay;
        while (true)
        {
            MySqlMigrationDatabaseLock dbLock = CreateMigrationDatabaseLock();
            object? insertCount = await CreateInsertLockCommand(DateTimeOffset.UtcNow)
                .ExecuteScalarAsync(CreateRelationalCommandParameters(), cancellationToken)
                .ConfigureAwait(false);
            if ((long)insertCount! == 1)
            {
                return dbLock;
            }

            await Task.Delay(_retryDelay, cancellationToken).ConfigureAwait(true);
            if (retryDelay < TimeSpan.FromMinutes(1))
            {
                retryDelay = retryDelay.Add(retryDelay);
            }
        }
    }

    /// <inheritdoc />
    public override string GetBeginIfExistsScript(string migrationId)
        => throw new NotSupportedException("MigrationScriptGenerationNotSupported");

    /// <inheritdoc />
    public override string GetBeginIfNotExistsScript(string migrationId)
        => throw new NotSupportedException("MigrationScriptGenerationNotSupported");

    /// <inheritdoc />
    public override string GetCreateIfNotExistsScript()
    {
        string script = GetCreateScript();
        return script.Insert(script.IndexOf("CREATE TABLE", StringComparison.Ordinal) + 12, " IF NOT EXISTS");
    }

    /// <inheritdoc />
    public override string GetEndIfScript()
        => throw new NotSupportedException("MigrationScriptGenerationNotSupported");
    #endregion

    #region Protected methods
    /// <inheritdoc />
    protected override bool InterpretExistsResult(object? value)
        => (long)value! != 0L;
    #endregion

    #region Private methods
    private IRelationalCommand CreateDeleteLockCommand(int? id = null)
    {
        string sql = $"""
        DELETE FROM `{LockTableName}`
        """;

        if (id != null)
        {
            sql += $""" WHERE `Id` = {id}""";
        }

        sql += ";";
        return Dependencies.RawSqlCommandBuilder.Build(sql);
    }

    private string CreateExistsSql(string tableName)
    {
        RelationalTypeMapping stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));

        return $"""
        SELECT COUNT(*)
        FROM information_schema.tables
        WHERE table_schema = {stringTypeMapping.GenerateSqlLiteral(Dependencies.Connection.DbConnection.Database)}
        AND table_name = {stringTypeMapping.GenerateSqlLiteral(tableName)}
        LIMIT 1;
        """;
    }

    private IRelationalCommand CreateLockTableCommand()
        => Dependencies.RawSqlCommandBuilder.Build(
            $"""
            CREATE TABLE IF NOT EXISTS `{LockTableName}` (
                `Id` INT NOT NULL,
                `Timestamp` TEXT NOT NULL,
                CONSTRAINT `PK_{LockTableName}` PRIMARY KEY (`Id`)
            );
            """);

    private IRelationalCommand CreateInsertLockCommand(DateTimeOffset timestamp)
    {
        string timestampLiteral = Dependencies.TypeMappingSource.GetMapping(typeof(DateTimeOffset)).GenerateSqlLiteral(timestamp);

        return Dependencies.RawSqlCommandBuilder.Build(
            $"""
            INSERT IGNORE INTO `{LockTableName}` (`Id`, `Timestamp`) VALUES(1, {timestampLiteral});
            SELECT ROW_COUNT();
            """);
    }

    private MySqlMigrationDatabaseLock CreateMigrationDatabaseLock()
        => new(CreateDeleteLockCommand(), CreateRelationalCommandParameters(), this);

    private RelationalCommandParameterObject CreateRelationalCommandParameters()
        => new(
            Dependencies.Connection,
            null,
            null,
            Dependencies.CurrentContext.Context,
            Dependencies.CommandLogger, CommandSource.Migrations);
    #endregion
}

MySqlMigrationDatabaseLock.cs

/// <inheritdoc />
public class MySqlMigrationDatabaseLock(
    IRelationalCommand releaseLockCommand,
    RelationalCommandParameterObject relationalCommandParameters,
    IHistoryRepository historyRepository,
    CancellationToken cancellationToken = default)
: IMigrationsDatabaseLock
{
    /// <inheritdoc />
    public virtual IHistoryRepository HistoryRepository => historyRepository;

    /// <inheritdoc />
    public void Dispose()
        => releaseLockCommand.ExecuteScalar(relationalCommandParameters);

    /// <inheritdoc />
    public async ValueTask DisposeAsync()
        => await releaseLockCommand.ExecuteScalarAsync(relationalCommandParameters, cancellationToken).ConfigureAwait(false);
}

Program.cs

builder.Services
    .AddDbContext<AppDbContext>(options => options
        .UseMySQL(connectionString)
        .ReplaceService<IHistoryRepository, MySqlHistoryRepository>()
);

Answer 2

I used MySql.EntityFrameworkCore and had same problem. When I replaced it on Pomelo.EntityFrameworkCore.MySql all migrations accepted fine. I recommend use Pomelo.EntityFrameworkCore.MySql, it don't need a lot of change after migration from MySql.EntityFrameworkCore, little bit differs connection and thats all.