MySQL Version:5.5.17-55
Daily receiving below error on slave and slave is stopped with below error.
show slave status;
Relay_Log_File: mysqld-relay-bin.024556
Last_Error: Error 'Duplicate entry '3377' for key "PRIMARY" on query.Default database: 'jiwire'. Query:'INSERT INTO <tablename>(columnist) select distinct <columnlist> from table_name where <condition>;
Info: primary key column is auto increment and first column and not specified in insert statement.
Reviewed relay log file and found that before insert statement insert_id is set to 3377.
Queried database (master) and (slave) and found that both has record exists for 3377.
==MASTER==
mysql> select * from partner_payment_production_line_item where partner_payment_production_line_item_id = 3377\G
*************************** 1. row ***************************
partner_payment_production_line_item_id: 3377
name: 811-1_06_Retargeting_AdRoll_Display_160x600
partner_payment_id: 411
line_item_start_date: 2012-05-18
line_item_end_date: 2012-12-31
cost_method: 1
cost: 10.0000
ps_ad_id: 2628
order_name: Marriott - Manhattan Beach Marriott
object_type_id: 711
common_attributes_id: 1
production_line_item_id: 304
lineitem_name: 811-1_06_Retargeting_AdRoll_Display_160x600
cost_type_name: CPM
capping_limit: 291667
capped_on: impressions
1 row in set (0.00 sec)
===SLAVE====
mysql> select * from partner_payment_production_line_item where partner_payment_production_line_item_id = 3377\G
*************************** 1. row ***************************
partner_payment_production_line_item_id: 3377
name: Boingo Survey 300x250 KKW, KKD, HDC, CBB, CWS, JFK, EWR, LGA, DCA, IAD, BWI, ORD, MDW, TWS
partner_payment_id: 422
line_item_start_date: 2012-05-16
line_item_end_date: 2014-12-31
cost_method: 1
cost: 0.0000
ps_ad_id: 2646
order_name: Boingo access unlimited house ad 300x250
object_type_id: 711
common_attributes_id: 1
production_line_item_id: NULL
lineitem_name: Boingo Survey 300x250 KKW, KKD, HDC, CBB, CWS, JFK, EWR, LGA, DCA, IAD, BWI, ORD, MDW, TWS
cost_type_name: CPM
capping_limit: 0
capped_on: NULL
1 row in set (0.00 sec)
Please find some of important variable with value assigned to it on Master.
innodb_autoinc_lock_mode=1
auto_increment_increment=1
auto_increment_offset=1
Can you please advise what is causing replication fail and what are possible solutions with pros and cons.
Thanks for your help in advance.
How to repeat:
It is not reproducible.
Edited 1 time(s). Last edit at 05/23/2012 05:00AM by Amit Shah.