migrations multiple versions - Extending Umbraco and using the API

Nik 1593 posts 7151 karma points MVP 6x c-trib

Oct 20, 2017 @ 09:38

Migrations - Multiple versions

Extending Umbraco and using the API

Umbraco 7

Hi all,

Hoping someone with Migrations experience can help here.

I created a migration that adds 3 tables to the Umbraco database, these are added using code based around Sebastian's really helpful blog post here:

https://cultiv.nl/blog/using-umbraco-migrations-to-deploy-changes/

The code takes into account the various comments in the thread, particularly from Shannon about the best way to update the database etc.

This all worked fine and I was able to deploy version 1.0.0 with no problems.

I then realised, damn I need a couple of additional columns on one of the tables so I created migration version 1.0.1 (The code is very similar to this for this next migration https://our.umbraco.org/forum/umbraco-cloud/75800-guide-for-custom-tables-in-uaas)

On a site where version 1.0.0 was already deployed there are no issues, however when version 1.0.0 is missing and the migration process has to apply both, I'm getting an error about duplicate columns called ID.

The code for version 1.0.1 looks like this:

public override void Up()
    {
        try
        {
            Logger.Info(GetType(), "Applying FW SMS migration Up method");
            if (CheckIfColumnExists("smsSubscriber", "SubscribedOn") == false)
                Alter.Table("smsSubscriber").AddColumn("SubscribedOn").AsDateTime().WithDefaultValue(DateTime.Now);
        }
        catch (Exception e)
        {
            Logger.Error(GetType(), $"An error occured when trying to apply the migration - {typeof(SmsSubscriberTableUpdateMigration)}", e);
        }
    }

The exception that is being thrown is being caught by the following code in my Migration Runner startup code:

try
{
    migrationsRunner.Execute(applicationContext.DatabaseContext.Database);
}
catch (Exception e)
{
    LogHelper.Error<MigrationEvents>("Error running FW-SMS migration", e);
}

And not by the try catch in my Up method so I'm a bit confused as to why I would be getting this error. Anyone got any ideas?

Additional Info Umbraco 7.7.3 Umbraco Cloud Windows 7 VS 2015

Cheers

Copy Link

Matt Brailsford 4124 posts 22215 karma points MVP 9x c-trib

Oct 20, 2017 @ 09:42

What's in your 1.0.0 migration? You didn't update it did you? Also, can you show your actual migration definition (ie, the attribute on your class).

Copy Link

Nik 1593 posts 7151 karma points MVP 6x c-trib

Oct 20, 2017 @ 09:47

Here you go Matt,

Migration 1.0.0:

Definition

[Migration("1.0.0", 1, "FW-SMS")]

Up method

try{
  var tables = SqlSyntax.GetTablesInSchema(Context.Database).ToList();
  AddNewTables(tables);

} catch (exception e)

AddNewTables

private bool AddNewTables(List<string> tables)
{
    var updated = false;
    if (!tables.InvariantContains("smsSubscriber"))
    {
        Create.Table<SmsSubscriber>();
        updated = true;
    }

    if (!tables.InvariantContains("smsDetail"))
    {
        Create.Table<SmsDetail>();
        updated = true;
    }

    if (!tables.InvariantContains("smsSendDetail"))
    {
        Create.Table<SmsSendDetails>();
         updated = true;
    }

    return updated;
}

Migration 1.0.1

Definition

[Migration("1.0.1", 1, "FW-SMS")]

Copy Link

Matt Brailsford 4124 posts 22215 karma points MVP 9x c-trib

Oct 20, 2017 @ 09:51

And what's the exact error stack trace?

Copy Link

Nik 1593 posts 7151 karma points MVP 6x c-trib

Oct 20, 2017 @ 09:54

2017-10-20 10:17:07,160 [P8560/D41/T18] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (55aff5d4). System.Data.SqlServerCe.SqlCeException (0x80004005): A column ID occurred more than once in the specification. 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.<>cDisplayClass2_0.0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 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) 2017-10-20 10:17:40,158 [P8560/D41/T18] ERROR Umbraco.Forms.Web.Migrations.MigrationEvents - Error running FW-SMS migration System.Data.SqlServerCe.SqlCeException (0x80004005): A column ID occurred more than once in the specification. 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__DisplayClass2_0.<ExecuteNonQueryWithRetry>b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 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)

