U4-7220 - Upgrading 4.9.0 to 7.3.0 fails due to missing UniqueID field on cmsPropertyType table

Created by Tom Fulton 09 Oct 2015, 22:23:31 Updated by Lokesh kumar Chippada 04 Dec 2015, 14:09:16

Relates to: COU-241

When upgrading my 4.9.0 install to 7.3.0, the Migration step TargetVersionSixZeroOne.UpdatePropertyTypesAndGroups is failing due to missing UniqueID column (which is added in TargetVersionSevenThreeZero.AddUniqueIdPropertyTypeColumn)

I believe to the problem described in U4-7100, which was the same error with another migration:

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.

Stacktrace: 2015-10-09 15:31:46,941 [P17528/D3/T10] 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.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.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, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 266 at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.Database.Update(String tableName, String primaryKeyName, Object poco, Object primaryKeyValue, IEnumerable1 columns) at Umbraco.Core.Persistence.Database.Update(Object poco, Object primaryKeyValue, IEnumerable1 columns) at Umbraco.Core.Persistence.Migrations.Upgrades.TargetVersionSixZeroOne.UpdatePropertyTypesAndGroups.UpdatePropertyTypesAndGroupsDo(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.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService) ClientConnectionId:ef6b51f8-1a52-40ea-8dbc-6ad351d7b0b3


Lokesh kumar Chippada 03 Nov 2015, 12:42:42


Hi Tom,

Have you fixed it?

Please let me know the solution if you already fixed it.

Thanks Lokesh

Lokesh kumar Chippada 03 Nov 2015, 15:35:59

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

Claus Jensen 19 Nov 2015, 13:06:13

Hi @Lokesh & @tomnf,

I've tried to replicate this without any luck, doing an upgrade from both 4.9.0 and 4.11.10 to both 7.3.0 and 7.3.1.

Neither of the versions I'm upgrading from has the column you are referring to causing the problem - and none of the upgrades has failed in any way.

Are you sure about the version numbers you are referring to here?

Lokesh kumar Chippada 19 Nov 2015, 13:40:24

Hi, @Claus.Jensen

I got this error when i tried to upgrade DB from version 4.11.8 to 7.3.1

After that i restored the DB and upgraded to 7.2.8, which is successful Then upgraded to 7.3.1

Lokesh kumar Chippada 19 Nov 2015, 13:42:38

Hi, @Claus.Jensen

I got this error when i tried to upgrade DB from version 4.11.8 to 7.3.1

After that i restored the DB and upgraded to 7.2.8, which is successful Then upgraded to 7.3.1

Claus Jensen 19 Nov 2015, 14:03:01

Hi @Lokesh

Since we're having trouble replicating it, the only way we can try to solve the issue is if you can supply us with a copy of your site and database before the upgrade is done. That way we can try to investigate what is preventing you from upgrading.

Best, Claus

Tom Fulton 24 Nov 2015, 00:47:52

Hi @Claus.Jensen - I found an old DB that I can reproduce this on. If you want to shoot me an email, I can send you over a test case if you like. me at tomfulton.net.

Sebastiaan Janssen 24 Nov 2015, 10:41:37

@tomnf Awesome, our emails are here, I'd like a copy too please: http://umbraco.com/about-us/team (preferably a WeTransfer/Dropbox link, attachment is likely too large).

Claus Jensen 24 Nov 2015, 10:50:06

@tomnf We will distribute internally whenever you submit something :)

Claus Jensen 30 Nov 2015, 17:26:17

I've debugged this and the reason why it is happening is due to the 6.0.2 migration UpdatePropertyTypesAndGroups making use of the PropertyTypeDto to which we added UniqueId in 7.3.0.

I have no idea why this didn't cause any errors when I tested 4.9/4.11 to 7.3 - maybe because I did it with relatively clean installs?

The stuff happening in the 6.0.2 migration was causing the mapper to try to map the PropertyTypeDto type to the database table, which at this point has not been updated to support the new added UniqueId property. I have changed this migration to make use of a dynamic instead, to avoid using the actual type which is not really valid/compatible at this point.

The reason why this worked with a partial upgrade from 4.x to 7.2.x and then to 7.3.0 is because the migration causing the error will not be run twice - and when running the migration with 7.2.x code, the property causing the problem had not been added to the type yet.

Claus Jensen 30 Nov 2015, 17:29:10

PR here: https://github.com/umbraco/Umbraco-CMS/pull/934

Testing: Make sure a 4.9/4.11.8 site can be upgraded directly to 7.3.3 without having to do a 7.0.0/7.2.8 upgrade in the process.

Claus Jensen 30 Nov 2015, 17:36:15

and @tomnf just fyi - I've successfully tested that the upgrade now works on the site/db you sent us.

Thank you very much for your help on this issue!

Also - if you did do the upgrade by using the workaround with upgrading to anything between 6.0.2 - 7.2.8 - and then to 7.3.0 afterwards, your site was upgraded just fine. Nothing is broken and there's no need to attempt to re-upgrade or anything (this also applies for @Lokesh)

Tom Fulton 02 Dec 2015, 00:10:02

@Claus.Jensen FYI, I tested (albiet on the same database as you) against the latest from dev-v7 and it looks good from here too. Nice work! I did just have to run [http://issues.umbraco.org/issue/U4-5673 this fix] first but it sounds like that's expected.

Interesting fix with dynamic, good to know for future migrations!

Claus Jensen 02 Dec 2015, 06:12:33

@tomnf Sounds great!

Ahh yes - I wasn't really sure where that thing came from so I had manually taken that out beforehand - sorry for not mentioning that ;)

I'm not really a fan of using dynamic too much, but unfortunately it's the prettiest way of getting around the issue here since we really don't have a way of using the "old" type.

Lokesh kumar Chippada 04 Dec 2015, 14:09:16

@Claus.Jensen Thanks for fixing it..

Upgrade is smooth :)

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 7.3.0, 7.3.1

Due in version: 7.3.3

Sprint: Sprint 4

Story Points: