Bug #95734 Failing SET statement in a stored procedure changes variables

Description:
At https://dev.mysql.com/doc/refman/8.0/en/set-variable.html one can read:
If any variable assignment in a SET statement fails, the entire statement fails and no variables are changed, nor is the mysqld-auto.cnf file changed.

This is not true for stored procedures. For example p0() defined as:
CREATE PROCEDURE p0() SET GLOBAL binlog_cache_size = 4096, SESSION binlog_cache_size = 16384;
changes a value of binlog_cache_size. 

How to repeat:
SET GLOBAL binlog_cache_size = 8192;
SELECT @@binlog_cache_size;
CREATE PROCEDURE p0() SET GLOBAL binlog_cache_size = 4096, SESSION binlog_cache_size = 16384;
--ERROR 1229
CALL p0();
SELECT @@binlog_cache_size;

Hello Przemysław,

Thank you for the report and feedback.

Thanks,
Umesh

Posted by developer:
 
When a SET statement containing multiple assignments is used inside a stored procedure, each assignment is compiled into a separate stored-program instruction. These instructions are executed sequentially. Execution continues until either all instructions complete successfully or an instruction raises an error. If an error is not handled, execution stops at the failing instruction. Assignments completed by earlier instructions remain effective, the failing assignment does not take effect, and subsequent instructions are not attempted. 

This explains the behavior in the reported example:

CREATE PROCEDURE p0()
  SET GLOBAL binlog_cache_size = 4096,
      SESSION binlog_cache_size = 16384;

When p0() is called, the valid GLOBAL assignment is executed first and changes binlog_cache_size to 4096. The following SESSION assignment is then attempted and fails because binlog_cache_size is a global-only variable:

mysql> CALL p0();
ERROR 1229 (HY000): Variable 'binlog_cache_size' is a GLOBAL variable
and should be set with SET GLOBAL

The failure of the second instruction does not undo the successfully completed first instruction. Therefore, binlog_cache_size retains the value 4096. This is expected behavior. The behavior can be further illustrated with the following expanded example. First, initialize the variable values:

mysql> SET SESSION sort_buffer_size = 32768;
Query OK, 0 rows affected (0.001 sec)

mysql> SET SESSION foreign_key_checks = 1;
Query OK, 0 rows affected (0.000 sec)

mysql> SET GLOBAL binlog_cache_size = 8192;
Query OK, 0 rows affected (0.001 sec)

Next, create the procedure:

DELIMITER //

CREATE PROCEDURE p1()
BEGIN
  SET GLOBAL binlog_cache_size   = 16384,
      SESSION foreign_key_checks = 0,
      SESSION binlog_cache_size  = 4096,
      SESSION sort_buffer_size   = 8388608;
END//

DELIMITER ;

The compiled instructions can be inspected using SHOW PROCEDURE CODE:

mysql> SHOW PROCEDURE CODE p1;
+-----+-------------------------------------------------+
| Pos | Instruction                                     |
+-----+-------------------------------------------------+
|   0 | stmt "SET GLOBAL binlog_cache_size   = 16384"   |
|   1 | stmt "SET       SESSION foreign_key_checks = 0" |
|   2 | stmt "SET       SESSION binlog_cache_size  ..." |
|   3 | stmt "SET       SESSION sort_buffer_size   ..." |
+-----+-------------------------------------------------+

When the procedure is called, instructions 0 and 1 complete successfully. Instruction 2 is then attempted and fails because binlog_cache_size is a global-only variable. As a result, instruction 3 is not attempted:

mysql> CALL p1();
ERROR 1229 (HY000): Variable 'binlog_cache_size' is a GLOBAL variable
and should be set with SET GLOBAL

The variable values after the call confirm this behavior:

mysql> SELECT @@sort_buffer_size,
    ->        @@foreign_key_checks,
    ->        @@binlog_cache_size\G
*************************** 1. row ***************************
  @@sort_buffer_size: 32768
  @@foreign_key_checks: 0
  @@binlog_cache_size: 16384
1 row in set (0.001 sec)

The successfully completed instructions changed binlog_cache_size to 16384 and foreign_key_checks to 0. The instruction that would have changed sort_buffer_size was not reached, so its value remains 32768. Based on the above, the reported behavior is expected, and we are closing this report as "Not a Bug".

P.S. If restoring the previous values is required, they can be saved before making the changes and explicitly restored in an EXIT HANDLER FOR SQLEXCEPTION. The handler runs when an SQL exception occurs and then exits the block in which it was declared.

Thank you for the explanation. I agree that is how the stored-program parser works. sp_create_assignment_lex() and sp_create_assignment_instr() (sql/parse_tree_helpers.cc) turn each assignment of a multi-assignment SET into its own sp_instr_stmt. Those instructions run one after another, so an assignment that already succeeded stays in effect when a later one fails.

However, that is exactly what the manual says does not happen. The "SET Syntax for Variable Assignment" page (https://dev.mysql.com/doc/refman/8.4/en/set-variable.html) states, with no exception for stored programs:

▎ If any variable assignment in a SET statement fails, the entire statement fails and no variables are changed, nor is the mysqld-auto.cnf file changed.

The test case shows that the same SET statement behaves differently depending on where it runs. As a top-level statement, it fails and no variable is changed. Inside a stored procedure, it fails but binlog_cache_size is changed anyway. So the code and the documentation do not match, and one of them has to change:

1. Code: make a multi-assignment SET inside a stored program atomic, as it already is outside one. For example, the parser could generate a single sp_instr_stmt for the whole SET statement when all targets are user or system variables. Then all assignments are checked before any is applied, as happens for a top-level SET.
2. Documentation: if the current behaviour is intended, document it. Inside a stored program, each assignment of a SET statement is executed separately, and assignments before a failing one remain in effect.

As long as the manual promises that t think "Not a Bug" is the rightresolution. Could you please reopen or as a documentation bug?