Click here to Skip to main content
14,644,700 members

How do I set a longer mysql primary key so 1600 fits in 3072

Ger Hayden asked:

Open original thread
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
Tags: C#, MySQL

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.




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