Click here to Skip to main content
14,176,371 members
Rate this:
 
Please Sign up or sign in to vote.
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
   
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
   
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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

This is a truly awful primary key. Why don't you just put a GUID somewhere and create this as an index?
   
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.
Ger Hayden 18-Apr-19 1:17am
   
Trouble is, I'm not confident enough to go changing said key and risk getting ground up in the gears of Identity Server or Microsoft Identity. My original response stems as much from frustration with that restriction as any other. Also to your credit, it was Richard who used the T word.
Christian Graus 18-Apr-19 1:18am
   
Why would Identity Server or Microsoft Identity care about the layout of your database beyond the tables that Identity uses?
Ger Hayden 18-Apr-19 2:00am
   
It doesn't. The offending table, 'aspnetuserlogins' which was part of the migration that comes with the Identity Server examples. I did a Find all on the IS example to illustrate this reply. It is only ever referenced in the migration as is the case with the other Identity related tables. It is using the machinery of Microsoft Identity to manage its data. Using a different key to that table is putting me off. I have gone with reducing the length of the existing key components.

That said, this interaction is becomming increasingly useful as its making me think - I have a 90 minute window twice a day for this stuff and its consumed most of this morning. Since we are talking a key here, the change I have made in reducing the length of the key components comes with risk. Adding a column and deeming it to be the key, say a Guid as per your suggestion. It won't break anything because Identity is not looking for it, particularly If I use the same name, it gaurantees that any reference to it is. I might need some wizardry under the hood to ensure that if there is a query based on it then the same data is retrieved.
Christian Graus 18-Apr-19 2:05am
   
Yeah, often, discussing your problem helps because you realise there's another way to fix it :)

I guess your core issue is the lack of key length in MySQL. If the two columns are GUIDs (but actually strings in Identity), it's possible to find guides online to change what those keys are (usually to strongly type them as GUID, but there's no reason you couldn't change them to Long, for example)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190525.1 | Last Updated 17 Apr 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100