OptimisticConcurrencyException with dotConnect for MySQL 7.5
OptimisticConcurrencyException with dotConnect for MySQL 7.5
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.
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.
Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5
Please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.
Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5
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.
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.
Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5
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
An update and a select queries are fired on MySql in a transaction, for example:
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:
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
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.
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;
}
}
}
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;
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;
Here comes the workaround. On the MySql site I found this
and the counterpart of CLIENT_FOUND_ROWS for dotConnect is this parameterFor 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.
Adding FoundRows=true to my connection string in web.config did the trick; OptimisticConcurrencyException is not raised.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.
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.
Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5
There is the EmptyUpdates configuration option which can turn off the generation of fake updates for parent entities.
Please set it in the code
OR in the *.config file of your application
A similar issue was discussed at http://forums.devart.com/viewtopic.php?t=21698.
Please set it in the code
Code: Select all
var config = Devart.Data.MySql.Entity.Configuration.MySqlEntityProviderConfig.Instance;
config.DmlOptions.EmptyUpdates = false;
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>
Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5
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.
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.
Re: OptimisticConcurrencyException with dotConnect for MySQL 7.5
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.