List:Commits« Previous MessageNext Message »
From:Sven Sandberg Date:April 28 2011 7:33pm
Subject:Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872
View as plain text  
On 04/28/2011 08:01 PM, Andrei Elkin wrote:
> Guilhem, hello.
>
>>>> create table t1(a int auto_increment primary key, b char(1))
> engine=innodb;
>>>> insert into t1 values
> (null,'A'),(null,'A'),(1025,'A'),(null+sleep(5),'A');
>>>> insert into t1 values(null,'B');
>>>> ERROR 23000: Duplicate entry '1026' for key 'PRIMARY'
>>>> select * from t1;
>>>> a	b
>>>> 1026	B
>>>> drop table t1;
>>>>
>>>> Notice how the "reap" gets "dup entry" error: the 4-row INSERT fails.
>>>> This is no replication bug (only the second INSERT was committed and
>>>> sent to binlog).
>>>
>>> Frankly, I felt there is a bug to report. But you beat me to find out
>>> that's it's all actually fine.
>>> So my congrats! :-)
>>
>> I tried a bit harder to break it, but couldn't. That taught me that a
>> theory I made in a previous mail was wrong, I'll
>> send a correction in minutes.
>
> I am answering that mail separately.
>
>
>>
>>>> I think this is no bug at all; by specifying an explicit value the
>>>> user is bound to get dup key errors sometimes anyway in collision with
>>>> autoinc values from other threads. Just wanted to share this, I don't
>>>> plan to file a bug. I am using all default values of options.
>>>
>>> I agree. That must be the very nature of the interleaved (I read
>>> --innodb_autoinc_lock_mode=2 was there) locking.
>>
>> In the testcase above I was using defaults (so, innodb-autoinc-lock-mode=1).
>>
>> There is however a strange sentence in
>>
> http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable
>>
>> "Therefore, if you are using statement-based replication, either avoid
>> INSERT ... ON DUPLICATE KEY UPDATE or use innodb_autoinc_lock_mode = 0
>> (“traditional” lock mode). "
>
> I studied it to find out that the docs meant to say `the Mixed-mode
> inserts' whole class which INSERT ... ON DUPLICATE KEY belongs to.
>
>>
>> I see this as a problem because:
>> - the default binlogging mode is SBR
>> - the default innodb_autoinc_lock_mode is 1 (according to the doc)
>> - INSERT ... ON DUPLICATE KEY UPDATE prints no warning of the type
>> "this statement is not safe for SBR"
>
> That's right.
>
> You'll be laughing though
>
> connection one;
> send insert into t1 values (null,'A'),(null,'A'),(1025,'A'),(1027 + sleep(10),'A'),
> (null,'A');
>
> connection two;
> insert into t1 values(null,'B');
>
> connection one;
> reap;
>
> *at this point*:
>
> [Warning] Unsafe statement written to the binary log using statement format since
> BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may
> return a different value on the slave. Statement: insert into t1 values
> (null,'A'),(null,'A'),(1025,'A'),(1027 + sleep(10),'A'), (null,'A')
>
>
> I was confused for few seconds until realized it's about `sleep()'
> (which also is deterministic).

Well, it's a matter of definition whether sleep is deterministic or not. 
It returns 0 on success and 1 if it was interrupted. If the query is 
interrupted on slave and not on master, or vice versa, then slave goes 
out of sync. Here is a test case that shows two cases where the slave 
goes out of sync:

--source include/have_binlog_format_statement.inc
--source include/master-slave.inc
# query finishes on master, is interrupted on slave
create table t1 (a int);
insert into t1 values (sleep (2));
select * from t1;
--source include/sync_slave_io_with_master.inc
--let $id= `select id from information_schema.processlist where info 
like 'insert%'`
eval kill $id;
select * from t1;
# query is interrupted on master, finishes on slave
--connection master
create table t2 (a int);
send insert into t2 values (sleep (2));
--connection master1
--let $id= `select id from information_schema.processlist where info 
like 'insert%'`
eval kill $id;
select * from t2;
--sync_slave_with_master
select * from t2;

This is obviously a somewhat esoteric situation, but the general 
strategy when it comes to unsafe statements is that it's better to be 
safe than sorry...

>
> So I confirm your finding: no warnings for the mixed-mode INSERT when
> --innodb_autoinc_lock_mode>= 1.
>
> `sleep()' and some other func:s are regarded over-pessimistically is somewhat known
> issue.
>
>> So in the default configuration, according to the manual, INSERT...ON
>> DUPLICATE KEY UPDATE won't replicate.
>> Using MIXED mode doesn't change this (the INSERT is still replicated
>> statement-based).
>> I don't intend to investigate this more, I leave this up to you
>> replication guys, to decide what to do with this. To me it looks like
>> a trap waiting to catch users.
>
> Thanks for putting it so nicely on the plate!
> I am reporting a bug.
>
> cheers,
>
> Andrei

Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot27 Apr
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Sven Sandberg28 Apr
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot28 Apr
      • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Sven Sandberg28 Apr
        • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot30 Apr
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot2 May
Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot27 Apr
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot27 Apr
Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot27 Apr
Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Guilhem Bichot27 Apr
Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Sven Sandberg28 Apr
Re: bzr commit into mysql-trunk branch (guilhem.bichot:3350) WL#5872Sven Sandberg28 Apr