Few days ago we upgraded our test server with mysql 5.1.30 (before we had 5.0.27) and tests started to fail. Error message was similar to described in here "Mysql::Error: Duplicate entry". Nothing has been changed in the code, test runs just fine when run manually (not as a rake task). After some digging and debugging we had observed a pattern. The error happened when fixtures seeded, some new ARecord objects created.

Was it bug or a feature of mysql InnoDB? Kind of both. Here is the example of sql to test <= 5.1.30 InnoDB

select version();

DROP TABLE IF EXISTS test_inc;
CREATE TABLE `test_inc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

select 'insert explicit 9. must become 10';
insert into test_inc ( id, account_id) values (9,1);
select * from test_inc;
show create table test_inc \G
select 'delete all. stays 10';
delete from test_inc;
select * from test_inc;
show create table test_inc \G

select 'insert autoincrement. must become 11';
insert into test_inc ( account_id) values (1) ;
select * from test_inc ;
show create table test_inc \G
select 'delete all. stays 11';
delete from test_inc ;
select * from test_inc ;
show create table test_inc \G

select 'insert explicit 11. must become 12, but stays 11';
insert into test_inc ( id, account_id) values (11,1);
select * from test_inc;
show create table test_inc \G

/*
select 'insert explicit 12. must become 13';
insert into test_inc ( id, account_id) values (12,1);
select * from test_inc;
show create table test_inc \G
*/

select * from test_inc;
show create table test_inc \G
select 'insert auto-increment';
insert into test_inc ( account_id) values (1);
select * from test_inc;
show create table test_inc \G

and the output

$ mysql -u root -p test <mysql-bug.sql
Enter password:
version()
5.1.30
insert explicit 9. must become 10
insert explicit 9. must become 10
id      account_id
9       1
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
delete all. stays 10
delete all. stays 10
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
insert autoincrement. must become 11
insert autoincrement. must become 11
id      account_id
10      1
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
delete all. stays 11
delete all. stays 11
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
insert explicit 11. must become 12, but stays 11
insert explicit 11. must become 12, but stays 11
id      account_id
11      1
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
id      account_id
11      1
*************************** 1. row ***************************
       Table: test_inc
Create Table: CREATE TABLE `test_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
insert auto-increment
insert auto-increment
ERROR 1062 (23000) at line 44: Duplicate entry '11' for key 'PRIMARY'

If you uncomment lines in the SQL commands - No errors. Run it on 5.0 version - No errors. Change to MyISAM - No errors.

We have tried 32 bit Mysql 5.1.22rc and 5.1.30 GA Intel compiled binaries on Linux (Debian etch and CentOS 5)

Solution - make the id of the test fixtures big enough or in setup of the test insert record with big ID

Bug has been opened with mysql. Let's wait, meantime be aware