What could cause duplicate ids on a auto increment primary key field (mysql)?

Question

RESOLVED

From the developer: the problem was that a previous version of the code was still writing to the table which used manual ids instead of the auto increment. Note to self: always check for other possible locations where the table is written to.

We are getting duplicate keys in a table. They are not inserted at the same time (6 hours apart).

Table structure:

CREATE TABLE `table_1` (
  `sales_id` int(10) unsigned NOT NULL auto_increment,
  `sales_revisions_id` int(10) unsigned NOT NULL default '0',
  `sales_name` varchar(50) default NULL,
  `recycle_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`sales_id`),
  KEY `sales_revisions_id` (`sales_revisions_id`),
  KEY `sales_id` (`sales_id`),
  KEY `recycle_id` (`recycle_id`)
) ENGINE= MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26759 ;

The insert:

insert into `table_1` ( `sales_name` ) VALUES ( "Blah Blah" )

We are running MySQL 5.0.20 with PHP5 and using mysql_insert_id() to retrieve the insert id immediately after the insert query.

Answer 1

Does the sales_id field have a primary (or unique) key? If not, then something else is probably making inserts or updates that is re-using existing numbers. And by "something else" I don't just mean code; it could be a human with access to the database doing it accidentally.

Answer 2

• If you have a unique key on other fields, that could be the problem.

• If you have reached the highest value for your auto_increment column MySQL will keep trying to re-insert it. For example, if sales_id was a tinyint column, you would get duplicate key errors after you reached id 127.

Answer 3

As the other said; with your example it's not possible.

It's unrelated to your question, but you don't have to make a separate KEY for the primary key column -- it's just adding an extra not-unique index to the table when you already have the unique (primary) key.

Answer 4

We are getting duplicate keys in a table.

Do you mean you are getting errors as you try to insert, or do you mean you have some values stored in the column more than once?

Auto-increment only kicks in when you omit the column from your INSERT, or try to insert NULL or zero. Otherwise, you can specify a value in an INSERT statement, over-riding the auto-increment mechanism. For example:

INSERT INTO table_1 (sales_id) VALUES (26759);

If the value you specify already exists in the table, you'll get an error.

Answer 5

I have had a few duplicate key error suddenly appear in MySql databases in the past even though the primary key is defined and auto_increment. Each and every time it has been because the table has become corrupted.

If it is corrupt performing a check tables should expose the problem. You can do this by running:

CHECK TABLE tbl_name

If it comes back as corrupt in anyway (Will usually say the size is bigger than it actually should be) then just run the following to repair it:

REPAIR TABLE tbl_name

Answer 6

Please post the results of this query:

SELECT `sales_id`, COUNT(*) AS `num`
FROM `table_1`
GROUP BY `sales_id`
HAVING `num` > 1
ORDER BY `num` DESC