MySQL Bugs: #2485: CREATE FUNCTION fails and mysql lies about why.

Bug #2485	CREATE FUNCTION fails and mysql lies about why.
Submitted:	22 Jan 2004 15:39	Modified:	6 Aug 2004 4:07
Reporter:	Dale Hopkins	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.0-alpha an 4.0.17	OS:	Linux (RHEL 3 + Update 1 x86)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
mySQL refuses to create UDFs.  mySQL returns the following no matter what function name is typed in, and regardless of what the SONAME is:
ERROR 1125 (HY000): Function 'danger_is_my_middle_name' already exist

where 'danger_is_my_middle_name' is some arbitrary function name.

The command:
CREATE FUNCTION DoesNotExist77 RETURNS INTEGER SONAME "/dev/null"
fails, not because the file "/dev/null" does not contain any data, but rather because mySQL seems to think that the function DoesNotExist77 already exists.

How to repeat:
Install mySQL from the latest source with the following options:

./configure --prefix=/usr/local/mysql5 --with-mysqld-ldflags=-rdynamic
make
make install

Then try to create a UDF, it fails everytime.  I have tried this with both mySQL version 4.0.17 and wiht version 5.0.0-alpha, neither allow me to create a UDF.

Suggested fix:
Perhaps the check for whether a UDF already exists has a bug.

Try making the example UDF (sql/udf_example.cc) in the source distribution, and see if that also reports such errors.  The comments at the beginning of the file explain how to build and test it.

Otherwise, I am receiving meaningful error messages when I attempt to reference a nonexistent function, so try the sample and see what it reports.

Dean, I can compile the udf_example.cc program with the following command:

gcc -shared -o udf_example.so -lstdc++ -I/usr/local/mysql/include/mysql udf_example.cc

I put udf_example.so in a directory under the $LD_LIBRARY_PATH. Then I issued the following query to mySQL:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.0-alpha

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

mysql> CREATE FUNCTION metaphone RETURNS STRING SONAME "udf_example.so";             ERROR 1125 (HY000): Function 'metaphone' already exist

The problem seems to be that mySQL doesn't even look at the SONAME portion of the query it seems to just think that any UDF name already exists.

Thanks.  There was a definite problem with 5.0.0, that has already been corrected in the development source tree.  Can you try this with 4.0 and see what that reports?

No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.

I followed UDFs' example reported in http://dev.mysql.com/doc/mysql/en/UDF_compiling.html
.
I'm using mysql-4.1.1-alpha.tar.gz sources under linux, compiled with configuration --prefix=/ict/app/mysql4 --with-mysqld-ldflags=-rdynamic.
I used the udf_example.cc file and followed alll written in the documentation.

After the instruction 
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
I have the same error... 'Function already exists'.

I tested this issue against the latest BK 5.0 source tree and the example works fine:

[miguel@light sql]$ /home/miguel/dbs/5.0/bin/mysql --defaults-file=/home/miguel/dbs/5.0/my.cnf -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.2-alpha-debug-log

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

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
Query OK, 0 rows affected (0.00 sec)

mysql> select metaphon("qrqrqwrw");
+----------------------+
| metaphon("qrqrqwrw") |
+----------------------+
| KRKRKR               |
+----------------------+
1 row in set (0.00 sec)

mysql>