recursion limit 🗃️ SQL

Msg 530: The statement terminated. The maximum recursion 100 has been exhausted before statement completion. / MySQL 3636 Recursive query aborted after 1001 iterations

A recursive CTE exceeded the database’s recursion limit — often an infinite loop in hierarchical data.

Seen on: SQL

Meaning

Cycles in parent/child data (a node that is its own ancestor) or missing termination conditions make recursion endless.

Common causes

  • Cycle in hierarchical data
  • Missing stop condition in the recursive part
  • Legitimately deep hierarchies over the default limit

⚡ Quick fix

  1. Detect cycles (track visited ids/path)
  2. Add a depth limit column
  3. Raise the limit (OPTION (MAXRECURSION n), cte_max_recursion_depth) only for legitimate depth

Detailed fix by platform

SQL

  1. WITH RECURSIVE t AS (SELECT id, parent_id, 1 depth FROM nodes WHERE id = 1 UNION ALL SELECT n.id, n.parent_id, t.depth + 1 FROM nodes n JOIN t ON n.parent_id = t.id WHERE t.depth < 50) SELECT * FROM t;

How to diagnose

  1. Data — Any cycles?
  2. Depth — Expected maximum?

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.