MySQL Bugs: #97808: Truncate table in Master-slave replication

Bug #97808	Truncate table in Master-slave replication
Submitted:	27 Nov 2019 6:20	Modified:	27 Dec 2019 9:12
Reporter:	DBAJJ J	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	Master-slave, MySQL, replication

Description:
Hi,
I am using MySQL master slave replication. All the servers are using the version 5.6. The replication is in the format that Master A replicates to Slave B and Slave B replicates to Slave C.

I have issued a truncate table statement on the master and the changes got replicated on slave B, but not on Slave C. Can anyone explain logic behind this or if any configuration needs to be added.

All the 3 servers are using binlog-format MIXED.

How to repeat:
Create 3 Mysql Instances with binlog format mixed

Server 1 is master and the other 2 are slaves

Replication should be in the format

Server 1(Master) --> Server 2 (Slave 1) --> Server 3 (Slave 2)

Create tables and insert some values, finally issue a truncate on master and check on both slaves

Hi,

I am not able to reproduce this. Are other DML's issued on master replicated trough B to C and you only have issue with truncate or none of them go trough?

Can you share all three config files?

Master config

Attachment: master.cnf (application/octet-stream, text), 2.59 KiB.

Slave 1

Attachment: SlaveA.cnf (application/octet-stream, text), 2.67 KiB.

Slave 2

Attachment: SlaveB.ini (application/octet-stream, text), 13.83 KiB.

All DML statements are being replicated whereas TRUNCATE issued in master is replicated in 1st slave but not in 2nd slave, also have noticed that deletions done through workbench(without writing a query) are also not replicated to the 2nd slave but replicated in 1st slave. i doubt this functionality is due to filtered replication enabled in 2nd slave.

But the table belong to the replicated i.e(binlog-do-db) DB.

Is there any way to overcome this issue without issuing 'use DB' everytime

Hi,

This is not a bug.

> replicate-do-db=prodA
> replicate-do-db=prodB
...

Do the following test if you can and you will see it all works as expected. You can create similar env to what you have or perform it directly on your system taking care you know what you are doing.

On your master:

create database SOMEUNIQUEDATABASE;
use prodA;
create table SOMEUNIQUETABLE (x int auto_increment primary key, y int) engine = innodb;
insert into SOMEUNIQUETABLE values (1,1), (2,2), (3,3), (4,4);

-- check now on your slave 2 that you have this table and that data is there
-- now back on master

use prodA;
truncate SOMEUNIQUETABLE ;

-- check now on your slave 2 that table is truncated as expected
-- back to your master

use prodA;
insert into SOMEUNIQUETABLE values (1,1), (2,2), (3,3), (4,4);

-- check now on your slave 2 that you have this table and that data is there
-- now back on master

use SOMEUNIQUEDATABASE;
truncate prodA.SOMEUNIQUETABLE;

-- check now your slave, you see that table is not truncated, as expected, this is how filtering works

thanks