MySQL Bugs: #24923: Functions with ENUM issues.

Bug #24923	Functions with ENUM issues.
Submitted:	8 Dec 2006 18:57	Modified:	23 Oct 2007 19:43
Reporter:	Thomas Johnson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0/5.1BK	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	administrator, Browser, enum, functions, procedures, showing, stop

Description:
After modifying a Function, all the functions and procedures stop showing up in MySQL Browser and Administrator tools (tables and views still show up).  I drop the function and they all show up again.

How to repeat:
Execute the following to stop them from showing up.  Please note I have a schema called PLS.

DELIMITER $$

DROP FUNCTION IF EXISTS `pls`.`ConvertStatus` $$
CREATE FUNCTION `ConvertStatus`(inStatus int)
RETURNS ENUM('UPLOAD','SCAN','MNF','VOID','XUPLOAD','XSCAN','NOTIFY','XVOID','RECALL')
BEGIN
  case inStatus
  when 0 then
    return 'UPLOAD';
  when 1 then
    return 'SCAN';
  when 2 then
    return 'MNF';
  when 3 then
    return 'VOID';
  when 4 then
    return 'XUPLOAD';
  when 5 then
    return 'XSCAN';
  when 6 then
    return 'NOTIFY';
  when 7 then
    return 'XVOID';
  when 8 then
    return 'RECALL';
  end case;

  return null;
END $$

DELIMITER ;

Now if you look in Browser or Administrator you cannot see any procedure or function.

Now execute the following:

DELIMITER $$

DROP FUNCTION IF EXISTS `pls`.`ConvertStatus` $$

DELIMITER ;

Now the functionas and procedure show up in Browser or Administrator.

MySQL Browser v.1.1.20
MySQL Administrator v.1.1.19
MySQL server v.5.0.27-community-nt via TCP/IP

Suggested fix:
Functions and procedures should be visible.

Thank you for a problem report. Please, try to repeat with a newer version, 1.2.6 (from GUI Tools 5.0r6), and inform about the results.

I discovered it has something to do with character length of the return for ENUM.  When I shrunk the Enum list down to:
RETURNS ENUM('UPLOAD','SCAN','MNF','VOID','XUPLOAD','XS')
Everything works.  But when I chnage it to
RETURNS ENUM('UPLOAD','SCAN','MNF','VOID','XUPLOAD','XSC')
It stops working again.
As a work Around I decided to return a varchar(10) instead of using an ENUM.

I found the "Edit All Stored Procedures" didn't work either during this time.  

I donwloaded and tried the r6 (v.1.2.6) of the GUI tools.  They had the same results too.

Thank you for the bug report. This is a server bug instead of GUI-Tools when
the SHOW CREATE FUNCTION function_name is affected by this particular function:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select (name) from mysql.proc;
Empty set (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION `ConvertStatus`(inStatus int)
    -> RETURNS
    -> ENUM('UPLOAD','SCAN','MNF','VOID','XUPLOAD','XSCAN','NOTIFY','XVOID','RECALL')
    -> BEGIN
    ->   case inStatus
    ->   when 0 then
    ->     return 'UPLOAD';
    ->   when 1 then
    ->     return 'SCAN';
    ->   when 2 then
    ->     return 'MNF';
    ->   when 3 then
    ->     return 'VOID';
    ->   when 4 then
    ->     return 'XUPLOAD';
    ->   when 5 then
    ->     return 'XSCAN';
    ->   when 6 then
    ->     return 'NOTIFY';
    ->   when 7 then
    ->     return 'XVOID';
    ->   when 8 then
    ->     return 'RECALL';
    ->   end case;
    -> 
    ->   return null;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;
mysql> select (name) from mysql.proc;
+---------------+
| name          |
+---------------+
| ConvertStatus | 
+---------------+
1 row in set (0.00 sec)

mysql> select ConvertStatus(2);
ERROR 1457 (HY000): Failed to load routine test.ConvertStatus. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
mysql> select (name) from mysql.proc;
+---------------+
| name          |
+---------------+
| ConvertStatus | 
+---------------+
1 row in set (0.00 sec)

mysql> drop function ConvertStatus;
Query OK, 0 rows affected (0.00 sec)

mysql> select (name) from mysql.proc;
Empty set (0.00 sec)

mysql>

mysql.proc.returns can only store 64 characters, and the data gets silently truncated when trying to create a function with RETURNS value that is longer.

The fix is to:
 * check for truncation when storing data in mysql.proc
 * extend mysql.proc.returns to allow at least 65555 characters

A workaround:
alter table mysql.proc modify column returns varchar(65555);

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35736

ChangeSet@1.2584, 2007-10-17 12:13:56+04:00, anozdrin@station. +8 -0
  Fix for BUG#24923: Functions with ENUM issues.
  
  The problem was that the RETURNS column in the mysql.proc was of
  CHAR(64). That was not enough for storing long-named datatypes.
  
  The fix is to change CHAR(64) to LONGBLOB, and to throw warnings
  at the time a stored routine is created if some data is truncated
  during writing into mysql.proc.

Pushed into 5.1-runtime.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35747

ChangeSet@1.2585, 2007-10-17 15:44:22+04:00, anozdrin@station. +2 -0
  Fix result files (BUG#24923).

Pushed into 5.1.23-beta

Noted in 5.1.23 changelog.