?
Solved

How do I fix lft and rgt in mosets tree?

Posted on 2013-11-11
7
Medium Priority
?
1,722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

777 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