Mysql - duplicate entry error for key with auto increment

Question

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?

Answer 1

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.