Bug #65983 a executing subquery , never stop
Submitted: 23 Jul 2012 10:41 Modified: 23 Aug 2012 11:28
Reporter: jay cn Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.40 OS:Linux
Assigned to: CPU Architecture:Any

[23 Jul 2012 10:41] jay cn
Description:
the slave  host send a error, so login slave host to check this question ,only a query is running ,all are usual,next, check error log ,found:
120702 15:36:35 [ERROR] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query.Query: 'UPDATE tbla  SET b=8131808522759.

the query statement is reading tbla, i think the query block the update statement,but innodb is a nonlock engine,why this query block a update statement?

table  struct :
tbla: id ,a ,b,c,d,e,f
primary key id ,index b

query  statement:
select count(id) from tbla  where b  in (select b from tbla WHERE b >0 group by b  having count(b) > 1);

this query start, a long time past,but dont't return result,
if i  untuck it to two parts :
first, select b from tbla WHERE b >0 group by b  having count(b) > 1
second, select count(id) from tbla  where b  in (results in first);
it retun result.

why this query  don't return reslut and  happen a lock  ?

How to repeat:
mysql> create table  tbla(id int NOT NULL  auto_increment, b int,primary key (`id`)  )engine=innodb ;

product some datas:

for  i in seq `1 100000`;do echo "insert into tbla(b) values ($i)";done  >a.log

insert into  tabla :
mysql  < a.log 
and
insert into tbla(b) values (2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2),(2);

execute query :

select count(id) from tbla  where b  in (select b from tbla WHERE b >0 group by b  having count(b) > 1);

it will work a long time  , i don't wait it stop....

this time ,if you execute this query  :
update tbla  set  b=5 where id=7, the statement is blocked.

show innodb status  found: table in use 2, lock 1.
[23 Jul 2012 11:28] Valeriy Kravchuk
Please, check if the same problem still happens with a newer version, 5.1.63. If it does, please, send the output of:

explain select count(id) from tbla  where b  in (select b from tbla WHERE b >0 group by b  having count(b) > 1);

from your environment.
[24 Aug 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".