Roles and Policies : duplicates after migration to eZ Platform

le mardi 15 octobre 2019

When migrating to eZ Platform, eZ Systems provides migration script to adapt database to eZ's new needs.

One of them, vendor/ezsystems/ezpublish-kernel/data/update/mysql/dbupdate-5.4.0-to-6.13.0.sql , aims to insert new Policies content/publish within ezpolicy_limitation_value

If you execute many times those scripts, it's possible that you insert duplicates in Roles and policies tables, and then, drastically increase the number of results when displaying R&P interface.

To verify if you're in that case, execute this SQL request on your project : 


FROM ezpolicy_limitation_value v1
    SELECT v2.limitation_id,
           count(value) as count,
    FROM ezpolicy_limitation_value v2
    GROUP BY limitation_id, value
    HAVING COUNT(value) > 1
    ORDER BY limitation_id, id
) doublon_limitation_id ON doublon_limitation_id.limitation_id = v1.limitation_id AND doublon_limitation_id.value = v1.value
WHERE not in (
    FROM ezpolicy_limitation_value v3
    GROUP BY limitation_id, value
    HAVING COUNT(value) > 1
    ORDER BY limitation_id, id
ORDER BY v1.limitation_id, v1.value,
LIMIT 500; 

If you've got a least one result, duplicates have been inserted and your Roles and policies interface should raise an error while loading. One of our customer got more than 136 duplicates just for one policy (multiplied by number of policies within a role) and this resulted in a huge amount of results when trying to get results for each Roles : ~9M results just for one Role having duplicates within ezpolicy_limitation_value .

To safely remove those duplicates, please check this KaliopMigration script that should save you time ;)

To check how many results you've got for each role, you can execute this request : 



SELECT,, count(
FROM ezrole
LEFT JOIN ezpolicy ON = ezpolicy.role_id
LEFT JOIN ezpolicy_limitation ON = ezpolicy_limitation.policy_id
LEFT JOIN ezpolicy_limitation_value ON ezpolicy_limitation_value.limitation_id =

Feel free to reach us if you need more information or help on your project ;)