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: | |
Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
Version: | 5.7.19 | OS: | Windows |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
Tags: | Adding Multiple Databases to replication, binlog |
[7 Oct 2017 8:03]
mohamed atef
[9 Oct 2017 9:28]
MySQL Verification Team
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
[9 Oct 2017 19:12]
mohamed atef
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
[9 Oct 2017 19:36]
MySQL Verification Team
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
[9 Oct 2017 20:35]
MySQL Verification Team
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
[9 Oct 2017 21:29]
mohamed atef
### 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