MySQL Bugs: #95734: Failing SET statement in a stored procedure changes variables

Bug #95734	Failing SET statement in a stored procedure changes variables
Submitted:	11 Jun 2019 11:54	Modified:	11 Jun 2019 12:21
Reporter:	Przemysław Skibiński (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	8.0.16, 5.7.26	OS:	Any
Assigned to:		CPU Architecture:	Any

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.