SqlException after an upgrade from v13.2.2 to v14.0.0-rc1

AleksandrRogov commented 4 months ago

Which Umbraco version are you using? (Please write the exact version, example: 10.1.0)

v14.0.0-rc1

Bug summary

Upgraded an existing Umbraco Web project from v13.2.2 to v14.0.0-rc1. After a successful build and run got an SqlException error when trying to access /umbraco . Details are in the specifics.

Specifics

The full error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ApplicationType'.
Invalid column name 'ClientType'.
Invalid column name 'JsonWebKeySet'.
Invalid column name 'Settings'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at OpenIddict.Core.OpenIddictApplicationCache`1.<>c__DisplayClass6_0.<<FindByClientIdAsync>g__ExecuteAsync|0>d.MoveNext()
--- End of stack trace from previous location ---
   at OpenIddict.Core.OpenIddictApplicationManager`1.FindByClientIdAsync(String identifier, CancellationToken cancellationToken)
   at OpenIddict.Core.OpenIddictApplicationManager`1.OpenIddict.Abstractions.IOpenIddictApplicationManager.FindByClientIdAsync(String identifier, CancellationToken cancellationToken)
   at Umbraco.Cms.Infrastructure.Security.OpenIdDictApplicationManagerBase.CreateOrUpdate(OpenIddictApplicationDescriptor clientDescriptor, CancellationToken cancellationToken)
   at Umbraco.Cms.Api.Management.Security.BackOfficeApplicationManager.EnsureBackOfficeApplicationAsync(Uri backOfficeUrl, CancellationToken cancellationToken)
   at Umbraco.Cms.Api.Management.Middleware.BackOfficeAuthorizationInitializationMiddleware.InitializeBackOfficeAuthorizationOnceAsync(HttpContext context)
   at Umbraco.Cms.Api.Management.Middleware.BackOfficeAuthorizationInitializationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
ClientConnectionId:a0e77d28-fbcc-462a-8cdc-44fd0f5f0956
Error Number:207,State:1,Class:16

It looks like the Database Migrations generated in Umbraco.Cms.Persistence.EFCore.SqlServer did not get executed.

I added a test code inside INotificationAsyncHandler<UmbracoApplicationStartedNotification> where I injected UmbracoDbContext which showed that there are Pending Migrations: 20230807654321_AddOpenIddict and 20240403140654_UpdateOpenIddictToV5.

Once I tried to execute migrations myself I got the following error: Microsoft.Data.SqlClient.SqlException: 'There is already an object named 'umbracoOpenIddictApplications' in the database.' . Which is correct, because it was added at some point in time in v13.

I also noticed that v14-rc1 is using dbo.__EFMigrationsHistory table while v13 did not use it (I suppose there was a custom migrations history table). Maybe that's the case? Because it did create 20230622184303_InitialCreate which is an empty migration.

Steps to reproduce

Install v13 Umbraco Web project
Build and Run the project (so the database with all tables gets created)
Upgrade to v14-rc1
Build and Run
Navigate to /umbraco.

Expected result / actual result

/umbraco would open without any error.

github-actions[bot] commented 4 months ago

Hi there @AleksandrRogov!

Firstly, a big thank you for raising this issue. Every piece of feedback we receive helps us to make Umbraco better.

We really appreciate your patience while we wait for our team to have a look at this but we wanted to let you know that we see this and share with you the plan for what comes next.

We'll assess whether this issue relates to something that has already been fixed in a later version of the release that it has been raised for.
If it's a bug, is it related to a release that we are actively supporting or is it related to a release that's in the end-of-life or security-only phase?
We'll replicate the issue to ensure that the problem is as described.
We'll decide whether the behavior is an issue or if the behavior is intended.

We wish we could work with everyone directly and assess your issue immediately but we're in the fortunate position of having lots of contributions to work with and only a few humans who are able to do it. We are making progress though and in the meantime, we will keep you in the loop and let you know when we have any questions.

Thanks, from your friendly Umbraco GitHub bot :robot: :slightly_smiling_face:

AleksandrRogov commented 4 months ago

It looks like this issue happened only in my two separate environments. My colleagues did not have this issue. It could be because they joined this project later and started working from v12. While I worked on v11 and then upgraded to v12 which they picked up later.

It is interesting because the tables for OpenIddict were created but the Migration History was not updated. I am not sure why. It could be that the migrations silently failed but I could not find anything in the logs.

If someone has this error the workaround will be to manually insert those MigrationIds into the History table:

INSERT INTO [dbo].[__EFMigrationsHistory]
VALUES ('20230622184303_InitialCreate', <ef core version>)
INSERT INTO [dbo].[__EFMigrationsHistory]
VALUES ('20230807654321_AddOpenIddict', <ef core version>)

Where <ef core version> is the version of Entity Framework Core that you have currently installed in your project.

Thank you!

kumaheiyama commented 1 week ago

@AleksandrRogov I have encountered the same issue going from v13.4.1 to v14.2.0. However I have both those migrations in the history table, as well as the following 20240403140654_UpdateOpenIddictToV5

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ApplicationType'. Invalid column name 'ClientType'. Invalid column name 'JsonWebKeySet'. Invalid column name 'Settings'.

UPDATE: I was able to solve the problem by simply adding the missing columns to the dbo.umbracoOpenIddictApplications table, each with the nvarchar(max) datatype. After that I was able to successfully upgrade to v14.2.0 through the regular upgrade process in Umbraco. 🥳

umbraco / Umbraco-CMS