Can't update a char / Row not found or updated

Can't update a char / Row not found or updated

Postby Laurent » Fri 04 Nov 2011 14:29

I have this annoying case where updating rows on a MySql database fails with a conflict error "Row not found or updated." Checking the ConflictsMembers property of the data context returns zero, so the conflict has occurred on db side, not in linq to sql.

I've turned on debugging, and this is (partially) the query that linq connect builds. I've removed null checks and sanitized the output to pinpoint the problematic area.

Code: Select all
UPDATE candidates SET Sex = :p1
WHERE CandidateId = :key1
AND ((Sex = :chk5))

-- p1: Input Char (Size = 1; DbType = AnsiStringFixedLength) [ ]
-- key1: Input Int (Size = 0; DbType = Int32) [166741]
-- chk5: Input Char (Size = 1; DbType = AnsiStringFixedLength) [?]

-- Context: Devart.Data.MySql.Linq.Provider.MySqlDataProvider Model: an Build: 3.0.10.0


In the database, Sex is a char and can be anything. This program synchronizes several databases together so we're not validating what is the right or wrong values, we just push them.

The code above is trying to update the row and push updated values. The value in db for sex is 0x18, and the update is trying to write :p1 0x20 (space)

In the debugging, we see that :chk5 is set to a question mark, most likely because it's non-printable at this point (0x18).

The problem is that it appears that this question mark is also sent to the query itself, which will fail the WHERE clause to find the original data in the row of 0x18.

How to fix this? Is this a bug in the current version?
Laurent
 
Posts: 1
Joined: Fri 04 Nov 2011 14:10

Postby StanislavK » Fri 04 Nov 2011 16:18

Could you please specify the following:
- the version of LinqConnect you are using;
- the version of the MySQL server you are connecting to;
- the character set of the database you are working with;
- the character set used in the connection string;
- the regional settings of the machine where this issue occurs.

If possible, please send us a small test project. We couldn't reproduce the issue in our environment.

As a workaround for this problem, you can cancel checking this column on CUD operations. To do so, please select the corresponding entity field in your model and set its 'Update Check' property to 'Never'.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)