Siddharth Shanker Mishra

MySQL is widely used database around the world. Many asp.net based applications also use this database as their preferred backend database. If using entity framework, this database has some good support, but it is not as seamless as MS SQL migrations. When using the official MySQL nuget packages from Oracle, still we get many migrations issue.

Here I am listing the common EF migration errors with MySQL.

Error: Error in SQL Syntax near ‘CONSTRAINT’

Some time when change some properties in the entity class and then run the migration command, we may see below error:

Failed executing DbCommand (5ms) [Parameters=[], CommandType=’Text’, CommandTimeout=’30’]
ALTER TABLE TableOne DROP CONSTRAINT FK_TableOne_TableTwo_ColumnName;
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_TableOne_TableTwo_ColumnName’ at line 1

The issue here is the generated SQL command by Entity Framework. This command ‘ALTER TABLE TableOne DROP CONSTRAINT FK_TableOne_TableTwo_ColumnName;’ is valid for SQL server and oracle database, but in MySQL it will not work. MySQL drop foreign key statement have different syntax. Here is the link for MySQL command to drop foreign key:

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#:~:text=the%20foreign%20key.-,Dropping%20Foreign%20Key%20Constraints,drop%20the%20foreign%20key%20constraint.

The correct syntax for this is:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

So, to fix this error, we need to change the generated C# migration file. So instead of this:

migrationBuilder.DropForeignKey(
name: “FK_TableOne_TableTwo_ColumnName”,
table: “TableOne”);

Change this code to this:

migrationBuilder.Sql(“ALTER TABLE `TableOne` DROP FOREIGN KEY FK_TableOne_TableTwo_ColumnName;”);

Easy fix.

I will add more errors here related to asp.net and MySQL migration in future.