List:General Discussion« Previous MessageNext Message »
From:Hank Date:June 17 2011 1:07am
Subject:Re: Found serious replication data-corruption bug in 5.5.8/5.5.11
(auto-increment in primary key)
View as plain text  
 Sveta Smirnova at Mysql just confirmed this bug in 5.5.13:
http://bugs.mysql.com/45670


On Wed, Jun 15, 2011 at 5:38 PM, Claudio Nanni <claudio.nanni@stripped>wrote:

> No worries!
>
> I think I would have figured that out!
>
> I'll feedback you tomorrow.
>
> Thanks again
>
> Claudio
>
> 2011/6/15 Hank <heskin@stripped>
>
>> Oops... big typo in above steps... add the following line:
>>
>>  replicate-ignore-table=db.log
>>
>> to the SLAVE my.cnf, and restart the SLAVE server.
>>
>> The master does not need to be restarted or changed. Just the SLAVE.
>>
>> Sorry about that.
>>
>> -Hank Eskin
>>
>>
>>
>> On Wed, Jun 15, 2011 at 5:19 PM, Claudio Nanni <claudio.nanni@stripped
>> >wrote:
>>
>> > Great investigation Hank,
>> > congratulations.
>> >
>> > I will try this tomorrow morning(11:20pm now)  and let you know if I can
>> > reproduce it on my environments.
>> >
>> > Thanks!
>> >
>> > Claudio
>> >
>> >
>> > 2011/6/15 Hank <heskin@stripped>
>> >
>> >> Two additional notes:
>> >>
>> >> 1.  Using the "replicate-wild-ignore-table" option in my.cnf produces
>> the
>> >> same results.
>> >>
>> >> 2.  If the my.cnf "replicate-ignore-table=db.log" setting on the master
>> >>  is
>> >> removed and mysql restarted so "db.log" is no longer ignored in
>> >> replication,
>> >> this bug goes away and correct results are reported on the slave.
>> >>
>> >> -Hank Eskin
>> >>
>> >>
>> >> On Wed, Jun 15, 2011 at 4:38 PM, Hank <heskin@stripped> wrote:
>> >>
>> >> >
>> >> > This is a follow-up to my previous post.  I have been narrowing
> down
>> >> what
>> >> > is causing this bug.  It is a timing issue of a replication
> ignored
>> >> table
>> >> > with an auto-increment primary key values leaking over into a
>> >> non-ignored
>> >> > table with inserts immediately after the ignore table has had rows
>> >> inserted.
>> >> >
>> >> > Basically, data from the ignored table is corrupting a non-ignored
>> table
>> >> on
>> >> > the slave upon immediate inserts.
>> >> >
>> >> > Here is how to repeat:
>> >> >
>> >> > On a master issue:
>> >> >
>> >> > use db;
>> >> > drop table test;
>> >> > CREATE TABLE test (id int NOT NULL,   cnt int unsigned  NOT NULL
>> >> >  AUTO_INCREMENT,  PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ;
>> >> > drop table log;
>> >> > CREATE TABLE log (id int unsigned NOT NULL AUTO_INCREMENT, log
>> >> varchar(20),
>> >> >  PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT = 44444;
>> >> >
>> >> > make sure those two tables are created on the slave through
> regular
>> >> > replication.
>> >> >
>> >> > on slave>
>> >> >
>> >> > desc test;
>> >> > desc log;
>> >> >
>> >> > Once replicated, on the master, add the following line to the
>> [mysqld]
>> >> > section of my.cnf, and then restart mysql.
>> >> >
>> >> >          replicate-ignore-table=db.log
>> >> >
>> >> > The on the master, issue the following statements as a copy/paste
> of
>> all
>> >> of
>> >> > them at once.
>> >> > It's critical that the statements are executed in immediate
>> succession
>> >> (no
>> >> > delays)
>> >> >
>> >> > insert into log values (null,"info1");
>> >> > insert into log values (null,"info2");
>> >> > insert into log values (null,"info3");
>> >> > insert into log values (null,"info4");
>> >> > insert into test values (1,null);
>> >> > insert into log values (null,"info5");
>> >> > insert into test values (1,null);
>> >> > insert into log values (null,"info6");
>> >> > insert into test values (2,null);
>> >> > insert into log values (null,"info7");
>> >> > insert into test values (2,null);
>> >> > insert into log values (null,"info8");
>> >> >
>> >> > Here are the results from the master (all correct):
>> >> >
>> >> > master>select * from log;
>> >> > +-------+-------+
>> >> > | id    | log   |
>> >> > +-------+-------+
>> >> > | 44444 | info1 |
>> >> > | 44445 | info2 |
>> >> > | 44446 | info3 |
>> >> > | 44447 | info4 |
>> >> > | 44448 | info5 |
>> >> > | 44449 | info6 |
>> >> > | 44450 | info7 |
>> >> > | 44451 | info8 |
>> >> > +-------+-------+
>> >> > master>select * from test;
>> >> > +----+-----+
>> >> > | id | cnt |
>> >> > +----+-----+
>> >> > |  1 |   1 |
>> >> > |  1 |   2 |
>> >> > |  2 |   1 |
>> >> > |  2 |   2 |
>> >> > +----+-----+
>> >> > Here are the results from the slave:
>> >> >
>> >> > slave>select * from log;
>> >> >
>> >> > Empty set (0.00 sec)  <--- as expected, since it is ignored
>> >> >
>> >> > slave>select * from test;
>> >> > +----+-------+
>> >> > | id | cnt   |
>> >> > +----+-------+
>> >> > |  1 | 44447 |   <-- should be "1", but has values from "log" on
> the
>> >> master
>> >> > |  1 | 44448 |   <-- should be "2"
>> >> > |  2 | 44449 |   <-- should be "1"
>> >> > |  2 | 44450 |   <-- should be "2"
>> >> > +----+-------+
>> >> >
>> >> > If there is the slightest delay between the inserts into "log" and
>> >> "test",
>> >> > the replication happens correctly.
>> >> >
>> >> > Thoughts?
>> >> >
>> >> > -Hank Eskin
>> >> >
>> >>
>> >
>> >
>> >
>> > --
>> > Claudio
>> >
>>
>
>
>
> --
> Claudio
>

Thread
Found serious replication data-corruption bug in 5.5.8/5.5.11(auto-increment in primary key)Hank15 Jun
  • Re: Found serious replication data-corruption bug in 5.5.8/5.5.11(auto-increment in primary key)Hank15 Jun
    • Re: Found serious replication data-corruption bug in 5.5.8/5.5.11(auto-increment in primary key)Claudio Nanni15 Jun
      • Re: Found serious replication data-corruption bug in 5.5.8/5.5.11(auto-increment in primary key)Hank15 Jun
        • Re: Found serious replication data-corruption bug in 5.5.8/5.5.11(auto-increment in primary key)Claudio Nanni15 Jun
          • Re: Found serious replication data-corruption bug in 5.5.8/5.5.11(auto-increment in primary key)Hank17 Jun