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

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

Question

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?

1
1
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; }
    [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; }
}

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

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

What isengine = InnoDb from? In the migration source code, it is hard coded.
You can view the migration source code using the https://github.com/mysql/mysql-connector-net/blob/6.9/Source/MySql.Data.EntityFramework5/MySqlMigrationSqlGenerator.cs 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();
        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, 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"));
    }

}

EDIT
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).

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