Bug #96913 column changed to "not null" when maxlength change
Submitted: 18 Sep 2019 2:21 Modified: 23 Mar 18:13
Reporter: heartice li Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version: OS:Microsoft Windows (windows 10)
Assigned to: CPU Architecture:Any

[18 Sep 2019 2:21] heartice li
Description:
when i change the maxlength attribulte of a property of one model,the migration script changed column of table from nullable to not null.

How to repeat:
In Visual Studio 2019 Ver. 16.2.3, I create a DbContext:
public class EfDb : DbContext
{
    public EfDb(DbContextOptions<EfDb> dbContextOptions) : base(dbContextOptions)
    {
    }
    public DbSet<User> Users { get; set; }
}

public class User
{
    [Key]
    public long Id { get; set; }
    [MaxLength(32)]
    public string Name { get; set; }
    [MaxLength(64)]
    public string Pass { get; set; }
    [MaxLength(16)]
    public string Tele { get; set; }
}
And I Installed Nugut package "MySql.Data.EntityFrameworkCore 8.0.17" and "Microsoft.EntityFrameworkCore.Tools 2.2.6", and enable the migration named "Init" with "Add-Migration Init".

I used the "EfDb" like this

var optionsBuilder = new DbContextOptionsBuilder<EfDb>();
optionsBuilder.UseMySQL("...");
optionsBuilder.UseLoggerFactory(new LoggerFactory());
var db = new EfDb(optionsBuilder.Options);
if(db.Database.GetPendingMigrations().Any())
{
    db.Database.Migrate();
}

And I added a test user (Name="admin",Pass="admin",Tele=null)。

Then I think the tele is too short for tele,So I Changed the model "User", Changed the MaxLengthAttribute of "Tele" from "16" to "128"

and I add a migration named "Tele" with "Add-Migration Tele".

When it run agin,an exception was thrown like this:
MySql.Data.MySqlClient.MySqlException:“Data truncated for column 'Tele' at row 1”

In the LoggerFactory,the log is like this

Debug : Opened connection to database 'ConsoleApp1' on server '127.0.0.1'. 
Debug : Beginning transaction with isolation level 'RepeatableRead'. 
Debug : Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE Users MODIFY Tele varchar(128) NOT NULL; 
Error : Failed executing DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] ALTER TABLE Users MODIFY Tele varchar(128) NOT NULL;

and I ran "Script-Migration" form nugut console window,it created script like this

ALTER TABLE Users MODIFY Tele varchar(128) NOT NULL;

INSERT INTO __EFMigrationsHistory (MigrationId, ProductVersion) VALUES ('20190906063501_Tele', '2.2.6-servicing-10079');

but when i use the sqlserver,it generated the script like this

ALTER TABLE [Users] ALTER COLUMN [Tele] nvarchar(128) NULL;

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20190906064642_Tele', N'2.2.6-servicing-10079');

GO

As you see: the migration for the change of tele's maxlength only, sqlserver is keeping the column null, but mysql is changed it to "NOT NULL". and I changed the maxlength attribute only.