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