Error adding integer auto-increment column with migration (since 8.0.1)

[atsidaev]

Steps to reproduce

Here is the minimal reproducible example. Use an empty database and the following data context:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
public class ApplicationDbContext : DbContext
{
	public DbSet<User> Users { get; set; }
	protected override void OnConfiguring(DbContextOptionsBuilder modelBuilder)
	{
		var connectionString = "..."; 
		modelBuilder.UseMySql(
			connectionString,
			ServerVersion.AutoDetect(connectionString));
	}
}
public class User
{
	[Key]
	[Required, MaxLength(32)]
	public string Name { get; set; }
}

Create initial migration

dotnet ef migrations add Migration1

Change User class this way (i.e. remove an old key and add new auto-incremented integer key field)

public class User
{
	[Required, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int Id { get; set; }
	[Required, MaxLength(32)]
	public string Name { get; set; }
}

Add migration:

dotnet ef migrations add Migration2

Now attempt to create the table in the database:

dotnet ef database update

An error is occurred. This happens starting from 8.0.1 version of Pomelo.EntityFrameworkCore.MySql. 8.0.0 works as expected.

The issue

This is the log with the exception:

Applying migration '20240425143650_Migration1'.
Applying migration '20240425143716_Migration2'.
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE `Users` ADD `Id` int NOT NULL DEFAULT 0 AUTO_INCREMENT,
ADD CONSTRAINT `PK_Users` PRIMARY KEY (`Id`);
MySqlConnector.MySqlException (0x80004005): Invalid default value for 'Id'
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 894
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 37
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
   at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 309
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 108
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Invalid default value for 'Id'

I compared dotnet ef migrations script result for versions 8.0.0 and 8.0.2 of Pomelo.EntityFrameworkCore.MySql, and I see that for 8.0.0 there was two-step procedure. AUTO_INCREMENT was not set directly in the ALTER TABLE call, instead of this the POMELO_AFTER_ADD_PRIMARY_KEY procedure was used with some auto-detection logic for identity columns inside.

ALTER TABLE `Users` ADD `Id` int NOT NULL DEFAULT 0;

ALTER TABLE `Users` ADD CONSTRAINT `PK_Users` PRIMARY KEY (`Id`);
CALL POMELO_AFTER_ADD_PRIMARY_KEY(NULL, 'Users', 'Id');

And for 8.0.2, despite the POMELO_AFTER_ADD_PRIMARY_KEY procedure still exists in the migration script, it is not called. The column is created directly with DEFAULT 0 AUTO_INCREMENT combination of params, which cannot be accepted by MySQL Server for some reason.

ALTER TABLE `Users` ADD `Id` int NOT NULL DEFAULT 0 AUTO_INCREMENT,
ADD CONSTRAINT `PK_Users` PRIMARY KEY (`Id`);

Further technical details

MySQL version: 8.0.29
Operating system: Microsoft Windows 10 Pro
Pomelo.EntityFrameworkCore.MySql version: 8.0.2
Microsoft.AspNetCore.App version: