How to Make EnableRetryOnFailure Retry MySQL Errors in EF Core
EnableRetryOnFailure looks like a switch, and with Oracle's MySQL provider, it isn't one: until you list error numbers, it retries nothing. Here's the list I use, how to confirm it's working, and how long a request can wait.
Prerequisites
- A .NET C# project using
MySql.EntityFrameworkCore. I used 10.0.12, with EF Core 10.0.14 and MySQL 9.7.4. Part 1 of the series sets one up.
Step 1: List the Numbers
options.UseMySQL(connectionString, mySql =>
{
mySql.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(5),
errorNumbersToAdd: [0, 1040, 1042, 1053, 1213]);
});
| Number | Error | When it happens |
|---|---|---|
1213 | Deadlock found when trying to get lock | MySQL picked your transaction to undo |
1042 | Unable to connect to any of the specified MySQL hosts | MySQL is down |
1053 | Server shutdown in progress | MySQL is stopping |
1040 | Too many connections | No free connections |
0 | The driver's own error | A dropped connection, and also a full pool |
Leave out 1205, the lock wait timeout. Each retry would wait the whole limit again.
Step 2: Check It's Working
Two requests that update the same two products in opposite orders will deadlock. The log shows the retry:
Attempt 1
Attempt 1
A transient exception occurred during execution. The operation will be retried after 0ms.
Attempt 2
{"attempts":1}
200
{"attempts":2}
200
A transient exception occurred during execution. is the line to look for. It's logged at Information, under Microsoft.EntityFrameworkCore.Infrastructure.
Step 3: Know How Long It Waits
With MySQL stopped, and 3 retries:
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.
The delays grow, with a little randomness, up to maxRetryDelay. So 3 retries cover about 4 seconds of outage. After that, you get RetryLimitExceededException.
Step 4: Know What This Doesn't Do
- It repeats operations, so transactions need a wrapper. See How to Use a Transaction with a Retrying Execution Strategy in EF Core.
- A restart can still fail some requests. A retry can pick up a broken connection and hit a
NullReferenceException. 0can't be narrowed. A full connection pool gets retried too, and that took 64 seconds to fail.
A custom execution strategy solves the last two in about 20 lines.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
errorNumbersToAdd: [] | No retries, and no warning | List them |
EnableRetryOnFailure() with nothing else | The same | List them |
Raising maxRetryCount to 50 | Requests hang for minutes | Keep it small, and answer 503 |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
No will be retried line | The number isn't listed, or the log level hides it | Check both |
does not support user-initiated transactions | A transaction outside the strategy | The fix |
| One order became two | The first attempt committed | The fix |
Conclusion
With Oracle's provider, retries start only when you list the numbers. List 1213, 1042, 1053, 1040 and 0, look for will be retried in the log, and remember that it's only safe if a repeat is safe.
Additional Resources
- Connection Resiliency — how retries work.
- Server Error Message Reference — the numbers.
