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)
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 8.0.16, 5.7.26
• OS: Any
• 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