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)
Let's start with the error message:
Replication is complaining about the index called
app
. Evidently, you have a UNIQUE index on that column. Also, note the query'sON DUPLICATE KEY
clause. You haveapp=VALUES(app)
. This column cannot be replaced inON DUPLICATE KEY
for two reasons:ON DUPLICATE KEY
actionapp
if the action were allowedRecommendation : You should remove
app=VALUES(app)
from theON 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.