Solved

How do I fix lft and rgt in mosets tree?

Posted on 2013-11-11
7
1,522 Views
Last Modified: 2014-04-07
Hi,

I am using Mosets Tree 2.2.0 in Joomla 1.6.1.

I find that from time to time, the nested model left and right indexes bleed into each other and cross boundaries they shouldn't, I have tried recounting the category indexes which does not fix the issue.

I can easily fix the issue by manually resetting the the left and right indexes in MySQL so that they match the correct tree structure, but, we have allot of categories and I don't want to be doing this every time as it is time consuming. So my questions are:

1) Why does this happen?
2) How can I prevent it from happening?
3) How can I fix it quickly when it happens? (I could write an automated query that fixes lft and rgt to run daily, hopefully the answer is easier than that)

Reference:
http://en.wikipedia.org/wiki/Nested_set_model
0
Comment
Question by:jwleys
  • 7
7 Comments
 

Author Comment

by:jwleys
ID: 39640054
I found this, which may present me with a method for fixing quickly. Potentially I could write a scheduled event in MySQL which repairs the nested set model left and right indexes.

Reference MySQL scheduled event documentation:
http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Reference repairing nested set model left and right:
stackoverflow.com/questions/889527/move-node-in-nested-set
0
 

Author Comment

by:jwleys
ID: 39640068
another good resource for repairing nested set model left and right:
stackoverflow.com/questions/3623645/how-to-repair-a-corrupted-mptt-tree-nested-set-in-the-database-using-sql
0
 

Author Comment

by:jwleys
ID: 39640553
Found this function in administrator/com_mtree/admin.mtree.class.php

function updateLftRgt() //trying to troubleshoot it:

function updateLftRgt() {
		
		# Retrieve parent's rgt value
		$this->_db->setQuery( "SELECT rgt FROM #__mt_cats WHERE cat_id = $this->cat_parent LIMIT 1" );
		$parent_rgt = $this->_db->loadResult();
		
		if ( is_null($parent_rgt) && $this->cat_parent == 0 ) {
			$this->_db->setQuery( "SELECT rgt FROM #__mt_cats WHERE cat_parent = -1 LIMIT 1" );
			$parent_rgt = $this->_db->loadResult();

			if ( is_null($parent_rgt) ) {
				$this->_db->setQuery( "SELECT rgt FROM #__mt_cats WHERE cat_name = 'Root' LIMIT 1" );
				$parent_rgt = $this->_db->loadResult();

				if ( is_null($parent_rgt) ) {
					$this->_db->setQuery( "SELECT (MAX(rgt) +1) FROM #__mt_cats" );
					$parent_rgt = $this->_db->loadResult();
				}

			}

		}

		# Update all category's lft and rgt (+ 2) to the right of this category
		$this->_db->setQuery("UPDATE #__mt_cats SET lft = lft+2 WHERE lft >= $parent_rgt");
		if (!$this->_db->query()) {
				echo "<script> alert('".$this->_db->getErrorMsg()."'); window.history.go(-1); </script>\n";
				return false;
		}

		$this->_db->setQuery("UPDATE #__mt_cats SET rgt = rgt+2 WHERE rgt >= $parent_rgt");
		if (!$this->_db->query()) {
				echo "<script> alert('".$this->_db->getErrorMsg()."'); window.history.go(-1); </script>\n";
				return false;
		}

		# Update this category's lft and rgt
		$this->_db->setQuery("UPDATE #__mt_cats SET lft = ".$parent_rgt.", rgt = ".($parent_rgt+1)." WHERE cat_id = $this->cat_id");
		if (!$this->_db->query()) {
				echo "<script> alert('".$this->_db->getErrorMsg()."'); window.history.go(-1); </script>\n";
				return false;
		}
		$this->lft = $parent_rgt;
		$this->rgt = $parent_rgt+1;

		return true;

	}

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jwleys
ID: 39660555
I wrote a query for checking the current status of the nested set model which I have found helpful for troubleshooting:

SELECT
p.cat_parent as pparent, p.cat_id as pid, p.cat_name as pname, p.lft as plft, p.rgt as prgt,
c.cat_parent as cparent, c.cat_id as cid, c.cat_name as cname, c.lft as clft, c.rgt as crgt  
FROM
  health3_mt_cats as c
LEFT JOIN
  health3_mt_cats as p
ON
  p.lft=(
    SELECT
      MAX(lft)
    FROM
      health3_mt_cats AS l
    WHERE
      c.lft > l.lft
      AND c.lft < l.rgt
  )

