Code first DbMigrator causes error when building from different machines

Question

We have a project under SCM. When I build it from my machine and publish to a remote server via msdeploy, everything works fine.

When my colleague tries the same thing with the same project, freshly pulled from SCM, on the remote server entity framework 4.3.1 DbMigrator throws:

Automatic migration was not applied because it would result in data loss.

As it turns out, it seems that the person who makes the initial publish to the remote server is the "winner". If we drop the database on the remote server, then my colleaugue can publish and I get locked out. My publications result in the same error above.

The config for DbMigrator looks something like this:

        var dbMgConfig = new DbMigrationsConfiguration()
        {
            AutomaticMigrationsEnabled = true,
            //***DO NOT REMOVE THIS LINE, 
            //DATA WILL BE LOST ON A BREAKING SCHEMA CHANGE,
            //TALK TO OTHER PARTIES INVOLVED IF THIS LINE IS CAUSING PROBLEMS    
            AutomaticMigrationDataLossAllowed=false,
            //***DO NOT REMOVE THIS LINE,
            ContextType = typeof(TPSContext),
            MigrationsNamespace = "TPS.Migrations",
            MigrationsAssembly = Assembly.GetExecutingAssembly()
        };

I assume this has something to do with the new table __MigrationHistory and the nasty looking long hex string stored in its rows.

I don't want to take full responsibilty for publishing to live. What can I look out for?

Answer 1

We changed our code from:

        dbMgConfig.AutomaticMigrationDataLossAllowed = false;
        var mg = new DbMigrator(dbMgConfig);
        mg.Update(null);

to

        dbMgConfig.AutomaticMigrationDataLossAllowed = true;
        var mg = new DbMigrator(dbMgConfig);
        var scriptor = new MigratorScriptingDecorator(mg);
        string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);
        throw new Exception(script);

so that we could observe what changes DbMigrator is attempting on the remote server.

In the case outlined at the start of this question (i.e. colleague makes upload which creates database, followed by me making upload generated from the same source on a different machine), the following SQL statements are generated:

