Query not replicating all records
Posted by:
Don Lewis
Date: February 19, 2014 09:03AM
I have multiple heiarchy slaves.
M1 replicates to M2 and M2 replicates to M3.
When I run a query:
INSERT INTO inventory_threshold (STORE,KSN,division,THRESHOLD,SOURCE,ROW_LAST_MOD_USER,ROW_LAST_MOD_TS) (SELECT a.store, a.ksn, a.div, a.threshold, a.source, a.row_last_mod_user, CURRENT_TIMESTAMP(6) FROM inventory_threshold_kettle a WHERE NOT EXISTS (SELECT 'X' FROM inventory_threshold b WHERE a.store = b.store AND a.ksn = b.ksn AND a.div = b.div));
It replicates to M2 fine, but M3 has a different number of records.
I drop and recreate the table each time, to verify it is not an autoincrement issue:
CREATE TABLE `inventory_threshold` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`STORE` char(7) NOT NULL,
`KSN` char(9) NOT NULL,
`DIV` char(5) NOT NULL,
`THRESHOLD` int(11) NOT NULL,
`SOURCE` char(7) NOT NULL DEFAULT '0007840',
`ROW_LAST_MOD_USER` char(8) NOT NULL DEFAULT '',
`ROW_LAST_MOD_TS` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`),
KEY `STOREDIVKSN` (`STORE`,`DIV`,`KSN`),
KEY `DIVKSN` (`DIV`,`KSN`)
);
server info:
version: 5.6.13-log
2.6.32-431.1.2.0.1.el6.x86_64
CentOS release 6.5 (Final)
binlog_format=mixed
I did run it with binlog_format=row, and it works fine, but there are other problems with going with row based replication.