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