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
