May 9, 2012

Using Entity Framework Code First with MySQL's Connector/Net


This issue has been resolved.
For versions 6.6.3 or newer of Connector/Net, this workaround is not required.

Our team recently came across a Chinese post reporting an issue when using Code First with MySQL. You get the following exception while trying to create the database.
MySql.Data.MySqlClient.MySqlException: You have an error in 
your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'NOT 
NULL,
        `ProductVersion` mediumtext NOT NULL);

ALTER TABLE `__MigrationH' at line 5

The Problem

If you look at the full SQL that it is trying to run, the problem becomes clearer.
CREATE TABLE `__MigrationHistory` (
    `MigrationId` mediumtext NOT NULL,
    `Model` varbinary NOT NULL,
    `ProductVersion` mediumtext NOT NULL);

ALTER TABLE `__MigrationHistory`
ADD PRIMARY KEY (MigrationId);
In MySQL, varbinary types must specify a max length. That's not the only problem though; a mediumtext primary key also must specify a key length.
Interestingly, if you look at your database after recieving this exception, all of your tables are created and, if you try to run your app again everything appears to work.
So what's the problem? The problem is that there is no __MigrationHistory table. This table is essential for the Database.CompatibleWithModel method to work properly which, in turn, is used by the CreateDatabaseIfNotExists and DropCreateDatabaseIfModelChanges database initializers.

A Workaround

Until the Connector/Net provider is updated to properly handle the __MigrationHistory table, we'll need to create it ourselves fixing the two problems mentioned above. I've created a database initializer to do this for you modeled after the behavior of the CreateDatabaseIfNotExists initializer. Most of the code here can also be used to create one that mirrors DropCreateDatabaseIfModelChanges too. Here it is.
class CreateMySqlDatabaseIfNotExists<TContext>
    : IDatabaseInitializer<TContext>
        where TContext : DbContext
{
    public void InitializeDatabase(TContext context)
    {
        if (context.Database.Exists())
        {
            if (!context.Database.CompatibleWithModel(false))
            {
                throw new InvalidOperationException(
                    "The model has changed!");
            }
        }
        else
        {
            CreateMySqlDatabase(context);
        }
    }

    private void CreateMySqlDatabase(TContext context)
    {
        try
        {
            // Create as much of the database as we can
            context.Database.Create();

            // No exception? Don't need a workaround
            return;
        }
        catch (MySqlException ex)
        {
            // Ignore the parse exception
            if (ex.Number != 1064)
            {
                throw;
            }
        }

        // Manually create the metadata table
        using (var connection = ((MySqlConnection)context
            .Database.Connection).Clone())
        using (var command = connection.CreateCommand())
        {
            command.CommandText =
@"
CREATE TABLE __MigrationHistory (
    MigrationId mediumtext NOT NULL,
    Model mediumblob NOT NULL,
    ProductVersion mediumtext NOT NULL);

ALTER TABLE __MigrationHistory
ADD PRIMARY KEY (MigrationId(255));

INSERT INTO __MigrationHistory (
    MigrationId,
    Model,
    ProductVersion)
VALUES (
    'InitialCreate',
    @Model,
    @ProductVersion);
";
            command.Parameters.AddWithValue(
                "@Model",
                GetModel(context));
            command.Parameters.AddWithValue(
                "@ProductVersion",
                GetProductVersion());

            connection.Open();
            command.ExecuteNonQuery();
        }
    }

    private byte[] GetModel(TContext context)
    {
        using (var memoryStream = new MemoryStream())
        {
            using (var gzipStream = new GZipStream(
                memoryStream,
                CompressionMode.Compress))
            using (var xmlWriter = XmlWriter.Create(
                gzipStream,
                new XmlWriterSettings { Indent = true }))
            {
                EdmxWriter.WriteEdmx(context, xmlWriter);
            }

            return memoryStream.ToArray();
        }
    }

    private string GetProductVersion()
    {
        return typeof(DbContext).Assembly
            .GetCustomAttributes(false)
            .OfType<AssemblyInformationalVersionAttribute>()
            .Single()
            .InformationalVersion;
    }
}
There you have it. We basically let the Database.Create call do as much work as it can, then take over when it fails to create the __MigrationHistory table.

You can use the new initializer by calling Database.SetInitializer. One of the best places to do this is in your context's static constructor.
class MyContext : DbContext
{
    static MyContext()
    {
        Database.SetInitializer(
            new CreateMySqlDatabaseIfNotExists<MyContext>();
    }

    public MyContext()
        : base("Name=LocalMySqlServer")
    {
    }

    // Add DbSet properties here
}
Alternatively, you can set it in your App/Web.config.
<entityFramework>
  <contexts>
    <context type="MyNamespace.MyContext, MyAssembly">
      <databaseInitializer type="MyNamespace.
CreateMySqlDatabaseIfNotExists`1[[MyNamespace.MyContext,
MyAssembly]], MyAssembly" />
    </context>
  </contexts>
