MySQL Bugs: #86199: Connection invalid when commiting transaction with EntityFrameworkCore

Bug #86199	Connection invalid when commiting transaction with EntityFrameworkCore
Submitted:	5 May 2017 18:38	Modified:	11 Sep 2017 18:57
Reporter:	Maxime Beaudry	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	MySql.Data.EntityFrameworkCore 7.0.7-m61	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using MySql.Data.EntityFrameworkCore version 7.0.7-m61, when I try to commit a transaction where a FirstOrDefaultAsync call was made, the call to Commit throws the following exception:

System.InvalidOperationException: Connection must be valid and open to commit transaction

How to repeat:
1) Create database:
-------------------

DROP SCHEMA bugdemo;
CREATE SCHEMA bugdemo;
USE bugdemo;

CREATE TABLE Demos (
  `Key`						INT NOT NULL AUTO_INCREMENT,
  Id						INT,
   PRIMARY KEY (`Key`)
);

INSERT INTO Demos (Id) VALUES (1),(2),(3),(5),(8);

SELECT * FROM Demos;
SELECT * FROM Demos WHERE Id IN (2, 5, 200);

2) Execute code:
----------------

        private static async Task AsyncCode(Context context)
        {
            using (var transaction = context.Database.BeginTransaction())
            {
                var row = await context.Demos.FirstOrDefaultAsync(x => x.Key == 666);

                if (row == null)
                {
                    row = new Demo { Key = 666 };
                }

                context.Demos.Add(row);

                context.SaveChanges();

                transaction.Commit();
            }
        }

==> Note that if I replace FirstOrDefaultAsync by FirstOrDefault, it does not fail and my data is properly saved.

Hello  Maxime Beaudry,

Thank you for the bug report.
Could you please provide repeatable test case (exact steps/sample project, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

I added the code to reproduce the problem here: https://github.com/mabead/Bug.MySqlEntityFrameworkCoreConnectionInvalid

Simply compile it and run in VS2017 and you will have the following exception:

Unhandled Exception: System.AggregateException: One or more errors occurred. (Connection must be valid and open to commit transaction) ---> System.InvalidOperationException: Connection must be valid and open to commit transaction
   at MySql.Data.MySqlClient.MySqlTransaction.Commit()
   at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
   at MyConsole.Program.<AsyncCode>d__0.MoveNext() in C:\git\mabead\Bug.MySqlEntityFrameworkCoreConnectionInvalid\Console\Program.cs:line 42
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.Wait()
   at MyConsole.Program.Main(String[] args) in C:\git\mabead\Bug.MySqlEntityFrameworkCoreConnectionInvalid\Console\Program.cs:line 53

Note that if you replace the FirstOrDefaultAsync by FirstOrDefault, there is no crash.

Hello  Maxime Beaudry,

Thank you for the feedback.
Verified this behavior on Visual Studio 2015 (C#.Net) and Connector/NET 7.0.7-m61 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: 86199.png (image/png, text), 109.75 KiB.

Any update?

Posted by developer:
 
Hi,

This bug was fixed in version 6.10.3

Please try 6.10.3 version and let us know if it works.

Thank you,
Roberto

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.9 release, and here's the changelog entry:

When attempting to commit a transaction in which the FirstOrDefaultAsync
method was called, the connector returned
System.InvalidOperationException: Connection must be valid and open to
commit transaction, instead of committing the transaction.

Thank you for the bug report.