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
• Status: No Feedback
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.1.36
• OS: Windows (XP)
• CPU Architecture: Any

[9 Sep 2009 18:58] Rolando Edwards

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

[9 Sep 2009 19:01] Rolando Edwards

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

[9 Sep 2009 19:08] Peter Laursen

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
*/

[9 Sep 2009 19:09] Peter Laursen

.. and I am not a MySQL person!

[9 Sep 2009 19:10] Rolando Edwards

I was using 32-bit

[9 Sep 2009 19:19] Rolando Edwards

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

[9 Sep 2009 19:22] Peter Laursen

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

[9 Sep 2009 19:23] Rolando Edwards

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

[9 Sep 2009 19:49] Sveta Smirnova

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.

[9 Oct 2009 23:00] Bugs System

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".