search

OrchardCMS / OrchardCore

Orchard Core is an open-source modular and multi-tenant application framework built with ASP.NET Core, and a content management system (CMS) built on top of that framework.
https://orchardcore.net
BSD 3-Clause "New" or "Revised" License
7.46k stars 2.41k forks source link

PostgreSQL database issue with latest #8545

Closed jptissot closed 3 years ago

jptissot commented 3 years ago

Describe the bug

Error occurs on Postgresql when enabling our Hackathon feature. Works fine with SQLite but with postgresql, the site completely crashes.

To Reproduce

Create a tenant with a long enough name to go over the 64 character limit for table names on postgresql

Expected behavior

Migrations to execute without crashing the site.

Screenshots

fail: OrchardCore.Data.Migration.DataMigrationManager[0]
      => SpanId:332116e92b5d7a4f, TraceId:d4cacbf54ed6af418a87fa58e897948c, ParentId:0000000000000000 => ConnectionId:0HM6C8V30N54S => RequestPath:/tj5ogvh/Admin/Features RequestId:0HM6C8V30N54S:00000016
      Error while running migration version 0 for 'OrchardCore.Workflows'.
      System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
       ---> Npgsql.PostgresException (0x80004005): 42P07: relation "tj5ogvh_IDX_WorkflowBlockingActivitiesIndex_DocumentId_Activity" already exists
         at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteNonQuery()
         at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2813
         at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 572
         at YesSql.Sql.SchemaBuilder.Execute(IEnumerable`1 statements)
         at YesSql.Sql.SchemaBuilder.AlterTable(String name, Action`1 table)
         at YesSql.Sql.SchemaBuilder.AlterIndexTable(Type indexType, Action`1 table, String collection)
         at YesSql.Sql.SchemaBuilderExtensions.AlterIndexTable[T](ISchemaBuilder builder, Action`1 table, String collection)
         at OrchardCore.Workflows.Migrations.Create()
        Exception data:
          Severity: ERROR
          SqlState: 42P07
          MessageText: relation "tj5ogvh_IDX_WorkflowBlockingActivitiesIndex_DocumentId_Activity" already exists
          File: index.c
          Line: 884
          Routine: index_create
         --- End of inner exception stack trace ---
         at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
         at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId)
fail: OrchardCore.Data.Migration.DataMigrationManager[0]
      => SpanId:332116e92b5d7a4f, TraceId:d4cacbf54ed6af418a87fa58e897948c, ParentId:0000000000000000 => ConnectionId:0HM6C8V30N54S => RequestPath:/tj5ogvh/Admin/Features RequestId:0HM6C8V30N54S:00000016
      Error while running migration version 0 for 'StatCan.OrchardCore.Hackathon'.
      System.ObjectDisposedException: The CancellationTokenSource has been disposed.
         at System.Threading.CancellationTokenSource.ThrowObjectDisposedException()
         at System.Threading.CancellationTokenSource.CancelAfter(Int32 millisecondsDelay)
         at System.Threading.CancellationTokenSource.CancelAfter(TimeSpan delay)
         at Npgsql.Util.ResettableCancellationTokenSource.Stop()
         at Npgsql.NpgsqlReadBuffer.<Ensure>g__EnsureLong|40_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
         at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.Consume(Boolean async)
         at Npgsql.NpgsqlDataReader.Close(Boolean connectionClosing, Boolean async, Boolean isDisposing)
         at Npgsql.NpgsqlConnector.CloseOngoingOperations(Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlTransaction.Dispose(Boolean disposing)
         at YesSql.Session.ReleaseTransaction()
         at YesSql.Session.Cancel()
         at YesSql.Services.DefaultQuery.Query`1.FirstOrDefaultImpl()
         at OrchardCore.Data.Documents.DocumentStore.GetOrCreateMutableAsync[T](Func`1 factoryAsync)
         at OrchardCore.Documents.DocumentManager`1.GetOrCreateMutableAsync(Func`1 factoryAsync)
         at OrchardCore.Queries.Services.QueryManager.SaveQueryAsync(String name, Query query)
         at OrchardCore.Queries.Recipes.QueryStep.ExecuteAsync(RecipeExecutionContext context)
         at OrchardCore.Recipes.Services.RecipeExecutor.<>c__DisplayClass7_0.<<ExecuteStepAsync>b__0>d.MoveNext()
      --- End of stack trace from previous location ---
         at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell)
         at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteStepAsync(RecipeExecutionContext recipeStep)
         at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken)
         at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken)
         at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken)
         at OrchardCore.Recipes.Services.RecipeMigrator.ExecuteAsync(String recipeFileName, IDataMigration migration)
         at StatCan.OrchardCore.Hackathon.HackathonMigrations.CreateAsync() in C:\repos\statcan\StatCan.OrchardCore\src\Modules\StatCan.OrchardCore.Hackathon\HackathonMigrations.cs:line 31
         at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId)
