Yes, it's basic out-of-the box mysql replication. This appears to be an instance of this bug: http://bugs.mysql.com/bug.php?id=45670
But that bug report was closed two years ago. I have no idea if it's the server sending bad data or the slaves. I think it's the slaves, because on the slave error, it clearly is getting this statement: "insert into test values (1,null)" to replicate, but when it is executed, the "null" is converted into a random number. But it's happening on all of my slaves, a mix of 32 and 64 bit 5.5.8 and 5.5.11 boxes. <http://bugs.mysql.com/bug.php?id=45670> -Hank On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni <claudio.na...@gmail.com>wrote: > 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 >
