cant use my MySql database in ASP.NET Core with Identity

Question

I have created a ASP.NET Core Web with MVC. I scaffolded it with Identity and added my default layout page and used the login page. Now I have ran into the problem that when I log in it doesnt find the column "username" and "password" I belive. Im not quite sure and would appriciate all the help I can get :). This is the error that my website puts out:

MySqlException: Unknown column 'u.AccessFailedCount' in 'field list'

and the red part in the error page is:

var result = await _userManager.CreateAsync(user, Input.Password);

Thanks beforehand!

UPDATE:

Here is some code, I get a lot of "random" values that is not included in my ApplicationUser for some reason.

My ApplicationUser:

public class ApplicationUser : IdentityUser
    {
        public string Username { get; set; }
        public string Password { get; set; }
    }

My AuthDbContext:

public class AuthDbContext : IdentityDbContext<ApplicationUser>
    {
        public AuthDbContext(DbContextOptions<AuthDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.Entity<ApplicationUser>(entity =>
            {
                entity.ToTable(name: "users");
            });
            
            // Customize the ASP.NET Identity model and override the defaults if needed.
            // For example, you can rename the ASP.NET Identity table names and more.
            // Add your customizations after calling base.OnModelCreating(builder);
        }
    }

The migrationfile that gets all the random values, I only want to add a table named "users" with Id NOT NULL AUTO_INCREMENT PRIMARY KEY, Username VARCHAR(40) and Password VARCHAR(40). But I get soo many other columns that I dont know where they are comming from.

