Hank, I can't reproduce it right now, But it really seems a bug. Just a shot in the dark, Are you sure you have statement based and not mixed replication? I don't even know if that would affect , just an idea.
Claudio On Jun 14, 2011 3:07 AM, "Hank" <hes...@gmail.com> wrote: > Hello All, > > I have a 64bit, 5.5.8 master, and this bug appears on both 5.5.11 and 5.5.8 > 32 and 64-bit slaves (statement based replication). > > I'm finding an auto-increment field (part of a compound primary key) updates > correctly using "null" to insert the next value on the master.. but when > this statement is replicated on the slaves, instead of inserting the next > value of the auto-increment field, it inserts 65535 for 'smallint' > definitions of 'cnt' and seemingly high random numbers around 469422 for > definitions of 'int' or 'bigint'. > > Easy to repeat: > > master:> CREATE TABLE test ( id int NOT NULL, cnt int unsigned NOT NULL > AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; > master:> desc test; >> +-------+---------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-------+---------+------+-----+---------+----------------+ > | id | int(11) | NO | PRI | NULL | | > | cnt | int(11) | NO | PRI | NULL | auto_increment | > +-------+---------+------+-----+---------+----------------+ > > master:> insert into test values (1,null); > master:> select * from test; > +----+-----+ > | id | cnt | > +----+-----+ > | 1 | 1 | <--- looks good! > +----+-----+ > > slave:> desc test; >> +-------+---------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-------+---------+------+-----+---------+----------------+ > | id | int(11) | NO | PRI | NULL | | > | cnt | int(11) | NO | PRI | NULL | auto_increment | > +-------+---------+------+-----+---------+----------------+ > > slave:> select * from test; > +----+--------+ > | id | cnt | > +----+--------+ > | 1 | 469422 | <---- should be "1" > +----+--------+ > > But the problem continues... > > master:> insert into test values (1,null); > master:> select * from test; > +----+-----+ > | id | cnt | > +----+-----+ > | 1 | 1 | <--- correct ! > | 1 | 2 | <--- correct ! > +----+-----+ > > slave> select * from test; > +----+--------+ > | id | cnt | > +----+--------+ > | 1 | 469422 | <---- should be "1" > | 1 | 470673 | <---- should be "2" > +----+--------+ > > Now if I repeat the entire scenario using "smallint" for the 'cnt' field, > here are the results: > > master> CREATE TABLE test (id int NOT NULL, cnt smallint unsigned NOT NULL > AUTO_INCREMENT, PRIMARY KEY (id,cnt) ) ENGINE=MyISAM ; > master> desc test; > +-------+----------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-------+----------------------+------+-----+---------+----------------+ > | id | int(11) | NO | PRI | NULL | | > | cnt | smallint(5) unsigned | NO | PRI | NULL | auto_increment | > +-------+----------------------+------+-----+---------+----------------+ > master:> insert into test values (1,null); > master:> select * from test; > +----+-----+ > | id | cnt | > +----+-----+ > | 1 | 1 | <---- correct! > +----+-----+ > > slave> select * from test; > +----+--------+ > | id | cnt | > +----+--------+ > | 1 | 65535 | <---- should be "1" > +----+--------+ > > but this is different: > > master:> insert into test values (1,null); > master:> select * from test; > +----+-----+ > | id | cnt | > +----+-----+ > | 1 | 1 | <---- correct! > | 1 | 2 | <---- correct! > +----+-----+ > > slave> select * from test; > +----+-------+ > | id | cnt | > +----+-------+ > | 1 | 65535 | <---- should be "1", missing second record, too > +----+-------+ > slave> show slave status; > > .... Error 'Duplicate entry '1-65535' for key 'PRIMARY'' on query. Default > database: 'test'. Query: 'insert into test values (1,null)' > > .. at which point I have to restart the slave due to the error: > > slave> SET GLOBAL sql_slave_skip_counter=1; slave start; > slave> select * from test; > +----+-------+ > | id | cnt | > +----+-------+ > | 1 | 65535 | <---- should be "1", still missing second record, too (of > course) > +----+-------+ > > > Now if I manually replicate the statements just on the slave - it works > perfectly: > > slave:> truncate table test; > slave:> insert into test values (1,null); > slave:> select * from test; > +----+-----+ > | id | cnt | > +----+-----+ > | 1 | 1 | > +----+-----+ > slave:> insert into test values (1,null); > slave:> select * from test; > +----+-----+ > | id | cnt | > +----+-----+ > | 1 | 1 | > | 1 | 2 | > +----+-----+ > > So something in the replication code is munging the 'null' into some random > value and trying to insert it. Seems strange that direct statements would > work, but replicated statements do not. > > Nothing really changed on my system, but for some reason, this all started > happening about a week or so ago. I've been running this 5.5.8/5.5.11 > configuration for months now (since 5.5.8 was released). The PHP code > that does this hasn't changed one bit, and this is a simplified version of > the database and code that is running in production. > > Additional note: If I drop the 'id' field, and the primary key is just the > auto-increment field, it works correctly in replication. > > Any ideas? Can anyone else replicate these results? > > -Hank
