MySQL Bugs: #44854: SP retruns 0 rows affected, but it had inserted 1 row

Bug #44854	SP retruns 0 rows affected, but it had inserted 1 row
Submitted:	13 May 2009 20:32	Modified:	28 May 2009 0:17
Reporter:	Eric Zwiebel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.31-community-log, 5.1.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
ExecuteNonQuery for command with OUT params returns 0 instead of the actual number of affected records.

How to repeat:
Below is some C# code to replicate the problem:

using (MySqlConnection con = new MySqlConnection("Database=test;Data Source=192.168.30.65;User Id=root;Password=password;use affected rows=true;"))
{
	con.Open();

	MySqlCommand command = new MySqlCommand();

	MySqlParameter idParam = new MySqlParameter("p_Id", MySqlDbType.Int64);
	idParam.Direction = System.Data.ParameterDirection.InputOutput;

	MySqlParameter udateParam = new MySqlParameter("p_UDate", MySqlDbType.DateTime);
	idParam.Direction = System.Data.ParameterDirection.InputOutput;

	MySqlParameter versionParam = new MySqlParameter("p_Version", MySqlDbType.Int16);

	int recordsAffected = 0;

	command.CommandType = System.Data.CommandType.Text;
	command.Connection = con;

	//create table
	command.CommandText = @"
			DROP TABLE IF EXISTS `test`.`updatetest`;
			CREATE TABLE  `test`.`updatetest` (
			  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
			  `Version` smallint(5) unsigned NOT NULL,
			  `UDate` datetime NOT NULL,
			  PRIMARY KEY (`Id`),
			  UNIQUE KEY `Id` (`Id`)
			) ENGINE=InnoDB DEFAULT CHARSET=latin1; 
			";
	command.ExecuteNonQuery();

	//create insert proc
	command.CommandText = @"
		DROP PROCEDURE IF EXISTS test.insert_updatetest ;
		CREATE PROCEDURE test.insert_updatetest
		(INOUT p_Id bigint,p_Version smallint unsigned,INOUT p_UDate datetime)
		BEGIN 
			INSERT INTO		test.updatetest	(Version,UDate)
			VALUES			(p_Version,CURRENT_TIMESTAMP);
			SELECT			Id,UDate
			INTO			p_Id,p_UDate
			FROM			test.updatetest
			WHERE			(Id=LAST_INSERT_ID());
		END ;";
	command.ExecuteNonQuery();

	//create no ouput update proc
	command.CommandText = @"
		DROP PROCEDURE IF EXISTS test.updatenoouput_updatetest ;
		CREATE PROCEDURE test.updatenoouput_updatetest
		(p_Id bigint,p_Version smallint unsigned,p_UDate datetime)
		BEGIN 
			UPDATE			test.updatetest
			SET				Version=p_Version,UDate=CURRENT_TIMESTAMP
			WHERE			(Id=p_Id)
			AND				(UDate=p_UDate);
		END ;";
	command.ExecuteNonQuery();

	//create update proc
	command.CommandText = @"
		DROP PROCEDURE IF EXISTS test.update_updatetest ;
		CREATE PROCEDURE test.update_updatetest
		(p_Id bigint,p_Version smallint unsigned,INOUT p_UDate datetime)
		BEGIN 
			UPDATE			test.updatetest
			SET				Version=p_Version,UDate=CURRENT_TIMESTAMP
			WHERE			(Id=p_Id)
			AND				(UDate=p_UDate);

			SELECT			UDate
			INTO			p_UDate
			FROM			test.updatetest
			WHERE			(Id=p_Id);
		END ;";
	command.ExecuteNonQuery();

	//insert
	command.CommandText = "test.insert_updatetest";
	command.CommandType = System.Data.CommandType.StoredProcedure;

	idParam.Value = 0;
	command.Parameters.Add(idParam);

	versionParam.Value = 1;
	command.Parameters.Add(versionParam);

	udateParam.Value = new DateTime(2000, 1, 1);
	command.Parameters.Add(udateParam);

	recordsAffected = command.ExecuteNonQuery();

	System.Diagnostics.Debug.WriteLine(string.Format("Rows Inserted: {0}; Id={1},Version={2},UDate={3}", recordsAffected.ToString(), idParam.Value.ToString(), versionParam.Value.ToString(), udateParam.Value.ToString()));

	System.Threading.Thread.Sleep(1500);

	//Update
	command.Parameters.Clear();
	command.CommandText = "update_updatetest";
	command.CommandType = System.Data.CommandType.StoredProcedure;

	command.Parameters.Add(idParam);

	versionParam.Value = 2;
	command.Parameters.Add(versionParam);

	command.Parameters.Add(udateParam);

	recordsAffected = command.ExecuteNonQuery();

	System.Diagnostics.Debug.WriteLine(string.Format("Rows Updated: {0}; Id={1},Version={2},UDate={3}", recordsAffected.ToString(), idParam.Value.ToString(), versionParam.Value.ToString(), udateParam.Value.ToString()));

	//Update no output
	command.Parameters.Clear();
	command.CommandText = "test.updatenoouput_updatetest";
	command.CommandType = System.Data.CommandType.StoredProcedure;

	command.Parameters.Add(idParam);

	versionParam.Value = 3;
	command.Parameters.Add(versionParam);

	command.Parameters.Add(udateParam);

	recordsAffected = command.ExecuteNonQuery();

	System.Diagnostics.Debug.WriteLine(string.Format("Rows Updated: {0};", recordsAffected.ToString()));
}

