Why does replication fail with error Duplicate entry for key?

Question

We have been replicating for a year or two now with little problem. Sometimes we get an erroneous SQL query which stops replications, and we use the following commands to get it going again:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS \G;

This is usually fine, however today we've started getting errors while trying to replicate a (FYI Invision Power Board) database:

               Last_SQL_Error: Error 'Duplicate entry 'forums-pid-994' for key '
app'' on query. Default database: 'forum_db'. Query: 'INSERT INTO ibf_rep
utation_cache (`app`,`type`,`type_id`,`rep_like_cache`) VALUES('forums','pid',99
4,'a:2:{s:10:\"cache_data\";a:0:{}s:12:\"cache_expire\";i:1326339370;}') ON DUPL
ICATE KEY UPDATE app=VALUES(app),type=VALUES(type),type_id=VALUES(type_id),rep_l
ike_cache=VALUES(rep_like_cache)'

There are many queries like this it seems as part of the Invision Power Board software so getting rid of it isn't a choice. The weird thing is, when I run that exact query on the same MySQL server it goes through without problem.

Note: We upgraded from MySQL 5.1.36 to MySQL 5.5.16 yesterday so this is almost certainly related. It is on a Windows server.

Here is the layout of the table:

mysql> DESC forum_db.ibf_reputation_cache;
+----------------+---------------------+------+-----+---------+----------------+

| Field          | Type                | Null | Key | Default | Extra          |

+----------------+---------------------+------+-----+---------+----------------+

| id             | bigint(10) unsigned | NO   | PRI | NULL    | auto_increment |

| app            | varchar(32)         | NO   | MUL | NULL    |                |

| type           | varchar(32)         | NO   | MUL | NULL    |                |

| type_id        | int(10) unsigned    | NO   |     | NULL    |                |

| rep_points     | int(10)             | NO   |     | 0       |                |

| rep_like_cache | mediumtext          | YES  |     | NULL    |                |

+----------------+---------------------+------+-----+---------+----------------+

6 rows in set (0.12 sec)

Answer 1

Let's start with the error message:

               Last_SQL_Error: Error 'Duplicate entry 'forums-pid-994' for key '
app'' on query. Default database: 'forum_db'. Query: 'INSERT INTO ibf_rep
utation_cache (`app`,`type`,`type_id`,`rep_like_cache`) VALUES('forums','pid',99
4,'a:2:{s:10:\"cache_data\";a:0:{}s:12:\"cache_expire\";i:1326339370;}') ON DUPL
ICATE KEY UPDATE app=VALUES(app),type=VALUES(type),type_id=VALUES(type_id),rep_l
ike_cache=VALUES(rep_like_cache)'

Replication is complaining about the index called app. Evidently, you have a UNIQUE index on that column. Also, note the query's ON DUPLICATE KEY clause. You have app=VALUES(app). This column cannot be replaced in ON DUPLICATE KEY for two reasons:

1. it is the same column that triggered the ON DUPLICATE KEY action
2. it will needlessly affect the index app if the action were allowed

Recommendation : You should remove app=VALUES(app) from the ON DUPLICATE KEY clause.

It is possible that previous versions of MySQL simply ignored offending columns in the ON DUPLICATE KEY clause and the latest version you are now using is more aware of this issue.