MySQL Bugs: #30422: DROP FUNCTION command is not working properly

Bug #30422	DROP FUNCTION command is not working properly
Submitted:	14 Aug 2007 23:06	Modified:	15 Nov 2007 17:53
Reporter:	Howard Finer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	5.0.46	OS:	Linux (2.6.9-42.ELsmp)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Using the mysql command line tool, create a user defined function using the create function command.  Try to drop the function with the DROP FUNCTION command.  The server often crashes.  When it does not crash, the response is often that the function does not exist.  However, if you try to create the function again, it reports that it does exist.

Note that when DROP FUNCTION reports that the function does not exist, the table mysql.func does show the entry.  By removing that entry, and restarting mysql, a recovery is possible.  Occaissionally just trying the DROP FUNCTION command numerous times eventually cleans things up.

(I am in the process of developing a user defined function and therefore need to reload it quite often.  This bug is making it very difficult to develop the UDF.)

How to repeat:
It is very repeatable via the steps above.

Suggested fix:
Fix the DROP FUNCTION command.

The mysql error file shows the following when the server crashes:

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb158a90c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8189a4a
0x97e3e7
0xcbad
New value of fp=0x5ab158ad failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x978e360 = drop function msgqueue
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Thank you for the bug report. Could you please test with the latest
released version 5.0.45. Thanks in advance.

It appears that 5.0.46-D is the currently accessible version at mysql.mysql.com.  Should I be using that one?

Thank you for the feedback.

Yes, you can test with versiion 5.0.46

Is 5.0.46 a GA release?  Which release is the latest GA release?  We don't want to be using a alpha/beta release.

I have updated to 5.0.46, and have managaed to crash the server again.  I believe the issue is this:
I am creating a function, and then am creating a trigger to use it.  If I forget to drop the trigger, and attempt to drop the function, the server crashes rather than preventing me from dropping the function.

One good thing with version 46 is that if I then go back into the mysql console and drop the trigger, I am able to properly drop the function.  This did not wortk in version 38.

Howard

Correction to my previous posting:  even when I drop the trigger first, the drop function statement still crashes the server:

mysql> drop trigger call_trace_trigger;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function msgqueue;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for the feedback.

I can not repeat described behaviour in myself environment.

Please provide output of select action_statement, event_manipulation, action_timing from information_schema.triggers where trigger_schema='YOUR_SCHEMA' and trigger_name='call_trace_trigger' and source of your UDF function.

The select statement returned the following:
mysql> select action_statement, event_manipulation, action_timing from information_schema.triggers where trigger_schema="APPDB" and trigger_name="call_trace_trigger";
+-------------------------------------+--------------------+---------------+
| action_statement                    | event_manipulation | action_timing |
+-------------------------------------+--------------------+---------------+
| DO dbchange("CALL_TRACE","dbproxy") | UPDATE             | AFTER         | 
+-------------------------------------+--------------------+---------------+
1 row in set (0.00 sec)

The udf is a very simple program, and simply uses the system call to call a program.  I have uploaded it.

Howard

user defined fuction that crashes mysql during the drop function statement

Attachment: UserDefinedFunction.cpp (application/octet-stream, text), 2.53 KiB.

the backtrace from running resolve_stack_trace is:
0x818f1a7 handle_segfault + 745
0x43d05a (?)
0xcbad5a (?)
0xb560fe (?)
0xcbb2bb (?)
0xcbad8a (?)
0x826e828 _Z19mysql_drop_functionP3THDPK13st_lex_string + 436
0x81aa3ec _Z21mysql_execute_commandP3THD + 22252
0x81ac171 _Z11mysql_parseP3THDPKcjPS2_ + 235
0x81acf4e _Z16dispatch_command19enum_server_commandP3THDPcj + 3240
0x81ad8a7 _Z10do_commandP3THD + 161
0x81ae283 handle_one_connection + 2299
0xdc8371 (?)
0xc2fffe (?)

Hi Howard, 

in which order do you perform the installation 
of a new UDF .so library file and dropping the
function?

I have seen crashes myself when installing a
new version of a UDF .so and only then trying
to DROP the function but never the other way round ...

I suspect that I have always copied over the new .so prior to attempting to drop the function.  I will test to see if dropping the function and then copying the .so prevents the problem from happening.

Do you have any results of your tests already?

I have not reproduced it with when first dropping the function and then copying the new .so over.

So, do you still think it is a bug? Looks like just a proper order of actions should be used. Maybe, we should just add warning to http://dev.mysql.com/doc/refman/5.0/en/drop-function.html?

I don't think it is a bug, but the documentation should be updated to reflect the order the commands must be issued in.

Thanks,
Howard

So, this is a documentation request.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I added a note about upgrading UDFs to the CREATE FUNCTION and DROP FUNCTION sections.