MySQL 5.1.* Strange trigger replication behavior -

i have mysql master-slave configuration.

on both servers have 2 tables: table1 , table2

i have following trigger on both servers:

  trigger: test_trigger     event: update     table: table1 statement: insert table2 values(null)    timing: after 

the structure of table2 following:

+-------+---------+------+-----+---------+----------------+ | field | type    | null | key | default |          | +-------+---------+------+-----+---------+----------------+ | id    | int(11) | no   | pri | null    | auto_increment | +-------+---------+------+-----+---------+----------------+ 

the problem that, on mysql 5.1.*, when slave calls trigger adds id inserted on master , not id should insert according own auto_increment value.

let's have following data:

on master:

select * table2; empty set (0.08 sec) 

on slave:

select * table2;  +----+ | id | +----+ |  1 | +----+ 1 row in set (0.00 sec) 

(just ignore fact slave not complete mirror of master)

given above scenario, when update row table1 on master, slave stops , returns error:

error 'duplicate entry '1' key 'primary'' on query.

i don't see why slave tries insert specific id.

it's strange on mysql 5.0.* doesn't happen.

switch row-based replication if possible.

auto increment pretty broken basic cases statement based replication.

for statement generates more 1 auto_increment value (via triggers, multi row inserts, etc.) 1-st auto_increment value correct on slave (only 1-st logged).

if slave reads auto_increment value log, not 'use' it, value gets used next statement (which can unrelated). happens when slave skips corresponding insert statement reason (an ignored table/db in configuration, conditional insert in proc/trigger, etc.).

i had similar problem audit-log type table (a trigger inserts event in table2 every change table1) along several other auto-increment related problems.

i'm not sure solution fit case i'm going post in case:

• add 'updated_count' field table1. starts @ 0 (on insert) , gets incremented 1 on every update (using before insert/update triggers).

• remove table2's auto_increment , change pk composite key (table1_pk,table1_update). use table1's pk , 'updated_count' in after insert/update triggers table2's pk.