deletePermissions throws ConstraintViolationException

[dgildeh]

On Alfresco Community v3.2.0 (r2 2440) schema 3,300 I have written a patch that does the essentially executes the following code to loop through a set of nodes, remove their current permissions and apply new ones:

for (NodeRef node : nodes) {
            
            // Delete all the permissions on the node - line 97
            permissionService.deletePermissions(node);

            // Clear the accounts inherited permissions
            permissionService.setInheritParentPermissions(node, false);
            // Assign the new permissions to the node
            ….
} 
‍‍‍‍‍‍‍‍‍‍‍‍

However whenever it hits the deletePermissions line it throws this error:

01:42:34,622 ERROR [org.alfresco.repo.admin.patch.PatchExecuter] 10030001 org.hibernate.exception.ConstraintViolationException: could not delete: [org.alfresco.repo.domain.hibernate.DbAccessControlListImpl#39]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2546)
   at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2702)
   at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:77)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:322)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:306)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:187)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.alfresco.repo.domain.hibernate.DirtySessionMethodInterceptor.flushSession(DirtySessionMethodInterceptor.java:342)
   at org.alfresco.repo.domain.hibernate.AclDaoComponentImpl.deleteAccessControlList(AclDaoComponentImpl.java:1233)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   at org.alfresco.repo.domain.hibernate.DirtySessionMethodInterceptor.invoke(DirtySessionMethodInterceptor.java:419)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy10.deleteAccessControlList(Unknown Source)
   at org.alfresco.repo.domain.hibernate.DMPermissionsDaoComponentImpl.deletePermissions(DMPermissionsDaoComponentImpl.java:132)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.alfresco.repo.service.StoreRedirectorProxyFactory$RedirectorInvocationHandler.invoke(StoreRedirectorProxyFactory.java:221)
   at $Proxy15.deletePermissions(Unknown Source)
   at org.alfresco.repo.security.permissions.impl.PermissionServiceImpl.deletePermissions(PermissionServiceImpl.java:855)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy25.deletePermissions(Unknown Source)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   at net.sf.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:80)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:49)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:148)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy29.deletePermissions(Unknown Source)
   at my.patches.ChangeSecurityPatch.applyInternal(ChangeSecurityPatch.java:98)
   at org.alfresco.repo.admin.patch.AbstractPatch$1$1.execute(AbstractPatch.java:409)
   at org.alfresco.repo.admin.patch.AbstractPatch$1$1.execute(AbstractPatch.java:403)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:327)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:234)
   at org.alfresco.repo.admin.patch.AbstractPatch$1.doWork(AbstractPatch.java:435)
   at org.alfresco.repo.admin.patch.AbstractPatch$1.doWork(AbstractPatch.java:399)
   at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:514)
   at org.alfresco.repo.admin.patch.AbstractPatch.apply(AbstractPatch.java:439)
   at org.alfresco.repo.admin.patch.PatchServiceImpl.applyPatch(PatchServiceImpl.java:302)
   at org.alfresco.repo.admin.patch.PatchServiceImpl.access$200(PatchServiceImpl.java:56)
   at org.alfresco.repo.admin.patch.PatchServiceImpl$2.execute(PatchServiceImpl.java:231)
   at org.alfresco.repo.admin.patch.PatchServiceImpl$2.execute(PatchServiceImpl.java:229)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:327)
   at org.alfresco.repo.admin.patch.PatchServiceImpl.applyPatchAndDependencies(PatchServiceImpl.java:234)
   at org.alfresco.repo.admin.patch.PatchServiceImpl.applyOutstandingPatches(PatchServiceImpl.java:168)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy77.applyOutstandingPatches(Unknown Source)
   at org.alfresco.repo.admin.patch.PatchExecuter.applyOutstandingPatches(PatchExecuter.java:89)
   at org.alfresco.repo.admin.patch.PatchExecuter.onBootstrap(PatchExecuter.java:132)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
   at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaster.java:77)
   at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:75)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:246)
   at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:617)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:355)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:189)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:69)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3843)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4342)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:525)
   at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:627)
   at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:553)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:488)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
   at org.apache.catalina.core.StandardService.start(StandardService.java:516)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:578)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`alfresco/alf_node`, CONSTRAINT `fk_alf_node_acl` FOREIGN KEY (`acl_id`) REFERENCES `alf_access_control_list` (`id`))
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
   at com.mysql.jdbc.Util.getInstance(Util.java:381)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2046)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1964)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1949)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
   at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2528)
   … 121 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I've found several similar issues on this forum with no solutions, this one is extremely close to what I'm getting - looping through a list of nodes to set permissions is throwing the same error: http://forums.alfresco.com/fr/viewtopic.php?f=11&t=4401

This is not causing a problem on my local machines when I run the patch, only as soon as I deploy it on my test server (which is a replica of live at the moment). Also manually removing permissions one by one using Alfresco Explorer is working fine.

I know many of the similar issues have not received any solution but it would be great if one could be found for everyone on this one!

Thanks in advance.

[gyro_gearless]

I suppose deletePermissions() is broken in 3.2, see for example http://issues.alfresco.com/jira/browse/ALF-4837

For one of my webscripts, i use this as workaround:

                    //
                    // The obvious way - it just doesn't work (see http://issues.alfresco.com/jira/browse/ALF-4837)
                    //
                    // this.permissionService.deletePermissions(targetPathRef);
                    
                    Set<AccessPermission> setPermissions = this.permissionService.getAllSetPermissions(targetPathRef);
                    for (AccessPermission p : setPermissions)
                    {
                        if (p.isSetDirectly())
                        {
                            String authority = p.getAuthority();
                            String permission = p.getPermission();
                            
                            if (smLogger.isDebugEnabled())
                            {
                                smLogger.debug("- " + authority + ":" + permission);
                            }
                            this.permissionService.deletePermission(targetPathRef, authority, permission);
                        }
                    }
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[dgildeh]

Thanks Andreas - I tried a similar solution likes yours and worked around the issue. I noticed the issue you pointed to is still not resolved in 3.4 so hope they put a fix for this soon, was worried my database was corrupted!