Pomelo.entityframeworkcore.mysql: Adding Events via Migration causes Recursion of EVENT DDL statements is forbidden when body is present Error

[samuelcavendish]

Steps to reproduce

Scripting custom sql migrations to create events with -i flag causes a EVENT DLL Forbidden statement. I've created an example repo here https://github.com/samuelcavendish/PomeloIdempotentScript.

The script generated through Script-Migration -o migrate.sql -i can be found here https://github.com/samuelcavendish/PomeloIdempotentScript/blob/master/PomeloEventMigration/migrate.sql

The event migration:

    public partial class EventMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                CREATE EVENT IF NOT EXISTS ErrorLogsMaintainance
                ON SCHEDULE
                    EVERY 1 DAY
                    STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY)
                DO
                    DELETE FROM ErrorLogs
                    WHERE Date < CURRENT_DATE - INTERVAL 5 DAY;
            ");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql("DROP EVENT IF EXISTS ErrorLogsMaintainance");
        }
    }

Generates the following sql:

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20191108140919_Event Migration') THEN


                    CREATE EVENT IF NOT EXISTS ErrorLogsMaintainance
                    ON SCHEDULE
                        EVERY 1 DAY
                        STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY)
                    DO
                        DELETE FROM ErrorLogs
                        WHERE Date < CURRENT_DATE - INTERVAL 5 DAY;


    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

The issue

The sql generated causes the exception Recursion of EVENT DDL statements is forbidden when body is present. This is due to the fact it's trying to create the event from within the procedure which is forbidden in my sql.

Is there a way to create a event safely within an idempotent script?

[lauxjpn]

It seems it is not supported by MySQL to create an event from a stored procedure. Take a look at this question on StackOverflow.

Unfortunately, using a stored procedure for idempotent scripts is the only option we have right now. The root cause for this is described in https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/776#issuecomment-538019065.

@samuelcavendish With referring to this issue and the StackOverflow question, it might be worth to open an issue on the MySQL bug tracker.

[samuelcavendish]

Thanks for the response, I had a feeling that would be the case and I've not changed my DevOps pipeline to run the migration via the EF Code instead of using the idempotent script.

I'll see if I can find it already in the MySql tracker and if not look at getting it raised. Thanks for the input