List:Commits« Previous MessageNext Message »
From:Daogang Qu Date:August 26 2009 5:35am
Subject:Re: bzr commit into mysql-5.1 branch (Dao-Gang.Qu:3072) Bug#45677
View as plain text  
Hi Zhengxing,
Thanks for the great comments. I still can't simulate a test case for
calling functions.
Could you give me a simple case for it? Others have been investigated
and their
results were appended. Thanks a lot!

Best Regards,

Daogang
> 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