MySQL Bugs: #45677: Slave stops with Duplicate entry for key PRIMARY when using trigger

Bug #45677	Slave stops with Duplicate entry for key PRIMARY when using trigger
Submitted:	23 Jun 2009 13:08	Modified:	4 Nov 2010 10:05
Reporter:	Victor Kirkebo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.35 and 5.4.0	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any
Tags:	auto_increment, autoinc, duplicate, ERROR 1062, innodb, primary key, replication, trigger

Description:
Concurrent transactions that insert rows into a table with an auto_increment column can cause statement or mixed based replication to break with slave reporting error 1062 (Error 'Duplicate entry 'x' for key 'PRIMARY'').

This seems to only happen if:
1) at least one of the transactions is preceded by autocommit=0 or is using an explicit transactional statement such as 'start transaction' or 'begin'.
2) the transaction under 1) is doing an insert, update or delete on an innodb table
3) the transaction under 1) causes a trigger to insert 2 or more rows into the table with the auto_increment column (this table doesn't need to be an innodb table - I've tried both myisam an innodb)
4) before the transaction under 1) does a commit (or possibly a rollback) another concurrent transaction causes an insert on the auto_increment table. 

How to repeat:
# t/rpl_failure.test

source include/have_innodb.inc;
source include/master-slave.inc;

connection master;
--disable_warnings
drop database if exists tst;
--enable_warnings
create database tst;
use tst;

create table t1(f1 int) engine=innodb;
create table log(i1 int not null auto_increment, f1 int, primary key(i1)) engine=myisam;
create trigger tr1 after insert on t1 for each row insert into log(f1) values(new.f1);

set autocommit=0;
insert into t1(f1) values(1),(2);

connection master1;
use tst;
insert into log(f1) values(3);

connection master;
commit;
insert into t1(f1) values(4);
commit;

connection master;
select * from log;

connection slave;
use tst;
select sleep(2);
select * from log;
show slave status;

# end of t/rpl_failure.test

Running the test above with binlog_format=statement or mixed and any innodb_autoinc_lock_mode e.g :
./mysql-test-run.pl  --mysqld=--binlog_format=statement --mysqld=--innodb_autoinc_lock_mode=0 --do-test=rpl_failure

will produce this output:
# master:
select * from log;
i1      f1
1       1
2       2
3       3
4       4

#slave:
select * from log;
i1      f1
1       1
3       3
4       2
show slave status;
Slave_IO_State  Master_Host     Master_User     Master_Port     Connect_Retry   Master_Log_File Read_Master_Log_Pos     Relay_Log_File  Relay_Log_Pos   Relay_Master_Log_File   Slave_IO_Running        Slave_SQL_Running       Replicate_Do_DB Replicate_Ignore_DB     Replicate_Do_Table      Replicate_Ignore_Table  Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table     Last_Errno      Last_Error      Skip_Counter   Exec_Master_Log_Pos     Relay_Log_Space Until_Condition Until_Log_File  Until_Log_Pos   Master_SSL_Allowed      Master_SSL_CA_File      Master_SSL_CA_Path      Master_SSL_Cert Master_SSL_Cipher       Master_SSL_Key  Seconds_Behind_Master   Master_SSL_Verify_Server_Cert   Last_IO_Errno   Last_IO_Error   Last_SQL_Errno  Last_SQL_Error
Waiting for master to send event        127.0.0.1       root    12500   1       master-bin.000001       1245    slave-relay-bin.000003  1179    master-bin.000001       Yes     No                          1062    Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'tst'. Query: 'insert into t1(f1) values(4)'  0       1033    1546    None            0       No                                           NULL    No      0               1062    Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'tst'. Query: 'insert into t1(f1) values(4)'

Thank you for the report.

Verified as described.

See also bug #41140

The above test case work fine if we split the multi-values insert sentence to single-value sentence as following:

insert into t1(f1) values(1),(2);
to:
insert into t1(f1) values(1);
insert into t1(f1) values(2);

Because we can write a separate "SET INSERT_ID=N" sentence for every insert row sentence to binlog. So every insert sentence triggered can get specific INSERT_ID VALUE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81366

3072 Dao-Gang.Qu@sun.com	2009-08-23
      Bug #45677  	Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      Concurrent transactions that cause a trigger to insert 2 or more rows 
      into a table with an auto_increment column will generate wrong auto_increment 
      values in statement-based replication, because we just write one specific 
      'SET INSERT_ID=n' sentence to binlog for the first insert sentence, 
      the second insert sentence will generate its auto_increment value 
      base on the current auto_increment value of the table instead of the value  
      of the last INSERT_ID. So the duplicate entry error will be caused for the 
      key PRIMARY. In this case, the duplicate entry error can't be avoided in 
      statement-based replication base on current architecture.
      
      In mixed mode, the problem has been resolved by seting the insert sentence to
      unsafe sentence when using trigger.
     @ mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result
        Added test result for bug#45677.
     @ mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test
        Added test to verify if Concurrent transactions that insert rows into a table 
        with an auto_increment column can cause mixed based replication to break with 
        slave reporting error 1062 (Error 'Duplicate entry 'x' for key 'PRIMARY'').

