Symfony ACL reorder ACE

fix_ace.sql

DELIMITER ;;
DROP PROCEDURE IF EXISTS fix_ace_order;
CREATE PROCEDURE fix_ace_order()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE acl_id INT;
  DECLARE acl_cursor CURSOR FOR SELECT o.id
                                FROM acl_object_identities o
                                       LEFT JOIN acl_entries e ON (
                                    e.class_id = o.class_id AND
                                    e.object_identity_id = o.id)
                                group by concat(o.id, ';', e.ace_order)
                                having count(concat(o.id, ';', e.ace_order)) > 1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN acl_cursor;

  read_loop:
    LOOP
      FETCH acl_cursor INTO acl_id;
      IF done THEN
        LEAVE read_loop;
      END IF;
      SET @n := -1;
      UPDATE acl_entries e
      SET ace_order = (@n := @n + 1)
      WHERE object_identity_id = acl_id;
    END LOOP;

  CLOSE acl_cursor;
END;
;;

CALL fix_ace_order()
;;

This small SQL procedure allows to reorder broken ordering of ACE records in Symfony ACL database