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:MacOS
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.