Bug #69649 'dbo' prefix still causing troubles with automatic migrations
Submitted: 2 Jul 2013 16:06 Modified: 18 May 2015 18:15
Reporter: Developpeurs CPAM Email Updates:
Status: Not a Bug Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.7.4 OS:Windows
Assigned to: Roberto Ezequiel Garcia Ballesteros CPU Architecture:Any

[2 Jul 2013 16:06] Developpeurs CPAM

I have updated to the new version of .Net Connector for MySQL (6.7.4) yesterday, which was supposed to suppress the 'dbo' prefix when using automatic migrations with code-first, but i still have the bug.

How to repeat:
When adding a one-to-zero-or-one relationship between an existing entity and a new entity, migration crashes.
[3 Jul 2013 19:35] Gabriela Martinez Sanchez
We might need to check the code of the configuration class which should look like this:

public Configuration()
            this.AutomaticMigrationsEnabled = false; // or true if you want to use automatic migrations
            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
            CodeGenerator = new MySql.Data.Entity.MySqlMigrationCodeGenerator();

And on the Web/App.Config

      <clear />
      <remove invariant="MySql.Data.MySqlClient"/>
      <add name="MySql.Data.MySqlClient" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,Version=, Culture=neutral,PublicKeyToken=c5687fc88969c44d"/>

Have you done that already and still having the issue ?

Thanks in advance for your feedback.
[4 Jul 2013 8:19] Developpeurs CPAM
I have a "User(1) - (0..1)Agent" relationShip, and am trying to make sure that the "User" table is getting the Agent_ID field.

UserID is integer, and AgentID is fixed string, i guess i made wrong data annotations because when i simply set the navigation property in each of entity like below, it's working fine.

For User class : Public Property Agent As Agent
For Agent class : <Required> Public Property User As User

But Agent table is getting the User_Id field.
[4 Jul 2013 8:30] Developpeurs CPAM
Well it's not working sorry for my last post.
[4 Jul 2013 8:36] Developpeurs CPAM
I had already set my App.Config and my Configuration class like you said, and when adding the two navigation properties (after a first database generation without them and then switchig to MigrateDatabaseToLatestVersion), like i wrote above, i get the error :

Table 'sitewebmvc.dbo.agent' doesn't exist

Thx for help and sorry for my incorrect pôst.
[4 Jul 2013 19:56] Gabriela Martinez Sanchez
I was able to reproduce the behavior you mentioned. And I can confirm that this is a bug in the automatic code first migration. The steps to reproduce is as follow:

1 - Define a model with 2 entities like the following:

 public class Task
        public int TaskId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }        

    public class Note
        public int NoteId { get; set; }
        public string Content { get; set; }     

2 - define a context that contains the two entities:

 public class AutomaticMigrationExampleContext : DbContext
        public DbSet<Task> Tasks { get; set; }

        public DbSet<Note> Notes { get; set; }

3 - Set up the configuration class for the migrations:

 public Configuration()
            this.AutomaticMigrationsEnabled = true;
            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
            CodeGenerator = new MySql.Data.Entity.MySqlMigrationCodeGenerator();
            AutomaticMigrationDataLossAllowed = true;  // or false in case data loss is not allowed.

4 - Create the database and set the strategic.

  if (!db.Database.Exists())
     Database.SetInitializer<AutomaticMigrationExampleContext>(new MigrateDatabaseToLatestVersion<AutomaticMigrationExampleContext, Configuration>());
5- Try to access the notes content.

var notes = db.Notes.Tolist();

6 - Add the navigation property and FK column:

    public class Note
        public int NoteId { get; set; }
        public string Content { get; set; }
        public int? TaskId { get; set; } // FK column
        [ForeignKey("TaskId")]     // navigation property
        public virtual Task Task { get; set; }

7 - Run the application so the migration can occur:

The following exception is thrown:

Table 'tasks.dbo.notes' doesn't exist

Expected results a new FK should be created on Notes table.

There is a workaround in order to generate the correctly migration.

Please follow these steps:

On the Package Manager Console run:

PM> Add-Migration FKNotes;

The following message should be seen in the console:

Scaffolding migration 'NotesFK'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration 201307041921223_NotesFK' again.

Update the database:

PM> update-database -verbose;

The following output should be seen:

