In ASP.NET Core Web MVC Applicaiton, how do you create Identity Tables in MySQL


In ASP.NET Core Web MVC Application, I have not been able to successfully create the tables needed for Identity in MySQL

Steps to reproduce (in Visual Studio) :

  1. File -> New Solution -> Web Application (Model-View-Controller)
  2. Authentication -> Individual Authentication (in-app)
  3. Type solution name
  4. Manage Nuget Packages -> Install MySql.Data.EntityFrameworkCore
  5. Uninstall the SQLLite package.
  6. In Startup.cs, add:
    (o => o.UseMySQL(Configuration.
  1. In appsettings.json, set default connections to the MySQL database (example below)
"DefaultConnection": "Server=localhost;Database=TestDB; Uid=root;Pwd=YOURPASSWORDHERE"
  1. This is where the error occurs since you generated the DbContextModelSnapshot initially with SQLite, you have to delete and then regenerate it (I believe) for MySQL with the following command:
dotnet ef migrations add CreateIdentitySchema -o Data\Migrations

Then, of course, update the database with:

dotnet ef database update

However, we get the following error:

CREATE TABLE `AspNetUserRoles` (
   `UserId` varchar(767) NOT NULL,
   `RoleId` varchar(767) NOT NULL,
   PRIMARY KEY (`UserId`, `RoleId`),
   CONSTRAINT `FK_AspNetUserRoles_AspNetRoles_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `AspNetRoles` (`Id`) ON DELETE CASCADE,
   CONSTRAINT `FK_AspNetUserRoles_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE

MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 3072 bytes

This is because our Snapshot is generating our UserId to have a varchar length of 767 and our RoleId to have a varchar length of 767. In MySQL, each char (in UTF8) is worth 3 bytes. Hence 1534*3 > 3072 bytes. However, Identity only requires these columns to have a length of 128 bytes.

So my question is this: how do we get the migration to reflect this needed change?

asked on Stack Overflow Oct 8, 2020 by JJ Thompson

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0