Found a possible replication bug in 5.5.8/5.5.11 (auto-increment inprimary key)

--0016e659f30e8f194b04a5a1a8b2
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e659f30e8f194b04a5a1a8b2--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--90e6ba21223f88625c04a5a2dca7
Content-Type: text/plain; charset=ISO-8859-1

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" 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

--90e6ba21223f88625c04a5a2dca7--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--0016e65bc23c3294b604a5a2f0c1
Content-Type: text/plain; charset=ISO-8859-1

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.

-Hank


On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni 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" 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
>

--0016e65bc23c3294b604a5a2f0c1--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--0016e659f30e6ccbdb04a5a37cf3
Content-Type: text/plain; charset=ISO-8859-1

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_connect ion=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_connect ion=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 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.
>
> -Hank
>
>
>
> On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni 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" 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
>>
>
>

--0016e659f30e6ccbdb04a5a37cf3--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--90e6ba181822f929f904a5a63b9b
Content-Type: text/plain; charset=ISO-8859-1

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" 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_connect ion=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_connect ion=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 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.
>>
>> -Hank
>>
>>
>>
>> On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni >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" 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
>>>
>>
>>

--90e6ba181822f929f904a5a63b9b--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--0016e659f30e2ee05204a5ad1dcd
Content-Type: text/plain; charset=ISO-8859-1

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 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" 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_connect ion=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_connect ion=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 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.
> >>
> >> -Hank
> >>
> >>
> >>
> >> On Mon, Jun 13, 2011 at 10:33 PM, Claudio Nanni <
> claudio.nanni@gmail.com>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" 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
> >>>
> >>
> >>
>

--0016e659f30e2ee05204a5ad1dcd--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-increment in primary key)

>>>> 2011/06/13 22:38 -0400, Hank >>>>
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.
<<<<<<<<

If the master were sending random big numbers, and replication on the slave in the usual way handled out-of-bound numbers when not allowed to fail, then 65535 would be an expected value for a signless 16-bit number. Of course, if this were true, the slave would be getting not that statement but "insert into test values (1,469422)".


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--0016e65bc23c744ad404a5b73a2e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

The slave is receiving "null" as the statement based insert, not an out of
range number from the master.

I've been doing more research all day on this bug and have a bit more
information as to what's causing it. I plan to write it up tomorrow and
post it.

Basically, everything works perfectly, until I add a
"replication-ignore-table=3Dxxx" statement in my.cnf where "xxx" is a
different table with a unique id INT auto-increment as the single primary
key And then the values being inserted into the "test" table (above, not
ignored) represent the last-insert-id of the replication *ignored* table on
the slave

Yeah, pretty strange, I know. But totally repeatable.

-Hank


2011/6/14 Hal=E1sz S=E1ndor

> >>>> 2011/06/13 22:38 -0400, Hank >>>>
> 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 o=
n
> 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.
> <<<<<<<<
>
> If the master were sending random big numbers, and replication on the sla=
ve
> in the usual way handled out-of-bound numbers when not allowed to fail, t=
hen
> 65535 would be an expected value for a signless 16-bit number. Of course,=
if
> this were true, the slave would be getting not that statement but "insert
> into test values (1,469422)".
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dheskin@gmail.com
>
>

--0016e65bc23c744ad404a5b73a2e--

Re: Found a possible replication bug in 5.5.8/5.5.11 (auto-incrementin primary key)

--90e6ba1efc4ec379cc04a5b9dcea
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Very interesting. Waiting for update.
On Jun 15, 2011 4:51 AM, "Hank" wrote:
>
> The slave is receiving "null" as the statement based insert, not an out o=
f
> range number from the master.
>
> I've been doing more research all day on this bug and have a bit more
> information as to what's causing it. I plan to write it up tomorrow and
> post it.
>
> Basically, everything works perfectly, until I add a
> "replication-ignore-table=3Dxxx" statement in my.cnf where "xxx" is a
> different table with a unique id INT auto-increment as the single primary
> key And then the values being inserted into the "test" table (above, not
> ignored) represent the last-insert-id of the replication *ignored* table
on
> the slave
>
> Yeah, pretty strange, I know. But totally repeatable.
>
> -Hank
>
>
> 2011/6/14 Hal=E1sz S=E1ndor
>
> > >>>> 2011/06/13 22:38 -0400, Hank >>>>
> > 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.
> > <<<<<<<<
> >
> > If the master were sending random big numbers, and replication on the
slave
> > in the usual way handled out-of-bound numbers when not allowed to fail,
then
> > 65535 would be an expected value for a signless 16-bit number. Of
course, if
> > this were true, the slave would be getting not that statement but
"insert
> > into test values (1,469422)".
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dheskin@gmail.co=
m
> >
> >

--90e6ba1efc4ec379cc04a5b9dcea--