Using StartUp project 'AutomaticMigrationExample'.
Using NuGet project 'AutomaticMigrationExample'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'tasks' (DataSource: localhost, Provider: MySql.Data.MySqlClient, Origin: Configuration).
Applying code-based migrations: [201307041924181_NotesFK].
Applying code-based migration: 201307041924181_NotesFK.
alter table `Notes` add column `TaskId` int
alter table `Notes` add constraint `FK_Notes_Tasks_TaskId`  foreign key (`TaskId`) references `Tasks` ( `TaskId`)
CREATE index  `IX_TaskId` on `Notes` (`TaskId` DESC) using HASH
insert into `__MigrationHistory` (`migrationId`, `model`, `productVersion`)  values ( '201307041924181_NotesFK', 0x1F8B0800000000000400D559DD6EDB3614BE1FB077107439A05292B6C016C82DD2241E8CCD4E11B9BD0D18E9D8214A919A4805F6B3ED628FB457D8A1FE2C9192EDA8F5D0DE24B6C8F39DBF8F87E7C8FFFEFD4FF07E9330E7193249059FB8E7DE99EB008F444CF97AE2E66AF5EA57F7FDBB9F7F0A6EE364E37CAEF7BDD6FB5092CB89FBA4547AE9FB327A8284482FA15126A458292F12894F62E15F9C9DFDE69F9FFB80102E62394E709F73451328BEE0D76BC12348554ED85CC4C064F51C57C202D5599004644A2298B857B9120951349AD37586FF05BFDD902465E095B2AE73C52841BB42602BD749DF5C7E9210AA4CF07598E27EC296DB14707D459884CA8BCBF4CDB18E9C5D68477CC2B95085FA5181701B17D1C95B0C86DA6AB30A4727EE92C82FED1DB8E70FD8761EE0A38F99482153DB7B58B5E466B1EBF85D59DF146E440D396DC2C49D71F5FAC275163963E4914113290C65A84406BF03078C3CC41F895290211D6631142E589A0D3DFA6FAD0553821C739D29DD40FC27F0B57A6A34CDC9A67E821F5DE713A748491452590E6DCBCAEFFB95DE808C329A96A93AA9EEC0DF65726F7E1742C198FC6AB931F9ADE54E9D5F3CC70A77FEEF293EC0DF5E880579A6EBE200F780B9CE3DB062513ED1B4AC259E8EE243B93ACD44722F5815DAE2E14328F22CD2F416E6CA92646B50C771E54A4A11D142752B790F3D15E196C7CEA00D65FC6BAB31DC3953346534429513F7172B1A7D60B5D92DB0D2FB2ED899E79D5B78C859C83469084352489511CA954D70CA239A1236A4DA107841CDD3016EE0CD951B48816B4A0FC5EFEBF436F0C6293D1493C06F25DFAE1FFA70A104649501831761710A37CAA28B060941B5ECC7CB72C7C24E8A2D7A748575C4FA844BB219C22DAFBA08D5616AAD5B27CD0CEE3ED2374636F659B9D947F39678151BB3CC761DE939CC4D8A760D8C5F763075A7E30FB43AC19CA42916CB56EB533D71C2B2EFB97E15BEBCD3484A0C3F923D0D47636DA309AB3F5983B1AACB7A0C539A497543147924BA7C5FC789B56D2C216BF56D5EDA89AB9956EFD69F0F1C84AA23F4FA98B40BF0147D4E7431282E3F8305B658D1941246B2813BE85AB03CE1FB6BC4304AD921B531CA27C72374DA9D365067C1C60B7C231E565DB372601C0F33A147A5BB3CA9DF34DD76777554BAFBC5061355B5549D54F5B667FB509A86A90DD33C3C1EE765E43B69AAAD5A686E69B43735D1A87D4155870ECF8256612AB7B80E86E799C6BA28CDB7E15FCCD3EB5EF1F19AD122B6F58E39E17405522DC517C0EBE7ADF7D61820470C77BE9431FB9E273CAA2370B0D7B70684E3873B86D37651F2BF7E563B0EEA0719BD4E127863EA1A1BFB1E8A8C89F89801A6EC03C7CC19DD866EC4E0336A501968284E369CFC180389DD261E9E40060790B2924FDCF85160564B12F6B7E57DB3C9E068D207DB3B2C9C686A69FBBC6B320F0D2AD66C739AD9C4BE779123AD37B5C84D49D73B08FDDE9643D46147B367C657A226A96151BDC5284073502446EA5C658AAE48A4703902298B57489F09CB71CB6DF208F18CDFE52ACD15BA0CC923EBBC95D264DFA7BF18C0BA360777C57D23BF850B68264517E08E7FC8298B1BBBA7761D1F82D0A7A8BA1C742E95BE24D6DB066921F8914055F89AC3BF046C92114CDEF1903CC3B06D8763D88D5870430976E289AC3076F2FA87035FFF72F0EE3F0BBE06496B180000, '5.0.0.net45')
Running Seed method.

