Bug #61635 MySQL Connector/Net throws EntityCommandExecutionException on "ORDER" table
Submitted: 24 Jun 2011 19:40 Modified: 30 Jun 2011 21:41
Reporter: Robert Hood Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.3.7 OS:Microsoft Windows (Windows 7 64bit)
Assigned to: Julio Casal CPU Architecture:Any
Tags: DELIMITER, EntityCommandExecutionException

[24 Jun 2011 19:40] Robert Hood
Description:
When a table in the database contains a table named "ORDER", a System.Data.EntityCommandExecutionException is thrown when calling Load on a navigation property.  The inner exception is of type MySQLException, and the syntax is as following:  

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 'ORDER AS Extent1\r\n WHERE Extent1.CUSTOMER_ID = 1' at line 5

It appears as though 6.3.7 does not properly delimit the table names in the generated SQL.

How to repeat:
Generate an entity model against a database that contains a table named "ORDER".  Create a related table, such has customers.  Run the following code:

            BugReproEntities context = new BugReproEntities();

            var customers = from c in context.CUSTOMERs
                            select c;

            customers.ToList().ForEach(c => c.ORDERs.Load());

Suggested fix:
Return the code the delimits table names.
[30 Jun 2011 6:16] Meljean Legaspi
It also happens with me.  In my case, I have a "Product" table which have an indirect relationship to "Option" table through 2 other tables.  I have a query which eager loads the "Option" property.  But I always got the EntityCommandExecutionException like this:

{"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 'option AS Extent4 ON Extent4.OptionGroupId = Extent2.OptionGroupId) AS Join2 ON ' at line 128"}

I also get this exception when I used version 6.4.1 beta.
[30 Jun 2011 21:41] Julio Casal
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Fixed in 6.1.7, 6.2.5, 6.3.8, 6.4.3+

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html