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?

Solution 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.

Solution 2

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

Solution 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.....

Comments

[bricelam]

Can you each run Update-Database -Script -SourceMigration $InitialDatabase -Force and compare the two outputs?

[spender]

@Brice, can you tell me where this should be run. When run on my dev machine, this yields no output (I suppose because the database was created from this revision of the code). I assume that the best place to run such output would be in the following situation: Delete database on server, colleague publishes his version, we allow the database to be built, then I upload my version, and we script the output of database-update. This should reveal what's going on right? How would I go about running database-update on the remote server? Have I understood correctly?

[spender]

Please bear in mind, I've never used powershell in my life, so would require a little hand holding to figure out what to run remotely (if this is indeed what I need to do).

[bricelam]

Just run that command on each of your local dev boxes (inside VS package manager console). That command won't hit the database; it just tells what each machine is trying to do. Comparing the outputs may give more clues as to what exactly is going wrong.

[spender]

Ah, ok. Actually, we're currently running our own copies of the database so this would not be too revealing... however, it shouldn't be too difficult to switch to a common database. I'll let you know the result.

[spender]

@Brice, I have added significantly more detail to the question with more info for you.

[bricelam]

Thank you for your thorough investigation, @spender. Your workaround is exactly what I would suggest. I'll make sure we (the Entity Framework team) have a bug filed for this.

[spender]

@Brice. Good one. Thanks for spending time trawling SO for these sorts of thing. Nice to be in contact with the source! Would it help to post the class defs for that create this relationship?

[bricelam]

I think we've got enough info to repro. Thanks though.

[Yves M.]

We also do SCM. Never came accross this issue. But we only deploy from our Build-Server to the integration testing server. IMHO it does not make sense to build localy on a dev. instance and then deploy to a server...

[spender]

Ah yes, agreed... a build server would make all this go away. Another day, another project!

[Kenneth Cochran]

Since it resolved your original question you should move your work around to an answer and mark it accepted. The question of why this behaves differently on different machines is technically a separate question.

[spender]

Bang. There goes the data. Of course setting AutomaticMigrationDataLossAllowed to true makes the problem go away. It also makes existing data go away too. Useless. Given that my supplied code in my question explicitly explains "DO NOT REMOVE THIS LINE" for the line AutomaticMigrationDataLossAllowed=false, this is a bad answer. -1.

[Alok]

as this is not a real world solution for existing old databases. i liked the accepted answer better.