In our Nuxeo, we experimented a severe bug on the NX_ACCESS_ALLOWED function. Looking closer, we saw that one of our document :

• has no parentid + has a versionableid => in the while loop first iteration, newid and curid are set to versionableid
• on the next iteration, no line is found corresponding to this versionableid : SELECT parentid INTO newid FROM hierarchy WHERE hierarchy.id = curid; returns and does nothing. newid and curid remains the same, resulting in a infinite loop.

May be our data are corrupted, but I cannot see any foreign key constraint on the versionableid columns in the database ?

We applied the following patch (SET newid = NULL) to avoid the infinite loop:

WHILE curid IS NOT NULL DO
  BEGIN
    ...   
  END;
  -- OUR PATCH HERE
  SET newid = NULL;
  SELECT parentid INTO newid FROM hierarchy WHERE hierarchy.id = curid;
  IF first AND newid IS NULL THEN
    SELECT versionableid INTO newid FROM versions WHERE versions.id = curid;
  END IF;
  SET first = FALSE;
  SET curid = newid;
END WHILE;
RETURN FALSE; 

It seems that everytime we restart our nuxeo, our patch is overriden by the application. Can we do something about it?

Moreover, I think that you may gain in performance by avoiding the cursor in the fisrt part of the function. Something like this should perform the same task:

SELECT `grant` INTO gr FROM `acls`
  WHERE `acls`.`id` = curid 
  AND LOCATE(CONCAT('|',user,'|'), allusers) <> 0 
  AND LOCATE(CONCAT('|',permission,'|'), allperms) <> 0
  ORDER BY `pos`
  LIMIT 1; 
IF gr IS NOT NULL THEN
    RETURN gr;
END IF;

Thanks for your help, Best regards,

Xavier Pallot