Pomelo.entityframeworkcore.mysql: Error in migration - maxLength below 256 generate TEXT fields instead VARCHAR in KEY fields

[ClimberBear]

Steps to reproduce

1) Create a new WebApp application with identity project using Net Core 3.0.0 with
dotnet new webapp -o WebApp -au Individual
as described in https://docs.microsoft.com/es-es/aspnet/core/security/authentication/social/?view=aspnetcore-3.0&tabs=visual-studio-code

2) Change the generated SQLite configuration to MySQL
2.a) Add NuPackage Pomelo.EntityFrameworkCore.MySql 3.0.0-rc1.final (also tested with 3.0.0-rc3)
2.b) Change connection call in Startup.cs to

services.AddDbContext<ApplicationDbContext>(
    opts =>
    opts
        .UseLazyLoadingProxies()
        .UseMySql(Configuration.GetConnectionString("Legal"),
    mySqlOptions => {
        mySqlOptions.ServerVersion(new Version(10, 4, 6), ServerType.MariaDb);
}));

2.c) Edit the appsetting.json to configure your connection string 'Legal' to the MariaDB database

3) Edit the generated file 00000000000000_CreateIdentitySchema.cs to add the field length in key fields, as described in https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

4) Generate the SQL scripts executing
dotnet ef migrations script -o CreateTables.sql

The issue

The string fields with maxLength defined are generated as VARCHAR if the length is equal or greater than 256. But if lower, remain as TEXT fields (and the TEXT fields cannot be part of an index)

Example:

Table AspNetUserTokens in 00000000000000_CreateIdentitySchema is

migrationBuilder.CreateTable(
  name: "AspNetUserTokens",
  columns: table => new
  {
    UserId = table.Column<string>(nullable: false, maxLength: 256),
    LoginProvider = table.Column<string>(nullable: false,maxLength: 128),
    Name = table.Column<string>(maxLength: 128, nullable: false),
    Value = table.Column<string>(nullable: true)
  },
  constraints: table =>
  {
    table.PrimaryKey("PK_AspNetUserTokens", x => new { x.UserId, x.LoginProvider, x.Name });
    table.ForeignKey(
      name: "FK_AspNetUserTokens_AspNetUsers_UserId",
      column: x => x.UserId,
      principalTable: "AspNetUsers",
      principalColumn: "Id",
      onDelete: ReferentialAction.Cascade);
});      

And the generated SQL is

