MySQL Bugs: #88019: multi source replication Adding Multiple Databases to replication,binlog

Bug #88019	multi source replication Adding Multiple Databases to replication,binlog
Submitted:	7 Oct 2017 8:03	Modified:	9 Oct 2017 21:29
Reporter:	mohamed atef	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.7.19	OS:	Microsoft Windows
Assigned to:	Bogdan Kecman	CPU Architecture:	Any
Tags:	Adding Multiple Databases to replication, binlog

Description:
when using multi source replication with multiple databases in replicate_do_db
and binlog_do_db
the some transactions of the added datatbases
will be ignored

binlog-do-db=abc
binlog-do-db=abc2
replicate-do-db = abc
replicate-do-db = abc2

the database abc2 has some transactions will be ignored by the slave
and although log-slave-update is on
it will not written in slave log-bin file

How to repeat:
multi source replication Adding Multiple Databases to replication,binlog

Hi,

Check your binlog format and behavior explained here:
https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_repli...

note:
.. however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected ..

best regards
Bogdan

according to the document in the link
Row-based replication.  Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master:

USE prices;
UPDATE sales.february SET amount=amount+100;
The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, issuing the following statements on the master has no effect on the slave when using row-based replication and --replicate-do-db=sales:

USE prices;
UPDATE prices.march SET amount=amount-25;

and what is happening is

you can see in slave binlog that some transactions from database
abc_info
will be ignored and will not be written in slave binlog file
and will not be excuted in the slave
and this is some of config file of the master and slave

master-info-file = master.info
relay-log = abc-relay
replicate-do-db = abc
replicate-do-db = abc_info
enforce_gtid_consistency = ON
log-bin = abc-bin
master_info_repository = TABLE
relay_log_recovery
log_slave_updates
group_concat_max_len = 2048
sync_binlog = 1
max_heap_table_size = 2097152000
collation-server = utf8_unicode_ci
binlog-do-db = abc
binlog-do-db = abc_info
gtid-mode = ON
relay-log-info-repository = TABLE
binlog_format = ROW

when using multi source replication with multiple databases in
replicate_do_db
and binlog_do_db
the some transactions of the added datatbases
will be ignored

binlog-do-db=abc
binlog-do-db=abc_info
replicate-do-db = abc
replicate-do-db = abc_info

the database abc_info has some transactions will be ignored by the slave
and although log-slave-update is on
it will not written in slave log-bin file

Hi,

No, connect string does not help in any way.

What would help is step by step "what you do on master that is not reproduced on slave"

so connect to master via mysql client and execute something that will not be replicated to slave and show me what exactly you executed, and then show me the entries in the master and slave logs.

all best
Bogdan

I cannot compare the "situation" nor the "binlogs" with different versions of mysql. As I said, just binlog is not enough to determine anything, you need to show "what did you execute exactly on the master" in order to get to that situation.

You can for e.g. do on master

$ SET SESSION binlog_format = 'STATEMENT';
$ USE DATABASE_THAT_IS_NOT_REPLICATED;
$ DELETE FROM DATABASE_THAT_IS_REPLICATED.SOMETABLE;

And this delete will not go to slave.

So without what exactly you executed on master, that I can reproduce in controlled environment I can't agree there's a bug.

All best
Bogdan

### TABLE STRUCTURES
### THIS ONLY FOR DECLARE
USE ABC_INFO;
CREATE TABLE `abc_devices_logs` (
  `ID_` bigint(20) unsigned NOT NULL,
  `DEVICE_SERIAL` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `LOG_DATE` date NOT NULL,
  `LOG_STAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `LOG_DATEUN` bigint(20) unsigned GENERATED ALWAYS AS (cast(`LOG_DATE` as unsigned)) STORED,
  `LOG_STAMPUN` bigint(20) unsigned GENERATED ALWAYS AS (cast(`LOG_STAMP` as unsigned)) STORED,
  `LOGIN_STAMP` bigint(20) unsigned NOT NULL,
  `LOG_BY` bigint(20) NOT NULL,
  `LOG_IN` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID_`) USING BTREE,
  KEY `ABC_DEVICE_SERIAL_LOG` (`DEVICE_SERIAL`) USING BTREE,
  KEY `ABC_DEVICE_SERIAL_LOGDATE` (`LOG_DATE`) USING BTREE,
  KEY `ABC_DEVICE_SERIAL_LOGSTAMP` (`LOG_STAMP`) USING BTREE,
  KEY `ABC_DEVICE_LOG_BY_FK_idx` (`LOG_BY`) USING BTREE,
  KEY `LOGINSTDESC` (`LOGIN_STAMP`) USING BTREE
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

USE `abc_info`;
DROP TRIGGER IF EXISTS `abc_info`.`abc_devices_logs_BEFORE_INSERT`;

DELIMITER $$
USE `abc_info`$$
CREATE DEFINER=`root`@`%` TRIGGER `abc_info`.`abc_devices_logs_BEFORE_INSERT` BEFORE INSERT ON `abc_devices_logs` FOR EACH ROW
BEGIN
SET NEW.ID_=UUID_SHORT(),NEW.LOG_DATE=CURDATE(),NEW.LOG_STAMP=CURRENT_TIMESTAMP;

END$$
DELIMITER ;

