U4-7100 - Upgrading from 6.x to 7.3.0 fails due to missing UniqueID field on cmsPropertyType table

Created by Robert Foster 14 Sep 2015, 16:10:59 Updated by Rachel 11 Nov 2016, 15:45:00

Migration step UpdateRelatedLinksData in TargetVersionSeven fails on a database from pre-7.0.0 due to missing UniqueID column (which is added at AddUniqueIdPropertyTypeColumn in TargetVersionSevenThreeZero),

I believe this is happening because the POCO for cmsPropertyType (PropertyTypeDto) contains the UniqueID property and therefore any queries to the cmsPropertyType table in Umbraco 7.3.0 are going to throw an error prior to the table being upgraded.

Is it possible to move the AddUniqueIdPropertyTypeColumn Migration higher up the chain of upgrades so the column is supported?

Stacktrace of the error:

2015-09-14 18:14:27,637 [P5948/D7/T165] ERROR Umbraco.Core.DatabaseContext - Database configuration failed System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'UniqueID'. 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.get_MetaData() 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, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 248 at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at Umbraco.Core.Persistence.Database.<Query>d__71.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Umbraco.Core.Persistence.Migrations.Upgrades.TargetVersionSeven.UpdateRelatedLinksData.UpdateRelatedLinksDataDo(Database database) at Umbraco.Core.Persistence.Migrations.Syntax.Execute.Expressions.ExecuteCodeStatementExpression.Process(Database database) at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database) at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade) at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, Boolean isUpgrade) at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService) ClientConnectionId:cb640051-2a87-4b59-869e-d752db02dda9 Error Number:207,State:1,Class:16

Comments

Shannon Deminick 21 Sep 2015, 11:58:52

This has already been resolved, please test with latest nightly: http://nightly.umbraco.org/umbraco%207.3.0/


andrew shearer 07 Oct 2015, 23:00:24

Im getting this error too using the latest nuget package


andrew shearer 07 Oct 2015, 23:45:37

I was trying to go from 7.2.5 to 7.3


andrew shearer 07 Oct 2015, 23:46:05

Any more info I can provide to help troubleshoot this?


andrew shearer 09 Oct 2015, 05:18:43

hi - isn't anyone else getting this error?


Tom Fulton 09 Oct 2015, 22:25:39

Hi @shearer3000, I thought I was getting the same issue, but it turned out to be the same error message, from a different migration: TargetVersionSixZeroOne.UpdatePropertyTypesAndGroups. Wonder if that's the case for you as well? I created U4-7220 for this.


andrew shearer 10 Oct 2015, 19:50:11

hi Tom - do you use uSiteBuilder by chance? I noticed if i disabled that using then the upgrade was able to complete. I still get other problems trying to re-enable usitebuilder, but just FYI anyway.


Lokesh kumar Chippada 03 Nov 2015, 15:36:30

I managed to fix this by upgrading database to 7.2.8 and then upgrading to 7.3.1..


Lokesh kumar Chippada 03 Nov 2015, 15:39:13

And also need to delete duplicate rows from tables


Alejandro Ocampo 04 Dec 2015, 12:51:22

Hi, I'm having the same problem upgrading 7.2.4 to 7.3.3, I managed to upgrade it until 7.2.8 after that, this error is appearing I tried what @Lokesh mentioned but not luck. suggestions ? /cc @Shandem


Lokesh kumar Chippada 04 Dec 2015, 14:26:49

Hi @ja0b,

@Claus.Jensen already fixed it.. http://issues.umbraco.org/issue/U4-7220#comment=67-24534

I did testing from version 4 to 7.3.3 which is successful.

May be something going wrong when upgrading from 7.2.4 to 7.3.3


Alejandro Ocampo 09 Dec 2015, 14:12:32

@Lokesh it didn't work for me, what I did was use the redgate tool to sync the db schema. That was the only way I found.


Sebastiaan Janssen 06 Apr 2016, 13:56:54

Update: I just debugged a site with this problem, turns out it managed to execute code before Umbraco was properly started. The code tried to look up nodes in the database but since the database was not yet upgraded properly, the site crashed with this error. The solution was to move the code to an Umbraco ApplicationEventHandler. Another workaround I've blogged about here: https://cultiv.nl/blog/an-umbraco-database-upgrade-tip-from-the-trenches/ So: point a clean install to your existing database, run the database upgrade and then do the rest of your website upgrade.


Simon Dingley 03 Jun 2016, 09:43:47

Great advice Sebastiaan! Such an obvious thing to do yet I've never considered it and I think is something I will now do with all remote upgrades to bypass any potential interference from custom code during the database upgrade process.


Rachel 11 Nov 2016, 15:45:00

Good shout from Sebastian - I'm having this problem too and it's because of a package that iterates through published content at application start up.


Priority: Normal

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.3.0, 7.3.1

Due in version: 7.3.0

Sprint:

Story Points:

Cycle: