sql - Mysql - duplicate entry error for key with auto increment -

sql - Mysql - duplicate entry error for key with auto increment -

why error of form:

error in query: duplicate entry '10' key 1

...when doing insert statement like:

insert wp_abk_period (pricing_id, apartment_id) values (13, 27)

...with 13 , 27 beingness valid id-s existing pricing , apartment rows, , table 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 case , having on auto_increment sufficient beingness able not specify it?

note: if provide unused value id, insert wp_abk_period (id, pricing_id, apartment_id) values (3333333, 13, 27) works fine, again, set auto_increment shouldn't need this!

note 2: ok, finish "twilight zone" moment: after running query above huge number id, things started working normally, no more duplicate entry errors. can explain me wtf mysql doing produce weird behavior?

it auto_increment value table , actual values in id column have got out of whack.

this might help:

step 1 - max id table

select max(id) wp_abk_period

step 2 - align auto_increment counter on table

alter table wp_abk_period auto_increment = <value step 1 + 100>;

step 3 - retry insert

as why auto_increment has got out of whack don't know. added auto_increment after info in table? altered auto_increment value after info inserted table?

hope helps.

mysql sql database