0

I used the following code to clear all my products and unused attribute values in order to make a fresh re-import with the final CSVs:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;


    DELETE o, v
      FROM `eav_attribute` a
INNER JOIN `eav_attribute_option` o ON a.`attribute_id` = o.`attribute_id`
INNER JOIN `eav_attribute_option_value` v ON v.`option_id` = o.`option_id`
INNER JOIN `eav_entity_type` t ON t.`entity_type_id` = a.`entity_type_id`
 LEFT JOIN `catalog_product_entity_int` pi ON o.`option_id` = pi.`value` AND o.`attribute_id` = pi.`attribute_id`
 LEFT JOIN `catalog_product_entity_varchar` pv ON o.`option_id` = pv.`value` AND o.`attribute_id` = pv.`attribute_id`
     WHERE pi.`entity_id` IS NULL
       AND pv.`entity_id` IS NULL
       AND t.`entity_type_code` = "catalog_product"
       AND a.is_user_defined = 1

SET foreign_key_checks = 0;
TRUNCATE dataflow_batch_export ; 
TRUNCATE dataflow_batch_import ; 
TRUNCATE log_customer ; 
TRUNCATE log_quote ; 
TRUNCATE log_summary ; 
TRUNCATE log_summary_type ; 
TRUNCATE log_url ; 
TRUNCATE log_url_info ; 
TRUNCATE log_visitor ; 
TRUNCATE log_visitor_info ; 
TRUNCATE log_visitor_online ; 
TRUNCATE report_event ;
SET foreign_key_checks = 1;

But now when I try to make a fresh import I get the following errors:

 SKU CLSAN - 1 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4-1' for key 'PRIMARY' -
SKU CLSAN - 2 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4-1' for key 'PRIMARY' - ERROR ON RECORD #4
SKU KPBWFS - 3 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '7-5' for key 'PRIMARY' -
SKU KPBWFS - 4 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '7-5' for key 'PRIMARY' - ERROR ON RECORD #7
SKU KPBWNP - 5 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-8' for key 'PRIMARY' -
SKU KPBWNP - 6 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-8' for key 'PRIMARY' - ERROR ON RECORD #10
SKU KPBWSP - 7 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '13-11' for key 'PRIMARY' -
SKU KPBWSP - 8 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '13-11' for key 'PRIMARY' - ERROR ON RECORD #13
SKU CLBSD - 9 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '21-14' for key 'PRIMARY' -
SKU CLBSD - 10 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '21-14' for key 'PRIMARY' - ERROR ON RECORD #21
SKU CLBSK - 11 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '34-22' for key 'PRIMARY' -
SKU CLBSK - 12 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '34-22' for key 'PRIMARY' - ERROR ON RECORD #34
SKU LURS10W - 13 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '38-35' for key 'PRIMARY' -
SKU LURS10W - 14 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '38-35' for key 'PRIMARY' - ERROR ON RECORD #38

I have tried clearing cache and re-indexing before import but didn't seem to work, any suggestions on how to resolve this problem is really appreciated.

Thanks in advance.

  • Already table data exists in '4-1' thats why its throws that error. Could you please take backup of database and truncate that data. – Venkat Jul 29 '16 at 15:31
  • @Venkat could you please narrow me down on which table I should look for this values? – Artemis Chaitidis Jul 29 '16 at 15:33
  • From the above csv you have inserting record to this table catalog_product_link_type, catalog_product_link_attribute, cataloginventory_stock. Please check data of this tables – Venkat Jul 29 '16 at 15:48
2

Found the problem and though that someone might find it handy in the future, so my database product cleaning query was missing 1 truncate of the table catalog_product_relations which left behind data, thus generating the data integrity constrains.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.