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:
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.
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.
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
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.
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
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
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)
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 :)
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:
Full text dump is as follows:
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.
Good ideal thank you, will give that a go
No luck there - dumped out the SQL and diffed them, schema is the same as a freshly installed v9 instance...
Does it give the excatly same error message?
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...
Only from a fresh 8.17 to 9.0.0 which worked flawlessly. Will check to 9.0.1 as well shortly
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?
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
But then hit the same error again
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.
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
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:
I ran this and it appears to have worked without issue, however I still get an error when attempting to view the log
I've raised this as an issue now, referring back to this thread.
https://github.com/umbraco/Umbraco-CMS/issues/11442
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)
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 :)
is working on a reply...