Bug #58481 RBR hangs when deleting non-indexed table
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
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.