SqlServerDatabaseCreator: Exists() check stuck in long timeout loop

[brendan-nobadthing]

SqlServerDatabaseCreator.Exists()
checks the database by attempting to open a connection and then catching the SQLException.

With my DB connection string:

• Integrated security=true
• running as a windows user with full SQL permissions
• But the database does not exist yet.

my connection attempt is throwing error code -2 Timeout instead of the expected code 4060.
This alone takes 20 seconds to occur but then the Exists function will retry 29 more times - leaving my startup waiting for a long time.
Finally, after 30 attempts, the exception is thrown.

private bool Exists(bool retryOnNotExists)
        {
            var retryCount = 0;
            while (true)
            {
                try
                {
                    _connection.Open();
                    _connection.Close();
                    return true;
                }
                catch (SqlException e)
                {
                    if (!retryOnNotExists
                        && IsDoesNotExist(e))
                    {
                        return false;
                    }

                    if (!RetryOnExistsFailure(e, ref retryCount))
                    {
                        throw;
                    }
                }
            }
        }

// Login failed is thrown when database does not exist (See Issue #776)
        private static bool IsDoesNotExist(SqlException exception) => exception.Number == 4060;

        // See Issue #985
        private bool RetryOnExistsFailure(SqlException exception, ref int retryCount)
        {
            // This is to handle the case where Open throws (Number 233):
            //   System.Data.SqlClient.SqlException: A connection was successfully established with the
            //   server, but then an error occurred during the login process. (provider: Named Pipes
            //   Provider, error: 0 - No process is on the other end of the pipe.)
            // It appears that this happens when the database has just been created but has not yet finished
            // opening or is auto-closing when using the AUTO_CLOSE option. The workaround is to flush the pool
            // for the connection and then retry the Open call.
            // Also handling (Number -2):
            //   System.Data.SqlClient.SqlException: Connection Timeout Expired.  The timeout period elapsed while
            //   attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login
            //   handshake failed or the server was unable to respond back in time.
            // And (Number 4060):
            //   System.Data.SqlClient.SqlException: Cannot open database "X" requested by the login. The
            //   login failed.
            if (((exception.Number == 233) || (exception.Number == -2) || (exception.Number == 4060))
                && (++retryCount < 30))
            {
                ClearPool();
                Thread.Sleep(100);
                return true;
            }
            return false;
        }

If I override SqlServerDatabaseCreator.Exists() to retry fewer times and then return false, I get the required behavior - the DB is considered to "not exist" and then the database was created fine with the connection string that caused a timeout.

I can see that the retry loop has a fix that clears the thread pool - so at least one retry is required for that fix to occur.

1. are we sure that all the error conditions above (233, -2, 4060) benefit from the ClearPool() call.
   Looks like this was originally added for just 233.
2. There should certainly be far fewer retries if the exception is a timeout.
3. I've experienced that "timeout" can be thrown for a "not exists" state with a connection string that eventually worked so that should eventually return false - not throw the exception

[divega]

I've experienced that "timeout" can be thrown for a "not exists" state with a connection string that eventually worked

@brendan-ssw Thanks. This is very good data. Unfortunately the high variability of behaviors in this area means it takes a long time and tweaking for us to come up with logic that works well in most cases.

BTW, sometimes I wonder if we should just attempt connecting to the target database with a very short connection time-out period as our sole way to perform database existence checks.

cc @ajcvickers

[rowanmiller]

We should improve the algorithm for cases where the server is unreachable... agreed that 15min is too long 😄