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