U4-4463 - Upgrade 6.2.0 > 7.1.0 fails

Created by Sebastiaan Janssen 18 Mar 2014, 11:47:01 Updated by Shannon Deminick 19 Mar 2014, 02:26:53

The foreign key constraint does not exist. [ FK_cmsMacroProperty_cmsMacroPropertyType ]

I think it's the AlterCmsMacroPropertyTable migration that fails. Delete.ForeignKey().FromTable("cmsMacroProperty").ForeignColumn("macroPropertyType").ToTable("cmsMacroPropertyType").PrimaryColumn("id"); maybe check if the contraint exists first?

Full error: 2014-03-18 12:38:57,690 [25] ERROR Umbraco.Core.DatabaseContext - [Thread 12] Database configuration failed System.Data.SqlServerCe.SqlCeException (0x80004005): The foreign key constraint does not exist. [ FK_cmsMacroProperty_cmsMacroPropertyType ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args) 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()


Sebastiaan Janssen 18 Mar 2014, 12:02:44

Okay, commenting that one out and progressively commenting out other failing FK updates I eventually get to AddIndexToCmsMacroPropertyTable and that just fails with a:

There was an error parsing the query. [ Token line number = 1,Token line offset = 12,Token in error = FROM ]

So I think we need to do some more testing on SQL CE. The one thing that would help is if the log would reflect the final query being executed so we can actually see what it's trying to do.

Shannon Deminick 18 Mar 2014, 12:06:16

I'll have a look, i think that is actually one migration i didn't test!

Debugging these can be annoying, I've been knee deep in it so should be a quick fix if you wanna spend time elsewhere. We do log the sql statements so should show the last one try to execute.

Sebastiaan Janssen 18 Mar 2014, 12:06:19

Must add that this is 6.2.0 beta. I'll retry with a clean current build!

Shannon Deminick 18 Mar 2014, 12:07:17

Ok cool. Since we are running migrations for two versions, i might have to target some migrations better. Let me know how you go.

Sebastiaan Janssen 18 Mar 2014, 13:34:34

Unfortunately got the same error!

Shannon Deminick 18 Mar 2014, 23:12:15

Yeah, the statement was changed from:




in order to be compatible with MySql. For whatever reason the new (compatible) way doesn't generate the correct foreign key name. Argh!!

Priority: Major

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 7.1.0

Due in version: 7.1.0


Story Points: