Open Closed

Running migration for apb 8.0.2 times out the database when altering AbpAuditLogs #6835


User avatar
0
Sturla created

I´m updating my system (dev) to abp.io 8.0.2 where altering the AbpAuditLogs table causes the error here below.

It only had 155.174 rows.

The migration works if I delete everything from the table.

Question: How can I run this migration without timeout? I don´t want to delete all my auditlogs on production where I have 422.016 rows.

2024-03-10T16:27:33.2500677Z [16:27:33 INF] Migrating schema for host database...
2024-03-10T16:29:04.9441951Z [16:29:04 ERR] Failed executing DbCommand (30,021ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2024-03-10T16:29:04.9442707Z DECLARE @var96 sysname;
2024-03-10T16:29:04.9443161Z SELECT @var96 = [d].[name]
2024-03-10T16:29:04.9443548Z FROM [sys].[default_constraints] [d]
2024-03-10T16:29:04.9444163Z INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
2024-03-10T16:29:04.9444655Z WHERE ([d].[parent_object_id] = OBJECT_ID(N'[AbpAuditLogs]') AND [c].[name] = N'ExtraProperties');
2024-03-10T16:29:04.9445171Z IF @var96 IS NOT NULL EXEC(N'ALTER TABLE [AbpAuditLogs] DROP CONSTRAINT [' + @var96 + '];');
2024-03-10T16:29:04.9445624Z UPDATE [AbpAuditLogs] SET [ExtraProperties] = N'' WHERE [ExtraProperties] IS NULL;
2024-03-10T16:29:04.9446098Z ALTER TABLE [AbpAuditLogs] ALTER COLUMN [ExtraProperties] nvarchar(max) NOT NULL;
2024-03-10T16:29:04.9446562Z ALTER TABLE [AbpAuditLogs] ADD DEFAULT N'' FOR [ExtraProperties];
2024-03-10T16:29:05.6208988Z Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
2024-03-10T16:29:05.6209680Z  ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
2024-03-10T16:29:05.6210441Z    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2024-03-10T16:29:05.6211142Z    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2024-03-10T16:29:05.6213443Z    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
2024-03-10T16:29:05.6213994Z    at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
2024-03-10T16:29:05.6214477Z    at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
2024-03-10T16:29:05.6214897Z    at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
2024-03-10T16:29:05.6215422Z    at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
2024-03-10T16:29:05.6215857Z --- End of stack trace from previous location ---
2024-03-10T16:29:05.6216332Z    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
2024-03-10T16:29:05.6216910Z    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
2024-03-10T16:29:05.6217508Z    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
2024-03-10T16:29:05.6218117Z    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
2024-03-10T16:29:05.6219411Z    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
2024-03-10T16:29:05.6228716Z    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
2024-03-10T16:29:05.6274637Z    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
2024-03-10T16:29:05.6276499Z    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
2024-03-10T16:29:05.6318174Z    at BSR.Beinni.EntityFrameworkCore.EntityFrameworkCoreBeinniDbSchemaMigrator.MigrateAsync() in D:\a\1\s\src\BSR.Beinni.EntityFrameworkCore\EntityFrameworkCore\EntityFrameworkCoreBeinniDbSchemaMigrator.cs:line 28
2024-03-10T16:29:05.6318733Z    at BSR.Beinni.Data.BeinniDbMigrationService.MigrateDatabaseSchemaAsync(Tenant tenant) in D:\a\1\s\src\BSR.Beinni.Domain\Data\BeinniDbMigrationService.cs:line 96
2024-03-10T16:29:05.6322334Z    at BSR.Beinni.Data.BeinniDbMigrationService.MigrateAsync() in D:\a\1\s\src\BSR.Beinni.Domain\Data\BeinniDbMigrationService.cs:line 53
2024-03-10T16:29:05.6325296Z    at BSR.Beinni.DbMigrator.DbMigratorHostedService.StartAsync(CancellationToken cancellationToken) in D:\a\1\s\src\BSR.Beinni.DbMigrator\DbMigratorHostedService.cs:line 39
2024-03-10T16:29:05.6325981Z    at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
2024-03-10T16:29:05.6326556Z    at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
2024-03-10T16:29:05.6327040Z    at Microsoft.Extensions.Hosting.Internal.Host.&lt;StartAsync&gt;g__LogAndRethrow|15_3(&lt;&gt;c__DisplayClass15_0&)
2024-03-10T16:29:05.6327419Z    at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
2024-03-10T16:29:05.6328422Z    at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
2024-03-10T16:29:05.6329008Z    at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
2024-03-10T16:29:05.6329425Z    at BSR.Beinni.DbMigrator.Program.Main(String[] args) in D:\a\1\s\src\BSR.Beinni.DbMigrator\Program.cs:line 31
2024-03-10T16:29:05.6329797Z    at BSR.Beinni.DbMigrator.Program.&lt;Main&gt;(String[] args)
2024-03-10T16:29:05.6330103Z ClientConnectionId:ce9179cc-e820-4477-81be-a41837b33ad2
2024-03-10T16:29:05.6330426Z Error Number:-2,State:0,Class:11

this is my migration

    name: "ExtraProperties",
    table: "AbpAuditLogs",
    type: "nvarchar(max)",
    nullable: false,
    defaultValue: "",
    oldClrType: typeof(string),
    oldType: "nvarchar(max)",
    oldNullable: true);

migrationBuilder.AlterColumn&lt;string&gt;(
    name: "ConcurrencyStamp",
    table: "AbpAuditLogs",
    type: "nvarchar(40)",
    maxLength: 40,
    nullable: false,
    defaultValue: "",
    oldClrType: typeof(string),
    oldType: "nvarchar(40)",
    oldMaxLength: 40,
    oldNullable: true);

I´m guessing this is connected to the size of the database server or need to incresing the size of the database to handle the migration. I´m guessing the production server can handle the migration but is there any standard way to do this (other than beefing up the hardware)?

  • ABP Framework version: v8.0.2
  • UI Type: Blazor Server
  • Database System: EF Core (SQL Server
  • Tiered (for MVC) or Auth Server Separated (for Angular): no

2 Answer(s)
  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying?tabs=dotnet-core-cli#sql-scripts You can generate SQL script and migrate using SSMS.

    You may need to change the timeout manually:

    On the Connect to Server window, press the Options button and there is an option for Execution time-out. Make sure it is 0.

  • User Avatar
    0
    Sturla created

    I beefed up the database to 5x and just ran the migration and all was fine! But thanks for the suggestion.

Made with ❤️ on ABP v9.1.0-preview. Updated on November 11, 2024, 11:11