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

[8 Jun 2005 7:18] Dmitry Lenev
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.
[8 Jun 2005 7:19] Dmitry Lenev
See also bug report #11060 as original source for this bug.
[8 Jun 2005 21:06] Bugs System
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
[8 Jun 2005 21:57] Dmitry Lenev
Fixed in 5.0.8
[10 Jun 2005 20:02] Paul DuBois
Noted in 5.0.8 changelog.