ALTER TABLE [GalleryImages] DROP CONSTRAINT [FK_GalleryImages_Galleries_Gallery_Id]
ALTER TABLE [GalleryImages] DROP CONSTRAINT [FK_GalleryImages_Images_Image_Id]
ALTER TABLE [UserLightboxes] DROP CONSTRAINT [FK_UserLightboxes_Users_User_Id]
ALTER TABLE [UserLightboxes] DROP CONSTRAINT [FK_UserLightboxes_Lightboxes_Lightbox_Id]
ALTER TABLE [ImageLightboxes] DROP CONSTRAINT [FK_ImageLightboxes_Images_Image_Id]
ALTER TABLE [ImageLightboxes] DROP CONSTRAINT [FK_ImageLightboxes_Lightboxes_Lightbox_Id]
DROP INDEX [IX_Gallery_Id] ON [GalleryImages]
DROP INDEX [IX_Image_Id] ON [GalleryImages]
DROP INDEX [IX_User_Id] ON [UserLightboxes]
DROP INDEX [IX_Lightbox_Id] ON [UserLightboxes]
DROP INDEX [IX_Image_Id] ON [ImageLightboxes]
DROP INDEX [IX_Lightbox_Id] ON [ImageLightboxes]
CREATE TABLE [ImageGalleries] (
   [Image_Id] [int] NOT NULL,
   [Gallery_Id] [int] NOT NULL,
   CONSTRAINT [PK_ImageGalleries] PRIMARY KEY ([Image_Id], [Gallery_Id])
)
CREATE TABLE [LightboxImages] (
   [Lightbox_Id] [int] NOT NULL,
   [Image_Id] [int] NOT NULL,
   CONSTRAINT [PK_LightboxImages] PRIMARY KEY ([Lightbox_Id], [Image_Id])
)
CREATE TABLE [LightboxUsers] (
   [Lightbox_Id] [int] NOT NULL,
   [User_Id] [int] NOT NULL,
   CONSTRAINT [PK_LightboxUsers] PRIMARY KEY ([Lightbox_Id], [User_Id])
)
CREATE INDEX [IX_Image_Id] ON [ImageGalleries]([Image_Id])
CREATE INDEX [IX_Gallery_Id] ON [ImageGalleries]([Gallery_Id])
CREATE INDEX [IX_Lightbox_Id] ON [LightboxImages]([Lightbox_Id])
CREATE INDEX [IX_Image_Id] ON [LightboxImages]([Image_Id])
CREATE INDEX [IX_Lightbox_Id] ON [LightboxUsers]([Lightbox_Id])
CREATE INDEX [IX_User_Id] ON [LightboxUsers]([User_Id])
DROP TABLE [GalleryImages]
DROP TABLE [UserLightboxes]
DROP TABLE [ImageLightboxes]
ALTER TABLE [ImageGalleries] ADD CONSTRAINT [FK_ImageGalleries_Images_Image_Id] FOREIGN KEY ([Image_Id]) REFERENCES [Images] ([Id]) ON DELETE CASCADE
ALTER TABLE [ImageGalleries] ADD CONSTRAINT [FK_ImageGalleries_Galleries_Gallery_Id] FOREIGN KEY ([Gallery_Id]) REFERENCES [Galleries] ([Id]) ON DELETE CASCADE
ALTER TABLE [LightboxImages] ADD CONSTRAINT [FK_LightboxImages_Lightboxes_Lightbox_Id] FOREIGN KEY ([Lightbox_Id]) REFERENCES [Lightboxes] ([Id]) ON DELETE CASCADE
ALTER TABLE [LightboxImages] ADD CONSTRAINT [FK_LightboxImages_Images_Image_Id] FOREIGN KEY ([Image_Id]) REFERENCES [Images] ([Id]) ON DELETE CASCADE
ALTER TABLE [LightboxUsers] ADD CONSTRAINT [FK_LightboxUsers_Lightboxes_Lightbox_Id] FOREIGN KEY ([Lightbox_Id]) REFERENCES [Lightboxes] ([Id]) ON DELETE CASCADE
ALTER TABLE [LightboxUsers] ADD CONSTRAINT [FK_LightboxUsers_Users_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [Users] ([Id]) ON DELETE CASCADE
CREATE TABLE [__MigrationHistory] (
   [MigrationId] [nvarchar](255) NOT NULL,
   [CreatedOn] [datetime] NOT NULL,
   [Model] [varbinary](max) NOT NULL,
   [ProductVersion] [nvarchar](32) NOT NULL,
   CONSTRAINT [PK___MigrationHistory] PRIMARY KEY ([MigrationId])
)
BEGIN TRY
   EXEC sp_MS_marksystemobject '__MigrationHistory'
END TRY
BEGIN CATCH
END CATCH
INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201203030113082_AutomaticMigration', '2012-03-03T01:13:08.986Z', 0x[removedToShortenPost], '4.3.1')

As can be seen, the reason why DbMigrator is throwing is because it is attempting to rename 3 tables that are used for joining many2many relationships by inverting the names of tables that they bridge, eg GalleryImages to ImageGalleries or UserLightboxes to LightboxUsers.

A WORKAROUND

This looks like a bug in EF 4.3 where the naming of "association" tables appears to be of an indeterminate order. Given that the ordering of names for these sorts of tables appears to be undefined/indeterminate, we approached this from a different angle, using the fluent API to force EF to use the consistent naming across builds from different machines:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder
            .Entity<Gallery>()
            .HasMany(p => p.Images)
            .WithMany(p => p.Galleries)
            .Map(c =>
            {
                c.MapLeftKey("Gallery_Id");
                c.MapRightKey("Image_Id");
                c.ToTable("GalleryImages");
            });
        modelBuilder
            .Entity<User>()
            .HasMany(p => p.Lightboxes)
            .WithMany(p => p.Users)
            .Map(c =>
            {
                c.MapLeftKey("User_Id");
                c.MapRightKey("Lightbox_Id");
                c.ToTable("UserLightboxes");
            });
        modelBuilder
            .Entity<Image>()
            .HasMany(p => p.Lightboxes)
            .WithMany(p => p.Images)
            .Map(c =>
            {
                c.MapLeftKey("Image_Id");
                c.MapRightKey("Lightbox_Id");
                c.ToTable("ImageLightboxes");
            });
    }

With this in place, the error now goes away.

Answer 2

 public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        } 

Answer 3

I get the same error so I generated a script and ran it in Query Analyzer. It turns out to be a key length problem:

Warning! The maximum key length is 900 bytes. The index 'PK_dbo.__MigrationHistory' has maximum length of 1534 bytes. For some combination of large values, the insert/update operation will fail.

It looks like the EntityFramework team is aware of it:

http://entityframework.codeplex.com/workitem/1216

Not sure what problems this will cause.....

Answer 4

I've also encountered this problem. Oddly, the table in question contains absolutely no data, i.e. it's empty, something that Code First doesn't even seem to check when reporting that if it applied the migrations, data loss would occur.

Answer 5

I just had a really weird error similar to this for Entity Framework 6.2.0.

Configuration.cs:

public class Configuration : DbMigrationsConfiguration<ApplicationDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        AutomaticMigrationDataLossAllowed = false;
    }
...
}

The code below caused a StackOverflowException on one machine but worked fine on another.

var migrator = new DbMigrator(new Configuration());
if (migrator.GetPendingMigrations().Any())
{
    migrator.Update();
}

Solved it by upgrading like this instead:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationDbContext, Configuration>());