MySQL Bugs: #76292: EF6 Wrong SQL Statement to set primary Key

Bug #76292	EF6 Wrong SQL Statement to set primary Key
Submitted:	12 Mar 2015 13:16	Modified:	29 Jun 2016 20:16
Reporter:	Brian Dahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:		OS:	Windows
Assigned to:	Jose Gabriel Valencia Ornelas	CPU Architecture:	Any
Tags:	entity framework

Description:
When i had build a Table which had an entity with the Name "key" and this entity had to be the Primary key then produce the mysql connector for EF6 the following wrong Statement:
"ALTER TABLE `example_table` ADD PRIMARY KEY (key);"
but it have to be:
"ALTER TABLE `nav_1_dbinfo` ADD PRIMARY KEY (`key`);"

How to repeat:
Build a table with PK named key and try to Create a new DB.

Suggested fix:
add the ` to the SQL producing template...

Hello Brian Dahl,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013, MySQL Connector/Net 6.9.6 and EF6 , but couldn't trace out any issue primary keys.
Could you please list out the steps you tried out at your end? Please provide repeatable test case in order for us to confirm this issue at our end.

Thanks,
Chiranjeevi.

screenshot

Attachment: 76292.PNG (image/png, text), 54.70 KiB.

- Create a MySQL Database for example test_DB
- CREATE TABLE `new_table` (
  `key` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

- Create C# Project
- Add the MySQL EF 6 Libs to your Project
- Add ADO.NET Entity Data Model to your Project
- Select EF Designer from Database
- Produce your Connection String
- Select the Table
- If the Wizard is Finished Open EDMX File Click Right Mouse Button on the Diagram Click on Generate Database from Model, now you see the SQL String to Generate the Database.
You see the following construct:
CREATE TABLE `new_table`( 
	`key` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `key` ADD PRIMARY KEY (key);

And the last row you see the Problem, there is written 
"ALTER TABLE `key` ADD PRIMARY KEY (key);" Corectly it has be the following string "ALTER TABLE `key` ADD PRIMARY KEY (`key`);"

Hello Brian Dahl,

Thank you for your feedback.
I tried to reproduce the issue at my end using Visual Studio 2013 with MySQL Connector/Net 6.9.6 and EntityFramework(6.1.3), but I'm not seeing any reported issue.
Could you please retry and confirm us again?

Thanks,
Chiranjeevi.

screenshot

Attachment: 76292_1.PNG (image/png, text), 106.98 KiB.

And in the Alter Statement from your screenshot is the failure. You can reproduce it if you say Context.Database.Create(); (Context is your DB Context from ef6) or if you copy and paste it to directly in your SQL Shell you have to receive a error (PRIMARY KEY (key);") because the Name key in brackets are a Name but key is also a keyword....

Hello Brian Dahl,

Thank you for your feedback.
Verified this behavior on Visual Studio 2013 with MySQL Connector/Net 6.9.6, EntityFramework(6.1.3) and ADO.Net. 

Thanks,
Chiranjeevi.

mysql> use test
Database changed
mysql> desc new_table;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| key   | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.05 sec)

mysql> drop table new_table;
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE `new_table`(
    ->  `key` int NOT NULL AUTO_INCREMENT UNIQUE);
Query OK, 0 rows affected (0.31 sec)

mysql> ALTER TABLE `new_table` ADD PRIMARY KEY (key);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 

'key)' at line 1
mysql> 
mysql> ALTER TABLE `new_table` ADD PRIMARY KEY (`key`);
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>

http://bugs.mysql.com/bug.php?id=78257 marked as duplicate of this one.

I checked out the source code mysql-connector-net-6.9.7-src

The problem is in method GetTableCreateScript in mysql-connector-net-6.9.7-src\Source\MySql.Data.Entity\ProviderServices.cs line 465

      if (e.KeyMembers.Count > 0)
      {
        sql.Append(String.Format(
            "ALTER TABLE `{0}` ADD PRIMARY KEY (", typeName ));
        delimiter = "";
        foreach (EdmMember m in e.KeyMembers)
        {
>>        sql.AppendFormat("{0}{1}", delimiter, m.Name);
          delimiter = ", ";
        }
        sql.AppendLine(");");
        sql.AppendLine();
      }

It shoudl have been:
        sql.AppendFormat("{0}`{1}`", delimiter, m.Name);

6.7 doesn't have EF6 support only EF5, but the behavior was found in EF5 too,

the column name was enclosed in quotes to avoid this problems

Fixed as of the upcoming Connector/Net 6.8.8 release, and here's the changelog entry:

With Entity Framework 6, building a table with a primary key would not
enclose the key name in quotes, which caused a syntax error.

Thank you for the bug report.

This bug was also fixed in Connector/Net 6.9.9