Hello guys I have this nasty problem where nested set values are getting courrupted when doing some heave updates on them even when enabling transactions, I searched everywhere and tried countless SQL codes but none of them worked, so this forum seems to be my last resort.
My problem in great details
I'm trying to rebuild nested sets values using a query
The problem is that i'm getting an error
Query 3 ERROR at Line 110: : Can't reopen table: 'tmp_tree'
What seems to be the problem?
My directories table structure is: id, name, path, parent_id, _lft, _rgt
Explanation of bellow query:
Procedure Definition and Error Handling:
Drops the existing procedure if it exists. Changes the delimiter to // to allow using ; within the procedure. Creates the tree_recover procedure. Declares an exit handler to rollback and resignal on any SQL exception. Variable Declarations:
Declares variables for storing node IDs, parent IDs, left values, and a starting ID. Memory Table Setup:
Sets the maximum heap table size. Starts a new transaction. Creates a temporary table tmp_tree in MEMORY to perform the calculations efficiently. Data Initialization:
Inserts data from the original directories table into tmp_tree. Sets all _lft and _rgt values to NULL to prepare for recalculation. Root Node Initialization:
Finds root nodes (nodes with parent_id IS NULL) and initializes their _lft and _rgt values. Index Management:
Drops and recreates indexes on _lft and _rgt columns to optimize performance during range queries. Node Processing:
Selects an unprocessed node that has a processed parent. Retrieves the parent ID and left value of the parent node. Updates _rgt and _lft values of nodes to the right of the current node to make space. Sets the _lft and _rgt values for the current node. Final Update and Cleanup:
Updates the original directories table with the calculated _lft and _rgt values. Commits the transaction. Drops the temporary table.
When trying to execute this query:
This may help: https://stackoverflow.com/a/78584624/10138734
Code:
-- Drop the existing procedure if it exists to avoid conflicts
DROP PROCEDURE IF EXISTS tree_recover;
-- Change the delimiter to '//' to allow the use of semicolons within the procedure
DELIMITER //
-- Create the stored procedure tree_recover
CREATE PROCEDURE tree_recover ()
MODIFIES SQL DATA
BEGIN
-- Declare variables to store the current node's ID, parent ID, left value, and starting ID
DECLARE currentId CHAR(36);
DECLARE currentParentId CHAR(36);
DECLARE currentLeft INT;
DECLARE startId INT DEFAULT 1;
-- Declare an exit handler to rollback the transaction in case of an error
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK; -- Rollback the transaction
RESIGNAL; -- Propagate the exception
END;
-- Set the maximum size for MEMORY tables to 512MB to ensure enough space
SET max_heap_table_size = 1024 * 1024 * 512;
-- Start a new transaction
START TRANSACTION;
-- Drop temporary tables if they exist
DROP TEMPORARY TABLE IF EXISTS tmp_tree;
DROP TEMPORARY TABLE IF EXISTS tmp_unprocessed;
-- Create a temporary table in MEMORY to perform the updates efficiently
CREATE TEMPORARY TABLE tmp_tree (
id CHAR(36) NOT NULL, -- Node ID
parent_id CHAR(36) DEFAULT NULL, -- Parent node ID
_lft INT UNSIGNED DEFAULT NULL, -- Left value
_rgt INT UNSIGNED DEFAULT NULL, -- Right value
PRIMARY KEY (id), -- Primary key on ID
KEY parent_id_idx (parent_id) -- Index on parent ID for fast lookups
) ENGINE = MEMORY;
-- Insert all nodes from the original directories table into the temporary table
INSERT INTO tmp_tree (id, parent_id, _lft, _rgt)
SELECT id, parent_id, _lft, _rgt FROM directories;
-- Set all left and right values to NULL to prepare for recalculation
UPDATE tmp_tree SET _lft = NULL, _rgt = NULL;
-- Create another temporary table to track unprocessed nodes
CREATE TEMPORARY TABLE tmp_unprocessed (
id CHAR(36) NOT NULL PRIMARY KEY
) ENGINE = MEMORY;
-- Initialize tmp_unprocessed with all node IDs
INSERT INTO tmp_unprocessed (id)
SELECT id FROM tmp_tree;
-- Initialize left and right values for root nodes (nodes with no parent)
WHILE EXISTS (SELECT 1 FROM tmp_tree WHERE parent_id IS NULL AND _lft IS NULL AND _rgt IS NULL LIMIT 1) DO
-- Set the left and right values for the next root node
UPDATE tmp_tree
SET _lft = startId, _rgt = startId + 1
WHERE parent_id IS NULL AND _lft IS NULL AND _rgt IS NULL
LIMIT 1;
-- Increment the startId by 2 for the next node
SET startId = startId + 2;
END WHILE;
-- Process each node in the temporary table to set the left and right values
WHILE EXISTS (SELECT 1 FROM tmp_unprocessed LIMIT 1) DO
-- Select an unprocessed node that has a processed parent
SELECT id INTO currentId
FROM tmp_unprocessed
WHERE id IN (SELECT id FROM tmp_tree WHERE _lft IS NULL AND parent_id IS NOT NULL AND parent_id IN (SELECT id FROM tmp_tree WHERE _lft IS NOT NULL))
LIMIT 1;
-- Get the parent ID of the current node
SELECT parent_id INTO currentParentId
FROM tmp_tree WHERE id = currentId;
-- Get the left value of the parent node
SELECT _lft INTO currentLeft
FROM tmp_tree WHERE id = currentParentId;
-- Shift the right values of nodes to the right of the current node by 2
UPDATE tmp_tree SET _rgt = _rgt + 2 WHERE _rgt > currentLeft;
-- Shift the left values of nodes to the right of the current node by 2
UPDATE tmp_tree SET _lft = _lft + 2 WHERE _lft > currentLeft;
-- Set the left and right values for the current node
UPDATE tmp_tree
SET _lft = currentLeft + 1, _rgt = currentLeft + 2
WHERE id = currentId;
-- Mark the current node as processed by removing it from tmp_unprocessed
DELETE FROM tmp_unprocessed WHERE id = currentId;
END WHILE;
-- Update the original directories table with the calculated left and right values
UPDATE directories
JOIN tmp_tree ON directories.id = tmp_tree.id
SET directories._lft = tmp_tree._lft, directories._rgt = tmp_tree._rgt;
-- Commit the transaction to make the changes permanent
COMMIT;
-- Drop the temporary tables as they are no longer needed
DROP TEMPORARY TABLE tmp_tree;
DROP TEMPORARY TABLE tmp_unprocessed;
END//
-- Restore the delimiter to the default ';'
DELIMITER ;
Call tree_recover();