USE ABC_INFO;
CREATE TABLE `abc_devices_data` (
  `DEV_ID` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_MAC` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_MAN` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_TYP` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_MOD` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_SYS` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_MEM` varchar(35) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_IP4` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `DEV_IP6` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `HOST_IP` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `LAST_TMST` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `LAST_STAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `LAST_BY` bigint(20) NOT NULL,
  PRIMARY KEY (`DEV_ID`) USING BTREE,
  KEY `abc_devices_dataDEV_MAC` (`DEV_MAC`) USING BTREE,
  KEY `abc_devices_dataDEV_MAN` (`DEV_MAN`) USING BTREE,
  KEY `abc_devices_dataDEV_TYP` (`DEV_TYP`) USING BTREE,
  KEY `abc_devices_dataDEV_MOD` (`DEV_MOD`) USING BTREE,
  KEY `abc_devices_dataLAST_STAMP` (`LAST_STAMP`) USING BTREE,
  KEY `abc_devices_dataLAST_BY` (`LAST_BY`) USING BTREE,
  CONSTRAINT `DEV_ID_LOG_FK` FOREIGN KEY (`DEV_ID`) REFERENCES `abc_devices_settings` (`DEVICE_SERIAL`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

USE ABC;
CREATE TABLE `abc_users_logs_io` (
  `ID_` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `USERNAME` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `STATE_IO` tinyint(1) NOT NULL DEFAULT '1',
  `LOGIN_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LOGOUT_TIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `DEVICE_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DEVICE_SERIAL` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PUBLIC_IP` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CONNECT_ID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `HOST_NAME` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DEVICE_IP` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DEVICE_IP6` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CONNECT_HOST_IP` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ASM_VER` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BLD_VER` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DEVICE_TIMEZONE` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SERVER_TIMEZONE` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DBA_USER` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DBA_HOST` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DBU_USER` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `DBU_HOST` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID_`),
   KEY `ABC_LOGS_IO_USERNAME_FK_1_idx` (`USERNAME`) USING BTREE,
  KEY `SERVERUS_IDX` (`DBA_USER`) USING BTREE,
  KEY `deviceip` (`DEVICE_IP`) USING BTREE,
  KEY `DEVICEIP6` (`DEVICE_IP6`) USING BTREE,
  KEY `PUBLICIP` (`PUBLIC_IP`) USING BTREE,
  KEY `CONNECTHOSTIP` (`CONNECT_HOST_IP`) USING BTREE,
  KEY `DBAHOST` (`DBA_HOST`) USING BTREE,
  KEY `DBUHOST` (`DBU_HOST`) USING BTREE,
  KEY `DBUUSER` (`DBU_USER`) USING BTREE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TRIGGER IF EXISTS `abc`.`abc_users_logs_io_BEFORE_INSERT`;

DELIMITER $$
USE `abc`$$
CREATE DEFINER=`root`@`%` TRIGGER `abc_users_logs_io_BEFORE_INSERT` BEFORE INSERT ON `abc_users_logs_io` FOR EACH ROW BEGIN
SET NEW.ID_=UUID_SHORT();

END$$
DELIMITER ;

### END OF TABLES STRUCTURE
### WHAT IS EXECUTED IN THE MASTER
INSERT INTO `abc_info`.`abc_devices_logs` (`DEVICE_SERIAL`,`LOG_DATE`,`LOG_STAMP`,`LOG_DATEUN`,`LOG_STAMPUN`,`LOGIN_STAMP`,`LOG_BY`,`LOG_IN`) 
VALUES ('6KF20D2','2017-09-29','2017-09-29 16:07:41',20170929,20170929130741,20170929160741246,2,'2017-09-29 16:07:41');

 INSERT INTO `abc_info`.`abc_devices_data`
 (DEV_ID, DEV_MAC, DEV_MAN, DEV_TYP, DEV_MOD, DEV_SYS, 
 DEV_MEM, DEV_IP4, DEV_IP6, HOST_IP, LAST_TMST, LAST_BY)
 VALUES 
 ('6KF20D2','64:00:6A:30:8B:06','Dell Inc.','Desktop','OptiPlex 5040','x64-based PC',
 '8441483264','192.168.2.50','fe80::99a0:dff8:2c0f:7000%5','V4','Friday 29-September-2017 04:07:41.247978 PM',2)
 ON DUPLICATE KEY 
 UPDATE 
 DEV_MAC='64:00:6A:30:8B:06',DEV_MAN='Dell Inc.',DEV_TYP='Desktop',DEV_MOD='OptiPlex 5040',DEV_SYS='x64-based PC',
 DEV_MEM='8441483264',DEV_IP4='192.168.2.50',DEV_IP6='fe80::99a0:dff8:2c0f:7000%5',HOST_IP='V4',
 LAST_TMST='Friday 29-September-2017 04:07:41.247978 PM',LAST_BY=2;
 
 
 INSERT INTO `abc`.`abc_users_logs_io`

( USERNAME, STATE_IO, LOGIN_TIME,  DEVICE_ID, DEVICE_SERIAL, PUBLIC_IP,
 CONNECT_ID, HOST_NAME, DEVICE_IP, DEVICE_IP6, CONNECT_HOST_IP, ASM_VER,
 BLD_VER, DEVICE_TIMEZONE, SERVER_TIMEZONE, DBA_USER, DBA_HOST)
 
 VALUES 
 ('ALI',1,'2017-09-29 16:07:41','4C4C4544-004B-4610-8032-B6C04F304432','6KF20D2','192.168.2.50',
 '3f0c1ae1-238c-410e-9eb5-ca0178184e81','HQWAREHOUSE','192.168.2.50','fe80::99a0:dff8:2c0f:7000%5','127.0.0.1','1.5.0.0',
 '1.5.0.0','Arab Standard Time - Diff : 03:00:00','03:00:00','cuphq','localhost');
 
 ### end of what executed in the master