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.na...@gmail.com>wrote: > 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 >
