MySQL Bugs: #69488: Cannot work Entity Framework data migrations.

Bug #69488	Cannot work Entity Framework data migrations.
Submitted:	17 Jun 2013 7:28	Modified:	29 Aug 2016 11:36
Reporter:	Mikhail Filippov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.7.3, 6.9.9	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	entityframework

Description:
After migration enabling with MySql Connector 6.7.3 i'm try execute Update-Database from PAckage manager console in VS2012. I'm get error:
Update-Database -Verbose
Using StartUp project 'MySqlMigrations'.
Using NuGet project 'MySqlMigrations'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'todo' (DataSource: localhost, Provider: MySql.Data.MySqlClient, Origin: Configuration).
No pending code-based migrations.
Applying automatic migration: 201306170720086_AutomaticMigration.
alter table `dbo.Todoes` add column `CreateTime` datetime
MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'todo.dbo.todoes' 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 'todo.dbo.todoes' doesn't exist

How to repeat:
Create project with one data model class:
 public class Todo
    {
        public int Id { get; set; }
        public string Text { get; set; }
        public DateTime? DueDate { get; set; }
        public int Priority { get; set; }
    }
Run project with:
 Database.SetInitializer(new DropCreateDatabaseAlways<TodoContext>());
Enable-Migrations from Package Manager Console.
Set Sql Generator:
public Configuration()
        {
            SetSqlGenerator("MySql.Data.MySqlClient", new MySqlMigrationSqlGenerator());
            AutomaticMigrationsEnabled = true;
        }
Add new field to data model:
public class Todo
    {
        public int Id { get; set; }
        public string Text { get; set; }
        public DateTime? CreateTime { get; set; }
        public DateTime? DueDate { get; set; }
        public int Priority { get; set; }
    }
Run Update-Database from Package Manager Console.

Sample Project.

Attachment: MySqlMigrations.zip (application/x-zip-compressed, text), 2.81 MiB.

Hi Mikhail,

Thanks a lot for your interest in this product and for your feedback. This is not a bug but a misconfiguration on the Migrations configuration method. On EF 5 one of the changes is that they added a new Code Generator class to customize the generated code that handles the migrations (CSharp Code). So you also need to specify the code generator to use for MySql,  here is an example:

public Configuration()
    {
      CodeGenerator = new MySqlMigrationCodeGenerator();
      AutomaticMigrationsEnabled = false;  //or true
      SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
    }

Please give it a try and let us know if you had any other issue.

Thanks,

i have same bug (connector net 6.7.3 beta):

i add: 
CodeGenerator = new MySqlMigrationCodeGenerator();

but not help (migrations not work - same error - table not found)

small code:

    public sealed class InstanceGlobalConfiguration : BaseDbMigrationConfiguration<InstanceGlobalContext>
    {
        public InstanceGlobalConfiguration()
        {
            Configure();
        }
    }

    public class BaseDbMigrationConfiguration<T> : DbMigrationsConfiguration<T>
        where T : DbContext
    {
        public void Configure()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
            CodeGenerator = new MySql.Data.Entity.MySqlMigrationCodeGenerator();
            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
        }
    }

            using (var instanceContext = new InstanceContext(_instanceConnectionString))
            {
                instanceContext.Database.CreateIfNotExists();
                instanceContext.Database.Initialize(true); //crash here
            }

Hi Boris,
We found a bug related to EF migrations that affects automatic migrations too. This bug has been addressed and the fix will be released in the next version of 6.7 which is 6.7.4 shortly. If you like to work with migrations and MySql you can use Connector/Net 6.6.5 (http://dev.mysql.com/downloads/connector/net/) version while this fix is publicly available, although this version will work only with EF 4.3.1.

Please let us know if you need any further information or if you have any question also you can post them at the forums (http://forums.mysql.com/list.php?38) of the community.

Thank you for this bug report.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Same problem. I found in the source code that in the class MySqlMigrationSqlGenerator when the alter statment is created, the object is not trimed as it is for the create statments.

Thank you

I can confirm this bug with 6.8.3 
with the -Script parameter i got the following sql  snippet:

"alter table `dbo.Activities` drop foreign key `FK_dbo.Activities_dbo.GroupRanges_GroupRange_ID`
alter table `dbo.Activities` drop foreign key `FK_dbo.Activities_dbo.PriceRanges_PriceRange_ID`
alter table `Activities` drop index `IX_GroupRange_ID`
... "
which correspond to the
            DropForeignKey("dbo.Activities", "GroupRange_ID", "dbo.GroupRanges");
            DropForeignKey("dbo.Activities", "PriceRange_ID", "dbo.PriceRanges");

calls in the DbMigration. I got around this problem by manually correcting the sql script (removing the "dbo.") and executing it manually.
This also confirms the last comment.

Hello Ma Di,

Thank you for your feedback.
I tried to reproduce the issue at my end using Connector/Net 6.9.8 and Visual Studio 2013 (C#.Net) but not seeing any issues in data migrations.
Please try with latest version of MySQL Connector/Net and let us know if you are still having the issue with repeatable 
test case (exact steps/sample project, connection string etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

I've still have this bug with Version 6.9.8.0 when removing tables with automatic migrations.

The remove the table 'WNSReceiver' the following statements a generated:

alter table `dbo.WNSReceiver` drop foreign key `FK_dbo.WNSReceiver_dbo.Channel_ChannelId`
alter table `WNSReceiver` drop index `IX_ChannelId`
alter table `WNSReceiver` drop index `IX_ReceiverId`
drop table `WNSReceiver`

Obviously the first statement to drop the foreign key is the cause of the exception.

Hello  Udo Schäfer,

Thank you for your feedback.
Could you please provide repeatable test case (exact steps/sample project, create table statements/database etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is a sample project to reproduce the "DROP FOREIGN KEY" problem.

Attachment: ConsoleApplication1.zip (application/zip, text), 30.36 KiB.

To run the sample you have to adjust the connection string in app.config!

Steps to reproduce:

1. Run the sample to create a first database version.
2. Remove the property "Parent" from the class "TestChild".
3 Run the sample again.

The generated SQL commands have a "dbo." prefix for the ALTER TABLE DROP FOREIGN KEY statements.
My sample code removes all such prefixes in the SqlGenerator class, but generates an assertion when the bug occurs.

Regards,
Udo

Hello Udo Schäfer,

Thank you for the bug feedback and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.