MySQL Bugs: #89586: .Net Core2 EF MySQL having issue while changing foreign key column to nullable

Bug #89586	.Net Core2 EF MySQL having issue while changing foreign key column to nullable
Submitted:	8 Feb 2018 9:45	Modified:	13 Feb 2018 6:13
Reporter:	Harshvardhan Sharma	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.10.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	.NetCore, connector, MySQL, MySql.Data.EntityFrameworkCore

Description:

I am working on an application where I am using .Net Core 2, EF Core and MySQL as database server via Code First approach.

I have 2 tables:

    User
    Employee

User table is the main table which contains the user information and Employee table is the child table which has a column ID_User as shown below:

 public class User : BaseEntity
    {
        public int ID_User { get; set; }
        public string Name { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }

        public virtual ICollection<Employee> Employees{get;set;}
    }

 public class Employee : Entity
    {
        public int ID_Employee { get; set; }
        public string Name { get; set; }

        public int ID_User { get; set; }

        public virtual User User { get; set; }
    }

Everythihg works perfectly when I use the above mapping and I have enaugh data in both the tables.

Now, I want to make the column ID_User in Employee table as nullable

To implement this change I made folloeing change to my model:

public class Employee : Entity
    {
        public int ID_Employee { get; set; }
        public string Name { get; set; }

        public int? ID_User { get; set; }

        public virtual User User { get; set; }
    }

and in mapping file:

builder.HasOne(x=>x.User).WithMany(y=>y.Employees).HasForeignKey(z=>z.ID_User).IsRequired(false);

After running the dotnet ef migrations add empuser command it generated the following migration code:

  migrationBuilder.DropForeignKey(
            name: "FK_Employee_User_ID_User",
            table: "Employee");

        migrationBuilder.AlterColumn<int>(
            name: "ID_User",
            table: "Employee",
            nullable: true,
            oldClrType: typeof(int));

        migrationBuilder.AddForeignKey(
            name: "FK_Employee_User_ID_User",
            table: "Employee",
            column: "ID_User",
            principalTable: "User",
            principalColumn: "ID_User",
            onDelete: ReferentialAction.Restrict);

Now when I run dotnet ef database update it is giving me the following error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT FK_Employee_User_ID_User' at line 1

Please help.

Thanks

How to repeat:
create a 2 tables
User
Employee

User table contains the primary key and Employee contains a column ID_User which refer to the ID_User column of the User table.

in first case ID_User is mandatory in Employee table and will work good as expected.

But when you want to change it from mandatory to the nullable field and try to migrate it. 

The error will happen:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `FK_Employee_User_ID_User`' at line 1

Suggested fix:
Looks like the issue is with the SQL query generated against the migration. So this area can be looked into

I have also reported this issue on StackOverFlow:

https://stackoverflow.com/questions/48679013/net-core2-ef-mysql-having-issue-while-changin...

One more thing I would like share that when I use the Pomelo connecter for the same , it is working fine.

But I want to use the MySQL default connector as the project is very large and long-term, so cannot trust on any third party tool.

Hello Harshvardhan,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.

I've got almost the same problem. I want to change foreign key on delete action property from "cascade" to "restrict". I typed the required codes and added a new migration but I got { "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `FK_AspNetRoleClaims_AspNetRoles_RoleId`' at line 1" } error while updating database. The process worked with SQL Server but not worked with MySql.