Pomelo.entityframeworkcore.mysql: Error applying migration:

[nuno-tiago-reis]

Steps to reproduce

migrationBuilder.CreateTable
(
    name: "AspNetOrganizationRelations",
    schema: "Identity",
    columns: table => new
    {
        Id = table.Column<long>(nullable: false),
        Name = table.Column<string>(maxLength: 100, nullable: false),
        NormalizedName = table.Column<string>(maxLength: 100, nullable: false),
        CreatedAt = table.Column<DateTime>(nullable: false),
        UpdatedAt = table.Column<DateTime>(nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_AspNetOrganizationRelations", x => x.Id);
    }
);

migrationBuilder.DropPrimaryKey
(
    name: "PK_AspNetOrganizationRelations",
    schema: "Identity",
    table: "AspNetOrganizationRelations"
);

The issue

I'm applying a set of migrations that were working previously but now after upgrading to dotnet core 3.0 (and Pomelo.EntityFrameworkCore.MySql 3.0.0-rc2.final) I'm getting the following error:

MySql.Data.MySqlClient.MySqlException (0x80004005): PROCEDURE identity.POMELO_BEFORE_DROP_PRIMARY_KEY does not exist

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CALL POMELO_BEFORE_DROP_PRIMARY_KEY('Identity', 'AspNetOrganizationRelations');
      ALTER TABLE `Identity`.`AspNetOrganizationRelations` DROP PRIMARY KEY;
Application startup exception: MySql.Data.MySqlClient.MySqlException (0x80004005): PROCEDURE identity.POMELO_BEFORE_DROP_PRIMARY_KEY does not exist
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): PROCEDURE identity.POMELO_BEFORE_DROP_PRIMARY_KEY does not exist
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 116
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 226
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 74
   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.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at xxx.Startup.Configure(IApplicationBuilder builder) in D:\xxx\Startup.cs:line 449
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.WebHost.BuildApplication()
crit: Microsoft.AspNetCore.Hosting.WebHost[6]
      Application startup exception
MySql.Data.MySqlClient.MySqlException (0x80004005): PROCEDURE identity.POMELO_BEFORE_DROP_PRIMARY_KEY does not exist
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): PROCEDURE identity.POMELO_BEFORE_DROP_PRIMARY_KEY does not exist
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 116
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 226
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 74
   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.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at xxx.Startup.Configure(IApplicationBuilder builder) in D:\xxx\Startup.cs:line 449
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)
   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.WebHost.BuildApplication()

Further technical details

MySQL version: 8.0.17
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 3.0.0-rc2.final
Microsoft.AspNetCore.App version: 3.0.0

[lauxjpn]

MySQL does not support EF Core/MSSQL/Oracle schemas (which are not databases). What is called a schema in MySQL terminology, is just a database in EF Core.

See https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/892#issuecomment-546073417 for further details.

---

I think we really need to throw in case a schema has been set, as users trip over this issue a lot.

[mguinness]

From MySQL Glossary

In MySQL, physically, a _schema_ is synonymous with a _database_. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

Some other database products draw a distinction. For example, in the Oracle Database product, a _schema_ represents only a part of a database: the tables and other objects owned by a single user.

[nuno-tiago-reis]

I'm aware that in MySQL schemas are actually databases but these migrations were working fine with previous version (Pomelo.EntityFrameworkCore.MySq 2.2.0).

I also have three different schemas each with its own DbContext.

Is this something that was changed from 2.2.0 in 3.0.0?

[lauxjpn]

There have been many changes between 2.2.0 and 3.0.0. Fixing a couple of issues regarding generated scripts was one of those areas. See #776 and its corresponding PR's for details about this particular one.

I prepared a fix, that will throw on any use of the Schema properties, as this is an issue hot spot and not supported.

I also have three different schemas each with its own DbContext.

If with schemas you mean databases, than that is the way to do it!

[nuno-tiago-reis]

I just finished removing all the schema definitions from my migrations but the same error still occurs.
Are you sure that's the cause of the issue?

Edit: I also updated to 3.0.0-rc3.final and I'm getting no error so I'm assuming I cleaned everything up correctly.

