MySQL Forums
Forum List  »  Replication

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Query not replicating all records
1739
February 19, 2014 09:03AM
769
February 19, 2014 09:04AM
831
February 24, 2014 04:41PM
785
February 25, 2014 11:36AM
676
February 27, 2014 12:13AM


Sorry, you can't reply to this topic. It has been closed.

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.