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
