Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Warren Harding 132 posts 275 karma points
    Oct 20, 2021 @ 05:57
    Warren Harding
    0

    Upgrade from 8.17 to 9.0.1 causing error

    Hi there, I'm attempting to update a v 8.17 instance of Umbraco to 9.0.1. It's a fresh project created in Visual Studio that I'm pointing to the 8.17 database.

    It looks like it mostly completes and then on the /install?redir I get the following error:

    enter image description here Any ideas here, I'm lost - thank you!

    Full text dump is as follows:

    System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'passwordConfig'. Invalid column name 'securityStampToken'. Invalid column name 'emailConfirmedDate'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.getMetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 343 at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.b320() at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteScalar() at NPoco.Database.ExecuteScalarHelper(DbCommand cmd) at NPoco.Database.ExecuteScalar[T](String sql, CommandType commandType, Object[] args) at NPoco.Database.ExecuteScalar[T](Sql Sql) at NPoco.Database.ExecuteScalar[T](String sql, Object[] args) at NPoco.Database.PageImp[T,TRet](Int64 page, Int64 itemsPerPage, String sql, Object[] args, Func3 executeQueryFunc) at NPoco.Database.Page[T](Int64 page, Int64 itemsPerPage, String sql, Object[] args) at NPoco.Database.Page[T](Int64 page, Int64 itemsPerPage, Sql sql) at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.ContentRepositoryBase3.GetPage[TDto](IQuery1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, Func2 mapDtos, Sql1 filter, Ordering ordering) at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.MemberRepository.GetPage(IQuery1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, IQuery1 filter, Ordering ordering) at Umbraco.Cms.Infrastructure.PublishedCache.Persistence.NuCacheContentRepository.RebuildMemberDbCache(IContentCacheDataSerializer serializer, Int32 groupSize, IReadOnlyCollection1 contentTypeIds) at Umbraco.Cms.Infrastructure.PublishedCache.Persistence.NuCacheContentRepository.Rebuild(IReadOnlyCollection1 contentTypeIds, IReadOnlyCollection1 mediaTypeIds, IReadOnlyCollection1 memberTypeIds) at Umbraco.Cms.Infrastructure.PublishedCache.Persistence.NuCacheContentService.Rebuild(IReadOnlyCollection1 contentTypeIds, IReadOnlyCollection1 mediaTypeIds, IReadOnlyCollection1 memberTypeIds) at Umbraco.Cms.Infrastructure.PublishedCache.Persistence.NuCacheContentService.RebuildDatabaseCacheIfSerializerChanged() at Umbraco.Cms.Infrastructure.PublishedCache.NuCacheStartupHandler.Handle(UmbracoApplicationStartingNotification notification) at Umbraco.Cms.Core.Events.EventAggregator.PublishCore(IEnumerable1 allHandlers, INotification notification) at Umbraco.Cms.Core.Events.EventAggregator.PublishAsync[TNotification](TNotification notification, CancellationToken cancellationToken) at Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken) at Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.RestartAsync() at Umbraco.Cms.Web.BackOffice.Install.InstallApiController.CompleteInstall() at lambdamethod519(Closure , Object ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.gAwaited|120(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.gAwaited|100(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() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.gAwaited|240(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() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.gAwaited|170(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.gAwaitRequestTask|60(Endpoint endpoint, Task requestTask, ILogger logger) at Umbraco.Cms.Web.Common.Middleware.BasicAuthenticationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next) at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>cDisplayClass61.DisplayClass61.

  • Frank Laumann 39 posts 303 karma points
    Oct 20, 2021 @ 06:39
    Frank Laumann
    0

    Hi Warren,

    To me it looks like the user table in the database is not compatible for umbraco 9. I don't know how much experince you have with working within the database? But what I would try is to make a copy of the database, delete the user table (if possible) and create the user table using umbraco 9 scheme and then finish by figuring out how to insert the data rows (if anything else works) from the original user table.

    I hope atleast some of this helps.

  • Warren Harding 132 posts 275 karma points
    Oct 20, 2021 @ 08:44
    Warren Harding
    0

    Good ideal thank you, will give that a go

  • Warren Harding 132 posts 275 karma points
    Oct 20, 2021 @ 08:56
    Warren Harding
    0

    No luck there - dumped out the SQL and diffed them, schema is the same as a freshly installed v9 instance...

  • Frank Laumann 39 posts 303 karma points
    Oct 20, 2021 @ 09:01
    Frank Laumann
    0

    Does it give the excatly same error message?

  • Warren Buckley 2106 posts 4836 karma points MVP ∞ admin hq c-trib
    Oct 20, 2021 @ 11:09
    Warren Buckley
    0

    Just out of curiosity, have you tried upgrading a clean/minimal Umbraco 8.17 site to Umbraco 9.0.1 to see if that will work just fine for you or not.

    Then we can investigate further...

  • Warren Harding 132 posts 275 karma points
    Oct 20, 2021 @ 11:24
    Warren Harding
    0

    Only from a fresh 8.17 to 9.0.0 which worked flawlessly. Will check to 9.0.1 as well shortly

  • Warren Buckley 2106 posts 4836 karma points MVP ∞ admin hq c-trib
    Oct 20, 2021 @ 12:44
    Warren Buckley
    0

    Hmmmm 🤔 Keep us posted.

    Has the site been upgraded loads over time. IE was it originally an Umbraco V7 site that has been migrated up into V8 and patched upwards?

  • Warren Harding 132 posts 275 karma points
    Oct 21, 2021 @ 00:39
    Warren Harding
    0

    This site was built straight in 8 unfortunately. I've just created a fresh v 8.17 site with Visual Studio, ran through the installer then created a new solution based on the Umbraco Project 9.0.1 template. Pointed that at the 8.17 database and ran it. Worth noting that it didn't appear to pick up the previous version number

    enter image description here

    But then hit the same error again

    enter image description here

  • Frank Laumann 39 posts 303 karma points
    Oct 20, 2021 @ 13:42
    Frank Laumann
    0

    I found it. It is cmsMember which doesn't have the missing columns in previous versions. Also even though it looks like it fails, it actually still upgrade at least that is what it looks like from here. I guess a solution would be to add the missing columns to the table before upgrading.

    Let me know if this solve the issue.

  • Warren Harding 132 posts 275 karma points
    Oct 21, 2021 @ 00:34
    Warren Harding
    0

    Yes, it looked to work, but then I went to check the Log Viewer in Umbraco and it was throwing up error alerts too - so figure there's a few other things missing

  • Frank Laumann 39 posts 303 karma points
    Oct 21, 2021 @ 05:56
    Frank Laumann
    1

    So I tested if adding the 3 columns manually to cmsMember would fix the errror and it did.

    This minor step should indeed be added to the upgrade process for upgrades from version 9.0.0 and below. Maybe it is added but still throws the error?

    Best regards Frank

    Edit: I use this sql script to fix it:

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    SET ARITHABORT ON
    SET NUMERIC_ROUNDABORT OFF
    SET CONCAT_NULL_YIELDS_NULL ON
    SET ANSI_NULLS ON
    SET ANSI_PADDING ON
    SET ANSI_WARNINGS ON
    COMMIT
    BEGIN TRANSACTION
    GO
    ALTER TABLE dbo.cmsMember ADD
        passwordConfig nvarchar(500) NULL,
        securityStampToken nvarchar(255) NULL,
        emailConfirmedDate datetime NULL
    GO
    ALTER TABLE dbo.cmsMember SET (LOCK_ESCALATION = TABLE)
    GO
    COMMIT
    
  • Warren Harding 132 posts 275 karma points
    Oct 22, 2021 @ 04:35
    Warren Harding
    0

    I ran this and it appears to have worked without issue, however I still get an error when attempting to view the log

    Invalid object name 'umbracoLogViewerQuery'

    enter image description here

  • Rick Butterfield 2 posts 73 karma points MVP c-trib
    Oct 21, 2021 @ 10:39
    Rick Butterfield
    1

    I've raised this as an issue now, referring back to this thread.

    https://github.com/umbraco/Umbraco-CMS/issues/11442

  • Warren Harding 132 posts 275 karma points
    Oct 22, 2021 @ 04:55
    Warren Harding
    1

    I've checked the schema from 8.17 update to 9.0.1 after running the SQL mentioned above against a fresh 9.0.1 instance - tables umbracoExternalLoginToken and umbracoLogViewerQuery do not exist in the updated version.

    There's also the following foreign key diffs (right is updated db)

    enter image description here

  • Frank Laumann 39 posts 303 karma points
    Oct 22, 2021 @ 06:03
    Frank Laumann
    2

    Great find Warren. I have forwarded your find to make sure this is also fixed in the future. For now you could add the table manually but you would have to figure that out yourself. I don't think it is a good idea to have a lot of my homemade sql hotfix scripts laying around :)

Please Sign in or register to post replies

Write your reply to:

Draft