The same duplicate entry error produced when INSERT with AFTER/BEFOR TRIGGER, UPDATE with AFTER/BEFOR TRIGGER, and DELETE with AFTER/BEFOR TRIGGER in this situation.

The same duplicate entry error produced when insert multi-rows to a table which have a auto-increment column in one function in this situation.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82176

3095 Dao-Gang.Qu@sun.com	2009-09-02
      Bug #45677  	Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      Concurrent transactions that cause a trigger or call a function to insert or 
      update > 1 values into an autoinc column will generate wrong auto_increment 
      values in statement-based replication, because we just write one specific 
      'SET INSERT_ID=n' sentence to binlog for the first insert sentence, 
      the second insert sentence will generate its auto_increment value 
      base on the current auto_increment value of the table instead of the value  
      of the last INSERT_ID. So the duplicate entry error will be caused for the 
      key PRIMARY. In this case, the duplicate entry error can't be avoided in 
      statement-based replication base on current architecture.
      
      In mixed mode, the problem has been resolved by switching to row-based 
      binlogging.
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if concurrent transactions that cause a trigger or call
        a function to insert or update > 1 values into an autoinc column can cause
        mixed based replication to break with slave reporting error 1062 (Error
        'Duplicate entry 'x' for key 'PRIMARY'').
     @ sql/sql_base.cc
        Added function 'has_table_with_auto_increment_in_sub_statements' to check 
        if one (or more) tables have auto_increment columns in sub-statements.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_table_with_auto_increment_in_sub_statements'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82189

3095 Dao-Gang.Qu@sun.com	2009-09-02
      Bug #45677  	Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      Concurrent transactions that cause a trigger or call a function to insert or 
      update > 1 values into an autoinc column will generate wrong auto_increment 
      values in statement-based replication, because we just write one specific 
      'SET INSERT_ID=n' sentence to binlog for the first insert sentence, 
      the second insert sentence will generate its auto_increment value 
      base on the current auto_increment value of the table instead of the value  
      of the last INSERT_ID. So the duplicate entry error will be caused for the 
      key PRIMARY. In this case, the duplicate entry error can't be avoided in 
      statement-based replication base on current architecture.
      
      In mixed mode, the problem has been resolved by switching to row-based 
      binlogging.
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if concurrent transactions that cause a trigger or call
        a function to insert or update > 1 values into an autoinc column can cause
        mixed based replication to break with slave reporting error 1062 (Error
        'Duplicate entry 'x' for key 'PRIMARY'').
     @ sql/sql_base.cc
        Added function 'has_table_with_auto_increment_in_sub_statements' to check 
        if one (or more) tables have auto_increment columns in sub-statements.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_table_with_auto_increment_in_sub_statements'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82337

3093 Dao-Gang.Qu@sun.com	2009-09-03
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      One query that causes a trigger or calls a function to insert or 
      update > 1 autoinc fields is safe (but we do not distinguish this 
      because it is no possible), and the autoinc value is correct, the 
      problem is that there is only one autoinc value associated with 
      the query. So other autoinc values are generated base on the write 
      table instead of the last autoinc value. So These autoinc values 
      become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that cause 
      a trigger or call a function to insert or update autoinc fields 
      as unsafe, and will switch to row-format in Mixed mode.
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        suite/rpl/t/rpl_auto_increment_update_failure.test
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if one query that causes a trigger or 
        calls a function to insert or update > 1 autoinc fields 
        will make the autoinc values are become inconsistent on 
        master and slave.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82397

3093 Dao-Gang.Qu@sun.com	2009-09-04
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      One query that causes a trigger or calls a function to insert or 
      update > 1 autoinc fields is safe (but we do not distinguish this 
      because it is no possible), and the autoinc value is correct, the 
      problem is that there is only one autoinc value associated with 
      the query. So other autoinc values are generated base on the write 
      table instead of the last autoinc value. So These autoinc values 
      become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that cause 
      a trigger or call a function to insert or update autoinc fields 
      as unsafe, and will switch to row-format in Mixed mode.
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        suite/rpl/t/rpl_auto_increment_update_failure.test
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if one query that causes a trigger or 
        calls a function to insert or update > 1 autoinc fields 
        will make the autoinc values are become inconsistent on 
        master and slave.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82535

