MySQL Bugs: #60015: Insert fails using LINQ to Entity Framework and Concurrency Mode = Fixed

Bug #60015	Insert fails using LINQ to Entity Framework and Concurrency Mode = Fixed
Submitted:	8 Feb 2011 18:43	Modified:	2 Jun 2011 2:05
Reporter:	Matt Houser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.3.6	OS:	Any
Assigned to:	Julio Casal	CPU Architecture:	Any
Tags:	.net, connector, EF, entity framework, linq

Description:
When using Entity Framework and the .NET connector, if an EF object has a field with Concurrency Mode of "Fixed" and whose datatype is DateTime, then inserting the object fails with an exception.  The exception reads:

"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 'WHERE (`Id` = 3) AND (`LastModified` = '2011-02-08 10:03:44')' at line 1" 

How to repeat:
1.  Create a database with 2 tables.  On table 1 add a DateTime field.  On table 2, add a foreign key reference to table 1.

2.  Start a new VS2010 console project.

3.  Add an Entity Framework Model using VS2010.  On table 1, on the DateTime field, set the Concurrency Mode to "Fixed".

4.  Using LINQ (C# code), add a table 1 record.  Then add a table 2 record with a reference to the table 1 object.

The SaveChanges() call will fail with the above exception.

A sample project and Workbench model is available at http://www.houser.ca/files/MySqlEFTest.zip

Suggested fix:
The save should not fail.  When the above offending code (from the exception) is appended to a SQL Select statement, the statement executes without error.  So the exception seems to be a false positive.

Sample project and Workbench model

Attachment: MySqlEFTest.zip (application/x-zip-compressed, text), 12.18 KiB.

Using logging, the following query was produced:

=========================

110208 15:41:20	    1 Connect	root@localhost on eftest
		    1 Query	SHOW VARIABLES
		    1 Query	SHOW COLLATION
		    1 Query	SET character_set_results=NULL
		    1 Init DB	eftest
		    1 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
		    1 Query	BEGIN
		    1 Query	INSERT INTO `table1`(
`LastModified`, 
`Name`) VALUES (
'2011-02-08 15:41:20', 
'hello');
SELECT
`Id`
FROM `table1`
 WHERE  row_count() > 0 AND `Id`=last_insert_id()
		    1 Query	COMMIT
		    1 Init DB	eftest
		    1 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
		    1 Query	BEGIN
		    1 Query	UPDATE `table1` SET  WHERE (`Id` = 21) AND (`LastModified` = '2011-02-08 15:41:20')
110208 15:41:21	    1 Query	ROLLBACK 

===================

It appears that the UPDATE is not properly formulated.

Post the message explaining same problem when inserting and condition.

http://forums.mysql.com/read.php?38,406688,407623#msg-407623

 However, I couldn't find out origin.

I'm using Visual Studio 2010 Framework 4. 
Code is C# 
The connector is the 6.3.5 
MySQL is 5.1.36 and tables are InnoDb 

The field is a DateTime with Concurrency Mode set to Fixed

The .SaveChanges() method works without the sql syntax error but if the call is inside a transaction (called upon the context Connection), I got the same problem: "You have a SQL syntax error in your WHERE clause..."

Any shot of this one getting looked at?

This is not a Connector/Net bug but an Entity Framework 4.0 bug. Please request and apply the following Microsoft hotfix to see if the issue gets resolved for your scenario:

http://support.microsoft.com/kb/2390624

I requested, downloaded and installed the hotfix.

I am still getting an exception, however, it may be this problem now:
http://bugs.mysql.com/bug.php?id=60652

Once I get a fix for the above issue, I can re-test this one.

...Matt

Hi, I got the same issue as Matt. Any updates on this?
Thanks..