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