Now the database is updated with the model.
Please note that the TaskId column in the Notes entity must be a nullable column, thus the declaration int?.. Otherwise an exception will be thrown due to the falling constraints in the FK.

Please let us know if this workaround worked for you.

Lowing the severity since there is a feasible workaround.
[25 Jul 2013 6:26] Ted Eiles
I get similar errors when initializing a new database using code first with any navigation property.  Using Amazon RDS, it fails in MySql 5.6.12 and works on 5.5.32.
[25 Jul 2013 6:30] Ted Eiles
...and works on 5.5.32 on my local dev box.
[25 Jul 2013 15:07] Gabriela Martinez Sanchez
Hi Ted,
Thanks for your feedback.

This bug applies for migrations, so AFAIK initialization was not affected. Could you please describe what you're doing so we can discard any new bug?..

Thanks again.
[25 Jul 2013 16:13] Ted Eiles
The sql generated for a basic navigation property is the following (which fails):

alter table 
add constraint 
foreign key (`Baz_BarUid`) 
references `dbo.Bars` ( `BarUid`)

I remove occurrences of the incorrect schema dbo and it query works fine:

alter table 
add constraint 
foreign key (`Baz_BarUid`) 
references `Bars` ( `BarUid`)
[25 Jul 2013 16:31] Gabriela Martinez Sanchez
No problem at all Ted, I can confirm then that it is the same bug. I'll double check but I believe this bug has been addressed already with another bug. I'll check and confirm later with version numbers for releases.
[26 Jul 2013 15:03] Ted Eiles
ETA on this fix?  As for the severity, is S3 consistent with codefirst basically been broken (unless there is a work-around).

--- Ted
[26 Jul 2013 18:12] Gabriela Martinez Sanchez

We'll have a Maintenance release for the 6.7 version for the first or second week next October. This bug should be addressed by that release. 

But you can expect the fix before as this bug is pushed so you can apply the path.

Please keep following the updates on this report.

Sorry for the inconvenience.
[26 Aug 2013 9:23] Developpeurs CPAM

Sorry for my absence.

I didn't have the time to try this solution, thanks.for the help.
[26 Aug 2013 13:29] Developpeurs CPAM
I also have the problem when adding new entities to the model which are related to existing entities, not just new relationships between two existing entities.

For example, an entity 'user' which already exists, and a new entity 'work', with a relationship between them.

The first time i try to use automatic migrations to update the model, it says "Table 'my_schema_name.dbo.user' doesn't exist", and when re-trying to update, it says "Table 'work' already exists".

If it can help.

Thx for help.
[28 Aug 2013 16:36] Developpeurs CPAM
I found a solution online, which is about implementing a class which inherits from VisualBasicMigrationCodeGenerator, and use a 'TrimSchemaPrefix' function on overrided 'Generate' functions : 

Private Function TrimSchemaPrefix(table As String) As String
        If table.StartsWith("dbo.") Then
            Return table.Replace("dbo.", "")
        End If
End Function

It was not enough, so I had to add another test :

Private Function TrimSchemaPrefix(table As String) As String
	If table.StartsWith("dbo.") Then
		Return table.Replace("dbo.", "")
	End If

	If table.Contains(".dbo.") Then
		Return table.Replace(".dbo.", ".")
	End If

	Return table
End Function

There was also a problem with the generated SQL script, so i had to implement a class which inherits from MySqlMigrationSqlGenerator, and use the same 'TrimSchemaPrefix' function.

Finally, i needed to replace the default name of foreignkeys by a random GUID, because its length was sometimes > to 64.

Hard to describe, sorry if it's not clear.
[7 Nov 2013 7:29] ken chen
Has the bug fixed?
[29 Jan 2014 16:29] Developpeurs CPAM
I had to stop my project for many weeks, and i wanted to wait for the new version (6.8.3) before submit an answer. I don't use Automatic Migrations anymore, but Code-Based Migrations. It seems that the generated code when i use the command "Add-Migration" in the console still add dbo prefix.
[9 Feb 2014 0:15] Bruno Salvino
Gabriela Martinez Sanchez, setting the CodeGenerator property solved my issues. 

[2 Jun 2016 8:28] Ian Worthington

This is affecting the latest ODP.NET tools too. There is a very clear bug where if the schema is not set explicitly (AFTER CONNECTION) then it will default to the dbo user probably due to some upper class within EF itself.

People have to manually enter the following on their dbContext classes to make it work with migrations:

        protected override void OnModelCreating(DbModelBuilder modelBuilder)


[2 Jun 2016 12:06] Fernando Gonzalez.Sanchez
Hi, this database its only for MySQL bugs, for other Oracle's products please open a bug report using their technical support channel (that would support.oracle.com I guess).