Showing entries 1 to 2
Displaying posts with tag: duplicate entry (reset)
“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 …

[Read more]
Duplicate entries while deleting records?

This weekend I got a call from the support engineer on duty that night. He noticed in one of our clusters all slave machines were failing replication but he could not figure out why.

At first I thought it would be an easy problem of the master and slaves being out of sync and the row-based replication failing on not finding the row, but then I noticed all machines were actually still running statement-based replication. As far as I could recall we did that to circumvent another issue that has already been solved months ago but for some reason we never put it back to row-based replication.

A simple SHOW SLAVE STATUS revealed something similar to this:

Last_Errno: 1062
Last_Error: Error 'Duplicate entry '272369' for key 'PRIMARY'' on query. Default database: 'userdata'. Query: 'DELETE u, uf1, …

[Read more]
Showing entries 1 to 2