1

I am using MySQL 8.0.36, I have a recursive query to insert 5 million rows to a table. I get the error

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

then I ran the query SET GLOBAL cte_max_recursion_depth=10000000; Yet it seemed to have no effect. What is the problem?

2
  • I restarted the mysql service with sudo service mysql restart, doesn't work
    – umarkaa
    Apr 3 at 14:42
  • what is the way to affect the current session? I'd rather want to use that
    – umarkaa
    Apr 3 at 14:43

1 Answer 1

1

Setting global options does not affect the current session. Open a new session to inherit a recent change to a global variable.

If you change an option with SET GLOBAL and then restart the MySQL Server, those settings are reset to whatever is configured your my.cnf, or else the defaults.

what is the way to affect the current session? I'd rather want to use that

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

Check the Scope of that option. It says Global, Session. This means the option allows changes either at the global level or at the session level. You can either specify SESSION:

SET SESSION cte_max_recursion_depth=10000000;

Or just omit the scope qualifier. It defaults to session scope.

SET cte_max_recursion_depth=10000000;

However, I would caution that setting the recursion depth so high is probably not a good solution. It'll use a lot of resources.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.