Description:
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.
Description: 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.