views:

802

answers:

6

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.

+2  A: 

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.

staticsan
Sorry, yes, sales_id does have a primary key. Forgot to mention that. Good catch though +1
Darryl Hein
+1  A: 
  • 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.

too much php
nope and nope :(
Darryl Hein
+2  A: 

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.

Ask Bjørn Hansen
Thxs for noticing. I'll mention that to the developer.
Darryl Hein
+2  A: 

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.

Bill Karwin
+3  A: 

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
Jon Cahill
+1  A: 

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
chaos