Bug #96572 MySqlCommand.ExecuteNonQuery returns 1 for updates when it affected 0 rows.

• Submitted: 16 Aug 2019 15:34
• Modified: 20 Aug 2019 5:18
• Reporter: Perry Way
• Status: Closed
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Windows
• CPU Architecture: Any
• Tags: wrong return value

[16 Aug 2019 15:34] Perry Way

Description:
MySqlCommand.ExecuteNonQuery() function always returns non-zero value (positive) when performing updates, even when the table's row is not updated. When using SqlYog client interface, I form an update query that returns 0 rows affected, if I try the same exact sql update query in .NET C# using MySqlCommand.ExecuteNonQuery() I am getting a result of 1, not zero. This is a logical nightmare. I need to know if update occurred. If update didn't occur I need to do something else in my code (like usually an insert). I am finding it impossible to invent any work-arounds also. Having tried compound queries selecting the rows affected and using MySqlCommand.ExecuteScalar() and getting a return answer of 1 again, even though in SqlYog client interface the result is zero.

How to repeat:
form any update query that should affect a single row if the values in that row are not the values you are updating with, and execute in C# using MySqlCommand.ExecuteNonQuery(). You will always get a result of 1 when you should be getting a result of 0 (zero). 

Repeatable ad infinitum. 

Suggested fix:
I don't have a look under your hood so I cannot make any suggestions.

[16 Aug 2019 20:01] Bradley Grainger

Make sure you have "UseAffectedRows=true" in your connection string. From https://dev.mysql.com/doc/connector-net/en/connector-net-8-0-connection-options.html

> UseAffectedRows , Use Affected Rows
> Default: false
>
> When true, the connection reports changed rows instead of found rows.

[16 Aug 2019 20:20] Perry Way

Thanks a lot! Was not aware of that setting. Tested it, and it works.

[20 Aug 2019 5:18] MySQL Verification Team

Thank you, Bradley Grainger.
Closing the issue as per last note from Perry.

regards,
Umesh