That is the slave relay log dump I posted (and mis-labeled). Thanks. -Hank
On Tue, Jun 14, 2011 at 2:34 AM, Claudio Nanni <claudio.na...@gmail.com>wrote: > You should also have a look at the slave relay log. > > But in any case sounds like a bug. > > Claudio > On Jun 14, 2011 5:18 AM, "Hank" <hes...@gmail.com> wrote: > > Both my master and slave bin logs look OK (I think).. > > > > master bin log: > > > > /*!40019 SET @@session.max_insert_delayed_threads=0*/; > > /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; > > DELIMITER /*!*/; > > SET TIMESTAMP=1308012505/*!*/; > > SET @@session.pseudo_thread_id=999999999/*!*/; > > SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, > > @@session.unique_checks=1, @@session.autocommit=1/*!*/; > > SET @@session.sql_mode=0/*!*/; > > SET @@session.auto_increment_increment=1, > > @@session.auto_increment_offset=1/*!*/; > > /*!\C latin1 *//*!*/; > > SET > > > @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; > > SET @@session.lc_time_names=0/*!*/; > > SET @@session.collation_database=DEFAULT/*!*/; > > BEGIN > > /*!*/; > > use test/*!*/; > > SET TIMESTAMP=1308012505/*!*/; > > insert into test values (1,null) > > /*!*/; > > SET TIMESTAMP=1308012505/*!*/; > > COMMIT > > /*!*/; > > > > > > slave relay log: > > > > /*!40019 SET @@session.max_insert_delayed_threads=0*/; > > /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; > > DELIMITER /*!*/; > > SET TIMESTAMP=1308012505/*!*/; > > SET @@session.pseudo_thread_id=999999999/*!*/; > > SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, > > @@session.unique_checks=1, @@session.autocommit=1/*!*/; > > SET @@session.sql_mode=0/*!*/; > > SET @@session.auto_increment_increment=1, > > @@session.auto_increment_offset=1/*!*/; > > /*!\C latin1 *//*!*/; > > SET > > > @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; > > SET @@session.lc_time_names=0/*!*/; > > SET @@session.collation_database=DEFAULT/*!*/; > > BEGIN > > /*!*/; > > use test/*!*/; > > SET TIMESTAMP=1308012505/*!*/; > > insert into test values (1,null) > > /*!*/; > > SET TIMESTAMP=1308012505/*!*/; > > COMMIT > > /*!*/; > > > > > > -Hank > > > > > > On Mon, Jun 13, 2011 at 10:38 PM, Hank <hes...@gmail.com> wrote: > > > >> > >> 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 > >>> > >> > >> >
