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?

What's the character set of the NormalizedEmail field? Is it uf8 or utf8mb4? — Shadow, Apr 28 '17 at 09:31
@Shadow It is utf8_general_ci for all fields (Email, NormailizedEmail, Username, Id (Guid)) — jasdefer, Apr 28 '17 at 09:35
That's a collation, not character set. Are you sure that NormailizedEmail is plain utf8 and not utf8mb4? The math does not work out with utf8. — Shadow, Apr 28 '17 at 09:45
@Shadow it is utf8: CREATE TABLE `AspNetUsers` (...) ENGINE=InnoDB DEFAULT CHARSET=utf8; — jasdefer, Apr 28 '17 at 09:51
The default charset can be overridden by the field definition. utf8 (in MySQL) uses only 3 bytes. The field therefore can occupy a maximum of 3 * 255 + 1 = 766 bytes, which is within the 767 bytes limit. This is why I'm saying that with utf8 the math does not work out. You should not get an error message. — Shadow, Apr 28 '17 at 09:55
Possible duplicate of [MySQL varchar index length](http://stackoverflow.com/questions/15157227/mysql-varchar-index-length) — Rowland Shaw, Apr 28 '17 at 10:12
@Shadow Ah okay thanks. I am unfamiliar with MySql. Executing the query `SELECT character_set_name FROM information_schema.COLUMNS` delivers `utf8` for the usernames and email adresses. — jasdefer, Apr 28 '17 at 10:13
@RowlandShaw I'm not sure that it is a duplicate, since the OP claims that the NormalisedEmail field is varchar(255) and the character set is utf8. In this case the index should be allowed. The linked question also says that varchar(255) is allowed with utf8. I also checked MariaDB documentation and it also confirmed that utf8 there can take up to 3 bytes only. — Shadow, Apr 28 '17 at 10:22
@jasdefer If the character set is utf8 and the field type is varchar(255), then mariadb should not report an error on that index creation according - at least as far as I know. Either you got sg wrong (it's not this index that raised the error, or the charset is different, or the field is longer), or you ran into a bug. — Shadow, Apr 28 '17 at 10:27
@RowlandShaw that information was provided in the last edit about 7 mins ago. If you read a bit further up, the question still says `The type of the Email and NormalizedEmail column is varchar(255)` — Shadow, Apr 28 '17 at 13:10
@jasdefer using data annotations to define maximum length does not help? You could define the email fields as 255. See https://learn.microsoft.com/en-us/ef/core/modeling/max-length — Shadow, Apr 28 '17 at 13:16
@Shadow Yeah I made a mistake looking up the varchar lenght the first time, sorry for that. I used the fluent api rather than annotations. I changed properties `NormalizedUserName` and `NormalizedEmail` as well as the `NormalizedName` property of the `IdentityRole`. But this still feels somehow dirty, especially because I don't need this for a SQL server, even though I can add a flag to adjust the settings. But thank you very much for helping me here! — jasdefer, Apr 28 '17 at 13:41
@Shadow The columns for the `PasswordHash`, `PhoneNumber` and some more are `varchar(255)`. I mixed them up with the `varchar(256)` for the `NormalizedEmail`. That was my mistake. I did not see that EF sets the max length to 256 per default only for the `NormalizedEmail` and `NormalizedUserName` but not for the other properties. Thanks again! — jasdefer, Apr 28 '17 at 13:48

score 3 · Accepted Answer · answered Apr 28 '17 at 14:19

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:

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));
```
Limit the length of the NormalizedEmail field to 255 in EF code using maximum length restriction.

Creating MySql database with EF Core

1 Answers1