Description:
Using EF 5 Code First with automatic migrations enabled.
Adding a new POCO class and relating it to an existing one.
Running Update-Database -Verbose, fails as follows:
Using StartUp project 'MigrationTest'.
Using NuGet project 'MigrationTest'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'migrationtest' (DataSource: 127.0.0.1, Provider: MySql.Data.MySqlClient, Origin: Configuration).
No pending code-based migrations.
Applying automatic migration: 201311011025410_AutomaticMigration.
create table `People` (`Id` int not null auto_increment primary key ,`Name` longtext) engine=InnoDb auto_increment=0
alter table `Blogs` add column `Owner_Id` int
alter table `dbo.Blogs` add constraint `FK_dbo.Blogs_dbo.People_Owner_Id` foreign key (`Owner_Id`) references `dbo.People` ( `Id`)
MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'migrationtest.dbo.blogs' doesn't exist
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
Table 'migrationtest.dbo.blogs' doesn't exist
How to repeat:
1. VS2012, new project, C#, Console Application, named MigrationTest
2. Set target framework to .NET Framework 4.5
3. PM console: Install-Package EntityFramework -Version 5.0.0
4. PM console: Install-package MySql.Data (installs 6.7.4)
5. PM console: Install-package MySql.Data.Entities (installs MySQL.Data.Entities 6.7.4.1)
6. In app.config: remove <entityFramework> element
7. In app.config: add
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" />
</DbProviderFactories>
</system.data>
8. Add a file Model.cs with the following content
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MigrationTest
{
public class Blog
{
public int BlogId { get; set; }
public string Name { get; set; }
}
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
}
}
9. Add the following code to your Main function
using (var db = new BlogContext())
{
db.Blogs.Add(new Blog { Name = "Another Blog " });
db.SaveChanges();
foreach (var blog in db.Blogs)
{
Console.WriteLine(blog.Name);
}
}
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
10. In app.config: add connectionString to your MySql Db
<connectionStrings>
<add name="BlogContext" connectionString="server=127.0.0.1;database=migrationtest;uid=root;pwd=somepassword" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
11. Run the application, Db-table is created as expected in Db migrationtest
12. PM console: Enable-Migrations -EnableAutomaticMigrations
13. In the constructor Configuration() add the following call:
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
14. In Model.cs, modify code by adding class, relating it to the existing one, and adding to context, giving following:
public class Blog
{
public int BlogId { get; set; }
public string Name { get; set; }
virtual public Person Owner { get; set; }
}
public class Person
{
public Person()
{
Blogs = new List<Blog>();
}
public int Id { get; set; }
public string Name { get; set; }
public virtual List<Blog> Blogs { get; set; }
}
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Person> People{ get; set; }
}
15. PM console: Update-Database -Verbose, the following output is obtained
Using StartUp project 'MigrationTest'.
Using NuGet project 'MigrationTest'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'migrationtest' (DataSource: 127.0.0.1, Provider: MySql.Data.MySqlClient, Origin: Configuration).
No pending code-based migrations.
Applying automatic migration: 201311011025410_AutomaticMigration.
create table `People` (`Id` int not null auto_increment primary key ,`Name` longtext) engine=InnoDb auto_increment=0
alter table `Blogs` add column `Owner_Id` int
alter table `dbo.Blogs` add constraint `FK_dbo.Blogs_dbo.People_Owner_Id` foreign key (`Owner_Id`) references `dbo.People` ( `Id`)
MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'migrationtest.dbo.blogs' doesn't exist
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
Table 'migrationtest.dbo.blogs' doesn't exist
Suggested fix:
The following added code works as a workaround solution meanwhile.
Add the following class in Configuration.cs
public class MyOwnMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
{
protected override MigrationStatement Generate(AddForeignKeyOperation addForeignKeyOperation)
{
addForeignKeyOperation.PrincipalTable = addForeignKeyOperation.PrincipalTable.Replace("dbo.", "");
addForeignKeyOperation.DependentTable = addForeignKeyOperation.DependentTable.Replace("dbo.", "");
MigrationStatement ms = base.Generate(addForeignKeyOperation);
return ms;
}
}
Modify sql generator:
SetSqlGenerator("MySql.Data.MySqlClient", new MyOwnMySqlMigrationSqlGenerator());
Description: Using EF 5 Code First with automatic migrations enabled. Adding a new POCO class and relating it to an existing one. Running Update-Database -Verbose, fails as follows: Using StartUp project 'MigrationTest'. Using NuGet project 'MigrationTest'. Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Target database is: 'migrationtest' (DataSource: 127.0.0.1, Provider: MySql.Data.MySqlClient, Origin: Configuration). No pending code-based migrations. Applying automatic migration: 201311011025410_AutomaticMigration. create table `People` (`Id` int not null auto_increment primary key ,`Name` longtext) engine=InnoDb auto_increment=0 alter table `Blogs` add column `Owner_Id` int alter table `dbo.Blogs` add constraint `FK_dbo.Blogs_dbo.People_Owner_Id` foreign key (`Owner_Id`) references `dbo.People` ( `Id`) MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'migrationtest.dbo.blogs' doesn't exist at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() Table 'migrationtest.dbo.blogs' doesn't exist How to repeat: 1. VS2012, new project, C#, Console Application, named MigrationTest 2. Set target framework to .NET Framework 4.5 3. PM console: Install-Package EntityFramework -Version 5.0.0 4. PM console: Install-package MySql.Data (installs 6.7.4) 5. PM console: Install-package MySql.Data.Entities (installs MySQL.Data.Entities 6.7.4.1) 6. In app.config: remove <entityFramework> element 7. In app.config: add <system.data> <DbProviderFactories> <remove invariant="MySql.Data.MySqlClient" /> <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" /> </DbProviderFactories> </system.data> 8. Add a file Model.cs with the following content using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; using System.Text; using System.Threading.Tasks; namespace MigrationTest { public class Blog { public int BlogId { get; set; } public string Name { get; set; } } public class BlogContext : DbContext { public DbSet<Blog> Blogs { get; set; } } } 9. Add the following code to your Main function using (var db = new BlogContext()) { db.Blogs.Add(new Blog { Name = "Another Blog " }); db.SaveChanges(); foreach (var blog in db.Blogs) { Console.WriteLine(blog.Name); } } Console.WriteLine("Press any key to exit..."); Console.ReadKey(); 10. In app.config: add connectionString to your MySql Db <connectionStrings> <add name="BlogContext" connectionString="server=127.0.0.1;database=migrationtest;uid=root;pwd=somepassword" providerName="MySql.Data.MySqlClient" /> </connectionStrings> 11. Run the application, Db-table is created as expected in Db migrationtest 12. PM console: Enable-Migrations -EnableAutomaticMigrations 13. In the constructor Configuration() add the following call: SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); 14. In Model.cs, modify code by adding class, relating it to the existing one, and adding to context, giving following: public class Blog { public int BlogId { get; set; } public string Name { get; set; } virtual public Person Owner { get; set; } } public class Person { public Person() { Blogs = new List<Blog>(); } public int Id { get; set; } public string Name { get; set; } public virtual List<Blog> Blogs { get; set; } } public class BlogContext : DbContext { public DbSet<Blog> Blogs { get; set; } public DbSet<Person> People{ get; set; } } 15. PM console: Update-Database -Verbose, the following output is obtained Using StartUp project 'MigrationTest'. Using NuGet project 'MigrationTest'. Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Target database is: 'migrationtest' (DataSource: 127.0.0.1, Provider: MySql.Data.MySqlClient, Origin: Configuration). No pending code-based migrations. Applying automatic migration: 201311011025410_AutomaticMigration. create table `People` (`Id` int not null auto_increment primary key ,`Name` longtext) engine=InnoDb auto_increment=0 alter table `Blogs` add column `Owner_Id` int alter table `dbo.Blogs` add constraint `FK_dbo.Blogs_dbo.People_Owner_Id` foreign key (`Owner_Id`) references `dbo.People` ( `Id`) MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'migrationtest.dbo.blogs' doesn't exist at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() Table 'migrationtest.dbo.blogs' doesn't exist Suggested fix: The following added code works as a workaround solution meanwhile. Add the following class in Configuration.cs public class MyOwnMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator { protected override MigrationStatement Generate(AddForeignKeyOperation addForeignKeyOperation) { addForeignKeyOperation.PrincipalTable = addForeignKeyOperation.PrincipalTable.Replace("dbo.", ""); addForeignKeyOperation.DependentTable = addForeignKeyOperation.DependentTable.Replace("dbo.", ""); MigrationStatement ms = base.Generate(addForeignKeyOperation); return ms; } } Modify sql generator: SetSqlGenerator("MySql.Data.MySqlClient", new MyOwnMySqlMigrationSqlGenerator());