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 >> > >
