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 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 > /*!*/; > > > -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 >>> >> >>
