Description:
MySQL Connector/Net 6.4.3 fails and throws a OptimisticConcurrencyException when a row is updated using Entity Framework's context.SaveChanges().
Model is setup like this:
Member.MemberID
- Int32, Identity
Member.LastSignedInOn
- DateTime
Member.VersionCode
- string (database column is varchar(40))
- concurrency mode: Fixed
- store generated pattern: computed
Inserts succeed. Updates always fail. See the log below. The same holds true even when the concurrency field strategy uses a timestamp instead of a varchar.
The generated SQL update code is actually valid: it matches a row and affects a record. There is no problem with concurrency. The actual problem is with the SQL used to obtain the new computed value:
SELECT
`VersionCode`
FROM `member`
WHERE row_count() > 0 AND `MemberID`=last_insert_id()
The last_insert_id() will always be empty since the context is an UPDATE, and not an INSERT. The correct WHERE clause should use the primary key:
WHERE `MemberID` = 39
Hoping a fix for this can be made available quickly.
Thank you kindly for your help.
LOG -----------------------
mysql Information: 3 : 3: Query Opened: UPDATE `member` SET `LastSignedInOn`='2011-09-20 16:33:44' WHERE (`MemberID` = 39) AND (`VersionCode` = 'my!-f0f91b72-e3c1-11e0-a051-00ff126641cd');
SELECT
`VersionCode`
FROM `member`
WHERE row_count() > 0 AND `MemberID`=last_insert_id()
mysql Information: 4 : 3: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0
mysql Information: 5 : 3: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 4 : 3: Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1
mysql Information: 5 : 3: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 3: Query Closed
A first chance exception of type 'System.Data.OptimisticConcurrencyException' occurred in System.Data.Entity.dll
mysql Information: 3 : 3: Query Opened: ROLLBACK
How to repeat:
Environment:
Win 7, 64-bit
Visual Studio 2010 Professional Edition
MySQL Server Version 5.5.8
MySQL Connector/Net 6.4.3
Description: MySQL Connector/Net 6.4.3 fails and throws a OptimisticConcurrencyException when a row is updated using Entity Framework's context.SaveChanges(). Model is setup like this: Member.MemberID - Int32, Identity Member.LastSignedInOn - DateTime Member.VersionCode - string (database column is varchar(40)) - concurrency mode: Fixed - store generated pattern: computed Inserts succeed. Updates always fail. See the log below. The same holds true even when the concurrency field strategy uses a timestamp instead of a varchar. The generated SQL update code is actually valid: it matches a row and affects a record. There is no problem with concurrency. The actual problem is with the SQL used to obtain the new computed value: SELECT `VersionCode` FROM `member` WHERE row_count() > 0 AND `MemberID`=last_insert_id() The last_insert_id() will always be empty since the context is an UPDATE, and not an INSERT. The correct WHERE clause should use the primary key: WHERE `MemberID` = 39 Hoping a fix for this can be made available quickly. Thank you kindly for your help. LOG ----------------------- mysql Information: 3 : 3: Query Opened: UPDATE `member` SET `LastSignedInOn`='2011-09-20 16:33:44' WHERE (`MemberID` = 39) AND (`VersionCode` = 'my!-f0f91b72-e3c1-11e0-a051-00ff126641cd'); SELECT `VersionCode` FROM `member` WHERE row_count() > 0 AND `MemberID`=last_insert_id() mysql Information: 4 : 3: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0 mysql Information: 5 : 3: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0 mysql Information: 4 : 3: Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1 mysql Information: 5 : 3: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0 mysql Information: 6 : 3: Query Closed A first chance exception of type 'System.Data.OptimisticConcurrencyException' occurred in System.Data.Entity.dll mysql Information: 3 : 3: Query Opened: ROLLBACK How to repeat: Environment: Win 7, 64-bit Visual Studio 2010 Professional Edition MySQL Server Version 5.5.8 MySQL Connector/Net 6.4.3