Suggested fix:
Return the number of affected records rather than 0.

Update severity based on definition of significant functionality is missing.

Hi Eric and thanks for your report.

I am not sure this is a bug as you presented it, especially not in c/NET. First things first, PROCEDURE test.insert_updatetest is not correctly defined as there is no type for "p_Version smallint unsigned". I have put "IN" there:

DROP TABLE IF EXISTS `test`.`updatetest`;
CREATE TABLE  `test`.`updatetest` (
    `Id` bigint(20) NOT NULL AUTO_INCREMENT,
    `Version` smallint(5) unsigned NOT NULL,
    `UDate` datetime NOT NULL,
    PRIMARY KEY (`Id`),
    UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP PROCEDURE IF EXISTS test.insert_updatetest ;
delimiter //
CREATE PROCEDURE test.insert_updatetest
(INOUT p_Id bigint, IN p_Version smallint unsigned, INOUT p_UDate datetime)
BEGIN 
	INSERT INTO		test.updatetest	(Version,UDate)
	VALUES			(p_Version,CURRENT_TIMESTAMP);
	SELECT			Id,UDate
	INTO			p_Id,p_UDate
	FROM			test.updatetest
	WHERE			(Id=LAST_INSERT_ID());
END ;//
delimiter ;

Then I tried mysql command line client and got the same result as you did in C#:
mysql> set @p1 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @p2 = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @p2 = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @p3 = "2000-01-01";
Query OK, 0 rows affected (0.00 sec)

mysql> call insert_updatetest(@p1, @p2, @p3);
Query OK, 0 rows affected (0.33 sec)          <<<<<<<<

mysql> select @p1;
+------+
| @p1  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select @p2;
+------+
| @p2  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select @p3;
+---------------------+
| @p3                 |
+---------------------+
| 2009-05-20 09:23:39 |
+---------------------+
1 row in set (0.00 sec)

I will consult now if SP is supposed to return something other than 0 for your example.

Being that row is inserted, one would expect something other than 0 returned:
mysql> select * from updatetest;
+----+---------+---------------------+
| Id | Version | UDate               |
+----+---------+---------------------+
|  1 |       1 | 2009-05-20 09:23:39 |
+----+---------+---------------------+

Checking manual.

Nothing in manual that can justify such return.

triage: setting tag to CHECKED (feature request to provide some way to collect and return to the client information about rows affected, warnings, etc., encountered during a stored procedure/function)

I also encounter the same issue. I make a simple insert inside a transaction and although a row is actually inserted i get a 'zero rows affected' message.
If i make the same insert without using transactions i get '1 row affected' message.