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 >
