Bug #80159 MigrationSqlGenerator at CreateTableOperation Semicolon is missing
Submitted: 26 Jan 2016 17:38 Modified: 16 Jul 20:03
Reporter: Kai Scheddin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.9.3, 6.9.8 OS:Any
Assigned to: CPU Architecture:Any
Tags: CreateTableOperation, DropPrimaryKeyOperation, Generate, migration, missing, MySqlMigrationSqlGenerator, semicolon

[26 Jan 2016 17:38] Kai Scheddin
Description:
Concenated Create Table Statements are corrupted by missing semicolon and causes an interpretation error on MySQL Server side.

How to repeat:
1. Create Context:

 public class Context: CoreContext.CoreContextBase
    {

        public Context():base("DefaultConnection"){}

        public DbSet<Property1> Propertyx { get; set; }

        public override string DefaultSchema { get; set;} = "Context1";

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            var mb=modelBuilder.HasDefaultSchema(DefaultSchema);
        }
    }

2. Create Configuration (through enable-migration, with added MySqlMigrationSqlGenerator):

public class Context1DbConfig : DbMigrationsConfiguration<Context>
    {
        public Context1DbConfig()
        { 
            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
            AutomaticMigrationsEnabled = true;
            MigrationsDirectory = @"Context1Migrations";
           
        }

        protected override void Seed(DiffDbContextTest.Model1.Context context)
        {
        }
    }

3. Add-migration initial
4. Run following code:

 Context1DbConfig configuration = new Context1DbConfig();
                var migrator = new System.Data.Entity.Migrations.DbMigrator(configuration);
var scriptor = new System.Data.Entity.Migrations.Infrastructure.MigratorScriptingDecorator(migrator);
                string sql = scriptor.ScriptUpdate(null, null);

Finaly:
The sql string gives:
create table `Context1.Property1` (`Id` int not null  auto_increment ,`Text` longtext,`test` int not null ,primary key ( `Id`) ) engine=InnoDb auto_increment=0
create table `Context1.__MigrationHistory` (`MigrationId` nvarchar(100)  not null ,`ContextKey` nvarchar(200)  not null ,`Model` longblob not null ,`ProductVersion` nvarchar(32)  not null ,primary key ( `MigrationId`,`ContextKey`) ) engine=InnoDb auto_increment=0
INSERT INTO .....

Should be:

create table `Context1.Property1` (`Id` int not null  auto_increment ,`Text` longtext,`test` int not null ,primary key ( `Id`) ) engine=InnoDb auto_increment=0;
create table `Context1.__MigrationHistory` (`MigrationId` nvarchar(100)  not null ,`ContextKey` nvarchar(200)  not null ,`Model` longblob not null ,`ProductVersion` nvarchar(32)  not null ,primary key ( `MigrationId`,`ContextKey`) ) engine=InnoDb auto_increment=0;
INSERT INTO .....

Suggested fix:
MySqlMigrationSqlGenerator.cs:

Add following Method to class MySqlMigrationSqlGenerator:
 protected string EndsWithSemicolon(string str)
        {
            //remove last linefeed or whitespace end of string 
            string Ret = str.TrimEnd(new char[] { ' ', '\r', '\n', ';' });
            Ret += ";";
            return Ret;
        }

Modify last line of each Generate Method to:

string sql= sb.ToString() +..... ; //dependend on Method
return new MigrationStatement { Sql=EndsWithSemicolon(sql)};
[5 Feb 2016 7:48] Chiranjeevi Battula
Hello  Kai Scheddin,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi
[5 Feb 2016 7:49] Chiranjeevi Battula
Screenshot.

Attachment: 80159.PNG (image/png, text), 97.12 KiB.

[16 Jul 20:03] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.22 release, and here's the proposed changelog entry from the documentation team:

Create Table statements generated with Entity Framework were missing some
of the semicolons, which caused MySQL Server to return errors.

Thank you for the bug report.