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