MySQL 5.1.* 奇怪的触发器复制行为

Question

我有一个 MySQL 主从配置。

在两台服务器上我都有两个表:table1 和 table2

我在两台服务器上都有以下触发器:

  Trigger: test_trigger
    Event: UPDATE
    Table: table1
Statement: insert into table2 values(null)
   Timing: AFTER

table2的结构如下:

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

问题是，在 MySQL 5.1.* 上，当从站调用触发器时，它会添加在主站上插入的 id，而不是根据自己的 auto_increment 值应插入的 id 。

假设我有以下数据:

关于主控:

SELECT * FROM table2;
Empty set (0.08 sec)

在从属设备上:

SELECT * FROM table2;

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

(忽略从机不是主机的完整镜像这一事实)

鉴于上述情况，当我在 Master 上更新 table1 中的一行时，Slave 停止并返回错误:

Error 'Duplicate entry '1' for key 'PRIMARY'' on query.

我不明白为什么从站试图插入特定的 ID。

很奇怪的是，在 MySQL 5.0.* 上这并没有发生。

Answer 1

如果可能，切换到基于行的复制。

除了基于语句的复制的最基本情况之外，自动增量几乎对任何事情都造成了破坏。

对于任何生成多个 auto_increment 值的语句(通过触发器、多行插入等)，只有第一个 auto_increment 值在从站上始终是正确的(仅记录第一个)。

如果从属设备从日志中读取 auto_increment 值，但不“使用”它，则该值将用于下一条语句(可能完全不相关)。当从机由于某种原因跳过相应的插入语句时(配置中忽略的表/数据库、过程/触发器中的条件插入等)，就会发生这种情况。

我在审计日志类型表上遇到了类似的问题(对于 table1 的每次更改，触发器都会在 table2 中插入一个事件)以及其他几个与自动增量相关的问题。

我不确定这个解决方案是否适合您的情况，但我将发布它以防万一:

• 向 table1 添加“updated_count”字段。它从 0 开始(插入时)，每次更新时加 1(使用 BEFORE INSERT/UPDATE 触发器)。

• 删除 table2 的 auto_increment 并将其 PK 更改为复合键(table1_pk,table1_update)。然后在 AFTER INSERT/UPDATE 触发器中使用 table1 的 PK 和“updated_count”作为 table2 的 PK。