List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:August 24 2009 2:19am
Subject:Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677
View as plain text  
Hi Daogang,

Thank you for the work!

But I'm sorry that this patch is not correct, it set the statement to
unsafe when INSERT with AFTER TRIGGER, then how about other triggers
(BEFORE trigger, triggers for UPDATE or DELETE) or calling functions?
and the patch does not check if more than one auto_increment values are
used, there is no problem for statements that only uses one
auto_increment value.

I think the problem described by this bug is a limitation of
replication, and cannot be easily fix. Even if we can change the code to
include multiple Intvar_log_event in the binary log, and ask the slave
to use these values for auto_increment fields, but when using
mysqlbinlog to dump these events, all these Intvar_log_event will be
dumped as SET INSERT_ID, and the last one will overwrite any previous
such values.

Based on the above problems, I think this bug should not be fix, and
should be well documented about these limitations instead. I suggest
change the category of this bug to Documentation.

Dao-Gang.Qu@stripped wrote:
> #At file:///home/daogangq/mysql/bzrwork/bug45677/mysql-5.1-bugteam/ based on
> revid:joro@stripped
> 
>  3072 Dao-Gang.Qu@stripped	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'').
> 
>     added:
>       mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result
>       mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test
>     modified:
>       sql/sql_insert.cc
> === added file 'mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result'
> --- a/mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result	1970-01-01 00:00:00
> +0000
> +++ b/mysql-test/suite/rpl/r/rpl_duplicate_entry_failure.result	2009-08-23 14:38:51
> +0000
> @@ -0,0 +1,34 @@
> +stop slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +reset master;
> +reset slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +start slave;
> +use test;
> +create table t1(f1 int) engine=innodb;
> +create table t2(i1 int not null auto_increment, f1 int, primary key(i1))
> engine=innodb;
> +create trigger tr1 after insert on t1 for each row insert into t2(f1)
> values(new.f1);
> +set autocommit=0;
> +insert into t1(f1) values(1),(2);
> +insert into t2(f1) values(3),(4);
> +commit;
> +insert into t1(f1) values(5);
> +commit;
> +#on master
> +select * from t2;
> +i1	f1
> +1	1
> +2	2
> +3	3
> +4	4
> +5	5
> +#on slave
> +select * from t2;
> +i1	f1
> +1	1
> +2	2
> +3	3
> +4	4
> +5	5
> +DROP TABLE t1;
> +DROP TABLE t2;
> 
> === added file 'mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test'
> --- a/mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test	1970-01-01 00:00:00
> +0000
> +++ b/mysql-test/suite/rpl/t/rpl_duplicate_entry_failure.test	2009-08-23 14:38:51
> +0000
> @@ -0,0 +1,42 @@
> +#
> +# Bug45677
> +# This test verifies 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'').
> +#
> +
> +source include/have_binlog_format_mixed.inc;
> +source include/have_innodb.inc;
> +source include/master-slave.inc;
> +
> +connection master;
> +use test;
> +create table t1(f1 int) engine=innodb;
> +create table t2(i1 int not null auto_increment, f1 int, primary key(i1))
> engine=innodb;
> +create trigger tr1 after insert on t1 for each row insert into t2(f1)
> values(new.f1);
> +
> +set autocommit=0;
> +insert into t1(f1) values(1),(2);
> +
> +connection master1;
> +insert into t2(f1) values(3),(4);
> +
> +connection master;
> +commit;
> +insert into t1(f1) values(5);
> +commit;
> +
> +connection master;
> +--echo #on master
> +select * from t2;
> +
> +sync_slave_with_master;
> +connection slave;
> +--echo #on slave
> +select * from t2;
> +
> +connection master;
> +DROP TABLE t1;
> +DROP TABLE t2;
> +sync_slave_with_master;
> +
> 
> === modified file 'sql/sql_insert.cc'
> --- a/sql/sql_insert.cc	2009-08-13 02:48:57 +0000
> +++ b/sql/sql_insert.cc	2009-08-23 14:38:51 +0000
> @@ -732,6 +732,19 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
>  
>    prepare_triggers_for_insert_stmt(table);
>  
> +  /*
> +    BUG#45677 
> +    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, so in mixed mode we go to row-based
> +    when using trigger.
> +  */  
> +  if ((thd->lex->trg_chistics.event == TRG_EVENT_INSERT) &&
> +      (thd->lex->trg_chistics.action_time == TRG_ACTION_AFTER))
> +  {
> +    thd->lex->set_stmt_unsafe();
> +    thd->set_current_stmt_binlog_row_based_if_mixed();
> +  }
>  
>    if (table_list->prepare_where(thd, 0, TRUE) ||
>        table_list->prepare_check_option(thd))
> 

Thread
bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677Dao-Gang.Qu23 Aug
  • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677He Zhenxing24 Aug
    • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677He Zhenxing24 Aug
      • Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677Daogang Qu26 Aug