ASP.NET MVC MySQL数据库迁移至Plesk遇主键过长异常求助

Let's work through this issue together—this is a frequent pain point when pairing ASP.NET Identity with MySQL in Plesk environments, where default database settings often enforce strict index length limits. The root cause is that MySQL's InnoDB engine has a default maximum index length of 767 bytes, and ASP.NET Identity's default string fields (like UserName or Role.Name) use varchar(256). If your database uses utf8mb4 (which supports emojis and full Unicode), each character takes 4 bytes—256*4=1024 bytes, which exceeds the 767 limit.

Step 1: Update Your DbContext Configuration (Fix Index Lengths + Enable Auto-Increment Keys) #

Your current configuration is close, but we need to cover all Identity-related tables and switch to integer auto-increment keys to eliminate the length issue entirely. Here's the revised code:

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class AppIdentityDbContext : IdentityDbContext<ApplicationUser, IdentityRole<int>, int,
    IdentityUserClaim<int>, IdentityUserRole<int>, IdentityUserLogin<int>,
    IdentityRoleClaim<int>, IdentityUserToken<int>>
{
    public AppIdentityDbContext() : base("myConnection") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Map Identity tables to standard names (optional but cleaner)
        modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers");
        modelBuilder.Entity<IdentityRole<int>>().ToTable("AspNetRoles");
        modelBuilder.Entity<IdentityUserRole<int>>().ToTable("AspNetUserRoles");
        modelBuilder.Entity<IdentityUserClaim<int>>().ToTable("AspNetUserClaims");
        modelBuilder.Entity<IdentityUserLogin<int>>().ToTable("AspNetUserLogins");
        modelBuilder.Entity<IdentityRoleClaim<int>>().ToTable("AspNetRoleClaims");
        modelBuilder.Entity<IdentityUserToken<int>>().ToTable("AspNetUserTokens");

        // Limit string field lengths to fit within 767 bytes (191 chars for utf8mb4)
        modelBuilder.Entity<ApplicationUser>()
            .Property(u => u.UserName)
            .HasMaxLength(191)
            .IsRequired();
        modelBuilder.Entity<ApplicationUser>()
            .Property(u => u.Email)
            .HasMaxLength(191)
            .IsRequired(false);

        modelBuilder.Entity<IdentityRole<int>>()
            .Property(r => r.Name)
            .HasMaxLength(191)
            .IsRequired();

        // Explicitly set auto-increment for integer primary keys
        modelBuilder.Entity<ApplicationUser>()
            .Property(u => u.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        modelBuilder.Entity<IdentityRole<int>>()
            .Property(r => r.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

// Update your ApplicationUser to use integer primary keys
public class ApplicationUser : IdentityUser<int>
{
    // Add your custom user properties here
}

Key Changes: #

• Switched to IdentityUser<int> and IdentityRole<int> to use auto-incrementing integer primary keys instead of GUID strings—this completely avoids the index length problem.
• Capped string fields at 191 characters (191*4=764 bytes, which fits under the 767 limit for utf8mb4).
• Explicitly mapped all Identity tables to their standard names for clarity.

Step 2: Adjust MySQL Database Settings in Plesk #

1. Log into Plesk and go to Databases > Select your project's database > Database Server.
2. Check character set and collation:
   • If you need emoji support, keep utf8mb4_unicode_ci.
   • If not, switch to utf8_unicode_ci (each character takes 3 bytes, so 255*3=765 bytes fits under the limit).
3. If you need utf8mb4 and longer fields, enable large index support:
   • Go to Tools & Settings > Database Servers > Click the wrench icon for MySQL > Open my.cnf/my.ini.
   • Add these lines under [mysqld]:

     innodb_large_prefix = ON
     innodb_file_format = Barracuda
     innodb_file_per_table = ON
     

   • Restart the MySQL server from Plesk's interface.

Step 3: Re-Run Migrations in Plesk #

1. Republish your updated project to Plesk to apply the new DbContext code.
2. Open Websites & Domains > Your domain > Terminal (or use SSH if available).
3. Navigate to your project's root directory (usually httpdocs).
4. Run the migration command:

   # Using Package Manager Console
   Update-Database -Verbose
   
   # Or .NET CLI alternative
   dotnet ef database update
   

   • Ensure your database user has ALTER and CREATE permissions (check in Plesk's Database > User Management).

Step 4: Verify the Fix #

After running the migration:

• Use Plesk's phpMyAdmin to confirm all AspNet* tables are created.
• Check that AspNetUsers.Id and AspNetRoles.Id are auto-incrementing integer fields.
• Test creating a user or role to confirm no errors occur.

内容的提问来源于stack exchange，提问作者Engr Umair