CODEWITHSUNDEEP
mysqlsqldatabase
NeuronQ
NeuronQ

Reputation: 8195

Mysql - duplicate entry error for key with auto increment

Why do I get an error of the form:

Error in query: Duplicate entry '10' for key 1

...when doing an INSERT statement like:

INSERT INTO wp_abk_period (pricing_id, apartment_id) VALUES (13, 27)

...with 13 and 27 being valid id-s for existing pricing and apartment rows, and the table is defined as:

CREATE TABLE `wp_abk_period` (
  `id` int(11) NOT NULL auto_increment,
  `apartment_id` int(11) NOT NULL,
  `pricing_id` int(11) NOT NULL,
  `type` enum('available','booked','unavailable') collate utf8_unicode_ci default NULL,
  `starts` datetime default NULL,
  `ends` datetime default NULL,
  `recur_type` enum('daily','weekly','monthly','yearly') collate utf8_unicode_ci default NULL,
  `recur_every` char(3) collate utf8_unicode_ci default NULL,
  `timedate_significance` char(4) collate utf8_unicode_ci default NULL,
  `check_in_times` varchar(255) collate utf8_unicode_ci default NULL,
  `check_out_times` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_period_apartment1_idx` (`apartment_id`),
  KEY `fk_period_pricing1_idx` (`pricing_id`),
  CONSTRAINT `fk_period_apartment1` FOREIGN KEY (`apartment_id`) REFERENCES `wp_abk_apartment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_period_pricing1` FOREIGN KEY (`pricing_id`) REFERENCES `wp_abk_pricing` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Isn't key 1 id in this case and having it on auto_increment sufficient for being able to not specify it?

Note: If I just provide an unused value for id, like INSERT INTO wp_abk_period (id, pricing_id, apartment_id) VALUES (3333333, 13, 27) it works fine, but then again, it is set as auto_increment so I shouldn't need to do this!

Note 2: OK, this is a complete "twilight zone" moment: so after running the query above with the huge number for id, things started working normally, no more duplicate entry errors. Can someone explain me WTF was MySQL doing to produce this weird behavior?

Upvotes: 6

Views: 24814

Answers (5)

efess
efess

Reputation: 101

Late to the party, but I just ran into this tonight - duplicate key '472817' and the provided answers didn't help.

On a whim I ran:

repair table wp_abk_period

which output

Number of rows changed from 472816 to 472817

Seems like mysql had the row count wrong, and the issue went away.

My environment:

mysql  Ver 14.14 Distrib 5.1.73, for Win64 (unknown)

Create table syntax:

CREATE TABLE `env_events` (
  `tableId` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` varchar(50) DEFAULT NULL,
  `timestamp` int(11) DEFAULT NULL,
  `temperature` float DEFAULT NULL,
  `humidity` float DEFAULT NULL,
  `pressure` float DEFAULT NULL,
  `motion` int(11) DEFAULT NULL,
  PRIMARY KEY (`tableId`)
) ENGINE=MyISAM AUTO_INCREMENT=528521 DEFAULT CHARSET=latin1

Upvotes: 5

Sergey Shcherbakov
Sergey Shcherbakov

Reputation: 4778

When debugging this problem check the table name case sensitivity (especially if you run MySql not on Windows). E.g. if one script uses upper case to 'CREATE TABLE my_table' and another script tries to 'INSERT INTO MY_TABLE'. These 2 tables might have different contents and different file system locations which might lead to the described problem.

Upvotes: 0

Mickael
Mickael

Reputation: 71

I had the same problem and here is my solution :

My ID column had a bad parameter. It was Tinyint, and MySql want to write a 128th line.

Sometimes, your problem you think the bigger you have is only a tiny parameter...

Upvotes: 7

Magnus Lindgren
Magnus Lindgren

Reputation: 287

You can check the current value of the auto_increment with the following command:

show table status

Then check the max value of the id and see if it looks right. If not change the auto_increment value of your table.

Upvotes: 3

Tom Mac
Tom Mac

Reputation: 9853

It could be that your AUTO_INCREMENT value for the table and the actual values in id column have got out of whack.

This might help:

Step 1 - Get Max id from table

select max(id) from wp_abk_period

Step 2 - Align the AUTO_INCREMENT counter on table

ALTER TABLE wp_abk_period AUTO_INCREMENT = <value from step 1 + 100>;

Step 3 - Retry the insert

As for why the AUTO_INCREMENT has got out of whack I don't know. Added auto_increment after data was in the table? Altered the auto_increment value after data was inserted into the table?

Hope it helps.

Upvotes: 16

Related Questions