Duplicate entry '1' for key 'PRIMARY' in data migration in Magento

Question

I'm wrapping my head around why the following code is throwing an error at me: The error I get is:

[ERROR in query 5] Duplicate entry '1' for key 'PRIMARY'

This is the query I'm executing:

DROP TABLE IF EXISTS `core_store`;

CREATE TABLE `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
  `code` varchar(32) DEFAULT NULL COMMENT 'Code',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `name` varchar(255) NOT NULL COMMENT 'Store Name',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
  KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
  KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
  KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores';

LOCK TABLES `core_store` WRITE;
/*!40000 ALTER TABLE `core_store` DISABLE KEYS */;

INSERT INTO `core_store` (`store_id`, `code`, `website_id`, `group_id`, `name`, `sort_order`, `is_active`)
VALUES
    (0,'admin',0,0,'Admin',0,1),
    (1,'store',1,1,'Store',0,1);

/*!40000 ALTER TABLE `core_store` ENABLE KEYS */;
UNLOCK TABLES;

I also get this error when I remove all keys except the primary:

DROP TABLE IF EXISTS `core_store`;

CREATE TABLE `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
  `code` varchar(32) DEFAULT NULL COMMENT 'Code',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `name` varchar(255) NOT NULL COMMENT 'Store Name',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
  PRIMARY KEY (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores';

LOCK TABLES `core_store` WRITE;
/*!40000 ALTER TABLE `core_store` DISABLE KEYS */;

INSERT INTO `core_store` (`store_id`, `code`, `website_id`, `group_id`, `name`, `sort_order`, `is_active`)
VALUES
    (0,'admin',0,0,'Admin',0,1),
    (1,'store',1,1,'Store',0,1);

/*!40000 ALTER TABLE `core_store` ENABLE KEYS */;
UNLOCK TABLES;

Does anyone knows what's going wrong here? Does the ID being '0' has something to do with it perhaps? When I repeat the query with multiple rows with no ID=0-entry it works. So what's going on here?

This answer helped me: [enter link description here](http://stackoverflow.com/questions/1142472/how-to-force-mysql-to-take-0-as-a-valid-auto-increment-value) — Joshua Fricke, Jun 03 '16 at 21:40

score 1 · Answer 1 · edited May 23 '17 at 12:22

1

This answer helped me here.

The basic solution is this:

Blockquote SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

See my comment for an additional issue I solved too with this being overridden mid script.

edited May 23 '17 at 12:22

Community

1
1

answered Jun 03 '16 at 21:42

Joshua Fricke

381
3
14

Anshuman Nayak · Answer 2 · 2014-10-16T11:45:56.887

Hi due to foreign key constraint and also due to auto increment id the above query returning error. for first insert you are providing store_id = 0 but as its a auto increment id its value for first insert = 1 so for second insert query its throwing error 1-1 duplicate.

please run the additional update query to correct the store_id. i have changed the store_id to 1 and 2 instead of 0,1 for 2 insert statement then i am updaing them to correct values.

run the entire sql staring from SET FOREIGN KEY to Update queries not only update query.

i tested it before posting here.

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `core_store`;

CREATE TABLE `core_store` (
  `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
  `code` varchar(32) DEFAULT NULL COMMENT 'Code',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
  `group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
  `name` varchar(255) NOT NULL COMMENT 'Store Name',
  `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
  `is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
  KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
  KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
  KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores';

LOCK TABLES `core_store` WRITE;
/*!40000 ALTER TABLE `core_store` DISABLE KEYS */;

INSERT INTO `core_store` (`store_id`, `code`, `website_id`, `group_id`, `name`, `sort_order`, `is_active`)
VALUES
(1,'admin',0,0,'Admin',0,1),
(2,'store',1,1,'Store',0,1);

UPDATE `core_store` SET `store_id` = '0' WHERE `store_id` = '1' COLLATE utf8_bin LIMIT 1;

UPDATE `core_store` SET `store_id` = '1' WHERE `store_id` = '2' COLLATE utf8_bin LIMIT 1;

/*!40000 ALTER TABLE `core_store` ENABLE KEYS */;
UNLOCK TABLES;

Duplicate entry '1' for key 'PRIMARY' in data migration in Magento

2 Answers2