Bug #117258 Cannot Perform Database Migration using MySql.EntityFrameworkCore 9.0.0
Submitted: 21 Jan 20:36 Modified: 22 Jan 18:39
Reporter: Justin Cyr Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:9.0. OS:Any
Assigned to: Filipe Silva CPU Architecture:Any (x64)

[21 Jan 20:36] Justin Cyr
Description:
There was a change in the most recent version of EntityFramework where the database is locked during migration. Any database migration performed since updating to 9.0.0 (from 8.0.11) results in an invalid SQL error.

The MySQLHistoryRepository class uses the following SQL to attempt to acquire a lock on the database: "DECLARE @result int;EXEC @result = sp_getapplock @Resource = '__EFMigrationsLock', @LockOwner = 'Session', @LockMode = 'Exclusive';SELECT @result"

This results in a invalid SQL syntax error: "SQL Error [1064] [42000]: 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 'DECLARE @result int' at line 1
  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 'DECLARE @result int' at line 1"

I am not able to find any workaround.

How to repeat:
Perform any database migration using the "await _dbContext.Database.MigrateAsync();" method. All calls fail.
[22 Jan 18:39] Omar Chavez
Verified using MySql.EntityFrameworkCore 9.0.0 NuGet package
[23 Jan 14:23] MySQL Verification Team
This is a verified bug for the version 9.0 and higher.

Versions 8.0 and 8.4 of C/Net are not impacted.

This bug is valid for all operating systems.
[23 Jan 20:31] Omar Chavez
This affects only EFCore 9 implementation of MySql.EntityFrameworkCore, as a workaround please try with MySql.EntityFrameworkCore 8.0.11 NuGet package.