MySQL Bugs: #11158: Can't perform multi-delete in stored procedure

Bug #11158	Can't perform multi-delete in stored procedure
Submitted:	8 Jun 2005 7:18	Modified:	10 Jun 2005 20:02
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.7	OS:	Any (Any)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Multi-delete used in stored procedure fails with error telling that table from which delete should be performed was not locked for update.

How to repeat:
drop table if exists t1; 
create table t1 (
        id   char(16) not null default '',
        data int not null
);

drop table if exists t2;
create table t2 (
        s   char(16),
        i   int,
        d   double
);

delimiter | ;
drop procedure if exists bugNNNN|
create procedure bugNNNN() delete t1 from t1 , t2 where t1.id = t2.s|

insert into t1 values ('one', 1), ('two', 2)|
insert into t2 values ('one', 1, 1.0)|

# This generates error:
# ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
call bugNNNN()|

Suggested fix:
To be handled properly in stored routines multi-delete should report proper type of locks or elements of its table list right after the end of statement parsing, and not before execution as it happens now.

See also bug report #11060 as original source for this bug.

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

Fixed in 5.0.8

Noted in 5.0.8 changelog.