List:Commits« Previous MessageNext Message »
From:He Zhenxing Date:August 24 2009 3:56am
Subject:Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677
View as plain text  
He Zhenxing worte:
> 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.
> 

After rethink about this, I think probably it's better we fix this by
mark all these statements as unsafe.


> 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