0

I'm using a 3rd party plugin to setup our production website in a test/dev environment. It allows export/import of the categories, excluding the Category ID's (which are automatically generated). As a test, we created a full list of categories and a single product. Exported both, wiped both the product and categories, and the import works. Except that the Category ID's are being added incrementally (instead of being "reset" when I delete the categories). This makes sense from a Db perspective, so I'm assuming that the Category ID # is being incrementally counted from some Db count location.

Does anybody know where this count is coming from, and where I can reset it in Magento 2.1 ? (this is just for testing purposes).

Thanks in advance.

0

If you delete categories from admin panel, newly created categories will be having an id based on last created category id(irrespective of whether it's present or deleted). You need to truncate these categories using following sql queries which will reset the ids. Take db backup before executing it.

    SET FOREIGN_KEY_CHECKS = 0;

    TRUNCATE TABLE `catalog_category_entity`; 
    TRUNCATE TABLE `catalog_category_entity_datetime`; 
    TRUNCATE TABLE `catalog_category_entity_decimal`; 
    TRUNCATE TABLE `catalog_category_entity_int`; 
    TRUNCATE TABLE `catalog_category_entity_text`; 
    TRUNCATE TABLE `catalog_category_entity_varchar`; 
    TRUNCATE TABLE `catalog_category_product`; 
    TRUNCATE TABLE `catalog_category_product_index`;

    INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES ('1', '0', '0', '2016-06-23 00:00:00', '2016-06-23 00:00:00', '1', '0', '0', '1'), ('2', '3', '1', '2016-06-23 00:00:00', '2016-06-23 00:00:00', '1/2', '1', '1', '0');
    INSERT INTO `catalog_category_entity_datetime` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES ('1', '58', '0', '1', NULL), (2, 58, 0, 2, NULL);
    INSERT INTO `catalog_category_entity_decimal` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES
    (1, 69, 0, 2, NULL);
    INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES ('1', '66', '0', '1', '1'), (2, 43, 0, 2, 1), (3, 50, 0, 2, NULL), (4, 51, 0, 2, 1), (5, 66, 0, 2, 1), (6, 68, 0, 2, 0);
    INSERT INTO `catalog_category_entity_text` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES ('1', '64', '0', '1', NULL), (2, 64, 0, 2, NULL), (3, 44, 0, 2, NULL), (4, 47, 0, 2, NULL), (5, 48, 0, 2, NULL), (6, 61, 0, 2, NULL);
    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES ('1', '42', '0', '1', 'Root Catalog');
    INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES (2, 42, 0, 2, 'Default Category'), (3, 46, 0, 2, NULL), (4, 49, 0, 2, 'PRODUCTS'), (5, 57, 0, 2, NULL), (6, 60, 0, 2, NULL), (7, 65, 0, 2, NULL), (8, 113, 0, 2, 'default-category'), (9, 114, 0, 2, NULL), (10, 143, 0, 2, NULL), (11, 145, 0, 2, NULL), (12, 147, 0, 2, NULL);

    SET FOREIGN_KEY_CHECKS = 1;
  • Not working in my case Magento 2.2.4 Getting the following: 1701 - Cannot truncate a table referenced in a foreign key constraint (test_db.catalog_category_entity_decimal, CONSTRAINT CAT_CTGR_ENTT_DEC_ENTT_ID_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (entity_id) REFERENCES test_db.catalog_category_entity) – user2478262 Jul 11 '18 at 11:11
  • Have you used SET FOREIGN_KEY_CHECKS = 0; before executing truncate queries? – amitshree Jul 11 '18 at 11:48
0

If you have phpMyAdmin then you can do by going to table and clicking on Operations. After that you see a AUTO_INCREMENT which in my case is 42. You can reset it to 1 and it should solve your problem. enter image description here

If you have command line access only then you can run the below mysql query:

ALTER TABLE `catalog_category_entity` AUTO_INCREMENT=1

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.