Unhandled exception. Npgsql.NpgsqlException (0x80004005): Received backend message ReadyForQuery while expecting CommandCompleteMessage. Please file a bug.
   at Npgsql.NpgsqlConnector.ExecuteInternalCommand(Byte[] data, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.Rollback(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlTransaction.Rollback()
   at Npgsql.NpgsqlTransaction.Dispose(Boolean disposing)
   at YesSql.Session.ReleaseTransaction()
   at YesSql.Session.Cancel()
   at YesSql.Session.FlushAsync()
   at YesSql.Services.DefaultQuery.Query`1.ListImpl()
   at StatCan.OrchardCore.Hackathon.Indexes.IndexMigrations.CreateHackathonUsersIndex() in C:\repos\statcan\StatCan.OrchardCore\src\Modules\StatCan.OrchardCore.Hackathon\Indexes\Migrations.cs:line 80
   at System.Threading.Tasks.Task.<>c.<ThrowAsync>b__140_1(Object state)
   at System.Threading.QueueUserWorkItemCallback.<>c.<.cctor>b__6_0(QueueUserWorkItemCallback quwi)
   at System.Threading.ExecutionContext.RunForThreadPoolUnsafe[TState](ExecutionContext executionContext, Action`1 callback, TState& state)
   at System.Threading.QueueUserWorkItemCallback.Execute()
   at System.Threading.ThreadPoolWorkQueue.Dispatch()

I will add to this issue when I find more information. Just investigating right now.

Skrypt commented 3 years ago

"tj5ogvh_IDX_WorkflowBlockingActivitiesIndex_DocumentId_Activity" already exists.

It's probably trying to execute a migration that has already created this index previously. If the index is already there then you should either remove it so that the migration executes successfully or just increment the migration number in the database table and leave it like it is to avoid getting an exception raised.

Now, why is it doing this is what we need to find though. One reason could be that someone uses Azure Shell configurations for development but that it's ASPNETCORE_ENVIRONMENT variable is not set to development. This can execute the migration on the prod server database before even having it published.

jptissot commented 3 years ago

Log from the All Databases CI Artifact for the ComingSoon recipe.

https://github.com/OrchardCMS/OrchardCore/actions/runs/548869771

2021-02-08 18:22:20.6741||||Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager|WARN|No XML encryptor configured. Key {a898706b-b391-4aca-96c5-70865671e5d8} may be persisted to storage in unencrypted form. 
2021-02-08 18:22:21.2780||||Microsoft.AspNetCore.Server.Kestrel|WARN|Unable to bind to http://localhost:5000 on the IPv6 loopback interface: 'Cannot assign requested address'. 
2021-02-08 18:22:21.4220||||Microsoft.AspNetCore.Server.Kestrel|WARN|Unable to bind to https://localhost:5001 on the IPv6 loopback interface: 'Cannot assign requested address'. 
2021-02-08 18:23:07.0382|t1v3mvk||acae604c-4d49eaba22c11046.||OrchardCore.Data.Migration.DataMigrationManager|ERROR|Error while running migration version 0 for 'OrchardCore.Workflows'. System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
 ---> Npgsql.PostgresException (0x80004005): 42P07: relation "t1v3mvk_IDX_WorkflowBlockingActivitiesIndex_DocumentId_Activity" already exists
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in /_/Dapper/SqlMapper.cs:line 2813
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 572
   at YesSql.Sql.SchemaBuilder.Execute(IEnumerable`1 statements)
   at YesSql.Sql.SchemaBuilder.AlterTable(String name, Action`1 table)
   at YesSql.Sql.SchemaBuilder.AlterIndexTable(Type indexType, Action`1 table, String collection)
   at YesSql.Sql.SchemaBuilderExtensions.AlterIndexTable[T](ISchemaBuilder builder, Action`1 table, String collection)
   at OrchardCore.Workflows.Migrations.Create() in /_/src/OrchardCore.Modules/OrchardCore.Workflows/Migrations.cs:line 87
  Exception data:
    Severity: ERROR
    SqlState: 42P07
    MessageText: relation "t1v3mvk_IDX_WorkflowBlockingActivitiesIndex_DocumentId_Activity" already exists
    File: index.c
    Line: 884
    Routine: index_create
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId) in /_/src/OrchardCore/OrchardCore.Data/Migration/DataMigrationManager.cs:line 200    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId) in /_/src/OrchardCore/OrchardCore.Data/Migration/DataMigrationManager.cs:line 200
2021-02-08 18:23:07.1896|t1v3mvk||acae604c-4d49eaba22c11046.||Microsoft.AspNetCore.Server.Kestrel|ERROR|Connection id "0HM6CBPUHP1KS", Request id "0HM6CBPUHP1KS:0000002A": An unhandled exception was thrown by the application. Npgsql.PostgresException (0x80004005): 42P01: relation "t1v3mvk_ContentItemIndex" does not exist
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
   at YesSql.Store.ProduceAsync[T](WorkerQueryKey key, Func`2 work, Object[] args)
   at YesSql.Services.DefaultQuery.Query`1.ListImpl()
   at OrchardCore.ContentManagement.DefaultContentManager.ImportAsync(IEnumerable`1 contentItems) in /_/src/OrchardCore/OrchardCore.ContentManagement/DefaultContentManager.cs:line 452
   at OrchardCore.Contents.Recipes.ContentStep.ExecuteAsync(RecipeExecutionContext context) in /_/src/OrchardCore.Modules/OrchardCore.Contents/Recipes/ContentStep.cs:line 33
   at OrchardCore.Recipes.Services.RecipeExecutor.<>c__DisplayClass7_0.<<ExecuteStepAsync>b__0>d.MoveNext() in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 174
--- End of stack trace from previous location where exception was thrown ---
   at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell) in /_/src/OrchardCore/OrchardCore.Abstractions/Shell/Scope/ShellScope.cs:line 243
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteStepAsync(RecipeExecutionContext recipeStep) in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 159
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken) in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 103
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken) in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 120
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken)
   at OrchardCore.Setup.Services.SetupService.SetupInternalAsync(SetupContext context) in /_/src/OrchardCore/OrchardCore.Setup.Core/SetupService.cs:line 196
   at OrchardCore.Setup.Services.SetupService.SetupAsync(SetupContext context) in /_/src/OrchardCore/OrchardCore.Setup.Core/SetupService.cs:line 87
   at OrchardCore.Setup.Controllers.SetupController.IndexPOST(SetupViewModel model) in /_/src/OrchardCore.Modules/OrchardCore.Setup/Controllers/SetupController.cs:line 181
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at OrchardCore.Modules.ModularTenantRouterMiddleware.Invoke(HttpContext httpContext) in /_/src/OrchardCore/OrchardCore/Modules/ModularTenantRouterMiddleware.cs:line 64
   at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell) in /_/src/OrchardCore/OrchardCore.Abstractions/Shell/Scope/ShellScope.cs:line 243
   at OrchardCore.Modules.ModularTenantContainerMiddleware.Invoke(HttpContext httpContext) in /_/src/OrchardCore/OrchardCore/Modules/ModularTenantContainerMiddleware.cs:line 61
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
  Exception data:
    Severity: ERROR
    SqlState: 42P01
    MessageText: relation "t1v3mvk_ContentItemIndex" does not exist
    Position: 73
    File: parse_relation.c
    Line: 1180
    Routine: parserOpenTable    at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
   at YesSql.Store.ProduceAsync[T](WorkerQueryKey key, Func`2 work, Object[] args)
   at YesSql.Services.DefaultQuery.Query`1.ListImpl()
   at OrchardCore.ContentManagement.DefaultContentManager.ImportAsync(IEnumerable`1 contentItems) in /_/src/OrchardCore/OrchardCore.ContentManagement/DefaultContentManager.cs:line 452
   at OrchardCore.Contents.Recipes.ContentStep.ExecuteAsync(RecipeExecutionContext context) in /_/src/OrchardCore.Modules/OrchardCore.Contents/Recipes/ContentStep.cs:line 33
   at OrchardCore.Recipes.Services.RecipeExecutor.<>c__DisplayClass7_0.<<ExecuteStepAsync>b__0>d.MoveNext() in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 174