public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "AspNetRoles",
                columns: table => new
                {
                    Id = table.Column<string>(type: "varchar(767)", nullable: false),
                    Name = table.Column<string>(type: "varchar(256)", maxLength: 256, nullable: true),
                    NormalizedName = table.Column<string>(type: "varchar(256)", maxLength: 256, nullable: true),
                    ConcurrencyStamp = table.Column<string>(type: "text", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetRoles", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "users",
                columns: table => new
                {
                    Id = table.Column<string>(type: "varchar(767)", nullable: false),
                    Username = table.Column<string>(type: "text", nullable: true),
                    Password = table.Column<string>(type: "text", nullable: true),
                    UserName = table.Column<string>(type: "varchar(256)", maxLength: 256, nullable: true),
                    NormalizedUserName = table.Column<string>(type: "varchar(256)", maxLength: 256, nullable: true),
                    Email = table.Column<string>(type: "varchar(256)", maxLength: 256, nullable: true),
                    NormalizedEmail = table.Column<string>(type: "varchar(256)", maxLength: 256, nullable: true),
                    EmailConfirmed = table.Column<bool>(type: "tinyint(1)", nullable: false),
                    PasswordHash = table.Column<string>(type: "text", nullable: true),
                    SecurityStamp = table.Column<string>(type: "text", nullable: true),
                    ConcurrencyStamp = table.Column<string>(type: "text", nullable: true),
                    PhoneNumber = table.Column<string>(type: "text", nullable: true),
                    PhoneNumberConfirmed = table.Column<bool>(type: "tinyint(1)", nullable: false),
                    TwoFactorEnabled = table.Column<bool>(type: "tinyint(1)", nullable: false),
                    LockoutEnd = table.Column<DateTimeOffset>(type: "timestamp", nullable: true),
                    LockoutEnabled = table.Column<bool>(type: "tinyint(1)", nullable: false),
                    AccessFailedCount = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_users", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "AspNetRoleClaims",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("MySQL:ValueGenerationStrategy", MySQLValueGenerationStrategy.IdentityColumn),
                    RoleId = table.Column<string>(type: "varchar(767)", nullable: false),
                    ClaimType = table.Column<string>(type: "text", nullable: true),
                    ClaimValue = table.Column<string>(type: "text", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetRoleClaims", x => x.Id);
                    table.ForeignKey(
                        name: "FK_AspNetRoleClaims_AspNetRoles_RoleId",
                        column: x => x.RoleId,
                        principalTable: "AspNetRoles",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "AspNetUserClaims",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("MySQL:ValueGenerationStrategy", MySQLValueGenerationStrategy.IdentityColumn),
                    UserId = table.Column<string>(type: "varchar(767)", nullable: false),
                    ClaimType = table.Column<string>(type: "text", nullable: true),
                    ClaimValue = table.Column<string>(type: "text", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetUserClaims", x => x.Id);
                    table.ForeignKey(
                        name: "FK_AspNetUserClaims_users_UserId",
                        column: x => x.UserId,
                        principalTable: "users",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "AspNetUserLogins",
                columns: table => new
                {
                    LoginProvider = table.Column<string>(type: "varchar(128)", maxLength: 128, nullable: false),
                    ProviderKey = table.Column<string>(type: "varchar(128)", maxLength: 128, nullable: false),
                    ProviderDisplayName = table.Column<string>(type: "text", nullable: true),
                    UserId = table.Column<string>(type: "varchar(767)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetUserLogins", x => new { x.LoginProvider, x.ProviderKey });
                    table.ForeignKey(
                        name: "FK_AspNetUserLogins_users_UserId",
                        column: x => x.UserId,
                        principalTable: "users",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "AspNetUserRoles",
                columns: table => new
                {
                    UserId = table.Column<string>(type: "varchar(767)", nullable: false),
                    RoleId = table.Column<string>(type: "varchar(767)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetUserRoles", x => new { x.UserId, x.RoleId });
                    table.ForeignKey(
                        name: "FK_AspNetUserRoles_AspNetRoles_RoleId",
                        column: x => x.RoleId,
                        principalTable: "AspNetRoles",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_AspNetUserRoles_users_UserId",
                        column: x => x.UserId,
                        principalTable: "users",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateTable(
                name: "AspNetUserTokens",
                columns: table => new
                {
                    UserId = table.Column<string>(type: "varchar(767)", nullable: false),
                    LoginProvider = table.Column<string>(type: "varchar(128)", maxLength: 128, nullable: false),
                    Name = table.Column<string>(type: "varchar(128)", maxLength: 128, nullable: false),
                    Value = table.Column<string>(type: "text", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AspNetUserTokens", x => new { x.UserId, x.LoginProvider, x.Name });
                    table.ForeignKey(
                        name: "FK_AspNetUserTokens_users_UserId",
                        column: x => x.UserId,
                        principalTable: "users",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_AspNetRoleClaims_RoleId",
                table: "AspNetRoleClaims",
                column: "RoleId");

            migrationBuilder.CreateIndex(
                name: "RoleNameIndex",
                table: "AspNetRoles",
                column: "NormalizedName",
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_AspNetUserClaims_UserId",
                table: "AspNetUserClaims",
                column: "UserId");

            migrationBuilder.CreateIndex(
                name: "IX_AspNetUserLogins_UserId",
                table: "AspNetUserLogins",
                column: "UserId");

            migrationBuilder.CreateIndex(
                name: "IX_AspNetUserRoles_RoleId",
                table: "AspNetUserRoles",
                column: "RoleId");

            migrationBuilder.CreateIndex(
                name: "EmailIndex",
                table: "users",
                column: "NormalizedEmail");

            migrationBuilder.CreateIndex(
                name: "UserNameIndex",
                table: "users",
                column: "NormalizedUserName",
                unique: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "AspNetRoleClaims");

            migrationBuilder.DropTable(
                name: "AspNetUserClaims");

            migrationBuilder.DropTable(
                name: "AspNetUserLogins");

            migrationBuilder.DropTable(
                name: "AspNetUserRoles");

            migrationBuilder.DropTable(
                name: "AspNetUserTokens");

            migrationBuilder.DropTable(
                name: "AspNetRoles");

            migrationBuilder.DropTable(
                name: "users");
        }
    }

NOTE: I just migrated and it all seems to kinda work. But I only want my users to be able to login, I want to create all users backend via MySql CLI. But I have all those weard values that I dont know what to put in..

Have you performed a migration yet? – Neil Mar 23 '21 at 14:54 — Neil, Mar 23 '21 at 14:54
I have not, how do I do that? – MaxK123 Mar 23 '21 at 15:01 — MaxK123, Mar 23 '21 at 15:01

mamen · Accepted Answer · 2021-03-23T16:30:25.180

4

If your entity contains the property AccessFailedCount and the column is indeed missing in the database, you might have forgotten to create a new migration.

See the official documentation on how work with migrations for more information.

Or maybe you forgot to configure your model add the column to your OnModelCreating-Method in your DbContext. Please check this link for more information on how to configure the database-model.

UPDATE: Those extra-columns are generated by the Identity-server and should not be removed. Just apply the migration to the database and everything should work.

edited Mar 23 '21 at 16:30

answered Mar 23 '21 at 14:58

mamen

1,202
6
26

I dont have a AccessFailedCount column in my table, should I try to add one? – MaxK123 Mar 23 '21 at 15:02
1

Is there a `AccessFailedCount`-Property on your Entity? If so, then you have to create and apply a migration to the database to add the column. See my first link to find out, how migrations work. – mamen Mar 23 '21 at 15:04
I tried to migrate but I see that it adds email, and phone number and a lot of things. How do I make it only add a id, username and a password? – MaxK123 Mar 23 '21 at 15:08
1

You have to edit your User-Entity to contain only the columns you want to have in your database or mark them with the `[NotMapped]`-Property. See [this answer](https://stackoverflow.com/questions/51982019/exclude-properties-for-migrations) for more information on how to do that. – mamen Mar 23 '21 at 15:10
In my ApplicationUser that I belive is the User-Entity I only have two strings, Password and Username. Where does it get all those other values from? – MaxK123 Mar 23 '21 at 15:14
I can see in the InitialCreate.cs has a lot of diffrent values than I have in my model. Where do they come from? – MaxK123 Mar 23 '21 at 15:19
1

Can you please show more of your code? In particular, what type of entity does the `_userManager` manage? The entity should should have the `AccessFailedCount`-property. – mamen Mar 23 '21 at 15:25
1

Those "extra" columns you have in your user-table are generated by the Identity-Server and are needed for it to work. Just add the migration with your `AccessFailedCount`-Property and everything should work as expected. – mamen Mar 23 '21 at 16:27
I migrated and it seems to work, my plan was to only be able to create a user via the database, I dont want my users to be able to create new accounts. And now the users table has 15 rows that I have no idea what to put into. As the plan was in the begining to only have a username, id and a password. – MaxK123 Mar 23 '21 at 18:03

cant use my MySql database in ASP.NET Core with Identity

1 Answers1