Open in new window

0
 

Accepted Solution

by:
jwleys earned 0 total points
ID: 39676373
I have adapted the query from (http://stackoverflow.com/questions/3623645/how-to-repair-a-corrupted-mptt-tree-nested-set-in-the-database-using-sql) to work with Mosets Tree. I will report back once I have tested it tonight to see if it works:

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 `health4_tmp_tree` (
        `cat_id`        char(36) NOT NULL DEFAULT '',
        `cat_parent` char(36)          DEFAULT NULL,
        `lft`       int(11)  unsigned DEFAULT NULL,
        `rgt`      int(11)  unsigned DEFAULT NULL,
        PRIMARY KEY      (`cat_id`),
        INDEX USING HASH (`cat_parent`),
        INDEX USING HASH (`lft`),
        INDEX USING HASH (`rgt`)
    ) ENGINE = MEMORY
    SELECT `cat_id`,
           `cat_parent`,
           `lft`,
           `rgt`
    FROM   `health4_mt_cats`;

    # Leveling the playing field.
    UPDATE  `health4_tmp_tree`
    SET     `lft`  = NULL,
            `rgt` = NULL;

    # Establishing starting numbers for all root elements.
    WHILE EXISTS (SELECT * FROM `health4_tmp_tree` WHERE `cat_parent` IS NULL AND `lft` IS NULL AND `rgt` IS NULL LIMIT 1) DO

        UPDATE `health4_tmp_tree`
        SET    `lft`  = startId,
               `rgt` = startId + 1
        WHERE  `cat_parent` IS NULL
          AND  `lft`       IS NULL
          AND  `rgt`      IS NULL
        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 `health4_tmp_tree`;
    DROP INDEX `rgt` ON `health4_tmp_tree`;
    CREATE INDEX `lft`  USING BTREE ON `health4_tmp_tree` (`lft`);
    CREATE INDEX `rgt` USING BTREE ON `health4_tmp_tree` (`rgt`);

    # Numbering all child elements
    WHILE EXISTS (SELECT * FROM `health4_tmp_tree` WHERE `lft` IS NULL LIMIT 1) DO

        # Picking an unprocessed element which has a processed parent.
        SELECT     `health4_tmp_tree`.`cat_id`
          INTO     currentId
        FROM       `health4_tmp_tree`
        INNER JOIN `health4_tmp_tree` AS `parents`
                ON `health4_tmp_tree`.`cat_parent` = `parents`.`cat_id`
        WHERE      `health4_tmp_tree`.`lft` IS NULL
          AND      `parents`.`lft`  IS NOT NULL
        LIMIT      1;

        # Finding the element's parent.
        SELECT  `cat_parent`
          INTO  currentParentId
        FROM    `health4_tmp_tree`
        WHERE   `cat_id` = currentId;

        # Finding the parent's lft value.
        SELECT  `lft`
          INTO  currentLeft
        FROM    `health4_tmp_tree`
        WHERE   `cat_id` = currentParentId;

        # Shifting all elements to the right of the current element 2 to the right.
        UPDATE `health4_tmp_tree`
        SET    `rgt` = `rgt` + 2
        WHERE  `rgt` > currentLeft;

        UPDATE `health4_tmp_tree`
        SET    `lft` = `lft` + 2
        WHERE  `lft` > currentLeft;

        # Setting lft and rgt values for current element.
        UPDATE `health4_tmp_tree`
        SET    `lft`  = currentLeft + 1,
               `rgt` = currentLeft + 2
        WHERE  `cat_id`   = currentId;

    END WHILE;

    # Writing calculated values back to physical table.
    UPDATE `health4_mt_cats`, `health4_tmp_tree`
    SET    `health4_mt_cats`.`lft`  = `health4_tmp_tree`.`lft`,
           `health4_mt_cats`.`rgt` = `health4_tmp_tree`.`rgt`
    WHERE  `health4_mt_cats`.`cat_id`   = `health4_tmp_tree`.`cat_id`;

    COMMIT;

    DROP TABLE `health4_tmp_tree`;

END//

DELIMITER ;

Open in new window

0
 

Author Closing Comment

by:jwleys
ID: 39694116
This works like a charm
0
 

Author Comment

by:jwleys
ID: 39984222
Although the above works, Mosets Tree have their own in-built solution for fixing this, I would suggest you use it rather than my method:

http://www.example.com/administrator/index.php?option=com_mtree&task=rebuild_tree

Reference:
http://www.mosets.com/help/kb/mosets-tree/27
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now