MySQL Bugs: #6776: UDFs are not really dropped

Bug #6776	UDFs are not really dropped
Submitted:	23 Nov 2004 18:43	Modified:	9 Apr 2005 6:41
Reporter:	Per-Erik Martin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.1 bk	OS:	Unix
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
When doing a "DROP FUNCTION udfname" no error is given, but it turns
out that it's not removed from the mysql.func table.

How to repeat:
Create a UDF, drop it, and then check the contents of mysql.func.
An easy way to do this is to run the udf_test/udf_example:

Copy udf_example.so to some place where the server will find it (/usr/lib for example).

Start mysqld

cd tests in the source tree.

Start the mysql client:

mysql> source udf_test
/...output omitted.../
mysql> select * from mysql.func;
+----------------------+-----+----------------+-----------+
| name                 | ret | dl             | type      |
+----------------------+-----+----------------+-----------+
| metaphon             |   0 | udf_example.so | function  |
| myfunc_double        |   1 | udf_example.so | function  |
| myfunc_int           |   2 | udf_example.so | function  |
| lookup               |   0 | udf_example.so | function  |
| reverse_lookup       |   0 | udf_example.so | function  |
| avgcost              |   1 | udf_example.so | aggregate |
| myfunc_argument_name |   0 | udf_example.so | function  |
+----------------------+-----+----------------+-----------+
7 rows in set (0.00 sec)

mysql> 

The table should be empty at this point.

I've never experienced this with my UDF toy projects and haven't been
able to reproduce it with latest bitkeeper yesterday either.

It might be a character set related problem so could you please add
the relevant charset and collation entries from SHOW VARIABLES
and SHOW STATUS to this report?

With a newly created system database (mysql_install_db), and no special
configuration in .my.cnf (i.e. default settings only):

mysql> show create table mysql.func;
[...]
| func  | CREATE TABLE `func` (
  `name` char(64) collate utf8_bin NOT NULL default '',
  `ret` tinyint(1) NOT NULL default '0',
  `dl` char(128) collate utf8_bin NOT NULL default '',
  `type` enum('function','aggregate') collate utf8_bin NOT NULL default 'function',
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions' |
[...]
1 row in set (0.01 sec)

mysql> show variables like 'character_set_%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /usr/local/mysql-5.0/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
7 rows in set (0.00 sec)

mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)

mysql>

Correction: This is repeatable in 4.1, with a newlycreated database.
Apparently the change from latin1 to utf8 collations when creating
the mysql.func table is the problem.
(The frst attempt in 4.1 was with an existing 4.1 database where it
was latin1.)

I also have encountered this bug on XP and NT.  When we want to drop a UDF, we must DROP FUNCTION funcname and then check to see if it was deleted from mysql.func and deleted ourself if it was not.

I am now experiencing the same with 4.1.8 and 4.1.9 consistently,
no idea why i wasn't able to reproduce this before.

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/internals/22729

This patch has not been pushed into 4.1.11. Is there a reason not to apply it?

bye,

-christian-

No, you can apply it if you wish.
It'll go into 4.1.12 (did not make into 4.1.11 unfortunately)

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/internals/23816

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Pushed to 4.1 and 5.0 repositories