getting Error Code: 2013 Lost connection to MySQL server during query while running recursive stored procedure

Question

I have read almost all related questions. but its not working for me. I am trying to traverse through whole child parent data to do so I have made recursive stored procedure.

CREATE DEFINER=`root`@`localhost` PROCEDURE `g_tree`(in p_parent varchar(30), in depth int)
    BEGIN


DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;


/*table consisting all child*/
if depth = 0 then 
    drop table if exists final_child_tree;
    create temporary table final_child_tree (final_child varchar(15));
end if;


/*child_t stores all connections of p_parent as single record of set of strings*/
    drop temporary table if exists child_t;
    create temporary table child_t (t_child varchar(15), iterated int default 0);
    
/*make table consitsing all conneciton in distinct record*/
    set @list := (select child from connections where parent = p_parent );
    iterator:
    LOOP
    select * from child_t;
    IF CHAR_LENGTH(TRIM(@list)) = 0 OR @list IS NULL THEN

        select * from final_child_tree;
        LEAVE iterator;
        
    END IF;
    select * from final_child_tree;
    SET _next = SUBSTRING_INDEX(@list,',',1);

    SET _nextlen = CHAR_LENGTH(_next);

    SET _value = TRIM(_next);

    INSERT INTO child_t VALUES (_value,0);
    insert into final_child_tree values (_value);
    SET @list = INSERT(@list,1,_nextlen + 1,'');
    
    END LOOP;

/*get child of child*/
iterator :
loop 
    if exists (select t_child from child_t where iterated = 0 limit 1) then
        set @new_parent := (select t_child from child_t where iterated = 0 limit 1);
        set @childExists := (select count(*) from connections where parent = @newParent);
        if @new_parent != 0 and @childExists != 0 then 
            call g_tree (@new_parent,1);
        end if;
    else 
        leave iterator;
    end if;
end loop;

END

and its throwing an error :

Error Code: 2013 Lost connection to MySQL server during query

tables :

connection :

+--------+-------------+
| parent | child       |
+--------+-------------+
| 1      | c1,c2,c3    |
| c1     | c11,c12,c13 |
| c2     | c21,c22     |
+--------+-------------+

is there any other better way to do this ?

What does `SELECT VERSION();` return? Are you using a version of MySQL that supports recursive CTE queries? That's the best solution. — Bill Karwin, May 06 '22 at 20:27
Check my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462) for more information. — Bill Karwin, May 06 '22 at 20:28

score 1 · Accepted Answer · answered May 06 '22 at 21:08

1

The normalized way to store hierarchical data is to store a reference to the parent, not store a comma-separated list of the children.

id	parent_id
1	NULL
c1	1
c2	1
c3	1
c11	c1
c12	c1
c13	c1
c21	c2
c22	c2

Now you can use a recursive CTE query to get all the descendants from a given node of the tree:

WITH RECURSIVE cte (parent_id) AS (
 SELECT id FROM connection WHERE parent_id IS NULL
 UNION ALL
 SELECT id FROM connection JOIN cte on connection.parent_id = cte.parent_id
)
SELECT * FROM cte;

answered May 06 '22 at 21:08

Bill Karwin

538,548
86
673
828

never thought that solution would be this simple, will confirm this and mark it as correct answer. For sake of knowledge can you tell me why my sp is not working because I think it should work completely fine ! – ht006 May 06 '22 at 21:36
I'm not sure, but I would assume it's doing infinite recursion somehow. I do notice you have a mismatch between variables named `@new_parent` and `@newParent`. This is a good reason to `DEFINE` local variables, instead of using user-defined variables. – Bill Karwin May 06 '22 at 22:46

getting Error Code: 2013 Lost connection to MySQL server during query while running recursive stored procedure

1 Answers1