OptimisticConcurrencyException with dotConnect for MySQL 7.5

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
hro
Posts: 5
Joined: Fri 29 Mar 2013 16:35

OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by hro » Fri 29 Mar 2013 17:12

Since I update dotConnect for MySQL to version 7.5. I get errors when saving data with EntityFramework (SaveChanges); a OptimisticConcurrencyException is raised while I work with a standalone application.

I do not observe this behavior with the old version 6.1 of the driver.

Does anyone has already seen this problem and how can it be solved ?

Thank you in advance for your answers.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by Shalex » Mon 01 Apr 2013 13:02

Please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

jokrypta
Posts: 5
Joined: Tue 12 Jun 2012 03:54

Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by jokrypta » Fri 05 Apr 2013 05:30

I already talked about this problem here http://forums.devart.com/viewtopic.php? ... ion#p82080

but I really had no time to send a test project.

Now I am in trouble because have just updated from version 6.70.302 to 7.6.217.

On my developing machine everything works, on the production server an OptimisticConcurrencyException is thrown but only when trying to update/delete records on certain tables.

I immediately tried to downgrade again to version 6.70.302 but now Visual Studio fails to load Entity Developer extension on start up.

Please tell me what I could do to help solving this issue.

My developing machine is a Windows 7 32 bit, my server is a Windows Server 2008 R2 Datacenter 64 bit.

Thanks in advance.

G.A.

jokrypta
Posts: 5
Joined: Tue 12 Jun 2012 03:54

Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by jokrypta » Sat 06 Apr 2013 09:58

Hi there,

it seems I found a workaround and I'll post it here for your reference.

As I noticed from the MySql log, and I really don't know why this happens, extra update queries are generated and this is causing the issue.

This does not happen on my pc but it happens on the production server.

I am using Entity Framework and this is the code I use in my DAL to update a record

Code: Select all

public void Update(MyObject myobject)
{
	using (var ctx = new MyEntities())
	{
		ctx.MyObject.Attach(myobject);
		ctx.ObjectStateManager.ChangeObjectState(myobject, System.Data.EntityState.Modified);
		try
		{
			ctx.SaveChanges();
		}
		catch (OptimisticConcurrencyException ocex)
		{
			// do something here
			ctx.Refresh(RefreshMode.StoreWins, ocex.StateEntries[0].Entity);
			throw ocex;
		}
	}
}
An update and a select queries are fired on MySql in a transaction, for example:

Code: Select all

UPDATE mydb.mytable1
SET fk_ID_of_table2 = fk_ID_Value, field1 = value1, field2 = value2 ...
WHERE myID = IDValue
AND MyTimeStamp = '2013-04-05 08:24:28';

SELECT MyTimeStamp FROM mydb.mytable1
WHERE myID = IDValue AND ROW_COUNT() = 1;
On the server, and only when update is called on certain tables, extra queries are generated trying to update another table which is FK connected to the first table:

Code: Select all

UPDATE mydb.mytable2
SET MyTimeStamp = MyTimeStamp
WHERE fk_ID_of_table2 = fk_ID_Value
AND MyTimeStamp = '2013-04-05 08:30:01';

SELECT MyTimeStamp
FROM mydb.mytable2
WHERE fk_ID_of_table2 = fk_ID_Value AND ROW_COUNT() = 1;
MySql will return ROW_COUNT() = 0 for this second update query because no values are really changed; this means the second select query will return no records and I believe this is the point where the exception is raised.

Here comes the workaround. On the MySql site I found this
For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.
and the counterpart of CLIENT_FOUND_ROWS for dotConnect is this parameter
FoundRows - Specifies whether the provider will return the number of rows matched by the WHERE condition of the UPDATE statement instead of the rows actually changed as the number of changed rows.
Adding FoundRows=true to my connection string in web.config did the trick; OptimisticConcurrencyException is not raised.

I am pretty sure this will not brake my code but perhaps you should consider to fix it somehow, obviously if you think it is something to do with dotConnect.

Hope this helps.

G.A.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by Shalex » Tue 09 Apr 2013 16:45

There is the EmptyUpdates configuration option which can turn off the generation of fake updates for parent entities.

Please set it in the code

Code: Select all

var config = Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfig.Instance;
config.DmlOptions.EmptyUpdates = false;
OR in the *.config file of your application

Code: Select all

<configuration>
  <configSections>
    <section name="Devart.Data.MySql.Entity" type="Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfigurationSection, Devart.Data.MySql.Entity, Version=7.6.217.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
  </configSections> 
  <Devart.Data.MySql.Entity xmlns="http://devart.com/schemas/Devart.Data.MySql.Entity/1.0">
    <DmlOptions EmptyUpdates="false"/>
  </Devart.Data.MySql.Entity>
</configuration>
A similar issue was discussed at http://forums.devart.com/viewtopic.php?t=21698.

jokrypta
Posts: 5
Joined: Tue 12 Jun 2012 03:54

Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by jokrypta » Wed 10 Apr 2013 12:47

I took the time to test your solution by modifying my web.config with no success.

OptimisticConcurrencyException is still raised.

Putting FoundRows=true in my connection string is the only way to let it work so far.

Any ideas?

Best regards.
G.A.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5

Post by Shalex » Thu 11 Apr 2013 11:00

Please send us a small complete test project with the corresponding DDL/DML script to reproduce the issue in our environment. Also specify the version of .NET Framework which is installed on your workstation.

Post Reply