Akeneo categories bug, I do not hope for an answer, I hope for a little clearance

Question

So I have this problem, I have automated product import to akeneo from supplier, while trying to upgrade performance of the code i broke something, now I have problems with child categories, Cannot access them from products some suppliers go good, some bugged. Whole week now Im trying to connect dont swhat happened now I went across this category table

Im not sure what the columns lft rgt means it is not first time i see them but never actualy cleared what do they do. Just tought maybe this could help me to debug stuff

Answer 1

This is a nested set model, it's a technique for representing nested sets (also known as trees or hierarchies) in relational databases.

You can find more informations about this model here

Answer 2

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.