MySQL Bugs: #9503: SP: after some SP function errors server will not send OK (client hangs)

Bug #9503	SP: after some SP function errors server will not send OK (client hangs)
Submitted:	30 Mar 2005 23:12	Modified:	24 Jun 2005 13:03
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.4-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
I have two Linux shells running. I started the server with --skip-networking. On shell#1, I start mysql client with --user=root. On shell#2, I start mysql client with --user=user17 (so I would see 'user17@localhost' if I said 'select user()'). As root, I make a few objects and grant use of them to user17@localhost. Meanwhile as user17@localhost, I try to use the objects.
After some back-and-forth activity, the mysql client for user17@localhost hangs on shell#2.

Since I believe that it only happens with a CREATE VIEW statement, I have marked this as a server bug rather than a command-line clients bug.

How to repeat:
as user root, on shell #1, say:
delimiter //
create database db33//
use db33//
create table tg (s1 int)//
create view vg as select * from tg//
grant create view on vg to user17@localhost//
create function fg () returns int sql security invoker begin declare v int; select min(s1) into v from tg; return v; end//

as user user17@localhost, on shell #2, say:
delimiter //
use db33//
select fg() /* this will fail */ //

as user root, on shell #1, say:
grant execute on fg to user17@localhost//

as user user17@localhost, on shell #2, say:
select fg() /* this will fail */ //

as user root, on shell #1, say:
grant select on tg to user17@localhost//

as user user17@localhost, on shell #2, say:
create view vg as select * from tg /* this will fail */ //

as user root, on shell #1, say:
drop view vg//

as user user17@localhost, on shell #2, say:
create view vg as select * from tg /* this will hang */ //

I can't repeat this test suite:

mysql> grant execute on fg to user17@localhost//
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used
mysql>

Due to a change that occurred after the bug report,
the syntax of GRANT EXECUTE is now
"GRANT EXECUTE ON { PROCEDURE | FUNCTION } routine_name ..."
instead of
"GRANT EXECUTE ON routine_name ..."
Therefore the statement in the example script should now be:
mysql> grant execute on function fg to user17@localhost//
I have tested again; the bug still exists.

Bug have nothing with views at all, here is some shorter mysql-test script lead to this bug:
onnect (root,localhost,root,,test);
connection root;
delimiter //;
create database mysqltest//
use mysqltest//
create table t1 (s1 int)//
grant select on t1 to user1@localhost//
create function f1 () returns int sql security invoker begin declare v int;
select min(s1) into v from t1; return v; end//
delimiter ;//

connect (user1,localhost,user1,,test);
connection user1;
use mysqltest;
-- error 1370
select f1();

connection root;
grant execute on function f1 to user1@localhost;

connection user1;
do 1;

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

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

pushed to 5.0.9

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

Additional info:

Documented in 5.0.9 changelog; closed bug.