Error reading asp.net membership tables in MySQL

Question

I have an asp.net site that uses mysql for membership. The web application works well when I connect to mysql hosted on Windows, but it throws an error due to the table name (case sensitivity) on Linux.

The error does not apply to my tables, but it is related to the internal classes of the MySQL adapter when called to authenticate the user.

Here is the error:

Table < dbname.AspNetUsers' does not exist

Description: An unhandled exception occurred during the execution of the current web request. View the stack trace for more information about the error and its occurrence in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Table "dbname.AspNetUsers" does not exist

Given, I have a lowercase aspnetusers table in my database. The error occurs only when I change the host from mySQL to Windows on Linux.

Here is a line of code that throws an exception:

public static ApplicationSignInManager Create(IdentityFactoryOptions<ApplicationSignInManager> options, IOwinContext context) { return new ApplicationSignInManager(context.GetUserManager<ApplicationUserManager>(), context.Authentication); } 

Also note: I do not have membership tables in the asp.net web application, since I use configs for (membership, role provider) to connect and authenticate users.

Any help is appreciated.

Answer 1

I found that you can rearrange the table names for the identity provider and asp.net membership by overriding the OnModelCreation method in IdentityModels .cs.

Here is the solution:

  protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<ApplicationUser>().ToTable("aspnetusers"); modelBuilder.Entity<IdentityRole>().ToTable("aspnetroles"); modelBuilder.Entity<IdentityUserRole>().ToTable("aspnetuserroles"); modelBuilder.Entity<IdentityUserClaim>().ToTable("aspnetuserclaims"); modelBuilder.Entity<IdentityUserLogin>().ToTable("aspnetuserlogins"); } 

You noticed that you explicitly set all the standard asp.net membership table names in lower case, as I have this in my database, which caused the problem.

Now I can connect, authenticate and manage membership from my existing web application connected to a Linux-based MySQL database.

Hope this helps.