1

I have a mysql innoDB table with over 6000 records. The primary key (ID) is set to be auto_incremented. At this time the auto_increment value was 41804.

I inserted over 2000 records through insert statements, and found some of the queries to have some data problems. By this time the auto_increment value had changed to 42500.

I fixed the data issue in the insert statements, deleted the newly inserted records from the table and changed the auto_increment value to 41804 (to be incremented by 10) with

alter table mytable auto_increment = 41804

I ran the insert statements again - this time I got an error code: 1062 - Duplicate entry '320-41804' for key 'PRIMARY'

The max ID value is 41794 and the auto_increment value is 41804 - there is no record with ID value 41804 - but i'm still getting duplicate key error.

can any one shed some light on this?

much appreciated.

dagger
  • 515
  • 2
  • 6
  • 17
  • Please post the 'show create table' output – ethrbunny Mar 19 '13 at 12:32
  • CREATE TABLE `product` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `category_id` int(11) NOT NULL DEFAULT '0', `Snap` varchar(250) DEFAULT '', `Description` text, PRIMARY KEY (`ID`), KEY `Index_3654` (`category_id`), ) ENGINE=InnoDB AUTO_INCREMENT=52000 DEFAULT CHARSET=latin1 – dagger Mar 19 '13 at 12:38
  • issue resolved, there was a trigger I had forgotten about which was also inserting the newly created products in other tables, and so the composite primary key there was giving the problem. Thank you and sorry for taking up your time. – dagger Mar 19 '13 at 12:51

0 Answers0