MySQL 5.6 upgrade compatibility: Stored Procedure Syntax for setting DEFAULT values. (Doc ID 1669257.1)

Last updated on AUGUST 07, 2016

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

Goal

The MySQL manual states this change happened in 5.6:

Incompatible Change: It is now explicitly disallowed to assign the value DEFAULT to stored procedure or function parameters or stored program local variables (for example with a SET var_name = DEFAULT statement). This was not previously supported, or documented as permitted, but is flagged as an incompatible change in case existing code inadvertantly used this construct. It remains permissible to assign DEFAULT to system variables, as before, but assigning DEFAULT to parameters or local variables now results in a syntax error.

After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.

Will this syntax work in 5.6:

DECLARE fail VARCHAR(20) DEFAULT 'Fail';
DECLARE waiting VARCHAR(20) DEFAULT 'Waiting';
DECLARE passed VARCHAR(20) DEFAULT 'Passed';
DECLARE success VARCHAR(20) DEFAULT "Success";
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms