Bug #58481 RBR hangs when deleting non-indexed table
Submitted: 25 Nov 2010 6:39 Modified: 25 Nov 2010 7:08
Reporter: Giuseppe Maxia Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S1 (Critical)
Version:5.5.7,5.1.52 OS:Mac OS X
Assigned to: CPU Architecture:Any
Tags: INDEX, RBR, replication

[25 Nov 2010 6:39] Giuseppe Maxia
Description:
With row-based replication, the server hangs when performing replication of a delete statement coming applied to non-indexed table.

for example, using the employees database: (http://launchpad.net/test-db)

create table sal like salaries;
Query OK, 0 rows affected (0.01 sec)

alter table sal drop primary key, drop key emp_no;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

insert into sal select * from salaries where from_date < '1990-01-01';
Query OK, 286117 rows affected (4.71 sec)
Records: 286117  Duplicates: 0  Warnings: 0

#
#  check that replication is working fine
#

set binlog_format=row;
Query OK, 0 rows affected (0.0 sec)

delete from sal where from_date between '1989-01-03' and '1989-01-04' ;
Query OK, 416 rows affected (2.06 sec)

$ ./s1 -e 'show processlist'
+----+-------------+-----------+------+---------+------+------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                    | Info             |
+----+-------------+-----------+------+---------+------+------------------------------------------+------------------+
| 65 | system user |           | NULL | Connect |  227 | Waiting for master to send event         | NULL             |
| 66 | system user |           | NULL | Connect |    8 | invalidating query cache entries (table) | NULL             |
| 69 | msandbox    | localhost | NULL | Query   |    0 | NULL                                     | show processlist |
+----+-------------+-----------+------+---------+------+------------------------------------------+------------------+

#
# Notice that the query cache is disabled. So this behavior is definitely a bug.
#

# and after a while ....

$ ./s1 -e 'show processlist'
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                            | Info             |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+
| 65 | system user |           | NULL | Connect |  405 | Waiting for master to send event | NULL             |
| 66 | system user |           | NULL | Connect |  186 | Reading event from the relay log | NULL             |
| 74 | msandbox    | localhost | NULL | Query   |    0 | NULL                             | show processlist |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+

This is the same problem reported in bug#38207, which was incorrectly dismissed as a duplicate of a bug that is now closed.
As you can see, the problem is very much alive. The same issue happens in MySQL 5.1.

How to repeat:
use employees;
drop table if exists sal;
create table sal like salaries;
alter table sal drop primary key, drop key emp_no;
insert into sal select * from salaries where from_date < '1990-01-01';

set binlog_format=row;
delete from sal where from_date between '1989-01-03' and '1989-01-04' ;

# now check the process list in the slave
[25 Nov 2010 7:00] Shane Bester
Giuseppe, this is a duplicate of 
http://bugs.mysql.com/bug.php?id=53375

Please check if you agree.
[25 Nov 2010 7:08] Giuseppe Maxia
Duplicate of Bug#53375