MySQL Bugs: #65289: Cannot create an entity with a key of type string

Bug #65289	Cannot create an entity with a key of type string
Submitted:	11 May 2012 21:05	Modified:	28 Sep 2012 19:02
Reporter:	Brice Lambson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.5.4.0	OS:	Windows
Assigned to:	Gabriela Martinez Sanchez	CPU Architecture:	Any
Tags:	DDL

Description:
The Connector/Net provider does not support creating an entity with a key of type string (see User class below).

During database creation, a MySqlException is thrown saying "BLOB/TEXT column 'Name' used in key specification without a key length."

The DDL produced by the provider is as follows.

    CREATE TABLE `Users`(
            `Name` mediumtext NOT NULL);

    ALTER TABLE `Users` ADD PRIMARY KEY (Name);

Fixing this is critical for the provider to work properly with Entity Framwork versions 4.3 and later since the __MigrationsHitory table (which replaces the EdmMetadata table) uses a string property as its key.

I've posted a lengthy workaround at http://brice-lambson.blogspot.com/2012/05/using-entity-framework-code-first-with.html

How to repeat:
class Context : DbContext
{
    public DbSet<User> Users { get; set; }
}

public class User
{
    [Key]
    [StringLength(255)]
    public string Name { get; set; }
}

class Program
{
    static void Main()
    {
        using (var context = new Context())
        {
            // Throws
            context.Database.Initialize(force: false);
        }
    }
}

Suggested fix:
There are actually two problems here, the first is that MaxLength SSDL facet is being ignored. Instead of using MEDIUMTEXT for the column's type, it should be using VARCHAR(255).

The second problem is that it is trying to mark a MEDIUMTEXT column as the primary key without specifying a key length. This may be the right thing to do since there is no way to specify a key's length in the Entity Framework. Another option would be to use a good default value for the key length of BLOB and TEXT columns (e.g. One that works well for both InnoDB and MyISAM.)

[MaxLength(254)] seems to work here :)

I was wrong... no primary key were created.

using the fluent api and the [MaxLength(255)] seems to be a possible work around if anybody needs it. 

modelBuilder.Entity<MyEntity>()
    .Property(p => p.ID)
    .HasColumnType("varchar");

Thanks for your bug report. A fix is in progress.

A fix for this bug has been pushed in Branches 6.4.x, 6.5.x, 6.6,x to be
released for the future versions (6.4.6, 6.5.5, 6.6.3).

Added to changelog for 6.4.6, 6.5.5, 6.6.3: 

Connector/Net did not support creating an entity with a key of type
string. During database creation, a MySqlException was thrown saying
BLOB/TEXT column 'Name' used in key specification without a key
length. The DDL produced by the provider specified a MEDIUMTEXT
column for the primary key without specifying a length for the key.
This fix is particularly important when working with Entity Framework
versions 4.3 and later, since the __MigrationsHitory table (which
replaces the EdmMetadata table) uses a string property as its key.