efcore: SqlError number 4060 (user login failed as default database doesn't exist) shouldn't retry
SqlServerRetryingExecutionStrategy today treats a SqlError.Number == 4060 as a transient exception that can be retried. This error occurs when the login credentials are valid but the requested default database can’t be accessed (e.g. when it hasn’t been created by migrations yet, see details here). This leads to a poor developer experience in the case of an ASP.NET Core project configured with the EF Core migrations endpoint and developer exception page filter that displays an “Apply migrations” button, as the page won’t be displayed until the retry loop has been completely exhausted (minutes).
The Npgsql retry strategy treats the equivalent PostgreSQL error as fatal and doesn’t retry.
It’s possible that other 406x error numbers should be consider fatal too.
About this issue
- Original URL
- State: open
- Created 3 years ago
- Comments: 20 (20 by maintainers)
@stevendarby Yes, but I was responding to the comment about the possibility of treating 4060 differently based on whether the target was on-prem or Azure Sql. And for on-prem one can use the default
SqlServerExecutionStrategythat already only retries 4060 when the database is being created.In general, I don’t think that the developer exception page is appropriate when targeting Azure Sql. Migrations should be applied through a migrations project compiled to an exe, migrations bundle or a migrations script.
With the default retry strategy we only retry 4060 after creating the database https://github.com/dotnet/efcore/blob/4341961f4755af0b9ab5a3a2ffb7e6aa14159c10/src/EFCore.SqlServer/Storage/Internal/SqlServerDatabaseCreator.cs#L300
We also have https://github.com/dotnet/efcore/issues/30023 to add more context when deciding whether to retry.
@roji I don’t think on-prem is fundamentally different from Azure SQL. We should do the same for both. What is pragmatically different is that Azure SQL shows very clearly and painfully that the database deployment experience we have, especially for cloud native, is pretty bad. The way we should fix that is to fix it for both on-prem and Azure SQL, by having a good experience that works for both. Because the experience now for cloud is so bad, it is bringing the whole issue to the fore again, and, as we have talked about for years, the current experience from on-prem is also not good except in some narrow happy paths.
@roji
I’m pretty sure there are other error codes for things like that - see error 911 for example.
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors-0-to-999?view=sql-server-ver16
Azure SQL documentation explicitly lists 4060 as transient.
https://learn.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues?view=azuresql#list-of-transient-fault-error-codes
I’d want to understand more about how OP is coming across this; the example of migrations not being applied yet is given, but not sure if that gives the full picture?
Can the SQL team explain 4060 in more detail?
@roji Yeah, I get that. It makes sense, but @AndriySvyryd should comment.
I think one reason for this was that in SQL Server, CREATE DATABASE returns before the database is actually ready and operational, so there’s a race condition when trying to use it after creation (and the retry makes that go away (but @AndriySvyryd can confirm/provide more context). Of course if this error occurs without having just done CREATE DATABASE, it makes no sense to retry - but it’s a distinction we can’t make (we just had a discussion around how tricky a concept “transience” is… here’s yet another example).
IIRC on PostgreSQL when CREATE DATABASE returns, you’re guaranteed to be able to access the database, so it never makes sense to retry after “database doesn’t exist”.