Limiting Common Table Recursion I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.

 SELECT id,MK, pid,AFI 
   FROM M A 
  WHERE LEVEL &lt;= 2 -- limiting iterations here
  START WITH pid = 1 
CONNECT BY Pid = PRIOR id;


We have used recursive common expression table but we are unable to restrict the number of iterations to 2 (LEVEL &lt;= 2). We don't want to set the system variables like cte_max_recursion_depth. How else can we limit recursion depth?
Please note while @Kondybas' answer will produce expected results on correct data sets it will NOT restrict the number of iterations.
All rows will still be read but only 2 of them will be returned. That means:

Drop in performance
You will NOT avoid infinite loops that way!

If there is a loop in the tree of pid-id links in the table data due to some mistake such query can last forever (which might event corrupt the entire server by flooding the disk with endless temporary result data - checked in MariaDB 10.3.15.)
So you have to put a restricting condition inside the WITH clause as follows:
WITH RECURSIVE cte AS
  ( SELECT 1 AS lvl -- UDV initialization
         , A.id
         , A.MK
         , A.pid
         , A.AFI -- initial portion
      FROM M AS A 
     WHERE A.pid = 1

     UNION

    SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
         , z.id
         , z.MK
         , z.pid
         , z.AFI -- recursive portion
      FROM cte -- referring to the already gathered recursion
      JOIN M AS z ON z.pid = cte.id
      WHERE cte.lvl &lt; 2        -- *** the correct condition ***
  )
SELECT *
  FROM cte 
;

P.S. I guess it is also a good idea to set max_recursive_iterations to some reachable value like 10000 to prevent MariaDB from getting unresponsive.