Copy Link

Matt Brailsford 4124 posts 22215 karma points MVP 9x c-trib

Oct 20, 2017 @ 09:57

Ok, so it's not actually the "ID" column, just that you are trying to add a column with the same ID, so i'm guessing your CheckIfColumnExists may not be running properly. Maybe debug into that and see if it's returning a false negative?

Copy Link

Nik 1593 posts 7151 karma points MVP 6x c-trib

Oct 20, 2017 @ 10:00

I wonder, I've updated the POCO model, which is what is called during the create to include the column (i.e. this results in the column being created when 1.0.0 is called) so if it trying to do the migrations transactionally. So when the Up method checks, the column isn't there so it plans the Alter script.. but when it actually executes the SQL (which seems to be later on time wise), the column does then exist.

Copy Link

Matt Brailsford 4124 posts 22215 karma points MVP 9x c-trib

Oct 20, 2017 @ 10:03

My thoughts too. But even within a transaction, it should report the column exists as the script should be within the same transaction, but then there could be a bug, or i could be wrong. But it sounds like that's the problem area.

Like you say, because your property is on the model, the 1.0.0 now has it as part of the creation process, but the column check in 1.0.1 "should" catch that, but it sounds like it's not for whatever reason.

Copy Link

Nik 1593 posts 7151 karma points MVP 6x c-trib

Oct 20, 2017 @ 10:10

Not sure where your last message has gone Matt, seems to have disappeared. Looking into the logs further, I can see the Up's run well before the actual SQL code is executed to create/alter the database. The check for the column uses the following code:

protected bool CheckIfColumnExists(string tableName, string columnName)
{
    var columns = SqlSyntax.GetColumnsInSchema(Context.Database);
    var doesColumnExist =
        columns.Any(
            x =>
                string.Equals(x.TableName, tableName) &&
                string.Equals(x.ColumnName, columnName)
        );

    return doesColumnExist;
}

So I don't think it becomes part of the transactional SQL code but is simply executed in the Up method at the point that is running, so it's results are "true" at that point in time because the table doesn't even exist yet.

Copy Link

Matt Brailsford 4124 posts 22215 karma points MVP 9x c-trib

Oct 20, 2017 @ 10:19

Interesting! However i'm pretty sure I've done similar and not had problems (although maybe I've just never run the complete sweet of migrations from fresh, so you should very well be right.

Copy Link

Nik 1593 posts 7151 karma points MVP 6x c-trib

Oct 20, 2017 @ 10:23

Matt, you are a star, I've figured out what it is.

Well more a preventative check. I'm only checking that the column doesn't exist, but I'm not checking the table does. If I put the check on the table existing as well then when it applies this migration it won't do anything if the table is yet to be created (as that should be created in migration 1.0.0.

So if that isn't fully executed yet then there is no point in trying to execute the alter (and the alter is actually no longer needed because it's been put in by the create when that executes)

Copy Link

Matt Brailsford 4124 posts 22215 karma points MVP 9x c-trib

Oct 20, 2017 @ 12:17

Looking at some of the built in umbraco migrations, it would seem checking the table exists too is the right approach:

https://github.com/umbraco/Umbraco-CMS/blob/9badb35c054ecc91630b69b1b6753c78427cb4a6/src/Umbraco.Core/Persistence/Migrations/Upgrades/TargetVersionSevenThreeZero/AddUserColumns.cs

Copy Link

is working on a reply...

Flag this post as spam?

Migrations - Multiple versions