“Duplicate Entry” error for key PRIMARY on UPDATE query (RBR + Triggers)

Recently, I have faced one interesting issue with Master(Active)-Master(Passive) replication (RBR + Triggers). Passive master was stopped due to “Duplicate Entry” error with update statement. It was something like this.

Last_Error: Error ‘Duplicate entry ‘29014131’ for key ‘PRIMARY” on query. Default database: ‘db’. Query: ‘UPDATE `db`.`tab1` SET `empid`=’103′, `name`=’Nilnandan’,  `address`=’India ‘, `postcode`=’D100′, `phone`=’878 515 7788′;

Interesting thing was , id (primary key column) was not updated in above update statement. Initially I was confused but when I check further, found that both servers has binglog_format = row and there are some triggers in “tab1” table which is inserting records into “tab2” (Another table).

After some investigation, found that “If under row-based replication the slave applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.”

(http://dev.mysql.com/doc/refman/5.1/en/replication-features-triggers.html)

So looks like the same thing was happening here, when UPDATE tab1 statement runs on master server, it will trigger the procedure db.tab2_insert on the master. The statements from those triggers will replication to slave (passive master). When the UPDATE statement is translated to ROW, it would still be an update when it reaches the slave. So now the slave will have an UPDATE statement and INSERT statement from the triggers from the master. When the slave execute the update statement again it will fire its own trigger so triggers will be fired twice which leads to “Duplicate Entry” error.

Solution:

To avoid this issue,

1. Disable triggers on slave (Passive Master) if you want to use ROW based replication because changes will anyhow apply to slave.

2. If disable triggers on the slave is not possible solution for you due to some functionality then you can try and use STATEMENT based replication on session basis. (Check above manual link)

Leave a Reply