MySQL Bugs: #69649: 'dbo' prefix still causing troubles with automatic migrations

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:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.7.4	OS:	Windows
Assigned to:	Roberto Ezequiel Garcia Ballesteros	CPU Architecture:	Any

Description:
Hi. 

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.

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

<system.data>
    <DbProviderFactories> 
      <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=6.7.4.0, Culture=neutral,PublicKeyToken=c5687fc88969c44d"/>
    </DbProviderFactories>
  </system.data>

Have you done that already and still having the issue ?

Thanks in advance for your feedback.

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.

Well it's not working sorry for my last post.

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.

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())
                db.Database.Create();
     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.

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.

...and works on 5.5.32 on my local dev box.

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.

The sql generated for a basic navigation property is the following (which fails):

alter table 
`dbo.Foo` 
add constraint 
`FK_dbo.Foo_dbo.Bars_Baz_BarUid`
foreign key (`Baz_BarUid`) 
references `dbo.Bars` ( `BarUid`)

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

alter table 
`Foo` 
add constraint 
`FK_Foo_Bars_Baz_BarUid`
foreign key (`Baz_BarUid`) 
references `Bars` ( `BarUid`)

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.

ETA on this fix?  As for the severity, is S3 consistent with codefirst basically been broken (unless there is a work-around).

--- Ted

Ted,

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.

Re.

Sorry for my absence.

I didn't have the time to try this solution, thanks.for the help.

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.

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.

Has the bug fixed?

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.

Gabriela Martinez Sanchez, setting the CodeGenerator property solved my issues. 

Thanks!

Hello,

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)
        {

            modelBuilder.HasDefaultSchema("EDI");

....

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

Thanks.