MySQL Bugs: #33464: DROP FUNCTION caused a crash.

Bug #33464	DROP FUNCTION caused a crash.
Submitted:	21 Dec 2007 17:02	Modified:	11 Apr 2008 14:05
Reporter:	Horst Hunger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.1.23-rc	OS:	Linux (Suse 10.1)
Assigned to:	Chad MILLER	CPU Architecture:	Any

Description:
the funcs_1 test storedproc_06 crashed. The attached show program reproduced the bug showing the following output:
mysqltest: At line 40: query 'DROP FUNCTION fn1' failed: 2013: Lost connection to MySQL server during query

The result from queries just before the failure was:
< snip >
grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
flush privileges;

user_1@localhost        db_storedproc_1
DROP PROCEDURE IF EXISTS sp3;
DROP FUNCTION IF EXISTS fn1;
CREATE PROCEDURE sp3(v1 char(20))
BEGIN
SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
END//
CREATE FUNCTION fn1(v1 int) returns int
BEGIN
return v1;
END//
USE db_storedproc_1;

root@localhost  db_storedproc_1
drop user 'user_1'@'localhost';
DROP PROCEDURE sp3;
DROP FUNCTION fn1;

The backtrace:
Core was generated by `/work/merge/mysql-5.1-build/sql/mysqld --no-defaults --basedir=/work/merge/mysq'.
Program terminated with signal 6, Aborted.

warning: Can't read pathname for load map: Eingabe-/Ausgabefehler.
Reading symbols from /lib/libpthread.so.0...done.
Loaded symbols for /lib/libpthread.so.0
Reading symbols from /lib/libz.so.1...done.
Loaded symbols for /lib/libz.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/libcrypt.so.1...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libm.so.6...done.
Loaded symbols for /lib/libm.so.6
Reading symbols from /lib/libc.so.6...done.
Loaded symbols for /lib/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libgcc_s.so.1...done.
Loaded symbols for /lib/libgcc_s.so.1
#0  0xffffe410 in __kernel_vsyscall ()
(gdb) bt
#0  0xffffe410 in __kernel_vsyscall ()
#1  0xb7f1f5f6 in pthread_kill () from /lib/libpthread.so.0
#2  0x08412b43 in write_core (sig=6) at stacktrace.c:240
#3  0x0828299b in handle_segfault (sig=6) at mysqld.cc:2309
#4  <signal handler called>
#5  0xffffe410 in __kernel_vsyscall ()
#6  0xb7d9a7d0 in raise () from /lib/libc.so.6
#7  0xb7d9bea3 in abort () from /lib/libc.so.6
#8  0xb7d9401b in __assert_fail () from /lib/libc.so.6
#9  0x0826b1ad in Diagnostics_area::set_ok_status (this=0x89c4f08, thd=0x89c4270,
    affected_rows_arg=0, last_insert_id_arg=0, message_arg=0x0) at sql_class.cc:389
#10 0x081b0973 in send_ok (thd=0x89c4270, affected_rows=0, id=0, message=0x0)
    at sql_class.h:2105
#11 0x0829a333 in mysql_execute_command (thd=0x89c4270) at sql_parse.cc:4197
#12 0x0829bbde in mysql_parse (thd=0x89c4270, inBuf=0x8a3bb38 "DROP FUNCTION fn1",
    length=17, found_semicolon=0xb745031c) at sql_parse.cc:5565
#13 0x0829c79d in dispatch_command (command=COM_QUERY, thd=0x89c4270,
    packet=0x8a2cc81 "DROP FUNCTION fn1", packet_length=17) at sql_parse.cc:1090
#14 0x0829d953 in do_command (thd=0x89c4270) at sql_parse.cc:764
#15 0x0828b39a in handle_one_connection (arg=0x89c4270) at sql_connect.cc:1120
#16 0xb7f1b34b in start_thread () from /lib/libpthread.so.0
#17 0xb7e2f65e in clone () from /lib/libc.so.6
(gdb)

How to repeat:
put the attached program into t and call mysql-test-run.pl bug

The shortest way to exercise the bug is to have user X create a stored procedure or function, and then DROP the user, and then DROP the routine.

The cause of the crash is an assertion failure that we do not emit an error message (user not found) and then return "ok".  The assertion is valid, and we were ignoring the buggy behavior prior to the "Diagnostics" result-verification.

T@3    : | | | | | >replace_routine_table
T@3    : | | | | | | >find_acl_user
T@3    : | | | | | | | enter: host: 'localhost'  user: 'userbug33464'
T@3    : | | | | | | | info: strcmp('userbug33464','root'), compare_hostname('localhost','localhost'),
T@3    : | | | | | | | info: strcmp('userbug33464','root'), compare_hostname('localhost','zippy'),
T@3    : | | | | | | | info: strcmp('userbug33464','root'), compare_hostname('localhost','127.0.0.1'),
T@3    : | | | | | | <find_acl_user
T@3    : | | | | | | | error: error: 1133  message: 'Can't find any matching row in the user table'

The simplest thing that could possibly work is to push a flag from the SP code into the ACL code that means "do not emit an error".  That is ugly, though, and I'm averse to it.

---

The best fix, I think, is to avoid the error message altogether.  There is a hidden bug in behavior, and this would fix it:

When we drop a user, we should also drop all routines defined by that user. Reasons:

- This is the behavior that is most like all other DBMSes (though our user/schema model doesn't map directly to the other DBMSes).

- Storing the SECURITY CONTEXT DEFINER value as a string and looking up the user based on that string at routine-execution-time is a big gaping security problem.   Heirs of a username shouldn't automatically and silently be heirs of executing code.

---

An alternative is to refuse to drop users so long as they have routines defined.  This was my first idea, but antony and trudy convinced me otherwise.

Chad,
as a quick advice, try moving send_ok() out of the routine in question to the level of the caller and make the routine either return success or error.
But I will need to look more to come up with a better solution.

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

ChangeSet@1.2523, 2008-02-19 17:23:50-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#33464: DROP FUNCTION caused a crash
  
  The cause of the crash is an assertion failure that we do not emit 
  an error message (grant not found) and then return "ok".  The 
  assertion is valid, and we were ignoring the buggy behavior prior 
  to the "Diagnostics" result-verification.
  
  This patch moves the reporting of the message up to the three callers
  and in the one where it is not an error, it reports it as a warning.

Sent a review request by email.

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

ChangeSet@1.2523, 2008-02-22 17:16:21-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#33464: DROP FUNCTION caused a crash
  
  The cause of the crash is an assertion failure that we do not emit 
  an error message (grant not found) and then return "ok".  The 
  assertion is valid, and we were ignoring the buggy behavior prior 
  to the "Diagnostics" result-verification.
  
  Use an error handler to mutate missing-grant errors, when removing 
  routines, into messages.

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

ChangeSet@1.2523, 2008-02-25 12:34:23-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#33464: DROP FUNCTION caused a crash
  
  The cause of the crash is an assertion failure that we do not emit 
  an error message (grant not found) and then return "ok".  The 
  assertion is valid, and we were ignoring the buggy behavior prior 
  to the "Diagnostics" result-verification.
  
  Use an error handler to mutate innocuous missing-grant errors, when 
  removing routines, into warnings.

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

ChangeSet@1.2523, 2008-03-05 09:33:32-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#33464: DROP FUNCTION caused a crash
  
  The cause of the crash is an assertion failure that we do not emit 
  an error message (grant not found) and then return "ok".  The 
  assertion is valid, and we were ignoring the buggy behavior prior 
  to the "Diagnostics" result-verification.
  
  Use an error handler to mutate innocuous missing-grant errors, when 
  removing routines, into warnings.

Pushed to 5.1-build and 6.0-build.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented in the 5.1.24 and 6.0.5 changelogs as follows:

        Dropping a function after dropping the function's creator could
        cause the server to crash.