MySQL Bugs: #101497: Stored procedure returns wrong result when it's called sequentially

Bug #101497	Stored procedure returns wrong result when it's called sequentially
Submitted:	6 Nov 2020 12:44	Modified:	4 Apr 2021 12:17
Reporter:	Petko Petkov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	C#, stored procedure

Description:
When I call the same stored procedure more than once sequentially, the procedure returns different result on the second pass. 

The first execution of the procedure returns the expected result, but when it's executed again returns null.
 
The procedure just reads from table without changing data. 

The problem occurs only when the procedure it's called from the connector library. The bug is available only in 8.0.22 and above. 

How to repeat:

Test data: 

create table games
(
	game_id int auto_increment,
	name varchar(100) null,
	constraint games_pk
		primary key (game_id)
);

INSERT INTO games VALUES (1, 'test1'), (2, 'test2') , (3, 'test3');

The procedure:

CREATE DEFINER = 'dev'@'%'
PROCEDURE test.ZZ_test()
BEGIN

  SET @game_id = (SELECT game_id FROM games WHERE games.name = 'test2');

  SELECT @game_id AS result;

  SELECT game_id FROM games;

END

C# test class: 
    [TestClass]
    public class DebugTest
    {
        readonly MySqlConnection conn;

        public DebugTest()
        {
            const string myConnectionString = "connectionString";

            try
            {
                conn = new MySqlConnection {ConnectionString = myConnectionString};
                conn.Open();
            }
            catch (MySqlException)
            {
            }
        }

        [TestMethod]
        public void DebugZTest()
        {
            var res = Test();
            Assert.AreEqual(2, res);
         
            res = Test();
            Assert.AreEqual(2, res); //returns null
        }

        private long Test()
        {
            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "ZZ_test";
            cmd.CommandType = CommandType.StoredProcedure;

            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataRow row = ds.Tables[0].Rows[0];
            long res = row.Field<long>("result");
            return res;
        }
    }

Hello Petko,

Thank you for the bug report.
Discussed this issue internally with the Connector/Net developer and we tried to reproduce your issue on windows 10 with C/NET 8.0.23 and MySQL Server 8.0.23 but we are not seeing any issues at our end.
Could you please provide more information to reproduce this issue at our end?

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".