I had an ef core migration fail with a key length too long targetting MySql.
The same migration is successful targetting SqlSever. How do I get around it in MySql?
When I apply the primary key directly using MySql Workbench, I get the same issue.
Both Key components are 767 long and the error claims that the key must be less than 3072 so not sure why I am seeing it. Online searches suggest that this is an encoding issue because on numbers alone a combined length of under 1600 should fit inside 3072. The setting in workbench advised where anything beyond key choice criticism is offered does not appear in my version.
How do I change the encoding so that a composite key of 1600 does not exceed the 3072 limit?
These are the column definitions:
LoginProvider varchar(767) NO
ProviderKey varchar(767) NO
ProviderDisplayName text YES
UserId varchar(767) NO
The combined key length is less than 1600 so should be comfortably inside the 3072 limit but I believe character encoding comes into play to take it beyond that. I cannot figure out how to get the migration to succeed.
Some background
This migration comes from Combined example on the IdentityServer website:
here
This is the source code from within the EF Migration from my copy targetting MySQL:
modelBuilder.Entity("Microsoft.AspNetCore.Identity.IdentityUserLogin<string>", b =>
{
b.Property<string>("LoginProvider")
.HasMaxLength(767);
b.Property<string>("ProviderKey")
.HasMaxLength(767);
b.Property<string>("ProviderDisplayName");
b.Property<string>("UserId")
.IsRequired();
b.HasIndex("UserId");
b.ToTable("AspNetUserLogins");
});
This is the corresponding code from the published example targetting SqlServer:
migrationBuilder.CreateTable(
name: "AspNetUserLogins",
columns: table => new
{
LoginProvider = table.Column<string>(nullable: false),
ProviderKey = table.Column<string>(nullable: false),
ProviderDisplayName = table.Column<string>(nullable: true),
UserId = table.Column<string>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_AspNetUserLogins", x => new { x.LoginProvider, x.ProviderKey });
table.ForeignKey(
name: "FK_AspNetUserLogins_AspNetUsers_UserId",
column: x => x.UserId,
principalTable: "AspNetUsers",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
Clearly different but having run it, I extracted the create statement from Sql Server Management Studio
/****** Object: Table [dbo].[AspNetUserLogins] Script Date: 16-Apr-19 6:42:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserLogins](
[LoginProvider] [nvarchar](450) NOT NULL,
[ProviderKey] [nvarchar](450) NOT NULL,
[ProviderDisplayName] [nvarchar](max) NULL,
[UserId] [nvarchar](450) NOT NULL,
CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED
(
[LoginProvider] ASC,
[ProviderKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The key length sums to 900 in SqlServer, but the max I can get MySql to allow is 768.
That is before I even consider the migrations for the other two contexts in the example. They are a total failure under MySQL and right now all I can see myself doing is extracting the create statements and translating the SQL manually
What I have tried:
I have tried both an EF Core migration and directly through MySQL workbench:
Executing:
ALTER TABLE `identityserver_003`.`aspnetuserlogins`
ADD PRIMARY KEY (`LoginProvider`, `ProviderKey`);
;
ERROR 1071: Specified key was too long; max key length is 3072 bytes
SQL Statement:
ALTER TABLE `identityserver_003`.`aspnetuserlogins`
ADD PRIMARY KEY (`LoginProvider`, `ProviderKey`)
Operation failed: There was an error while applying the SQL script to the database.
I can only get past this by reducing the length of the two varchars to 384. The innodb_large_prefix system variable is not available so recommendations around changing it are not applicable.
This is my version info
innodb_version 8.0.12
protocol_version 10
slave_type_conversions
tls_version TLSv1,TLSv1.1,TLSv1.2
version 8.0.12
version_comment MySQL Community Server - GPL
version_compile_machine x86_64
version_compile_os Win64
version_compile_zlib 1.2.11