--- End of stack trace from previous location where exception was thrown ---
   at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell) in /_/src/OrchardCore/OrchardCore.Abstractions/Shell/Scope/ShellScope.cs:line 243
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteStepAsync(RecipeExecutionContext recipeStep) in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 159
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken) in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 103
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken) in /_/src/OrchardCore/OrchardCore.Recipes.Core/Services/RecipeExecutor.cs:line 120
   at OrchardCore.Recipes.Services.RecipeExecutor.ExecuteAsync(String executionId, RecipeDescriptor recipeDescriptor, Object environment, CancellationToken cancellationToken)
   at OrchardCore.Setup.Services.SetupService.SetupInternalAsync(SetupContext context) in /_/src/OrchardCore/OrchardCore.Setup.Core/SetupService.cs:line 196
   at OrchardCore.Setup.Services.SetupService.SetupAsync(SetupContext context) in /_/src/OrchardCore/OrchardCore.Setup.Core/SetupService.cs:line 87
   at OrchardCore.Setup.Controllers.SetupController.IndexPOST(SetupViewModel model) in /_/src/OrchardCore.Modules/OrchardCore.Setup/Controllers/SetupController.cs:line 181
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at OrchardCore.Modules.ModularTenantRouterMiddleware.Invoke(HttpContext httpContext) in /_/src/OrchardCore/OrchardCore/Modules/ModularTenantRouterMiddleware.cs:line 64
   at OrchardCore.Environment.Shell.Scope.ShellScope.UsingAsync(Func`2 execute, Boolean activateShell) in /_/src/OrchardCore/OrchardCore.Abstractions/Shell/Scope/ShellScope.cs:line 243
   at OrchardCore.Modules.ModularTenantContainerMiddleware.Invoke(HttpContext httpContext) in /_/src/OrchardCore/OrchardCore/Modules/ModularTenantContainerMiddleware.cs:line 61
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
jptissot commented 3 years ago

I am pretty sure this is due to the fact that the ComingSoon recipe enables the Workflows module ?

Skrypt commented 3 years ago

Ok, there is definitely something going wrong with the indices there.

Skrypt commented 3 years ago

I think the issue is related with the indices name length. When we look at the Workflow migrations there is 2 new indices created.

            SchemaBuilder.AlterIndexTable<WorkflowBlockingActivitiesIndex>(table => table
                .CreateIndex("IDX_WorkflowBlockingActivitiesIndex_DocumentId_ActivityId",
                    "DocumentId",
                    "ActivityId",
                    "WorkflowTypeId",
                    "WorkflowId")
            );

            SchemaBuilder.AlterIndexTable<WorkflowBlockingActivitiesIndex>(table => table
                .CreateIndex("IDX_WorkflowBlockingActivitiesIndex_DocumentId_ActivityName",
                    "DocumentId",
                    "ActivityName",
                    "WorkflowTypeId",
                    "WorkflowCorrelationId")
            );

Here because of the PostgreSQL name length limit it uses only "IDX_WorkflowBlockingActivitiesIndex_DocumentId_Activity" for both.

Fix suggested is to create a new Upgrade step that will remove these and rename it with a shorter name :

IDX_WFBA_DocumentId_ActivityId IDX_WFBA_DocumentId_ActivityName

One thing I've noticed is that we always use the word "index" in our index names... which I think is not necessary. The IDX prefix already tells us it's an index ...

/cc @jtkech @sebastienros

jtkech commented 3 years ago

@Skrypt Yes you're right

No luck, i did a func test on all databases, but i missed it after adding the last index related to ActivityName.

Hmm, on google they say it is 63 chars and IDX_WorkflowBlockingActivitiesIndex_DocumentId_ActivityName = 59 chars, maybe it depends on the version, would be interesting to know the real limitation, based on your test seems to be 55 chars.

At least we need to be aware of this limitation and to confirm the real limitation. Yes we can use an acronym and / or remove the Index part, , maybe only for the very long ones, for now just those that exceeds the limitation.

Just found this one IDX_ContentPickerFieldIndex_DocumentId_SelectedContentItemId

I saw that we can't alter a column with sqlite, but good to know that we can drop indexes with all providers. So, because we aded all the DocumentId indexes, maybe worth to add a migration step to all modules to remove all the redundant indexes, @Skrypt @deanmarcussen what you think? Maybe at least open an issue for this.

Oh yes, i didn't do it as we use drop index if exists that is not supported with my 2014 version, as they say in the doc

IF EXISTS Applies to: SQL Server ( SQL Server 2016 (13.x) through current version).

jptissot commented 3 years ago

Hmm, on google they say it is 63 chars and IDX_WorkflowBlockingActivitiesIndex_DocumentId_ActivityName = 59 chars, maybe it depends on the version, would be interesting to know the real limitation, based on your test seems to be 55 chars.

The issue is when you add a tenant prefix, you are pretty limited in length.

jtkech commented 3 years ago

@jptissot

But it adds a prefix to the table name, not to the index names of this table, right?