Attached a log from the initial steps of the migrations hoping it might help since I still see some TABLE_SCHEMA entries showing there. These are also the only logs in which the database name shows up other than the logs corresponding with the error.

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.0.0 initialized 'IdentityContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=IdentityManagement MigrationsHistoryTable=__EFMigrationsHistory
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='Identity' AND TABLE_NAME='__EFMigrationsHistory';
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `__EFMigrationsHistory` (
          `MigrationId` varchar(95) NOT NULL,
          `ProductVersion` varchar(32) NOT NULL,
          CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`)
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='Identity' AND TABLE_NAME='__EFMigrationsHistory';
info: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `MigrationId`, `ProductVersion`
      FROM `__EFMigrationsHistory`
      ORDER BY `MigrationId`;
info: Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20190322164258_AddedAspNetIdentity'.

[lauxjpn]

I'll take a look at it again tomorrow. I still have the code here that I used to test the issue, so if it still works for me, I will post it for you to check, so we can narrow down were the actual issue lies.

Do you use the command line tools to create a script of the migrations and then use the database update command or how are you applying the migrations?

[nuno-tiago-reis]

I was using automatic migrations on application startup:

using (var scope = builder.ApplicationServices.CreateScope())
{
    scope.ServiceProvider.GetService<IdentityContext>().Database.Migrate();
    scope.ServiceProvider.GetService<IdentitySeeder>().Seed();
}

I just tried using database update and the same error occurs.

Edit: I use the command line tools to create the migrations, yes.

[lauxjpn]

I can still not reproduce this issue. I used the following code trying to replicate it:

```c#
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
public class AspNetOrganizationRelations
{
public long Id { get; set; }
public string Name { get; set; }
public string NormalizedName { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime UpdatedAt { get; set; }
}

public class Context : DbContext
{
    public virtual DbSet<AspNetOrganizationRelations> AspNetOrganizationRelations { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql("server=127.0.0.1;port=3306;user=root;password=;database=Issue922")
            .UseLoggerFactory(LoggerFactory.Create(b => b
                .AddConsole()
                .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AspNetOrganizationRelations>(entity =>
        {
            // Added in migration `DropPrimaryKey`:
            // entity.HasNoKey();

            entity.Property(e => e.Id);

            entity.Property(e => e.Name)
                .IsRequired();

            entity.Property(e => e.NormalizedName)
                .IsRequired();
        });
    }
}

internal class Program
{
    private static void Main()
    {
    }
}

}

I then ran the following command to generate the first migration named `Initial`:

dotnet ef migrations add Initial --verbose

It looks like this:
```c#
public partial class Initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "AspNetOrganizationRelations",

            // No schema support!
            // schema: "Identity",

            columns: table => new
            {
                Id = table.Column<long>(nullable: false)
                    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
                Name = table.Column<string>(nullable: false),
                NormalizedName = table.Column<string>(nullable: false),
                CreatedAt = table.Column<DateTime>(nullable: false),
                UpdatedAt = table.Column<DateTime>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AspNetOrganizationRelations", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            // No schema support!
            // schema: "Identity",

            name: "AspNetOrganizationRelations");
    }
}

I then added entity.HasNoKey(); to the model definition and ran the following command to generate the second migration named DropPrimaryKey:

dotnet ef migrations add DropPrimaryKey --verbose

It looks like this:
```c#
public partial class DropPrimaryKey : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropPrimaryKey(
name: "PK_AspNetOrganizationRelations",

        // No schema support!
        // schema: "Identity",

        table: "AspNetOrganizationRelations");

    migrationBuilder.AlterColumn<long>(
        name: "Id",

        // No schema support!
        // schema: "Identity",

        table: "AspNetOrganizationRelations",
        nullable: false,
        oldClrType: typeof(long),
        oldType: "bigint")
        .OldAnnotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<long>(
        name: "Id",

        // No schema support!
        // schema: "Identity",

        table: "AspNetOrganizationRelations",
        type: "bigint",
        nullable: false,
        oldClrType: typeof(long))
        .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

    migrationBuilder.AddPrimaryKey(
        name: "PK_AspNetOrganizationRelations",

        // No schema support!
        // schema: "Identity",

        table: "AspNetOrganizationRelations",
        column: "Id");
}

}

I then ran the following command to generate a script:

dotnet ef migrations script

It looks like this:
```sql
DROP PROCEDURE IF EXISTS POMELO_BEFORE_DROP_PRIMARY_KEY;
DELIMITER //
CREATE PROCEDURE POMELO_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 //
DELIMITER ;

CREATE TABLE IF NOT EXISTS `__EFMigrationsHistory` (
    `MigrationId` varchar(95) NOT NULL,
    `ProductVersion` varchar(32) NOT NULL,
    CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`)
);

CREATE TABLE `AspNetOrganizationRelations` (
    `Id` bigint NOT NULL AUTO_INCREMENT,
    `Name` longtext CHARACTER SET utf8mb4 NOT NULL,
    `NormalizedName` longtext CHARACTER SET utf8mb4 NOT NULL,
    `CreatedAt` datetime(6) NOT NULL,
    `UpdatedAt` datetime(6) NOT NULL,
    CONSTRAINT `PK_AspNetOrganizationRelations` PRIMARY KEY (`Id`)
);

INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`)
VALUES ('20191107172223_Initial', '3.0.1-dev');

CALL POMELO_BEFORE_DROP_PRIMARY_KEY(NULL, 'AspNetOrganizationRelations');
ALTER TABLE `AspNetOrganizationRelations` DROP PRIMARY KEY;

ALTER TABLE `AspNetOrganizationRelations` MODIFY COLUMN `Id` bigint NOT NULL;

INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`)
VALUES ('20191107172455_DropPrimaryKey', '3.0.1-dev');


DROP PROCEDURE POMELO_BEFORE_DROP_PRIMARY_KEY;

Finally, I created a database using MySQL Workbench, set it as the default, copied the generated script over and ran it without any issues.

