A recursive CTE that carries its own path to detect a cycle

A depth limit stops a recursive query running forever and does not tell you the data has a cycle in it, which is a different problem with a different fix.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, CAST(id AS CHAR(200)) AS path, 0 AS depth
  FROM categories WHERE id = 12

  UNION ALL

  SELECT c.id, c.parent_id, CONCAT(t.path, ',', c.id), t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 20
    AND FIND_IN_SET(c.id, t.path) = 0    -- the cycle check
)
SELECT * FROM tree;

The depth guard bounds the damage and the path check identifies the problem, and a tree with genuine cycles needs both — the guard alone produces a truncated result that looks like a shallow tree. The CAST in the anchor is required and is easy to forget: without it MySQL infers the column width from the first row and silently truncates the path on deeper levels. cte_max_recursion_depth is the server-level backstop, defaulting to 1000.