EF6 MySql: Update-Database -Script generates SQL without semicolon

Question

I am having the following uneasy situation when using EF6 with MySql: each time I create a new migrations I apply the changes to my development environment using

     Update-Database

but when I want to generate the SQL script for my other environments (and to keep in source control) I use

     Update-Database -Script

and the generated SQL is something like this:

CREATE TABLE `AddressType` (`Id` NVARCHAR(10)  NOT NULL ,`Description` NVARCHAR(30)  NOT NULL ,PRIMARY KEY ( `Id`) ) ENGINE=INNODB AUTO_INCREMENT=0
CREATE TABLE `Bank` (`Id` INT NOT NULL ,`CNPJ` BIGINT NOT NULL ,`Name` NVARCHAR(100) ,`WebSite` NVARCHAR(500) ,PRIMARY KEY ( `Id`) ) ENGINE=INNODB AUTO_INCREMENT=0

When I try running the generated script on SQLyog and run it I get an error that my script has invalid syntax. I believe this is because EF only added one semicolon at the end of it, as it runs when I add the semicolons manually. One problem I have when I add the semicolons is that if the script fails for some reason the database is in inconsistent state, meaning the migration system will fail onwards because tables/columns will already exist.

Are there any settings that automatically add the semicolons after every statement? Is there any way I can ask MySql to do all or nothing when running my scripts / migrations?

Thanks.

Answer 1

You can accomplish this by extending the MySqlMigrationSqlGenerator as follows:

/// <summary>
/// Custom MigrationSqlGenerator to add semi-colons to the end of 
/// all migration statements.
/// </summary>
public class CustomMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator {
    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken) {
        IEnumerable<MigrationStatement> statements = base.Generate(migrationOperations, providerManifestToken);
        foreach (MigrationStatement statement in statements) {
            if (!statement.Sql.EndsWith(";")) {
                statement.Sql = statement.Sql.TrimEnd() + ";";
            }
        }
        return statements;
    }
}

And enable it in Configuration.cs:

public Configuration() {
    AutomaticMigrationsEnabled = false;
    SetSqlGenerator("MySql.Data.MySqlClient", new CustomMySqlMigrationSqlGenerator());
}