Function definition not properly passed to MySQL database using MySQL Workbench

Question

I'm using MySQL 5.5 (x64) and MySQL Workbench 5.2 deployed locally on a Windows 7 workstation for development purposes. I used MySQL Workbench to build a schema with the following function definition:

CREATE FUNCTION `db`.`get_public_name` (GPN_entID INT) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
    DECLARE GPN_pubName VARCHAR(64);

    SELECT public_name INTO GPN_pubName
        FROM entity WHERE id_entity=GPN_entID LIMIT 1;

    RETURN GPN_pubName;
END

I then attempt to "Forward Engineer" the schema to the database with the following options specified:

DROP Objects Before Each Create Object
Generate DROP SCHEMA
Add SHOW WARNINGS After Every DDL Statement
GENERATE INSERT Statements for Tables

After this, MySQL Workbench attempts to publish to the server:

CREATE FUNCTION `db`.`get_public_name` (GPN_entID INT) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
    DECLARE GPN_pubName VARCHAR(64);
    SELECT public_name FROM entity WHERE id_entity = GPN_entID;

    RETURN GPN_pubName;
END

This results in the following error:

Executing SQL script in server

ERROR: Error 1415: Not allowed to return a result set from a function

Upon closer examination, I noticed the "INTO" and "LIMIT" clauses of the SELECT statement have been removed from the original function definition. This looks like it might be a cached version of the function, but I have tried everything I can think of (short of uninstalling and reinstalling MySQL Workbench) to flush any such cache to reload the correct version, but to no avail.

So, why is this change happening and how do I prevent it from happening?

piotrekkr piotrekkr · Accepted Answer · 2011-12-12T22:32:22

Try changing to this:

SELECT public_name FROM entity WHERE id_entity = GPN_entID LIMIT 1 INTO GPN_pubName;

Function definition not properly passed to MySQL database using MySQL Workbench

2 Answers