Force engine=innodb when using Entity Framework Code First with mysql

asp.net-mvc ef-migrations entity-framework entity-framework-6 mysql

Question

I have created a new .NET MVC 5 web application using Entity Framework 6 and a msyql database. I am using code/model first. The database server has a default storage engine of MyISAM, but I would like for the tables that EF creates to be InnoDb. Does anyone know if there is as way to specify the storage engine that EF will use in the CREATE TABLE statement?

1
1
11/5/2015 11:24:59 AM

Popular Answer

Actually the engine used by MySQL EF provider is ALWAYS InnoDB and you can't change it without rewriting the DDL generator.

To try you can create a simple project and enable log on MySQL. You will notice that every create statement will terminate with engine=InnoDb auto_increment=0

For example this class

public class Blog
{
    public int BlogId { get; set; }
    [MaxLength(200)]
    public string Name { get; set; }
    [MaxLength(200)]
    public string Topic { get; set; }
    public DateTime LastUpdated { get; set; }

    [DefaultValue(0)]
    public int Order { get; set; }
    public virtual List<Post> Posts { get; set; }
}

with standard MySQL EF provider migration, generates this MySQL DDL statement

CREATE TABLE `Blogs` (
    `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

Where is engine = InnoDb from? It's hard coded in migration source code.
You can have a look at the migration source code https://github.com/mysql/mysql-connector-net/blob/6.9/Source/MySql.Data.EntityFramework5/MySqlMigrationSqlGenerator.cs method MySqlMigrationSqlGenerator.Generate(CreateTableOperation op). The last statement is sb.Append(") engine=InnoDb auto_increment=0");

So, the right question should be how can I change the engine from InnoDB to another engine. You can inherit MySqlMigrationSqlGenerator class and override the method, i.e.:

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();
        migrationStatements.AddRange(_specialStatements);
        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));
        }


        //columns 
        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;
                guidCols.Add(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));
            createTrigger.AppendLine("END");
            var sqlOp = new SqlOperation(createTrigger.ToString());
            _specialStatements.Add(Generate(sqlOp));
        }


        if (op.PrimaryKey != null) // && !sb.ToString().Contains("primary key")) 
        {
            sb.Append(",");
            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, in your migration configuration you can specify your own sql generator.

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

}

EDIT
There was a bug on MyOwnMigrationSqlGenerator class. Probably the best thing is to rewrite all MySqlMigrationSqlGenerator. In this case I just fixed the class accessing private fields of MySqlMigrationSqlGenerator (that is quite bad).

2
8/16/2016 10:18:35 AM


Related Questions





Related

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