Creating MySql database with EF Core

Question

I am using AspNetCore.Identity.EntityFrameworkCore (1.1.1) and SapientGuardian (7.1.23) to generate a code first database, because I have a MySql database (MariaDB). When using the Updata-Database command or context.Database.EnsureCreated(); the following exception is thrown:

Index column size too large. The maximum column size is 767 bytes.

I inspected some logs and found out that all tables are created. But while executing the Create Index [...] queries the problem occurs. The following query is the last one before the exception is thrown:

CREATE INDEX EmailIndex ON AspNetUsers (NormalizedEmail);

The type of the Email and NormalizedEmail column is varchar(255)

What is the cause for this problem and how can I fix it?

Edit:

SELECT character_set_name, column_type FROM information_schema.`COLUMNS` 
WHERE table_schema = "schema"
  AND table_name = "AspNetUsers"
  AND (column_name = "Email" OR COLUMN_NAME="NormalizedEmail");

Delivered:

|---------------------|------------------|
| character_set_name  |   column_type    |
|---------------------|------------------|
|          utf8       |   varchar(256)   |
|---------------------|------------------|
|          utf8       |   varchar(256)   |
|---------------------|------------------|

Edit2: Manually adjusting the migrations generated by ef core solves this problem. I changed the max length attribute for all indices from 256 to 255. But is there a way to configure entity framework core to adjust the migrations?

Answer 1

Just to summarise things up that was discussed in the comments:

The error message is related to the fact that in MySQL's innodb table the limit on single field index length is 767 bytes in most cases. The NormalizedEmail field has the type of varchar(256) and the character set of utf8. In MySQL utf8 characters can be up to 3 bytes long, therefore the index byte length in this case is 256 * 3 + 2 = 770 bytes, which is longer than the maximum limit.

There are 2 possible solutions:

1. Manually provide an index length prefix in the create index statement to 255. The drawback is that not the entire field is indexed.

   CREATE INDEX EmailIndex ON AspNetUsers (NormalizedEmail(255));
   

2. Limit the length of the NormalizedEmail field to 255 in EF code using maximum length restriction.