Fixed: EF Core EnableRetryOnFailure Doesn't Retry MySQL Errors with Oracle's MySql.EntityFrameworkCore
I had EnableRetryOnFailure turned on, so I assumed a deadlock or a short outage would be retried. With Oracle's MySQL provider, nothing was retried, ever, and no message says so. The provider asks each error whether it's temporary, and MySqlException always answers no. The fix is one argument.
What I Was Doing
My .NET C# Web API uses EF Core 10.0.14 and MySql.EntityFrameworkCore 10.0.12, with retries turned on like this:
mySql.EnableRetryOnFailure(
maxRetryCount: database.MaxRetryCount,
maxRetryDelay: TimeSpan.FromSeconds(database.MaxRetryDelaySeconds),
errorNumbersToAdd: []);
Two requests updated the same two products in opposite orders, which is a real deadlock. Here's one of them, inside CreateExecutionStrategy().ExecuteAsync:
await strategy.ExecuteAsync(
async ct =>
{
attempt++;
log.LogInformation("Attempt {Attempt}", attempt);
await using var transaction = await db.Database.BeginTransactionAsync(ct);
await db.Products.Where(p => p.Id == a)
.ExecuteUpdateAsync(s => s.SetProperty(p => p.ReorderLevel, p => p.ReorderLevel + 1), ct);
await Task.Delay(500, ct);
await db.Products.Where(p => p.Id == b)
.ExecuteUpdateAsync(s => s.SetProperty(p => p.ReorderLevel, p => p.ReorderLevel + 1), ct);
await transaction.CommitAsync(ct);
},
cancellationToken);
What I Expected
One request would hit a deadlock, and EF Core would run it again.
What I Got
a 500
b 200
Attempt 1
Attempt 1
MySql.Data.MySqlClient.MySqlException (0x80004005): Deadlock found when trying to get lock; try restarting transaction
One attempt each, no retry, and a 500. Stopping MySQL gave the same result: the request failed in under a tenth of a second, with no retry at all:
500 0.047864s
What I Checked
What the error says about itself. .NET's DbException.IsTransient "Indicates whether the error represented by this DbException could be a transient error, i.e. if retrying the triggering operation may succeed without any other change." So I had an endpoint catch the exception and report it, first for a real deadlock, then with MySQL stopped:
[{"type":"MySqlException","number":1213,"isTransient":false,"message":"Deadlock found when trying to get lock; try restarting transaction"}]
[{"type":"MySqlException","number":1042,"isTransient":false,"message":"Unable to connect to any of the specified MySQL hosts"},{"type":"SocketException","number":null,"isTransient":null,"message":"Connection refused"}]
isTransient is false for both, and these are the two errors you'd most want retried.
The provider's source. It's on GitHub, and it's short:
public static bool ShouldRetryOn([NotNull] Exception ex)
=> ex is MySqlException mySqlException
? mySqlException.IsTransient
: ex is TimeoutException;
And the MySqlException class never overrides IsTransient, so it keeps the base class's false for every error. The retrying strategy does look at one more thing:
protected override bool ShouldRetryOn(Exception exception)
=> exception is MySqlException mySqlException &&
_additionalErrorNumbers?.Contains(mySqlException.Number) == true
|| MySQLTransientExceptionDetector.ShouldRetryOn(exception);
That's errorNumbersToAdd.
Why It Happens
EF Core's connection resiliency page says providers "can supply execution strategies tailored to their specific database failure conditions and optimal retry policies." With this provider, the part that knows which failures are temporary always says "not this one". So the only errors retried are the ones you name yourself, and an empty list names none.
The Fix
List the error numbers you want retried:
mySql.EnableRetryOnFailure(
maxRetryCount: database.MaxRetryCount,
maxRetryDelay: TimeSpan.FromSeconds(database.MaxRetryDelaySeconds),
errorNumbersToAdd: [1213, 1042]);
| Number | Error |
|---|---|
1213 | Deadlock found when trying to get lock |
1042 | Unable to connect to any of the specified MySQL hosts |
1053 | Server shutdown in progress |
1040 | Too many connections |
0 | MySql.Data's number for a dropped connection, and for a full connection pool |
During a restart, this list still lets some requests fail with a NullReferenceException, and it can't tell the two meanings of 0 apart. For both, a small custom execution strategy does the same job and more.
After the Fix
{"attempts":1}
200
{"attempts":2}
200
Attempt 1
Attempt 1
A transient exception occurred during execution. The operation will be retried after 0ms.
Attempt 2
Both answered 200, and one took two attempts. With MySQL stopped, it now tries for a while before giving up:
500 4.734215s
A transient exception occurred during execution. The operation will be retried after 0ms.
A transient exception occurred during execution. The operation will be retried after 1045ms.
A transient exception occurred during execution. The operation will be retried after 3124ms.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
Assuming EnableRetryOnFailure() is enough | Nothing is retried | List the numbers |
| Checking only that the code compiles | A wrong list is silent | Look for will be retried in the log |
Adding 1205 (lock wait timeout) | Every retry waits the whole limit again | Answer 503 instead |
Reusing a scoped DbContext in a retry | A row can be saved twice | Call ChangeTracker.Clear() first |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
No will be retried line in the log | The error's number isn't listed, or the log level hides it | Check both. It's Information, in Microsoft.EntityFrameworkCore.Infrastructure |
The maximum number of retries (3) was exceeded | It retried, and kept failing | The fix |
does not support user-initiated transactions | A transaction outside the strategy | The fix |
FAQ
Will this stay true in later versions?
I checked MySql.Data 26.10.0 and MySql.EntityFrameworkCore 10.0.12. Check again after an upgrade: look for the will be retried line while something fails.
Conclusion
With Oracle's provider, EnableRetryOnFailure retries no MySQL error by itself, because MySqlException.IsTransient is always false. Pass the numbers in errorNumbersToAdd, and check the log to see it working.
Additional Resources
- Connection Resiliency — how execution strategies work.
- MySQLTransientExceptionDetector.cs — the provider's check.
