Search code examples
mysqldatabasemariadbdatagrip

Syntax error near RECURSIVE when trying to create a procedure in mysql

I am trying to implement a procedure in mysql using datagrip but I am still getting this errror.

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RECURSIVE descendants AS ( SELECT e.activity, CAST(e.activity AS CHAR(500)) AS' at line 3

I am running on windows

mysql Ver 8.0.15 for Win64 on x86_64 (MySQL Community Server - GPL) Server version: 5.5.5-10.1.37-MariaDB mariadb.org binary distribution

CREATE DEFINER=`root`@`localhost` PROCEDURE `findPaths`()
BEGIN
WITH RECURSIVE descendants AS
(
SELECT e.activity, CAST(e.activity AS CHAR(500)) AS path, n.cost as total_cost
FROM edges e JOIN nodes n
ON e.activity=n.code
WHERE e.activity='A'
UNION ALL
SELECT e.activity, CONCAT(d.path, ',', e.activity), d.total_cost+(SELECT cost FROM nodes WHERE code=e.activity)
FROM descendants d, edges e
WHERE e.parent=d.activity
)
SELECT * FROM descendants WHERE activity='M' OR activity='N' ORDER BY total_cost;

END

I want the procedure to be created and accessible in order to be run

Solution

  • Recursive CTE where added in MariaDB-10.2

    From your version string you have MariaDB-10.1.37