9

We have been experimenting with syncing our existing database(not Magento) to Magento 2.

In the process, we have created over 1000 categories, but then we rearranged all the IDs to sync with our system. The problem is that I can no longer import the new categories as I get the error "URL key for specified store already exists." and we are getting duplicate categories now.

I think the best solution would be to clear them all out and import fresh. I found this tutorial for 1.9

https://gist.github.com/jklance/9664371

When I look at the new database I notice small differences

  1. entity_type_id is in all the old category tables but none of the new ones.

  2. new database has an additional table catalog_category_product_index_tmp

My questions are these,

  1. can I use that code I found and modify to remove entity_type_id and add TRUNCATE TABLE catalog_category_product_index_tmp;

  2. or is there more I need to modify?

  3. or can you provide me a clean script to remove all categories?

we only have 1 test product in the system, so no need to worry about them

19

This one worked for me in Magento ver. 2.1.0

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', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
('2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');

INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES 
('1', '69', '0', '1', '1'),
('2', '46', '0', '2', '1'),
('3', '69', '0', '2', '1');

INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `entity_id`, `value`) VALUES 
('1', '45', '0', '1', 'Root Catalog'),
('2', '45', '0', '2', 'Default Category');

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite WHERE entity_type = 'category';
  • Just that so other people know, this worked perfectly for me. The one in the other answer did not work for me even after correcting the typos. – Hassan Al-Jeshi Sep 27 '16 at 6:42
  • Gives problems when creating products afterwards in Magento 2.2.3. Stuck when creating product: No such entity with id = 3 – Condor Apr 10 '18 at 7:53
3

This one will remove all categories in Magento EE 2.1

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`, `created_in`, `updated_in`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES 
('1', '1', '2', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
('2', '1', '2', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');

INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `row_id`, `value`) VALUES 
('1', '69', '0', '1', '1'),
('2', '46', '0', '2', '1'),
('3', '69', '0', '2', '1');

INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `row_id`, `value`) VALUES 
('1', '45', '0', '1', 'Root Catalog'),
('2', '45', '0', '2', 'Default Category');

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite WHERE entity_type = 'category';
DELETE FROM `sequence_catalog_category` WHERE sequence_value > 2;
3

The following delete all the categories magento programmatically except the root category.

function deleteAllCategories($objectManager) {

$categoryFactory = $objectManager->get('Magento\Catalog\Model\CategoryFactory');
$newCategory = $categoryFactory->create();
$collection = $newCategory->getCollection();
$objectManager->get('Magento\Framework\Registry')->register('isSecureArea', true);

foreach($collection as $category) {

    $category_id = $category->getId();

    if( $category_id <= 2 ) continue;

    try {
        $category->delete();
        echo 'Category Removed '.$category_id .PHP_EOL;
    } catch (Exception $e) {
        echo 'Failed to remove category '.$category_id .PHP_EOL;
        echo $e->getMessage() . "\n" .PHP_EOL;
    }
}

}

Please click for detailed explanation. http://www.pearlbells.co.uk/mass-delete-magento-2-categories-programmatically/

  • Will this reset the category id after deleting ? – Amit Singh Jul 1 '18 at 6:35
  • No, it won't reset it will start from n+1 ( n is the last category id in the previous version ) – Liz Eipe C Jul 2 '18 at 8:14
3

(works with Magento 2.2)

If trigger and foreign keys work properly, what should be the case, then this is how it works:

DELETE FROM `catalog_category_entity` WHERE `entity_id` >= 3;
ALTER TABLE `catalog_category_entity` AUTO_INCREMENT = 3;
1

I have been using this recently with apparent success on Magento 2.1:

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`,`created_in`,`updated_in`,`attribute_set_id`,`parent_id`,`path`,`POSITION`,`level`,`children_count`) 
    VALUES  (1,1,2147483647,0,0,'1',0,0,1),
            (2,1,2147483647,3,1,'1/2',1,1,0); 
INSERT INTO `catalog_category_entity_int`
    (`value_id`,`attribute_id`,`store_id`,`row_id`,`value`) 
    VALUES  (1,69,0,1,1),
            (2,46,0,2,1),
            (3,69,0,2,1); 
INSERT INTO `catalog_category_entity_varchar`
    (`value_id`,`attribute_id`,`store_id`,`row_id`,`value`)
    VALUES  (1,45,0,1,'Root Catalog'),
            (2,45,0,2,'Default Category'),
            (3,52,0,2,'PRODUCTS');
SET FOREIGN_KEY_CHECKS = 1;
DELETE FROM url_rewrite WHERE entity_type = 'category';

The INSERT statements are based on the contents of a completely clean installed Magento 2.

1

Imporving @Liz answer

public function deleteStoreCategories()
{
    $objectManager = Magento\Framework\App\ObjectManager::getInstance();
    $categoryFactory = $objectManager->get('Magento\Catalog\Model\CategoryFactory');
    $newCategory = $categoryFactory->create();
    $collection = $newCategory->getCollection();
    $objectManager->get('Magento\Framework\Registry')->register('isSecureArea', true);

    foreach ($collection as $category) {
        $category_id = $category->getId();

        if ($category_id <= 2) {
            continue;
        }

        try {
            $category->delete();
            echo 'Category Removed ' . $category_id . PHP_EOL;
        } catch (\Exception $e) {
            echo 'Failed to remove category ' . $category_id . PHP_EOL;
            echo $e->getMessage() . "\n" . PHP_EOL;
        }
    }
}
0

Magento EE 2.2.6

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;

TRUNCATE TABLE `sequence_catalog_category`;

TRUNCATE TABLE `visual_merchandiser_rule`;

INSERT INTO `catalog_category_entity` (`entity_id`, created_in, updated_in, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) 
VALUES ('1', '1', '2147483647', '0', '0', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1', '0', '0', '1'),
('2', '1', '2147483647', '3', '1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, '1/2', '1', '1', '0');

INSERT INTO `catalog_category_entity_int` (`value_id`, `attribute_id`, `store_id`, `row_id`, `value`) VALUES 
('1', '70', '0', '1', '1'),
('2', '47', '0', '2', '1'),
('3', '70', '0', '2', '1');

INSERT INTO `catalog_category_entity_varchar` (`value_id`, `attribute_id`, `store_id`, `row_id`, `value`) VALUES 
('1', '46', '0', '1', 'Root Catalog'),
('2', '46', '0', '2', 'Default Category');

INSERT INTO `sequence_catalog_category`(`sequence_value`) VALUES 
('1'),
('2');

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM url_rewrite WHERE entity_type = 'category';

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.