3093 Dao-Gang.Qu@sun.com	2009-09-06
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query. If more than one autoinc values are used in the query, the 
      autoinc values after the first one can be inserted wrongly on slave.
      So these autoinc values can become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        suite/rpl/t/rpl_auto_increment_update_failure.test
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if one query that causes a trigger or 
        calls a function to insert or update > 1 autoinc fields 
        will make the autoinc values are become inconsistent on 
        master and slave.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82623

3093 Dao-Gang.Qu@sun.com	2009-09-08
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query. If more than one autoinc values are used in the query, the 
      autoinc values after the first one can be inserted wrongly on slave.
      So these autoinc values can become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        suite/rpl/t/rpl_auto_increment_update_failure.test
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if one query that causes a trigger or 
        calls a function to insert or update > 1 autoinc fields 
        will make the autoinc values are become inconsistent on 
        master and slave.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/83153

3093 Dao-Gang.Qu@sun.com	2009-09-14
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query. If more than one autoinc values are used in the query, the 
      autoinc values after the first one can be inserted wrongly on slave.
      So these autoinc values can become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify if one query that invokes a trigger or
        calls a function that updated autoinc fields will make the 
        autoinc values become inconsistent on master and slave.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84726

3093 Dao-Gang.Qu@sun.com	2009-09-27
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query. If more than one autoinc values are used in the query, the 
      autoinc values after the first one can be inserted wrongly on slave.
      So these autoinc values can become inconsistent on master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify the following two properties:
        P1) insert/update in an autoinc column causes statement to 
        be logged in row format if binlog_format=mixed
        P2) if binlog_format=mixed, and a trigger or function contains 
            two or more inserts/updates in a table that has an autoinc 
            column, then the slave should not go out of sync, even if 
            there are concurrent transactions.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84783

3093 Dao-Gang.Qu@sun.com	2009-09-28
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query when binlogging. If more than one autoinc values are used 
      in the query, the autoinc values after the first one can be inserted 
      wrongly on slave. So these autoinc values can become inconsistent on 
      master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify the following two properties:
        P1) insert/update in an autoinc column causes statement to 
        be logged in row format if binlog_format=mixed
        P2) if binlog_format=mixed, and a trigger or function contains 
            two or more inserts/updates in a table that has an autoinc 
            column, then the slave should not go out of sync, even if 
            there are concurrent transactions.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

The bug just will be pushed to 5.1 base on the solution, 
we have a new solution for the bug on 5.4 later.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/84870

3139 Dao-Gang.Qu@sun.com	2009-09-28
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query when binlogging. If more than one autoinc values are used 
      in the query, the autoinc values after the first one can be inserted 
      wrongly on slave. So these autoinc values can become inconsistent on 
      master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify the following two properties:
        P1) insert/update in an autoinc column causes statement to 
        be logged in row format if binlog_format=mixed
        P2) if binlog_format=mixed, and a trigger or function contains 
            two or more inserts/updates in a table that has an autoinc 
            column, then the slave should not go out of sync, even if 
            there are concurrent transactions.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85123

3129 Dao-Gang.Qu@sun.com	2009-09-30
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query when binlogging. If more than one autoinc values are used 
      in the query, the autoinc values after the first one can be inserted 
      wrongly on slave. So these autoinc values can become inconsistent on 
      master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify the following two properties:
        P1) insert/update in an autoinc column causes statement to 
        be logged in row format if binlog_format=mixed
        P2) if binlog_format=mixed, and a trigger or function contains 
            two or more inserts/updates in a table that has an autoinc 
            column, then the slave should not go out of sync, even if 
            there are concurrent transactions.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

The patch still need push to 5.4, although we will have new solution 
for it on 5.4 later.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85287

3146 Dao-Gang.Qu@sun.com	2009-10-01
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query when binlogging. If more than one autoinc values are used 
      in the query, the autoinc values after the first one can be inserted 
      wrongly on slave. So these autoinc values can become inconsistent on 
      master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify the following two properties:
        P1) insert/update in an autoinc column causes statement to 
        be logged in row format if binlog_format=mixed
        P2) if binlog_format=mixed, and a trigger or function contains 
            two or more inserts/updates in a table that has an autoinc 
            column, then the slave should not go out of sync, even if 
            there are concurrent transactions.
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function 
        'has_write_table_with_auto_increment'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85395

