No worries! I think I would have figured that out!
I'll feedback you tomorrow. Thanks again Claudio 2011/6/15 Hank <hes...@gmail.com> > 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.na...@gmail.com > >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 <hes...@gmail.com> > > > >> 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 <hes...@gmail.com> 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
