MySQL - Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

배경

테스트에 사용할 더미 데이터 생성을 위해 MySQL에서 재귀 CTE를 사용하다가 아래와 같은 에러를 만났었다.

WITH RECURSIVE seq AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM seq WHERE seq < 1000000
)
SELECT
    DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) AS transaction_dt,
    FLOOR(50000 + (RAND() * 990)) * 100 AS request_amount,
    FLOOR(RAND() + (FLOOR(1000 + (RAND() * 500)) + 1)) * 100 AS processing_amount,
    DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) AS created_dt,
    LPAD(seq, 6, '0') AS approval_no
FROM seq
LIMIT 1000000;

Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

 

테스트 데이터를 대량으로 만들기 위해 WITH RECURSIVE 구문으로 시퀀스를 생성했는데, 1001번째에서 쿼리가 중단되는 문제가 발생했었다.

 

원인

알고 보니 MySQL은 무한 루프를 방지하기 위해 재귀 CTE의 최대 반복 횟수를 1000으로 제한하고 있었다. 따라서 1000개까지만 순차적으로 생성이 가능했고, 그 이상으로 반복하려 하면 위 에러가 발생했던 것이었다. 이 제한 값은 시스템 변수 cte_max_recursion_depth로 관리되고 있었고, 기본값은 1000이었다.

 

해결 방법

나는 세션 단위에서 cte_max_recursion_depth 값을 늘려주는 방식으로 해결했다.

SET SESSION cte_max_recursion_depth = 100000;

 

아래처럼 글로벌 설정도 가능하지만, 글로벌로 설정하면 MySQL 서버 전체에 영향을 주기 때문에 주의해야 한다.

SET GLOBAL cte_max_recursion_depth = 100000;

 

참조

https://dba.stackexchange.com/questions/338291/setting-cte-max-recursion-depth-value-has-no-effect

Setting cte_max_recursion_depth value has no effect

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_cte_max_recursion_depth

MySQL :: MySQL 8.0 Reference Manual :: 7.1.8 Server System Variables