How do I add migration to MYSQL database?

Question

I just hosted my .Net-core site to smarterasp.net but when I tried to update the database on MYSql I m getting error

Failed executing DbCommand (397ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

CREATE TABLE `AspNetUserRoles` (
    `UserId` varchar(255) NOT NULL,
    `RoleId` varchar(255) NOT NULL,
    CONSTRAINT `PK_AspNetUserRoles` 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
);

Specified key was too long; max key length is 1000 bytes

and my site is showing this in homepage

What is the default character set for this table? – Shadow Jun 06 '19 at 13:07 — Shadow, Jun 06 '19 at 13:07

score 1 · Answer 1 · answered Jun 06 '19 at 13:18

MySql has a limit over the max index key length. When you join UserId and RoleId it results in a long key for an Index (it is in bytes, not characters) so you should change the configuration for the innodb_large_prefix in your config file to ON. That will set your index key prefixes up to 3072 bytes as the mysql doc says.

[mysqld]
innodb_large_prefix = 1

You also need to change :

   SET GLOBAL innodb_file_format=Barracuda;
   SET GLOBAL innodb_file_per_table=1;
   SET GLOBAL innodb_large_prefix=1;
   logout & login (to get the global values);
   ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)

As you are using 255*2 chars for the key, that may result (depending on your char default set) in more than 1000 bytes.

You can take a look at the mysql docummentation You can red more here

How do I add migration to MYSQL database?

1 Answers1