Using MySQL Sandbox with replication (1 slave), tested this on 5.0.87 and 5.1.45 master [localhost] {msandbox} (test) > CREATE TABLE `a` ( -> `id` int(10) unsigned NOT NULL auto_increment, -> `c1` varchar(255) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB ; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > CREATE TABLE `b` ( -> `id` int(10) unsigned NOT NULL auto_increment, -> `a_id` int(10) unsigned NOT NULL, -> `op` enum('i','u','d') default 'i', -> `t` timestamp NOT NULL default CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB ; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > delimiter ;; master [localhost] {msandbox} (test) > create trigger a_ai after insert on a for each row begin insert into b (a_id, op) values (NEW.id, 'i'); end;; Query OK, 0 rows affected (0.01 sec) master [localhost] {msandbox} (test) > create trigger a_au after update on a for each row begin insert into b (a_id, op) values (NEW.id, 'u'); end;; Query OK, 0 rows affected (0.00 sec) ## STEP 1 ## MASTER - CONNECTION 1 master [localhost] {msandbox} (test) > insert into a (c1) values ('first row'); commit; Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) ## STEP 2 ## MASTER - CONNECTION 2 master [localhost] {msandbox} (test) > select * from a; +----+-----------+ | id | c1 | +----+-----------+ | 1 | first row | +----+-----------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > begin; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > update a set c1='first row+' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ## STEP 3 ## MASTER - CONNECTION 1 master [localhost] {msandbox} (test) > begin; Query OK, 0 rows affected (0.00 sec) master [localhost] {msandbox} (test) > insert into a (c1) values ('second row'); Query OK, 1 row affected (0.00 sec) master [localhost] {msandbox} (test) > commit; Query OK, 0 rows affected (0.00 sec) ## STEP 4 ## MASTER - CONNECTION 2 master [localhost] {msandbox} (test) > commit; Query OK, 0 rows affected (0.00 sec) ## FINISHED, lets check results ## MASTER master [localhost] {msandbox} (test) > select * from a; select * from b; +----+------------+ | id | c1 | +----+------------+ | 1 | first row+ | | 2 | second row | +----+------------+ 2 rows in set (0.00 sec) +----+------+------+---------------------+ | id | a_id | op | t | +----+------+------+---------------------+ | 1 | 1 | i | 2010-04-21 18:21:47 | | 2 | 1 | u | 2010-04-21 18:22:29 | | 3 | 2 | i | 2010-04-21 18:22:33 | +----+------+------+---------------------+ 3 rows in set (0.00 sec) ## SLAVE slave1 [localhost] {msandbox} (test) > select * from a; select * from b; +----+------------+ | id | c1 | +----+------------+ | 1 | first row | | 2 | second row | +----+------------+ 2 rows in set (0.00 sec) +----+------+------+---------------------+ | id | a_id | op | t | +----+------+------+---------------------+ | 1 | 1 | i | 2010-04-21 18:21:47 | | 2 | 2 | i | 2010-04-21 18:22:33 | ## Notice this row is out of order on slave +----+------+------+---------------------+ ## UPDATE can not run on slave, because id=2 is already taken 2 rows in set (0.00 sec) slave1 [localhost] {msandbox} (test) > show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: msandbox Master_Port: 25160 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 2190 Relay_Log_File: mysql_sandbox25161-relay-bin.000002 Relay_Log_Pos: 2110 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'update a set c1='first row+' where id=1' Skip_Counter: 0 Exec_Master_Log_Pos: 1965 Relay_Log_Space: 2503 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'update a set c1='first row+' where id=1' 1 row in set (0.00 sec) ######### ## On the 5.0.87 slave, replication does not fail, but results are still wrong ######### slave1 [localhost] {msandbox} (test) > select * from a; select * from b; +----+------------+ | id | c1 | +----+------------+ | 1 | first row+ | | 2 | second row | +----+------------+ 2 rows in set (0.00 sec) +----+------+------+---------------------+ | id | a_id | op | t | +----+------+------+---------------------+ | 1 | 1 | i | 2010-04-22 11:48:02 | | 2 | 2 | i | 2010-04-22 11:48:25 | ## Notice that this row and the next are out of order | 3 | 1 | u | 2010-04-22 11:48:15 | ## But at least the UPDATE was able to execute +----+------+------+---------------------+ 3 rows in set (0.00 sec) ######### ## full dump of binary log from 5.1.45 master ######### /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #100422 11:34:19 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log created 100422 11:34:19 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' q5bQSw8BAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAACrltBLEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #100422 11:34:19 server id 1 end_log_pos 249 Query thread_id=1 exec_time=0 error_code=0 use mysql/*!*/; SET TIMESTAMP=1271961259/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; SET PASSWORD FOR 'root'@'localhost'='*6C387FC3893DBA1E3BA155E74754DA6682D04747' /*!*/; # at 249 #100422 11:34:19 server id 1 end_log_pos 366 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1271961259/*!*/; grant all on *.* to msandbox identified by 'msandbox' /*!*/; # at 366 #100422 11:34:19 server id 1 end_log_pos 464 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1271961259/*!*/; delete from user where password='' /*!*/; # at 464 #100422 11:34:19 server id 1 end_log_pos 556 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1271961259/*!*/; delete from db where user='' /*!*/; # at 556 #100422 11:34:19 server id 1 end_log_pos 636 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1271961259/*!*/; flush privileges /*!*/; # at 636 #100422 11:35:24 server id 1 end_log_pos 834 Query thread_id=6 exec_time=0 error_code=0 use test/*!*/; SET TIMESTAMP=1271961324/*!*/; CREATE TABLE `a` ( `id` int(10) unsigned NOT NULL auto_increment, `c1` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB /*!*/; # at 834 #100422 11:35:27 server id 1 end_log_pos 1128 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961327/*!*/; CREATE TABLE `b` ( `id` int(10) unsigned NOT NULL auto_increment, `a_id` int(10) unsigned NOT NULL, `op` enum('i','u','d') default 'i', `t` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB /*!*/; # at 1128 #100422 11:35:36 server id 1 end_log_pos 1321 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961336/*!*/; CREATE DEFINER=`msandbox`@`%` trigger a_ai after insert on a for each row begin insert into b (a_id, op) values (NEW.id, 'i'); end /*!*/; # at 1321 #100422 11:35:40 server id 1 end_log_pos 1514 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961340/*!*/; CREATE DEFINER=`msandbox`@`%` trigger a_au after update on a for each row begin insert into b (a_id, op) values (NEW.id, 'u'); end /*!*/; # at 1514 #100422 11:35:53 server id 1 end_log_pos 1582 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961353/*!*/; BEGIN /*!*/; # at 1582 #100422 11:35:53 server id 1 end_log_pos 1610 Intvar SET INSERT_ID=1/*!*/; # at 1610 #100422 11:35:53 server id 1 end_log_pos 1712 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961353/*!*/; insert into a (c1) values ('first row') /*!*/; # at 1712 #100422 11:35:53 server id 1 end_log_pos 1739 Xid = 35 COMMIT/*!*/; # at 1739 #100422 11:36:40 server id 1 end_log_pos 1807 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961400/*!*/; BEGIN /*!*/; # at 1807 #100422 11:36:33 server id 1 end_log_pos 1835 Intvar SET INSERT_ID=2/*!*/; # at 1835 #100422 11:36:33 server id 1 end_log_pos 1938 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1271961393/*!*/; insert into a (c1) values ('second row') /*!*/; # at 1938 #100422 11:36:40 server id 1 end_log_pos 1965 Xid = 43 COMMIT/*!*/; # at 1965 #100422 11:36:43 server id 1 end_log_pos 2033 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1271961403/*!*/; BEGIN /*!*/; # at 2033 #100422 11:36:21 server id 1 end_log_pos 2061 Intvar SET INSERT_ID=2/*!*/; # at 2061 #100422 11:36:21 server id 1 end_log_pos 2163 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1271961381/*!*/; update a set c1='first row+' where id=1 /*!*/; # at 2163 #100422 11:36:43 server id 1 end_log_pos 2190 Xid = 40 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; ######## ## EXPLANATION ######## At position 1087 in binary log, the SET INSERT_ID=2; statement applies to the next sequence generated by the INSERT INTO `a`; However, at position 2033 in binary log, the SET INSERT_ID=2; statement applies to the next sequence generated by the INSERT INTO `b`, which is in fact run inside the TRIGGER and therefor not present in the binary log. Replication in 5.0.87 did not include the SET INSERT_ID statement at 2033, therefor the slave was able to execute the UPDATE statement successfully, even though it resulted in out-of-order records / records with different sequence id on the slave. I do not know if there is any good solution to this besides RBR, since it is impractical to modify the SET INSERT_ID syntax to specify which table / sequence that ID should be used by. At a minimum, I recommend removing the SET INSERT_ID at position 2033, thus returning to the functinality of 5.0.87 -- at least this will allow SBR to function, albeit yielding incorrect results.