1

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

Improve this question
1
  • What is the default character set for this table? Commented Jun 6, 2019 at 13:07

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments