Force engine=innodb when using Entity Framework Code First with mysql ef-migrations entity-framework entity-framework-6 mysql


Entity Framework 6 and a msyql database were used in the creation of a new.NET MVC 5 web application by me. I'm starting with code and model. MyISAM is the database server's default storage engine, however I would like that the tables that EF produces use InnoDb instead. Does anyone know if the SET UP A TABLE statement allows you to define the storage engine that EF will use?

11/5/2015 11:24:59 AM

Popular Answer

In reality, MySQL EF provider ALWAYS uses the InnoDB engine, and you can't alter it without rewriting the DDL generator.

Create a straightforward project and enable MySQL logging to test it out. Every create statement will end with, as you shall see.engine=InnoDb auto_increment=0

For instance, in this class

public class Blog
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Topic { get; set; }
    public DateTime LastUpdated { get; set; }

    public int Order { get; set; }
    public virtual List<Post> Posts { get; set; }

This MySQL DDL statement is generated with typical MySQL EF provider migration.

    `BlogId` INT NOT NULL auto_increment,
    `Name` NVARCHAR(200),
    `Topic` NVARCHAR(200),
    `LastUpdated` DATETIME NOT NULL,
    `Order` INT NOT NULL,
    PRIMARY KEY (`BlogId`)
    ) engine = InnoDb auto_increment = 0

What isengine = InnoDb from? In the migration source code, it is hard coded.
You can view the migration source code using the method.MySqlMigrationSqlGenerator.Generate(CreateTableOperation op) . The final assertion issb.Append(") engine=InnoDb auto_increment=0");

The correct query should therefore be, "How can I switch the engine from InnoDB to another engine?" You can override the function by inheriting from the MySqlMigrationSqlGenerator class, for example:

internal class MyOwnMigrationSqlGenerator : MySqlMigrationSqlGenerator

    public MyOwnMigrationSqlGenerator()
        Engine = "InnoDB";

    public MyOwnMigrationSqlGenerator(string engine)
        Engine = engine;

    private readonly List<MigrationStatement> _specialStatements = new List<MigrationStatement>();

    public string Engine { get; set; }

    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
        List<MigrationStatement> migrationStatements = base.Generate(migrationOperations, providerManifestToken).ToList();
        return migrationStatements;

    protected override MigrationStatement Generate(CreateTableOperation op)
        StringBuilder sb = new StringBuilder();
        string tableName = TrimSchemaPrefix(op.Name);
        var autoIncrementCols = (List<string>)(typeof(MySqlMigrationSqlGenerator).GetProperty("autoIncrementCols", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(this));
        var primaryKeyCols = (List<string>)(typeof(MySqlMigrationSqlGenerator).GetProperty("primaryKeyCols", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(this));

        sb.Append("create table " + "`" + tableName + "`" + " (");

        if (op.PrimaryKey != null)
            op.PrimaryKey.Columns.ToList().ForEach(col => primaryKeyCols.Add(col));

        sb.Append(string.Join(",", op.Columns.Select(c => "`" + c.Name + "` " + Generate(c))));

        // Determine columns that are GUID & identity 
        List<ColumnModel> guidCols = new List<ColumnModel>();
        ColumnModel guidPk = null;
        foreach (ColumnModel columnModel in op.Columns)
            if (columnModel.Type == PrimitiveTypeKind.Guid && columnModel.IsIdentity && String.Compare(columnModel.StoreType, "CHAR(36) BINARY", true) == 0)
                if (primaryKeyCols.Contains(columnModel.Name))
                    guidPk = columnModel;

        if (guidCols.Count != 0)
            var createTrigger = new StringBuilder();
            createTrigger.AppendLine(string.Format("DROP TRIGGER IF EXISTS `{0}_IdentityTgr`;", TrimSchemaPrefix(tableName)));
            createTrigger.AppendLine(string.Format("CREATE TRIGGER `{0}_IdentityTgr` BEFORE INSERT ON `{0}`", TrimSchemaPrefix(tableName)));
            createTrigger.AppendLine("FOR EACH ROW BEGIN");
            foreach (ColumnModel opCol in guidCols)
                createTrigger.AppendLine(string.Format("SET NEW.{0} = UUID();", opCol.Name));
            createTrigger.AppendLine(string.Format("DROP TEMPORARY TABLE IF EXISTS tmpIdentity_{0};", TrimSchemaPrefix(tableName)));
            createTrigger.AppendLine(string.Format("CREATE TEMPORARY TABLE tmpIdentity_{0} (guid CHAR(36))ENGINE=MEMORY;", TrimSchemaPrefix(tableName)));
            createTrigger.AppendLine(string.Format("INSERT INTO tmpIdentity_{0} VALUES(New.{1});", TrimSchemaPrefix(tableName), guidPk.Name));
            var sqlOp = new SqlOperation(createTrigger.ToString());

        if (op.PrimaryKey != null) // && !sb.ToString().Contains("primary key")) 
            sb.Append("primary key ( " + string.Join(",", op.PrimaryKey.Columns.Select(c => "`" + c + "`")) + ") ");

        string keyFields = ",";
        autoIncrementCols.ForEach(col => keyFields += (!primaryKeyCols.Contains(col) ? string.Format(" KEY (`{0}`),", col) : ""));
        sb.Append(keyFields.Substring(0, keyFields.LastIndexOf(",")));
        sb.Append(string.Format(") engine={0} auto_increment=0", Engine));

        return new MigrationStatement() { Sql = sb.ToString() };

    private string TrimSchemaPrefix(string table)
        if (table.StartsWith("dbo.") || table.Contains("dbo."))
            return table.Replace("dbo.", "");

        return table;


Then, you can define your own sql generator in your migration setup.

internal sealed class MyContextMigrationConfiguration : DbMigrationsConfiguration<MyContext>
    public MyContextMigrationConfiguration()
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
        SetSqlGenerator("MySql.Data.MySqlClient", new MyOwnMigrationSqlGenerator("MyPreferredEngine"));


The MyOwnMigrationSqlGenerator class contained a problem. Rewriting all instances of MySqlMigrationSqlGenerator is most likely the best option. In this instance, I simply fixed the class that was accessing MySqlMigrationSqlGenerator's private fields (that is quite bad).

8/16/2016 10:18:35 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow