Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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)
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Although I can't do this (it's off-the-shelf software) it is the right answer - I will show the developers this message and see if they can sort something out. Thanks :) –  Leonard Challis Jan 17 '12 at 10:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.