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

[11 Jun 2019 11:54] Przemysław Skibiński
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;
[11 Jun 2019 12:21] MySQL Verification Team
Hello Przemysław,

Thank you for the report and feedback.

Thanks,
Umesh