MySQL Bugs: #15439: UDF name case handling forces DELETE FROM mysql.func to remove the UDF

Bug #15439	UDF name case handling forces DELETE FROM mysql.func to remove the UDF
Submitted:	2 Dec 2005 14:30	Modified:	24 Jan 2007 20:40
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Windows (Windows XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Under some circumstances, case handling of UDF names leads to strange behaviour. This can result in a situation where the function can be successfully dropped without actually removing the corresponding entry from the mysql.func table. 

It can be inferred that the drop is indeed succesfull, because repeating the drop results in a 

ERROR 1305 (42000): FUNCTION mysql.cosh does not exist

At the same time, a SELECT on the func table proves the entry's still there.

When this situation occurs, the UDF cannot be recreated. The entry that's still in the func table blocks the create function ddl.
The only possibilty at that point is to manually remove the offending entry by issuing an appropriate DELETE on the func table.

It was expected that a successful drop would also remove the corresponding entry from the func table

How to repeat:
Here, we've already set up a dll ('OracleForMySQL.dll' in the bin directory under the MySQL root dir) that exports the functions cosH_init and cosH (the H is capital because for some (other?) reason, cosh can be compiled and created as UDF, but then always returns a 1. It's as if our own cosh is never reached in that case).
Once it is exported with the capital H, the CREATE FUNCTION command wants us to use it:

mysql> create function cosh returns real soname 'OracleForMySQL.dll';
ERROR 1127 (HY000): Can't find function 'cosh' in library'

Even though the backticks arent there to quote cosh (as in `cosh`)

Here comes the code:

mysql>  use mysql
Database changed

mysql> create function cosH returns real soname 'OracleForMySQL.dll';
Query OK, 0 rows affected (0.00 sec)

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

mysql> drop function cosh
    -> ;
ERROR 1305 (42000): FUNCTION mysql.cosh does not exist

mysql> select * from func;
+------+-----+--------------------+----------+
| name | ret | dl                 | type     |
+------+-----+--------------------+----------+
| cosH |   1 | OracleForMySQL.dll | function |
+------+-----+--------------------+----------+
1 row in set (0.00 sec)

mysql> create function cosH returns real soname 'OracleForMySQL.dll';
ERROR 1026 (HY000): Error writing file 'mysql.func' (errno: 121)
mysql> drop function `cosH`;
ERROR 1305 (42000): FUNCTION mysql.cosH does not exist

Now, all we can do is DELETE manually (risky business):

mysql> delete from func where binary name = 'cosH';
Query OK, 1 row affected (0.04 sec)

This proves it works after the DELETE

mysql> create function cosH returns real soname 'OracleForMySQL.dll';
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
mmm, hard to say. At least, the drop and the delete from the func tabe should not be detached. It should eiter fail completely, or succeed completely. 
At the same time, I do need to (like to) be able to refer to a case sensitive function name (as is apparent from the example)

Roland,

Thank you for a problem report. Can you upload a simplest .dll with only one UDF named 'cosH' (doing nothing special)? We'll need to create a repeatable test case anyway.

msvc 20003 project file, .cpp source, .def module definition .sql script, release dir with .dll and buildlog

Attachment: MySQLUDFCosHBug.zip (application/x-zip-compressed, text), 27.22 KiB.

Valeriy,

I've attached files. If you just need the dll, unzip (with path info) and grab the dll from the release subdirectory. The .cpp file contains the source and a description of the problem. The .sql script saves you some pain reproducing it in mysql. I put the .dll in the MySQL\bin dir BTW.

I hop it helps. Let me know if there's more you need.

regards, Roland

Verified as described on 5.0.16-nt with the code uploaded by reporter(http://bugs.mysql.com/file.php?id=2489):

mysql> use mysql;
Database changed
mysql> select * from func;
Empty set (0.00 sec)
mysql> create function cosh returns real soname 'MySQLUDFCosHBug.dll';
ERROR 1127 (HY000): Can't find function 'cosh' in library'
mysql> create function cosH returns real soname 'MySQLUDFCosHBug.dll';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from func;
+------+-----+---------------------+----------+
| name | ret | dl                  | type     |
+------+-----+---------------------+----------+
| cosH |   1 | MySQLUDFCosHBug.dll | function |
+------+-----+---------------------+----------+
1 row in set (0.00 sec)

mysql> select cosh();
+------------------+
| cosh()           |
+------------------+
| 0.00000000000000 |
+------------------+
1 row in set (0.00 sec)

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

mysql> select cosh();
ERROR 1305 (42000): FUNCTION mysql.cosh does not exist
mysql> select * from func;
+------+-----+---------------------+----------+
| name | ret | dl                  | type     |
+------+-----+---------------------+----------+
| cosH |   1 | MySQLUDFCosHBug.dll | function |
+------+-----+---------------------+----------+
1 row in set (0.00 sec)

mysql> drop function `cosH`;
ERROR 1305 (42000): FUNCTION mysql.cosH does not exist
mysql> create function cosH returns real soname 'MySQLUDFCosHBug.dll';
ERROR 1026 (HY000): Error writing file 'mysql.func' (errno: 121)
mysql> delete from func where name = 'cosH';
Query OK, 1 row affected (0.00 sec)

mysql> select * from func;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16-nt |
+-----------+
1 row in set (0.00 sec)

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

ChangeSet@1.2349, 2006-12-15 11:38:30+02:00, gkodinov@macbook.gmz +3 -0
  Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove
              the UDF
  When deleting a user defined function MySQL must remove it from both the
  in-memory hash table and the mysql.proc system table.
  Finding (and removal therefore) from the internal hash table is case 
  insensitive (or whatever the default charset is), whereas finding and 
  removal from the system table is case sensitive.
  As a result if you supply a function name that is not in the same character
  case to DROP FUNCTION the server will remove the function only from the
  in-memory hash table and will keep the row in mysql.proc system table.
  This will cause inconsistency between the two structures (that is fixed
  only by restarting the server).
  Fixed by using the name in the precise case (from the in-memory hash table)
  to delete the row in the mysql.proc system table.

Noted in 5.0.34, 5.1.15 changelogs.

Dropping a user-defined function sometimes did not remove the UDF
entry from the mysql.proc table.