0
160523 13:07:29 [ERROR] Slave SQL: Error 'Duplicate entry '914166' for key
'PRIMARY'' on query. Default database: 'zo_dev_20121216'. Query: 'UPDATE    
   activity
SET
   activity_type_id = 9,
   subject = 'Send departure email',
   date_due = '2016-05-26 01:00',
   date_start = '2016-05-23 01:00',
   activity_status_id = 1,
   content = 'Send departure email'
WHERE
   id = 2888555', Error_code: 1062
160523 13:07:29 [Warning] Slave: Duplicate entry '914166' for key 'PRIMARY'     
Error_code: 1062
160523 13:07:29 [ERROR] Error running query, slave SQL thread aborted. Fix     
the problem, and restart the slave SQL thread with "SLAVE START". We stopped   
at log 'mysql-bin.000004' position 14847360

Many people have suggested to use slave-skip-error option etc.. but that will completely avoid the query. How do you actually continue with the query that poses the error ? Also, I could not find a good answer as to why this actully occurs and what the number "914166" indicates ? Can someone please explain ?

enter image description here

enter image description here

Ravi
  • 2,472
  • 3
  • 20
  • 26
  • It means you have already inserted '914166' ID(Primary Key) in your slave database's table. So Primary key could not be duplicate that is why this error occurred. – Jay Doshi May 23 '16 at 14:05
  • There is no such number in the database table. I do not think it is pointing to a column value as there is another table in which a similar error occurs where the primary key is actually a string. But in the error it always shows a number. – Ravi May 23 '16 at 14:08
  • 1
    Is there a trigger on the table that does something to a different table? – stenix May 26 '16 at 06:00

2 Answers2

1

The number (914166) has to be an ID that is saved in an integer ID-column which is either the unique key or part of it.

Your query shows "id = 2888555" , which is probably the column MySQL is referring to.

Check values for the row with id 914166 and compare it to the values you wanted to set for id 2888555. You only have to look for columns that are part of your Primary Key. The values in these columns will be identical therefore MySQL throws out an error.

MySQL is basically saying:

Your query makes no sense! You are trying to update a row , but the columns you are trying to update are part of the Primary Key and there is already a row that has the same values. Please correct your query to not violate the Primary Key Constraint!

However there shouldn't be a problem as long as all your rows have different IDs. Posting table structure and indices would allow a more specific analysis of your problem at this point.

iLikeMySql
  • 736
  • 3
  • 7
  • There isn't a row in the table with the id 914166. I've updated the question with screenshots about structure and indices. – Ravi May 23 '16 at 15:23
  • Are you sure there isn't a row with that id on the slave? It could be that it's inserted in the slave, but not in the master. This way the master does the query ok, but the slave will not. – Nanne May 23 '16 at 15:24
  • There is a gap after id 335299. the next id after this is 2503679. Could this be a factor ? Also, no one has access to Slave for writes except the user used to replicate. – Ravi May 23 '16 at 15:32
  • have you checked both tables for id 914166 ? ther should be an entry in your slave. if not you have either a broken table or the error is thrown out accidentally instead of the real error (happens in mysql when there is an arbitrary error in your sql-code which triggers a seemingly random error message). – iLikeMySql May 24 '16 at 10:04
  • I recommend to double check your tables for the id 914166. If there really is none, check your code for errors. if that is fine too and the table is not too big, try to repair the table and repeat your update. – iLikeMySql May 24 '16 at 10:13
0

The triggered table causes the duplicate entry error. Thanks for the suggestions.

Ravi
  • 2,472
  • 3
  • 20
  • 26
  • Is the slave table the triggered table or is it another table that receives data from slave which is triggered through insert or update on slave? – iLikeMySql May 26 '16 at 11:26
  • another table that is triggerred by slave on update. I'm trying to use row based replication by setting binlog_format = 'ROW' but it still seems to invoke triggers on the slave though ? – Ravi May 26 '16 at 12:08
  • 1
    is the 'other table' a history-table or is it productive? do you really need a unique key on that 'other table'? Do you insert the data into the 'other table'? Because if yes it migh stay there and with the Unique Key on id you can only backup every id one time from slave. The problem would only show up if you try to backup data for the same id twice. Is that what happened? – iLikeMySql May 26 '16 at 12:26
  • It is a primary key in the other table which is an audit table. So, id would be auto increment and unique. You are correct about what has happened. The data got inserted into activity table -> trigger fired -> new row in audit table. Now due to replication (SBR), it tries to update the audit table and throws the duplicate error. – Ravi May 26 '16 at 12:33
  • Glad you could find the source of the problem. – iLikeMySql May 26 '16 at 13:06