</entityFramework>

The Fix

Like any good open source software user, I've filed two bugs with the Connecter/Net team. You can check the status to see what progress has been made towards an actual fix for the problem.
  • Bug #65289 - Cannot create an entity with a key of type string
  • Bug #65290 - Cannot create an entity with a property of type byte[]

25 comments :

Ismail said...
This comment has been removed by the author.
Ismail said...

I've also encountered this error and I posted it http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-automatic-migrations-walkthrough.aspx#10301376 and http://stackoverflow.com/q/10464621/148271.

I thought it is problem with EF rather than MySql connector. But you might be right.

Thanks for sharing the workaround.
And after reading your blog, I feel that I'm not a good Open Source developer.

Brice said...

lol, I wouldn't feel bad; I work on the Entity Framework team at Microsoft. I had extra incentive to throughly investigate the issue and file bugs. ;)

Leandro Ribeiro said...

Good job!

This works fine !

The MySQL Team too posted another solution, change and build the MySQL Code.

http://forums.mysql.com/read.php?38,519005,535207#msg-535207

Anonymous said...

Would you mind telling me where I should place this code? I'm building a CRM and migrating to MySQL. Love the EFCode first approach but my brains exploding from dealing with Tax Software all day.

Delush Sasanka said...

But it work fine with older versions of Entity framework?
why is that?

Brice Lambson said...

@Anonymous, The code can go anywhere in your project -- the examples are complete classes.

@Delush Sasanka, in EF 4.3, we switched from using the EdmMetadata table to using the __MigrationHistory.

The EdmMetadata table was relatively simple -- just an integer primary key and an additional string column. The MySQL provider had no problem handling this.

The __MigrationHistory table was more complex -- it included a string primary key and a binary column. This exposed the two MySQL bugs that I've listed.

To be clear, these two bugs existed prior to EF 4.3, it's just that the EF runtime was never hitting them. Other projects using EF may have been.

Christopher Chow said...
This comment has been removed by the author.
Christopher Chow said...

There is a new column in the migrations table in EF 4.3.1 "CreatedOn".

The CreateMyDatabase method needs to be changed to the following:

https://gist.github.com/3061139

Delush Sasanka said...

Thanks Brice,
I really have to thank you for this.
Cheers Brother..!

Leo said...

(sorry for my bad english)
Thank you for this post. Now what would be even better is Code First Migrations support for MySQL ;)
Has anybody been able to make it work?

Tim said...

It now works with the connector 6.6 (in alpha yet)

Delush Sasanka said...
This comment has been removed by the author.
Delush Sasanka said...

Hi Brice,

I tried Migration with MySQL, when tried to Update-Database got an error

"No MigrationSqlGenerator found for provider 'MySql.Data.MySqlClient'. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators."

when tried to use SetSqlGenerator method, found that Migration is not supported with "MySqlClient". Could you please find me a solution for this.

Brice Lambson said...

@Delush As far as I know, Devart has the only Migrations-enabled EF provider for MySQL.

Dorababu said...

