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