I only got two expected warnings, one of them being, that the stored procedure does not exist yet and therefore the DROP PROCEDURE IF EXISTS statement will not be executed (the other one is unrelated):

DROP PROCEDURE IF EXISTS POMELO_BEFORE_DROP_PRIMARY_KEY 0 row(s) affected, 1 warning(s):
1305 PROCEDURE issue922.POMELO_BEFORE_DROP_PRIMARY_KEY does not exist   0.000 sec

I suggest you test the code I provided to make sure that the same SQL is being generated and that it works as expected.

You can also post the full generated script (at a minimum for the migration in question) of your original migration for us to take a look at if you like.

Also, if you used calls like .HasDefaultSchema("Identity") before, please ensure that those (and other schema related calls) are also removed from the migrations .Designer.cs files (best is to do a solution wide search for schema).

[nuno-tiago-reis]

Here's the migration script generated. If I execute the script directly it seems to work but when it's being executed through database update it throws that error.

I also tried using a completely new database but the same error occurred (just in case there was a schema with the previous name defined somewhere).

I'll try to get a project with the minimum code required to reproduce this but meanwhile here's the script that was generated.

Migration.zip

[nuno-tiago-reis]

Here's the web application just containing the entity framework part.

MigrationErrorProject.zip

[lauxjpn]

Thank you for providing us with a project to reproduce this issue!

When I generate an idempotent script, it will not run without errors. There are a couple of custom SQL statements in the migration files, that do not terminate their SQL statements with a semicolon.

Take a look at the sample provided for Custom Migrations Operations in the EF Core docs.

This is an issue for the the following files and statements:

```c#
// 20191001150731_AddedSeveralColumnsToUsers.cs:
migrationBuilder.Sql("UPDATE Identity.AspNetUsers SET FirstName = KnownAs");
migrationBuilder.Sql("UPDATE Identity.AspNetUsers SET LastName = KnownAs");

// 20191001160342_AdjustedUserColumnRestrictions.cs:
migrationBuilder.Sql("UPDATE Identity.AspNetUsers SET PrivacyPolicyAccepted = true");

// 20191014150543_PopulatedCountriesTable:
('Zimbabwe','ZIMBABWE','The Republic of Zimbabwe','THE REPUBLIC OF ZIMBABWE','ZW','ZWE','716','ISO 3166-2:ZW','.zw','{createdAt}')

Looking at this, I also discovered a related bug in the current provider releases, where the `ALTER TABLE RENAME COLUMN` statements are being generated without a semicolon at end of the statement.

You might also want to remove the `Identity.` specified from the following lines:

```c#
// 20191001150731_AddedSeveralColumnsToUsers.cs:
migrationBuilder.Sql("UPDATE Identity.AspNetUsers SET FirstName = KnownAs;");
migrationBuilder.Sql("UPDATE Identity.AspNetUsers SET LastName = KnownAs;");

// 20191001160342_AdjustedUserColumnRestrictions.cs:
migrationBuilder.Sql("UPDATE Identity.AspNetUsers SET PrivacyPolicyAccepted = true;");

// 20191014150543_PopulatedCountriesTable.cs:
migrationBuilder.Sql
($@"
    INSERT INTO
        Identity.AspNetCountries (Name, NormalizedName, OfficialStateName, NormalizedOfficialStateName, 
// ...
migrationBuilder.Sql(@"DELETE FROM Identity.AspNetCountries");

// 20191014163847_AddedCountryToUsersAndOrganizations.cs:
migrationBuilder.Sql
(@"
    SET SQL_SAFE_UPDATES = 0;
    UPDATE Identity.AspNetOrganizations SET CountryId = 1;
    SET SQL_SAFE_UPDATES = 1;
");

These issues are unrelated to your reported one though, as they will only surface with scripts and not when executing the dotnet ef database update command or when applying the migrations from code because each statement will be run independently in those cases.

With the project you provided, I was able to reproduce the issue you reported.

The underlying problem was introduced in the fix for #776 (the one I referenced earlier). The stored procedures that we rely upon to add and drop primary keys, were moved to be created before any scripts are being executed, so their creation statements would not end up anymore inside of the stored procedures generated for idempotent scripts and consequently fail, because creating stored procedures from inside stored procedures is currently not supported by MySQL.

Unfortunately, we moved them at the beginning of the generation for scripts (the Migrator.GenerateScript() method), which is executed when e.g. calling dotnet ef migrations scripts (with or without the --idempotent option) but not in other cases, as when calling Migrator.Migrate() or Migrator.MigrateAsync(), which would mean that those stored procedures would not exist in those cases.

I will provide a fix for this shortly, where we move the creation of needed stored procedures to the Migrator.GenerateUpSql() and Migrator.GenerateDownSql() methods in all cases except when an idempotent script is being generated.

[nuno-tiago-reis]

Alright, thank you so much for your help :) Glad it got worked out!

I've fixed the non-related issues that you listed, thank you for that as well 👍

I'll let you know once if the issue was solved once that fix gets merged.

[nuno-tiago-reis]

Just confirming the issue is fixed 👍

[lauxjpn]

Thanks for reporting the issue in the first place and for verifying that it got fixed now!