Click here to Skip to main content
15,444,490 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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.HasKey("LoginProvider", "ProviderKey");

                   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
Posted
Updated 16-Apr-19 21:26pm
v4
Comments
ZurdoDev 16-Apr-19 12:04pm    
I'm confused. If I understand you correctly, you have surpassed a limit in MySql. So, what do you want from us?
Ger Hayden 16-Apr-19 13:02pm     CRLF
To be precise, how do I change the encoding so that a composite key of 1600 does not exceed the 3072 limit? I have updated the question with more detail.
GKP1992 17-Apr-19 3:56am     CRLF
This may be of some help. [^] Also, nvarchar uses 2 bytes per character whereas varchar uses 1. The direct correlation of success at 368 and failure at 767 is suggestive enough of that.
Ger Hayden 17-Apr-19 4:36am    
That might just be it. I will need to read it a little more carefully but at a glance it looks promising.

1 solution

This is a truly awful primary key. Why don't you just put a GUID somewhere and create this as an index?
 
Share this answer
 
Comments
Ger Hayden 16-Apr-19 1:52am    
You may take that up with Brock Allen & Dominick Baier at IdentityServer.io - It's a migration from thier sample code. My only intervention was to apply it to MySql.
Richard MacCutchan 17-Apr-19 4:22am    
Blimey Christian, you've only been back a day and the trolls are at you.
Christian Graus 17-Apr-19 18:26pm    
And that was before I started poking bigots with a stick.....
Ger Hayden 18-Apr-19 1:05am    
Could someone please do an audit of my interaction with this forum and point out what marks me as troll - or a bigot? Neither are something I would intentionally set out to be. If there was an error in my response to this solution it is that this key has its origins in Microsoft rather than Identity Server. The solution offered in this thread is both useless and unhelpful. I gave up searching for it on Stackoverflow because I got tired of reading key choice criticisms rather than any attempt to explain why 1 + 1 > 4. Kudos to GKP1992 for actually digging one out.
Christian Graus 18-Apr-19 1:06am    
I didn't say you were a bigot. Someone pointed out you were sarcastic when I pointed out that by definition the solution you're pursuing is awful. I'm not upset. Relax.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900