Description:
I am trying to setup Aurora Blue Green Replication , binlog format is row ,
we have few tables at primary database which has composite unique index and does not have any primary keys. Whenever there is a lag on Replica and Replace Into and Update ignore statements runs on these tables with composite unique index and no primary keys, The replication breaks with Duplicate entry or cannot find record update event,
2025-04-03T11:00:37.999427Z 563 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Could not execute Update_rows event on table brandcdn.tp_spectrum_tv_previous_airing_data; Duplicate entry 'sp_tv_demo_41286-3-29116-276-2025-03-21' for key 'tp_spectrum_tv_previous_airing_data.third_party_source_id', Error_code: 1062; Duplicate entry 'sp_tv_demo_41286-3-29116-276-2025-03-21' for key 'tp_spectrum_tv_previous_airing_data.third_party_source_id', Error_code: 1062;
CREATE TABLE `tp_spectrum_tv_previous_airing_data` (
`adgroupid` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`account_ul_id` bigint unsigned NOT NULL COMMENT 'Spectrum advertiser ul_id with region code prepended. Maps to ul_id column in tp_spectrum_accounts table.',
`third_party_source_id` int NOT NULL,
`third_party_account_id` int NOT NULL,
`zone_sys_code` int NOT NULL COMMENT 'The syscode of the zone. References id column in tp_spectrum_tv_zone_data.',
`date` date DEFAULT NULL,
UNIQUE KEY `third_party_source_id` (`adgroupid`,`third_party_source_id`,`third_party_account_id`,`zone_sys_code`,`date`),
KEY `account_ul_id` (`account_ul_id`),
KEY `date` (`date`),
KEY `third_party_source_id_2` (`third_party_source_id`),
KEY `adgroupid` (`adgroupid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Team:DATA Type:DataReporting Comments:This table contains airings data by date and syscode for a given ul id. Used in reports.'
/*!50100 PARTITION BY RANGE (to_days(`date`))
How to repeat:
Create table with composite unique indexes
Set up replication
Generate some lag, and run replace into or update ignore statements on primary database on the table
Replication will break
