DbMigrator code first causes an error when building from different machines

Question

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

When my colleague tries to do the same with the same project that was recently pulled from SCM, on the platform of the remote server entity 4.3.1 DbMigrator throws:

Automatic migration has not been applied, as this will lead to data loss.

As it turns out, it seems that the person who makes the initial publication on the remote server is the “winner”. If we drop the database on a remote server, then my colleague can publish and block. My publications lead to the same error above.

The configuration 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 guess this has something to do with the new __MigrationHistory table and the nasty long hexadecimal string stored in its rows.

I do not want to be fully responsible for publishing live. What can I watch?

Answer 1

We changed our code to:

  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 can observe what DbMigrator changes are DbMigrator made on the remote server.

In the case described at the beginning of this question (i.e., a colleague does a download that creates a database, and then I do a download generated from the same source on another machine), the following SQL expressions 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 you can see, the reason DbMigrator throws DbMigrator is because it tries to rename 3 tables, which are used to join many many relationships by inverting the names of the tables they join, for example GalleryImages to ImageGalleries or UserLightboxes to LightboxUsers .

WORK TIME

This looks like a bug in EF 4.3, where the names of the names of the “association” tables appear to be of an undefined order. Given that the ordering of names for these types of tables looks undefined / undefined, we approached this from a different angle using a free API to force EF to use consistent naming between rows 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"); }); } 

In this case, the error now disappears.

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. Turns out this is a key length issue:

Attention! The maximum key length is 900 bytes. The PK_dbo .__ MigrationHistory index has a maximum length of 1534 bytes. For some combination of large values, the insert / update operation will fail.

It appears that the EntityFramework team knows about this:

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

I don’t know what problems this will cause .....

Answer 4

I also ran into this problem. Oddly enough, in the table under consideration there are absolutely no data, i.e. Empty, something that Code First does not seem to even check, saying that if it applies migration, data loss will occur.

Answer 5

I just had a very strange error similar to this one for Entity Framework 6.2.0.

Configuration.cs:

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

The code below raises a StackOverflowException on one computer, but works fine on another.

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

Solved this by updating as follows:

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