List:General Discussion« Previous MessageNext Message »
From:Hank Date:June 14 2011 2:38am
Subject:Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment
in primary key)
View as plain text  
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.nanni@stripped>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" <heskin@stripped> 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
>

Thread
Found a possible replication bug in 5.5.8/5.5.11 (auto-increment inprimary key)Hank14 Jun
  • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Claudio Nanni14 Jun
    • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Hank14 Jun
      • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Hank14 Jun
        • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Claudio Nanni14 Jun
          • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Hank14 Jun
      • Re: Found a possible replication bug in 5.5.8/5.5.11(auto-increment in primary key)hsv15 Jun
        • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Hank15 Jun
          • Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)Claudio Nanni15 Jun