Por que a replicação falha com erro Entrada duplicada para chave?

Question

Estamos replicando há um ano ou dois agora com pouco problema. Às vezes, recebemos uma consulta SQL incorreta que interrompe as replicações e usamos os seguintes comandos para ativá-la novamente:

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

Geralmente isso é bom, mas hoje começamos a receber erros ao tentar replicar um banco de dados (FYI Invision Power Board):

               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)'

Existem muitas perguntas como esta, como parte do software Invision Power Board, portanto, livrar-se dele não é uma escolha. O mais estranho é quando eu executo aquela consulta exata no mesmo servidor MySQL que ela passa sem problemas.

Nota: Nós atualizamos do MySQL 5.1.36 para o MySQL 5.5.16 ontem, então isso é quase certamente relacionado. Está em um servidor Windows.

Aqui está o layout da tabela:

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

Vamos começar com a mensagem de erro:

               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)'

A replicação é reclamando sobre o índice chamado app . Evidentemente, você tem um índice UNIQUE nessa coluna. Além disso, observe a cláusula ON DUPLICATE KEY da consulta. Você tem app=VALUES(app) . Esta coluna não pode ser substituída em ON DUPLICATE KEY por dois motivos:

1. é a mesma coluna que acionou a ação ON DUPLICATE KEY
2. afetará desnecessariamente o índice app se a ação foi permitida

Recomendação: você deve remover app=VALUES(app) da cláusula ON DUPLICATE KEY .

É possível que versões anteriores do MySQL simplesmente ignorem colunas ofensivas na cláusula ON DUPLICATE KEY e a versão mais recente que você está usando agora esteja mais ciente deste problema.