AuditLog table migration fail while seeding data on MySql (MariaDB 10.5) #678
Description
Describe the bug
Can't use Skoruba.IdentityServer4.Admin to seed data on empty database. It has migration fail.
To Reproduce
- Host a empty MariaDB 10.5 on localhost.
- Editing
src\Skoruba.IdentityServer4.Admin\appsettings.json- ConnectionStrings replace by
server=localhost;database=IdentityServer4Admin;user=root;password=123456;treattinyasboolean=true - DatabaseProviderConfiguration.ProviderType replace by
MySql
- ConnectionStrings replace by
- Use VisualStudio run Skoruba.IdentityServer4.Admin
- Migration fail happend
Relevant parts of the log file
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.6 initialized 'IdentityServerConfigurationDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=Skoruba.IdentityServer4.Admin.EntityFramework.MySql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.6 initialized 'IdentityServerPersistedGrantDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=Skoruba.IdentityServer4.Admin.EntityFramework.MySql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.6 initialized 'AdminIdentityDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=Skoruba.IdentityServer4.Admin.EntityFramework.MySql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.6 initialized 'AdminLogDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=Skoruba.IdentityServer4.Admin.EntityFramework.MySql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.6 initialized 'AdminAuditLogDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=Skoruba.IdentityServer4.Admin.EntityFramework.MySql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 3.1.6 initialized 'IdentityServerDataProtectionDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MigrationsAssembly=Skoruba.IdentityServer4.Admin.EntityFramework.MySql
[18:06:28 ERR] Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE `AuditLog` MODIFY COLUMN `Id` bigint NOT NULL AUTO_INCREMENT;
[18:06:28 FTL] Host terminated unexpectedly
MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect table definition; there can be only one auto column and it must be defined as a key
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect table definition; there can be only one auto column and it must be defined as a key
at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 775
...
My workaround
I found it can be fix by editing
src\Skoruba.IdentityServer4.Admin.EntityFramework.MySql\Migrations\AuditLogging\20200419131142_ChangeAuditLogToLong.cs
...
protected override void Up(MigrationBuilder migrationBuilder)
{
//migrationBuilder.DropPrimaryKey("PK_AuditLog", "AuditLog"); <---comment this line
migrationBuilder.AlterColumn<long>(
name: "Id",
table: "AuditLog",
nullable: false,
oldClrType: typeof(int),
oldType: "int")
.Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn)
.OldAnnotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);
//migrationBuilder.AddPrimaryKey("PK_AuditLog", "AuditLog", "Id"); <---comment this line
}
...