Why mysql procedure returns session variables along with the OUT parameters?

Question

This was very strange. My store procedure called with nodejs returns the internal session variables instead only the OUT parameters :s

initialize_round

CREATE PROCEDURE initialize_round(
  IN initial_hp_life INT,
  IN player_1_pattern VARCHAR(100),
  IN player_2_pattern VARCHAR(100),
  OUT response_code INT,
  OUT response_message VARCHAR(100)
)
PROCEDURE_ID:BEGIN

   
  DECLARE room_id_in_round INT;
  ...
  select
    @room_id_in_round:=room_id
  from
    round
  where
    room_id = found_room_id;
  ...
  SET response_code=200;
  SET response_message='the first round was created';    
  select response_code,response_message;
END

This is the execution using nodejs and knex. Note that mysql driver, samples and mysql itself guide us to use session variables to get the output parameters:

response = await this.dbSession.raw(
`call initialize_round(?,?,?, @response_code, @response_message); SELECT @response_code, @response_message;`,params);
console.log(JSON.stringify(response));    

Also note the SELECT @response_code, @response_message; to get the expected values.

And the result is something like this:

[
  [
    [{
      "@room_id_in_round:=room_id": 3
    }, {
      "@room_id_in_round:=room_id": 3
    }, {
      "@room_id_in_round:=room_id": 3
    }, {
      "@room_id_in_round:=room_id": 3
    }], {
      "fieldCount": 0,
      "affectedRows": 0,
      "insertId": 0,
      "serverStatus": 234,
      "warningCount": 0,
      "message": "",
      "protocol41": true,
      "changedRows": 0
    },
    [{
      "@response_code": 200,
      "@response_message": "the first round was created"
    }]
  ],

As you can see the repeated @room_id_in_round is a session variable returned along with the expected OUT parameters (@response_code, @response_message).

I could not find any explanation on google nor stackoverflow. Some related questions are:

• How can a mysql session variable get the wrong value?
• How can I get and assign the return value from MySQL stored procedure
• MySQL stored procedure return value

I will try with java and c# to determine if it is a nodejs problem.

Research

• Session vars (@foo) keep their value until the session ends. An due to my pool connection in which the session is re-utilized, variables are "living" after the procedure execution

  • Keep in mind that this explains why session variables "appear", not: why nodejs mysql driver return them when only out parameters are required.

• If pool is used, session variables are a problem:

  • mysql connection pool, concurrent transactions and @ user variables
  • are session variables saved after query execution when using connection pool?

• On JAVA don't happen this. It is very easy to get the output value:

//CREATE PROCEDURE get_count_name1
// (IN the_name VARCHAR(64),OUT the_count INT)
stmt = conn.prepareCall({call get_count_name(?, ?)});
stmt.setString(1, name); 
stmt.registerOutParameter(2, java.sql.Types.INTEGER);
stmt.execute();
//get the out param which is in the 2 position
int count=stmt.getInt(2);