3615 Dao-Gang.Qu@sun.com	2009-10-01 [merge]
      Bug #45677 Slave stops with Duplicate entry for key PRIMARY when using trigger
      
      The problem is that there is only one autoinc value associated with 
      the query when binlogging. If more than one autoinc values are used 
      in the query, the autoinc values after the first one can be inserted 
      wrongly on slave. So these autoinc values can become inconsistent on 
      master and slave.
      
      The problem is resolved by marking all the statements that invoke 
      a trigger or call a function that updated autoinc fields as unsafe, 
      and will switch to row-format in Mixed mode. Actually, the statement 
      is safe if just one autoinc value is used in sub-statement, but it's 
      impossible to check how many autoinc values are used in sub-statement.)
     @ mysql-test/extra/rpl_tests/rpl_insert_id.test
        The test case is updated due to the patch of bug#45677
     @ mysql-test/suite/backup_ptr/t/backup_ptr_objects.test
        The test case is updated duo to the patch of bug#45677
     @ mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result
        Test result for bug#45677
     @ mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test
        Added test to verify the following two properties:
        P1) insert/update in an autoinc column causes statement to 
        be logged in row format if binlog_format=mixed
        P2) if binlog_format=mixed, and a trigger or function contains 
            two or more inserts/updates in a table that has an autoinc 
            column, then the slave should not go out of sync, even if 
            there are concurrent transactions.
     @ mysql-test/suite/rpl/t/rpl_variables_stm.test
        The test case is updated duo to the patch of bug#45677
     @ sql/sql_base.cc
        Added function 'has_write_table_with_auto_increment' to check 
        if one (or more) write tables have auto_increment columns.
        
        Removed function 'has_two_write_locked_tables_with_auto_increment', 
        because the function is included in function

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20091002112748-2xmjv846dk323nc3) (merge vers: 5.1.40) (pib:11)

Documented fix in the 5.1.40 changelog as follows:

        Concurrent transactions that inserted rows into a table with an
        AUTO_INCREMENT column could break statement-based or
        mixed-format replication error 1062 (Duplicate entry '...' for
        key 'PRIMARY') on the slave. This was especially likely to
        happen when one of the transactions activated a trigger that
        inserted rows into the table with the AUTO_INCREMENT column,
        although other conditions could also cause the issue to
        manifest.

Set status = NDI, waiting on 5.4 push.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)

Bugfix also documented in the 5.5.0 and 6.0.14 changelogs.

Closed.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

No additional changelog entries needed. Closed.

See also BUG#48608.

I still have similiar problem in 5.1.43: Duplicate entry for key PRIMARY on the slave, after INSERT...SELECT query. The key is on autoincrement column, all tables in the query are myisam and the INSERT is not fired by a trigger. Binary log is in statement-based format.

Hi Miloslav, 
Did you make sure if the 'rpl_auto_increment_update_failure.test' run OK?
If it's OK, please report a new bug. Thanks!

BTW: See BUG#48608.

See also bug #53079

Even i get the same error and my replication stops in between, how do i correct this?

See bug #50440 | bug #53079 | bug #48608 | bug #42415

To clarify this bug/what happened in this bug: 

The fix in this bug (implemented in MySQL 5.1.40+) marks the transactions/statements listed in the use case (see the initial description) as unsafe. Note that unsafe statements need to be replicated in MIXED or ROW based replication to make the safe.

As such, the consequences for the end user are:

#1 End user needs to upgrade to 5.1.40 or later. The latest available release of MySQL is recommended.
#2 If currently using STATEMENT based replication, end user needs to change to MIXED or ROW based replication.

#3 If the end user uses replication, note that the end user will need to upgrade all slaves and the master, since it is the master which determines the binary log format (SBR/MBR/RBR). In the my.cnf/my.ini the binary log format can be changed as follows (in the [mysqld] section):

#Default is STATEMENT (Options are STATEMENT, ROW, MIXED)
binlog_format=STATEMENT

The reason I wrote "all slaves" is that one cannot cannot upgrade the master before all the slaves are on the latest release - since otherwise the slaves will not comprehend what the master sends it.

Added to the existing changelog entry:

        As part of the fix for this issue, any statement that causes a trigger
        or function to update an AUTO_INCREMENT column is now considered
        unsafe for statement-based replication. For more information, see
        "Replication and AUTO_INCREMENT".

Added note to indicated section of RefMan as well as replication-features-triggers.

Closed.

With STATEMENT, no warning is issued when running the INSERT that fires the TRIGGER on an AUTO_INCREMENT, even on 5.1.48.

Any statement that causes a trigger or function to
update an AUTO_INCREMENT column is considered
unsafe for statement-based replication after a
patch of Bug#45827(http://lists.mysql.com/commits/9031).
I had backported the patch to mysql-5.1-rep2 long ago.
But I didn't see it in mysql-5.1-bugteam. The patch
must be missed to merge to mysql-5.1-bugteam.
I'm going to commit a patch to fix it in bug#45827.
Thanks for the check.

Sorry. The patch of bug#45827 backported into mysql-5.1-rep2
should be merged into 5.5 instead of 5.1. It's merged into 5.5 successfully.

For 5.1, We mark all the statements that invoke a trigger or call a function
that updated autoinc fields as unsafe and will switch to row-format in mixed
mode. But in statement mode, we didn't emitted the warning.

For 5.5, we emitted the warning in statement mode, because  a suppress warning
feature is introduced.