For people that has the exact same problem where the nested sets are complete off for some reason.
before doing anything of this please backup your database!
With some help from an another post: How to repair a corrupted MPTT tree (nested set) in the database using SQL?
changed the sql to work with Akeneo categories:
add this procedure to your database:
DROP PROCEDURE IF EXISTS tree_recover;
DELIMITER //
CREATE PROCEDURE tree_recover ()
MODIFIES SQL DATA
BEGIN
DECLARE currentId, currentParentId CHAR(36);
DECLARE currentLeft INT;
DECLARE startId INT DEFAULT 1;
# Determines the max size for MEMORY tables.
SET max_heap_table_size = 1024 * 1024 * 512;
START TRANSACTION;
# Temporary MEMORY table to do all the heavy lifting in,
# otherwise performance is simply abysmal.
CREATE TABLE `tmp_tree` (
`id` int(36) NOT NULL,
`parent_id` int(36),
`lft` int(11) unsigned,
`rgt` int(11) unsigned,
PRIMARY KEY (`id`),
INDEX USING HASH (`parent_id`),
INDEX USING HASH (`lft`),
INDEX USING HASH (`rgt`)
) ENGINE = MEMORY
SELECT `id`,
`parent_id`,
`lft`,
`rgt`
FROM `pim_catalog_category`;
# Leveling the playing field.
UPDATE `tmp_tree`
SET `lft` = 0,
`rgt` = 0;
# Establishing starting numbers for all root elements.
WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `parent_id` IS NULL AND `lft` = 0 AND `rgt` = 0 LIMIT 1) DO
UPDATE `tmp_tree`
SET `lft` = startId,
`rgt` = startId + 1
WHERE `parent_id` IS NULL
AND `lft` = 0
AND `rgt` = 0
LIMIT 1;
SET startId = startId + 2;
END WHILE;
# Switching the indexes for the lft/rgt columns to B-Trees to speed up the next section, which uses range queries.
DROP INDEX `lft` ON `tmp_tree`;
DROP INDEX `rgt` ON `tmp_tree`;
CREATE INDEX `lft` USING BTREE ON `tmp_tree` (`lft`);
CREATE INDEX `rgt` USING BTREE ON `tmp_tree` (`rgt`);
# Numbering all child elements
WHILE EXISTS (SELECT * FROM `tmp_tree` WHERE `lft` = 0 LIMIT 1) DO
# Picking an unprocessed element which has a processed parent.
SELECT `tmp_tree`.`id`
INTO currentId
FROM `tmp_tree`
INNER JOIN `tmp_tree` AS `parents`
ON `tmp_tree`.`parent_id` = `parents`.`id`
WHERE `tmp_tree`.`lft` = 0
AND `parents`.`lft` <> 0
LIMIT 1;
# Finding the element's parent.
SELECT `parent_id`
INTO currentParentId
FROM `tmp_tree`
WHERE `id` = currentId;
# Finding the parent's lft value.
SELECT `lft`
INTO currentLeft
FROM `tmp_tree`
WHERE `id` = currentParentId;
# Shifting all elements to the right of the current element 2 to the right.
UPDATE `tmp_tree`
SET `rgt` = `rgt` + 2
WHERE `rgt` > currentLeft;
UPDATE `tmp_tree`
SET `lft` = `lft` + 2
WHERE `lft` > currentLeft;
# Setting lft and rgt values for current element.
UPDATE `tmp_tree`
SET `lft` = currentLeft + 1,
`rgt` = currentLeft + 2
WHERE `id` = currentId;
END WHILE;
# Writing calculated values back to physical table.
UPDATE `pim_catalog_category`, `tmp_tree`
SET `pim_catalog_category`.`lft` = `tmp_tree`.`lft`,
`pim_catalog_category`.`rgt` = `tmp_tree`.`rgt`
WHERE `pim_catalog_category`.`id` = `tmp_tree`.`id`;
COMMIT;
DROP TABLE `tmp_tree`;
END//
DELIMITER ;
And call it with:
CALL tree_recover;
on your Akeneo Database
This command can take a couple minutes to days depending how many categories you have
BE AWARE: By executing this command the new lft and rgt calculated values are committed immediately after the process is done.