Hi I am using 6.3.5.0 MySql, and I have a table which was created as follows

CREATE TABLE `users` (
`UserID` int(11) NOT NULL AUTO_INCREMENT,
`UserName` varchar(45) DEFAULT NULL,
`Password` varchar(45) DEFAULT NULL,
`FirstName` varchar(45) DEFAULT NULL,
`LastName` varchar(45) DEFAULT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=7


I am having my routine as follows

CREATE DEFINER=`root`@`%` PROCEDURE `uspInsertUsers`(_UserName varchar(50),
_Password varchar(50),_FirstName varchar(50),_LastName varchar(50))
BEGIN
insert into users(UserName,Password,FirstName,LastName)values(_UserName,
_Password,_FirstName,_LastName);
END


In my code using entity framework I code as follows

MySqlParameter userName = new MySqlParameter("@UserName", txtUserName.Text);
//userName.Value = txtUserName.Text;
MySqlParameter password = new MySqlParameter("@Password", txtPassword.Text);
//password.Value = txtPassword.Text;
MySqlParameter FirstName = new MySqlParameter("@FirstName", txtFirstName.Text);
//FirstName.Value = txtFirstName.Text;
MySqlParameter LastName = new MySqlParameter("@LastName", txtLastName.Text);
//LastName.Value = txtLastName.Text;

entities.ExecuteStoreCommand("uspInsertUsers @UserName,@Password,@FirstName,@LastName", userName, password, FirstName, LastName);

But I am getting an exception as You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uspInsertUsers 'Dorababu','sairam','Dorababu','M'' at line 1

Can you help me

Brice Lambson said...

@Dorababu, you may want to try http://bugs.mysql.com/ instead. Things look good to my EF eyes, but I don't have a whole lot of experience with Connector/Net to say exactly what could be going wrong.

Anonymous said...

Hello sir is it necessary to Create a Custom MySql Database Initializer for MySQL 6.6.5 version . Can U explain how to Use EF Code First Approch with MYSQL 6.6.5 version

Brice Lambson said...

@Anonymous This issue was fixed in Connector/Net version 6.6.3. I've updated my post with this information. You should be able to use Code First normally as described in this post.

Anonymous said...

@Brice Sir ,
Thanks for the information given.

As i am new to MVC4 , EF and MYSQl ,I still have a doubt, I am using EF 5 and My SQL Connector 6.5.5 . I am unable to generate the database by using code first appraoch . What Will be the problem . What are the recommended version of EF and Connector to generate the daatebase by using code-first approach.

Anonymous said...

@Brice
Hello sir when i am trying to create Controller woth mvc read/ write actions with mysql database i am getting this error

"Unable to retrieve metadata for ProjectBuild"."Using the same DbCompiledModel to create contexts against different types of database servers is not supported. Instead, create a separate DbCompiledModel for each type of server being used.

what will be the problem

manny S said...

Brice. Thanks for the great writeups.
Generating a EDMX from MySQL takes a looooong time. Is that something that MSFT could fix or is it something that the folks writing the .net connector for mysql should fix? Our MySQl Database has about 60 tables and it takes 40 minutes to generate the entity data model. Love the speed and flexibility that entity framework brings to development and it would be great to see it play better with MySQL

Anonymous said...

Currently I'm working with an existing MySQL database (need to retain data and table structure) and adding in migrations. Unfortunately I'm running into problems with the 'update-database' command failing with saying that the database already exists. I've tried using CreateDatabaseIfNotExist as the initialiser, though that seems to be ignored and it tries to create the database anyway. I'm using version 6.6.5 of the MySQL connector.

Chandresh Desai said...

HI EF5 Code First will work with VS2012 mysql 5.6 and connector/Net 6.6.5,

I have tried bu not creating DB

can I get example for same?

Chandresh Desai said...

Anybody has done below example successfully

https://blogs.oracle.com/MySqlOnWindows/entry/building_an_mvc_3_application

I have tried above example , also tried work around, but no luck

Can you help me out?