MySQL Bugs: #65176: Update Exception when concurrency mode on fixed with Entity framewwork.

Bug #65176	Update Exception when concurrency mode on fixed with Entity framewwork.
Submitted:	2 May 2012 9:08	Modified:	13 Sep 2012 22:37
Reporter:	Gert Hermans	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Concurrency mode, entity framework, fixed, foreign key

Description:
I'm making a desktop application that connects to a MySql Database. I'm using entity framework to connect.

In order to implement Optimistic concurrency I have to put the Concurrency mode of the properties of the tbl_leden table on fixed. Then my problem starts. I have another table called tbl_inlog where I have a foreign key to the tbl_leden table. When I'm trying to save a new object from tbl_inlog I get an UpdateException.

This is the innerexception I'm getting:

    InnerException:MySql.Data.MySqlClient.MySqlException (0x80004005): 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 = 20) AND (naam = 'Frederiks')) AND (voornaam = 'Fred')) ' at line 1...

If I'm putting concurrency mode on none. I have no problems except that optimistic concurrency is not detected.
I've connected the application to a SQLSERVER database as a test. Then this error doesn't occur, which points out that the bug has something to do with the connection with the mySQL database.
I'm using the sqlconnector v6.6.4

any help is appreciated.

How to repeat:
insert an object which has a foreign key to an object which has concurrency mode=fixed.

The category is set incorrectly. Should be MySQL Connector/NET instead of Server InnoDB.

In the problem description you noted the version of Connector/NET 6.6.4.
Such version does not exist. Can you please clarify if it is 6.4.4 or 6.5.4?

The version of the mySQL connector is v6.4.4
sorry for the mistake

Hi Gert,

Please note that neither MySQL Server nor MySQL Connector/NET are operating 
with such categories as Optimistic Concurrency. So, this must be something 
specific to your project.

Can you provide a simple test case which would allow us to repeat the error?
Thanks.

I've send a zip-file (by ftp) containing a little project which shows the situation in this bug report. 
The file is called bug_#65176.zip

I'm having a similar issue.

Using EF Framework and MySql Connector 6.4.4 trying to implement optimistic concurrency.

I've got two tables 'Users' and 'Schedules' in a 1-*

Both 'Users' and 'Schedules' have a field named 'Version' which is a TimeStamp.  Version is also set to 'Default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'.

I can create and submit records to the Users table without issue.  I cannot submit records I've added to the 'Schedules' table.

Error Text:
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' = 1) AND ('Version' = '2012-06-04 14:28:41')' at line 1.

As soon as I turn Concurrency Mode on both properties to false in my EF model, everything works great.

Just some further notes.  

I update my the connector to the latest version 6.5.4, and I'm still having this issue.

However, I have noticed the following.. if this gives anyone an idea:

1.) 'Users' is changed & 'Schedules' no changes => No errors
2.) 'Users' is changed & 'Schedules' is changed => No errors
3.) 'Users' no changes & 'Schedules' is changed => Exception occurs

Hi,

I am using Connector/NET 6.6.1, following the descriptions and I cannot reproduce this issue... could you upload a sample project that illustrates the problem?

Thanks

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".