Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inconsistent rows-affected during batch sql #1096

Closed
DocCodes opened this issue Dec 1, 2021 · 3 comments · Fixed by #1112
Closed

Inconsistent rows-affected during batch sql #1096

DocCodes opened this issue Dec 1, 2021 · 3 comments · Fixed by #1112

Comments

@DocCodes
Copy link

DocCodes commented Dec 1, 2021

MySqlConnector version: 2.1.0
MariaDB: 10.6.5
.NET versions: 4.6.2, 4.8 and 5.0

The MySqlConnector does not calculate rows-affected during batch sql correctly. Depending on the order of the sql queries in the batch the rows-affected value is different.

-- DB create Table and StoredProcedure for this demo
-- Create Table
CREATE TABLE `test`.`Demo` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE = INNODB CHARSET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- Create StoredProcedure
DELIMITER $$
CREATE PROCEDURE `test`.`SP_Demo`(in pId INT, IN pName VARCHAR(50))
sp_master_label:BEGIN
DECLARE dResultCode INT DEFAULT 0; -- 1 = Execution related error
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- Error occured
    SET dResultCode = 1;
    
    SELECT dResultCode AS ResultCode;
END;

UPDATE `Demo` SET `Name` = pName WHERE (`Id` = pId);

SET dResultCode = 0;
SELECT dResultCode AS ResultCode;
END$$
DELIMITER ;

-- Pre-Add a row for the demo
INSERT INTO `test`.`Demo` (`Name`) VALUES ('Demo-Name');
// ------------------- C# Part
string connectionString = "Server=localhost;Port=3306;Database=test;User ID=<UserId>;Password=<Password>";
string sqlSp = "CALL `SP_Demo`(1, 'Demo-Name-Updated');";
string sqlInsert = "INSERT INTO `Demo` (`Name`) VALUES ('Demo-Name-Updated-Batch');";
int rowsAffected = 0;

// A batch with SP + Insert returns -1
rowsAffected = ExecuteSql(connectionString, sqlSp + sqlInsert);         // rowsAffected = -1
// Reversing batch order with Insert + SP returns 1
rowsAffected = ExecuteSql(connectionString, sqlInsert + sqlSp);         // rowsAffected = 1
// A batch with SP + SP + Insert returns 1
rowsAffected = ExecuteSql(connectionString, sqlSp + sqlSp + sqlInsert); // rowsAffected = 1

private int ExecuteSql(string connectionString, string sql)
{
	int rowsAffected = 0;
	
	using (MySqlConnector.MySqlConnection cn = new MySqlConnector.MySqlConnection(connectionString))
	{
		cn.Open();

		using (MySqlConnector.MySqlCommand cmd = new MySqlConnector.MySqlCommand(sql, cn))
		{
			cmd.CommandType = CommandType.Text;
			rowsAffected = cmd.ExecuteNonQuery();
		}

		cn.Close();
	}
	
	return rowsAffected;
}

I expect that a batch sql should return the same value for rows-affected no matter the order in which the sqls are executed if they does not affect each other.

@bgrainger
Copy link
Member

Thanks for the repro. It seems like something unexpected is happening here, but I'm not sure what yet.

bgrainger added a commit to bgrainger/MySqlConnector that referenced this issue Jan 2, 2022
Signed-off-by: Bradley Grainger <bgrainger@gmail.com>
@bgrainger
Copy link
Member

I opened #1112 to fix this.

Note that if the SQL command is executed when the rows have their original values, then the expected RecordsAffected value is 2 (one from the UPDATE in the sproc, and one from the INSERT), i.e., the sum of the two "1 row affected" in the output below:

MariaDB [test]> CALL `SP_Demo`(1, 'Demo-Name-Updated'); INSERT INTO `Demo` (`Name`) VALUES ('Demo-Name-Updated-Batch');
+------------+
| ResultCode |
+------------+
|          0 |
+------------+
1 row in set (0.007 sec)

Query OK, 1 row affected (0.007 sec)

Query OK, 1 row affected (0.002 sec)

@bgrainger
Copy link
Member

Fixed in 2.1.3.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
2 participants