CREATE TABLE `AspNetUserTokens` (
    `UserId` varchar(256) NOT NULL,
    `LoginProvider` TEXT NOT NULL,
    `Name` TEXT NOT NULL,
    `Value` TEXT NULL,
    CONSTRAINT `PK_AspNetUserTokens` PRIMARY KEY (`UserId`, `LoginProvider`, `Name`),
    CONSTRAINT `FK_AspNetUserTokens_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
);

Further technical details

MySQL version: MariaDB 10.4.6
Operating system: MacOS Catalina
Pomelo.EntityFrameworkCore.MySql version: 3.0.0-rc3 (also tested with 3.0.0-rc1)
Microsoft.AspNetCore.App version: 3.0.0

Related issues reviews:
https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length
https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/200

[mguinness]

Try using HasColumnType("varchar(128)") on the LoginProvider field in OnModelCreating.

[lauxjpn]

The solution @mguinness provided should definitely work.

But if we generate text columns for System.String properties with lengths < 256 but varchar columns for properties with lengths above that, this would be a bug.

@ClimberBear What does your model definition (OnModelCreating method) look like?

[ClimberBear]

Hi,

a) I have solved editing directly the generated sql, but, as commented, is any case is a bug. I have been investigating a little more, and the problem stands in the generation of TKey columns -- see https://docs.microsoft.com/es-es/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-3.0)

b) Notice that the model is generated as identity with
dotnet new webapp -o WebApp -au Individual
conceptually this has to work without any OnModelCreation additional rules.

This is not working, and I'm assuming that was never tested with IdentityModel

[lauxjpn]

I will take a look at it later today.

[lauxjpn]

I tested it mostly as you suggested and have a pretty good idea where the root cause of your issue lies.

Steps used for testing

I first created a new project:

dotnet new webapp -o WebApp -au Individual

I exchanged UseSqlite() with UseMySql() and updated the connection string in appsetting.json:

"DefaultConnection": "server=127.0.0.1;port=3306;uid=root;pwd=;database=Issue937"

Then I moved the existing Data\Migrations folder away from the project directory (as it contains generated code specific to Sqlite) and ran the following command to regenerate the migration:

dotnet ef migrations add CreateIdentitySchema -c ApplicationDbContext -o Data\Migrations

I then compared the old (moved) directory contents to the new Data\Migrations directory contents. Except for the data types, the definitions are almost the same (there is an additional model annotation). Here is an example of just one of the generated entities:

```c#
//
// 00000000000000_CreateIdentitySchema.cs:
//

migrationBuilder.CreateTable(
name: "AspNetUserTokens",
columns: table => new
{
UserId = table.Column(nullable: false),
LoginProvider = table.Column(maxLength: 128, nullable: false),
Name = table.Column(maxLength: 128, nullable: false),
Value = table.Column(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_AspNetUserTokens", x => new { x.UserId, x.LoginProvider, x.Name });
table.ForeignKey(
name: "FK_AspNetUserTokens_AspNetUsers_UserId",
column: x => x.UserId,
principalTable: "AspNetUsers",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});

//
// 00000000000000_CreateIdentitySchema.Designer.cs
// and ApplicationDbContextModelSnapshot.cs:
//

modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserToken", b =>
{
b.Property("UserId")
.HasColumnType("varchar(255) CHARACTER SET utf8mb4");

    b.Property<string>("LoginProvider")
        .HasColumnType("varchar(128) CHARACTER SET utf8mb4")
        .HasMaxLength(128);

    b.Property<string>("Name")
        .HasColumnType("varchar(128) CHARACTER SET utf8mb4")
        .HasMaxLength(128);

    b.Property<string>("Value")
        .HasColumnType("longtext CHARACTER SET utf8mb4");

    b.HasKey("UserId", "LoginProvider", "Name");

    b.ToTable("AspNetUserTokens");
});

I then ran the following command:

dotnet ef migrations script

For our sample table `AspNetUserTokens`, it generated the following SQL:

CREATE TABLE AspNetUserTokens (
UserId varchar(255) CHARACTER SET utf8mb4 NOT NULL,
LoginProvider varchar(128) CHARACTER SET utf8mb4 NOT NULL,
Name varchar(128) CHARACTER SET utf8mb4 NOT NULL,
Value longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT PK_AspNetUserTokens PRIMARY KEY (UserId, LoginProvider, Name),
CONSTRAINT FK_AspNetUserTokens_AspNetUsers_UserId
FOREIGN KEY (UserId) REFERENCES AspNetUsers (Id) ON DELETE CASCADE
);

So everything works as expected.

#### Probable cause of your issue

My best guess is, you did not regenerate the migration code for MySQL, but just kept using the one that was generated for Sqlite when creating the project.

You could probably also get away with replacing the data types manually (using search and replace) by replacing all/most occurrences of `.HasColumnType("TEXT")` in all files (including the `.Designer.cs` file) with something appropriate like `.HasColumnType("varchar(255)")`.
But some of the string properties are actually declared without a length (like `IdentityUserToken<string>.Value`) and should therefore use one of MySQLs `text` data types.

If most of these Sqlite `TEXT` datatypes do not get replaced with an appropriate `varchar(some_length)` data type, you force the script generator to emit the MySQL `text` data type instead of `varchar`, which is probably exactly what happened in your case.

#### Conclusion:

So to be safe, you should always regenerate the migrations after creating a new project and changing the database provider by using the command line above, to save yourself the trouble of manually needing to alter the generated code.

#### Regarding type changes

If you want to change the default model definition of `IdentityDbContext`, you can override the `OnModelCreating()` method before generating a migration:

```c#
protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<IdentityUserToken<string>>(entity =>
        entity.Property(e => e.Name).HasMaxLength(255));
}

Regarding maximum key length

If you want to limit the max. key length, there is actually already an option for that when using IdentityDbContext:

c# services.AddDefaultIdentity<IdentityUser>(options => { options.SignIn.RequireConfirmedAccount = true; options.Stores.MaxLengthForKeys = 64; // <-- limit key length })

[ClimberBear]

I thank you for your effort/time and I apologize for my mistake. You are totally right. I was not aware I had to regenerate the migration when changing the database engine (I thought Entity Framework isolates me from database engine).
Again, I'm sorry.

[lauxjpn]

No problem. This is probably a common mistake, so it is good to have it in here, so we can reference to it in the future.