Open Closed

Migrator tool fails with timeout exception on Azure SQL Database #11089


0
ricavir created

Abp 6.6.2 Angular .NET 5

Hi,

I'm getting a timeout exception when trying to migrate our Azure prod database. We never had this exception before for more than 100 migrations in the past years. This exception is only happening on production. We notice a DB consumption spike on azure every time we run the migrator : therefore, we upgraded our plan from 20DTU to 50DTU. Still same error. We have tried to add a connection timeout of 900s in the connection string but it also fails.

Changing the connection string to local DB works correctly.

Here is the exception :

2022-05-31 07:40:18 | Host database: Server=tcp:xxx.database.windows.net,1433;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx;
2022-05-31 07:40:25 | Continue to migration for this host database and all tenants..? (Y/N):
Y
2022-05-31 07:40:37 | HOST database migration started...
2022-05-31 07:41:37 | An error occured during migration of host database:
2022-05-31 07:41:37 | Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Dépassement du délai d'attente.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at Abp.Zero.EntityFrameworkCore.AbpZeroDbMigrator`1.CreateOrMigrate(AbpTenantBase tenant, Action`1 seedAction)
   at Abp.Zero.EntityFrameworkCore.AbpZeroDbMigrator`1.CreateOrMigrateForHost(Action`1 seedAction)
   at App.Migrator.MultiTenantMigrateExecuter.Run(Boolean skipConnVerification) in C:\Users\User\source\repos\App_AspnetZero\aspnet-core\src\App.Migrator\MultiTenantMigrateExecuter.cs:line 62
ClientConnectionId:f8435bcc-7872-4566-aac5-57cf9941e750
Error Number:-2,State:0,Class:11
2022-05-31 07:41:37 | Canceled migrations.
Press ENTER to exit...

Our production code is currently blocked because of this exception. Any help would be much apreciated :)


5 Answer(s)
  • 0
    ismcagdas created
    Support Team

    Hi @ricavir

    If the connection string is correct, the machine you are running the Migrator might not have access to your Azure SQL Database. You might need to add IP address of the machine to your Azure firewall. Could you check this ?

  • 0
    ricavir created

    Hi @ismcagdas,

    Thank you for your fast answer. I don't think it is a firewall issue as I'm still able to connect with SSMS. Just checked right now without any issue. The latest successful migration has been done 20 days ago with the same machine and same IP address. The migrations that are pending are really simple : just addind few properties to existing tables.

    Is it possible that the migration process consumes too much resource to such a point that process cannot ends before a timeout ? Could this be linked with a large amount of data in a table ?

  • 0
    ismcagdas created
    Support Team

    Hi,

    Could this be linked with a large amount of data in a table ?

    Yes, this might be a reason as well. In that case, there must be a way to increase timeout on Azure side.

  • 0
    ricavir created

    Hum, I think that the fact of reaching maximum DTU during the migration is causing an abort of the process. I will try to switch to a premium tier with higher DTU count tonight. I will give you a feedback afterwords

  • 0
    ricavir created

    I confirm that the issue was coming from Azure SQL database DTU count. I upgraded DTU to a significant amount (from 20 to 200 DTU's). This allowed to run the migrations correctly wihtout timeout. Then, back to original DTU count (20) and everything is working fine.

    I close this issue