MySQL Bugs: #13236: Multi-table update with subquery causes SQL thread to hold table lock

Bug #13236	Multi-table update with subquery causes SQL thread to hold table lock
Submitted:	15 Sep 2005 18:56	Modified:	14 Oct 2005 13:37
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1	OS:
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Multi-table update against a derived table causes SQL thread to maintain a table lock, causing all subsequent replicated statements to fail until SQL thread is restarted (because table_name is not locked with LOCK TABLES).

How to repeat:
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
INSERT INTO t1 VALUES (0);
INSERT INTO t2 VALUES (1),(2),(3);
UPDATE t1, (SELECT MAX(b) AS b FROM t2) AS x SET t1.a = x.b;
UPDATE t2 SET b = 1000;

Slave SQL thread will halt with ER_TABLE_NOT_LOCKED_FOR_WRITE, "Error 'Table 't2' was locked with a READ lock and can't be updated' on query. Default database: 'test'. Query: 'UPDATE t2 SET b = 1000'"

Suggested fix:
n/a

Thank you for bugreport.

I was no able to repeat this bug on current bk source repository of 4.1 with following mysql-test script:
source include/master-slave.inc;
--disable_warnings
drop table if exists t1,t2;
sync_slave_with_master;
reset master;
--enable_warnings

connection master;
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
INSERT INTO t1 VALUES (0);
INSERT INTO t2 VALUES (1),(2),(3);
UPDATE t1, (SELECT MAX(b) AS b FROM t2) AS x SET t1.a = x.b;
UPDATE t2 SET b = 1000;
sync_slave_with_master;

connection slave;
select * from t1;
select * from t2;

Feel free return it to open if you find bug in my script or you add some tips how to repeat this bug

Bug affect multiupdate queries, which use any type of subquery and  --replicate-ignore-table like condition is used on slave. such updates just skipped by slave.  Bug affect only 4.1 (it is impossible in 5.0 and above).

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

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

Thank you for bugreport!
Bugfix is pushed to 4.1.16.

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 4.1.16 changleog.

It is also happening with simple INSERTs and UPDATES in 4.1.13
Here is error for simple UPDATE
     Replicate_Ignore_Table: silo_s3.Member_DemogAnswerHistory,silo_s3.MemberMMinfo
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: silo_mme.%
                 Last_Errno: 1100
                 Last_Error: Error 'Table 'MemberMailInfo' was not locked with LOCK TABLES' on query. Default database: 'silo_s3'. Query: 'update MemberMailInfo set version=207, autoVerify=0, shortTerm_A_numSent=101, shortTerm_A_numBounced=6, shortTerm_B_numSent=24, shortTerm_B_numBounced=0, longTerm_A_numSent=200, longTerm_A_numBounced=7, longTerm_B_numSent=200, longTerm_B_numBounced=7 where member_id=201209357 and version=206'

This thing was running fine for few months and all of sudden this error started today