MySQL Bugs: #47227: Cannot Create Stored Procedure in MySQL 5.1.36 and 5.1.38

Bug #47227	Cannot Create Stored Procedure in MySQL 5.1.36 and 5.1.38
Submitted:	9 Sep 2009 18:58	Modified:	9 Oct 2009 19:49
Reporter:	Rolando Edwards	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.36	OS:	Microsoft Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
Every time I create a Stored Function in MySQL 5.1.36 and 5.1.38 in Windows, it simply fails to create it without a discernable explanation

How to repeat:
Run this in the mysql client

CREATE DATABASE IF NOT EXISTS sample;
DELIMITER $$
DROP FUNCTION IF EXISTS `sample`.`RandomZipCodeID` $$
CREATE FUNCTION `sample`.`RandomZipCodeID`() RETURNS int(11)
BEGIN

  DECLARE rv INT;
  SET rv = 1;
  RETURN rv;

END $$
DELIMITER ;

I got this error
ERROR 1307 (HY000): Failed to CREATE FUNCTION RandomZipCodeID

Use both 5.1.36 and 5.1.38

This works in RHEL 5 version of 5.1.36

BTW This works and replicates in MySQL Replication perfectly in RHEL 5 version of MySQL 5.1.36

works fine for me (as root) in both command line client and SQLyog. Server 5.1.38 (64 bit for Windows)

mysql> CREATE DATABASE IF NOT EXISTS sample;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `sample`.`RandomZipCodeID` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION `sample`.`RandomZipCodeID`() RETURNS INT(11)
    -> BEGIN
    ->
    ->   DECLARE rv INT;
    ->   SET rv = 1;
    ->   RETURN rv;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> SHOW CREATE FUNCTION `sample`.`RandomZipCodeID`;
+-----------------+-------------------------------------------------------------
---+----------------------------------------------------------------------------
-------------------------------------------------------------+------------------
----+----------------------+--------------------+
| Function        | sql_mode
   | Create Function
                                                             | character_set_cli
ent | collation_connection | Database Collation |
+-----------------+-------------------------------------------------------------
---+----------------------------------------------------------------------------
-------------------------------------------------------------+------------------
----+----------------------+--------------------+
| RandomZipCodeID | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI
ON | CREATE DEFINER=`root`@`localhost` FUNCTION `RandomZipCodeID`() RETURNS int(
11)
BEGIN

  DECLARE rv INT;
  SET rv = 1;
  RETURN rv;

END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------------+-------------------------------------------------------------
---+----------------------------------------------------------------------------
-------------------------------------------------------------+------------------
----+----------------------+--------------------+
1 row in set (0.00 sec)

mysql>

.. and ..

SELECT `sample`.`RandomZipCodeID`();

/* returns
`sample`.`RandomZipCodeID`()
----------------------------
                           1
*/

.. and I am not a MySQL person!

I was using 32-bit

BTW I made sure my.ini has
'log_bin_function_trust_creators'
since I did not use the keyword DETERMINISTIC

I do not have this in my configuration!  
But I do not user replication here either.  
Do you?

No, not in the Windows platform.
But, I do use binary logs.

Thank you for the report.

I can not repeat described behavior.

Did you do upgrade? Did you run mysql_upgrade if you did upgrade?

Please send us full error log file: probably it contains some information.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".