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 >
