MySQL Bugs: #103312: MysqlHelper.executenonquery dont return affected rows correctly

Bug #103312	MysqlHelper.executenonquery dont return affected rows correctly
Submitted:	13 Apr 2021 21:50	Modified:	13 Jan 2022 17:24
Reporter:	mohamed atef	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	mysqlhelper.executenonquery

Description:
when using insert into table on duplicate key update 
mysqlhelper.executenonquery don`t return the affected rows correctly
in dotnet connector version 8.0.23

How to repeat:
open workbench
create table 
CREATE TABLE `testignore` (
  `Id` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT  INTO `abc`.`testignore` (`Id`) VALUES ('1');
INSERT  INTO `abc`.`testignore` (`Id`) VALUES ('2');
then
SELECT * FROM abc.testignore;
2 row(s) returned;

then 
INSERT  INTO `abc`.`testignore` (`Id`) VALUES ('1') on duplicate key update Id=Id;
0 row(s) affected;

INSERT  INTO `abc`.`testignore` (`Id`) VALUES ('2') on duplicate key update Id=Id;
0 row(s) affected;

create windows form application
add mysql.data dll as refernce

private void button1_Click(object sender, EventArgs e)
        {
            string SQLADD = "INSERT  INTO `abc`.`testignore` (`Id`) VALUES (@P1) on duplicate key update Id=Id ;";
            try
            {
                int[] lst = new int[]
                {
                    1,2
                };
                foreach (int u in lst)
                {
                    MySqlParameter[] PARA = new MySqlParameter[]
                       {
                        new MySqlParameter(){ ParameterName="P1",MySqlDbType=MySqlDbType.Int32,Value= u }
                       };
                    int i = MySqlHelper.ExecuteNonQuery(MyCurrentConnString(), SQLADD, PARA);
                    MessageBox.Show(i.ToString());
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private string MyCurrentConnString()
        {
            MySqlConnectionStringBuilder cons = new MySqlConnectionStringBuilder
            {
                Server = "127.0.0.1",
                Port = 3306,
                UserID = "root",
                Password = "yousef",
                Database = "abc",
                ConvertZeroDateTime = true,
                SslMode = MySqlSslMode.None,
                AllowUserVariables = true
            };
            return cons.ToString();
        }

every time i value will be shown as 1 although 0rows affected

Suggested fix:
i don`t know

MySqlCommand.ExecuteNonQuery()
Also return Invalid Affected Rows

Hello mohamed atef,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Posted by developer:
 
This is an actual expected behavior due to "UseAffectedRows" (default false) connection option in C/NET. If this option is set to true, then the server flag "CLIENT_FOUND_ROWS" will be set and C/NET will return the expected value as in mysql client.

You can read more about this in here:
https://dev.mysql.com/doc/connector-net/en/connector-net-8-0-connection-options.html
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Posted by developer:
 
*If this option is set to true, then the server flag "CLIENT_FOUND_ROWS" will NOT be set and C/NET will return the expected value
as in mysql client.