MySQL Bugs: #17812: Previous lock table for write causes "stray" lock although table is recreated

Bug #17812	Previous lock table for write causes "stray" lock although table is recreated
Submitted:	1 Mar 2006 1:59	Modified:	9 Mar 2006 2:48
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.1.8	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Tomas Ulin	CPU Architecture:	Any

Description:
Hi,

This one took a while to track down. The test suite hung when running ./t/sp-threads.test. There are several bug test cases contained in this test file and each one run by itself causes no issues. But when 9486 & 11158 are run back to back the test will hang with the NDBD spinning.

connect (con1root,localhost,root,,);
connect (con2root,localhost,root,,);
connect (con3root,localhost,root,,);

connection con1root;
use test;

#
# BUG #9486 "Can't perform multi-update in stored procedure"
#
--disable_warnings
drop procedure if exists bug9486;
drop table if exists t1, t2;
--enable_warnings
create table t1 (id1 int, val int);
create table t2 (id2 int);

create procedure bug9486()
  update t1, t2 set val= 1 where id1=id2;
call bug9486();
# Let us check that SP invocation requires write lock for t2.
connection con2root;
lock tables t2 write;
connection con1root;
send call bug9486();
connection con2root;
--sleep 2
# There should be call statement in locked state.
--replace_column 1 # 3 localhost 6 #
show processlist;
unlock tables;
connection con1root;
reap;

drop procedure bug9486;
drop table t1, t2;

#
# BUG#11158: Can't perform multi-delete in stored procedure
#
--disable_warnings
drop procedure if exists bug11158;
--enable_warnings
create procedure bug11158() delete t1 from t1, t2 where t1.id = t2.id;
create table t1 (id int, j int);
insert into t1 values (1, 1), (2, 2);
create table t2 (id int);
insert into t2 values (1);
# Procedure should work and cause proper effect (delete only first row)
call bug11158();
select * from t1;
# Also let us test that we obtain only read (and thus non exclusive) lock
# for table from which we are not going to delete rows.
connection con2root;
lock tables t2 read;
connection con1root;
call bug11158();
connection con2root;
unlock tables;
connection con1root;
# Clean-up
drop procedure bug11158;
drop table t1, t2;

How to repeat:
Add the SQL from above into a files called ndb_1.test

./mysql-test-run --force --mysqld=--default-storage-engine=ndb --with-ndbcluster-all --ndb-extra-test --do-test=ndb_1

it is not binlog related as the same occurs with the mysql-test-run option

 --skip-master-binlog

Also this is most probably in 5.0 as well

simplified test case:

connect (con1root,localhost,root,,);
connect (con2root,localhost,root,,);

#
# this creating, locking, and dropping causes a subsequent hang on the delete below
# waiting for table t2
# the locking in the "other" connection is relevant, as without it there is no problem
#
connection con1root;
create table t2 (id2 int);

connection con2root;
lock tables t2 write;
unlock tables;

connection con1root;
drop table t2;

connection con1root;
create table t1 (id int, j int);
insert into t1 values (2, 2);
create table t2 (id int);

connection con2root;
lock tables t2 read;

connection con1root;
# here we get a hang although be shouldn't
delete t1 from t1, t2 where t1.id = t2.id;

connection con2root;
unlock tables;

connection con1root;
drop table t1, t2;

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

pushed to 5.1.8

(verified that this is not in 5.0)

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 bugfix in 5.1.8 changelog. Closed.