topic Re: Query to delete duplicate users from DB end in Alfresco Archive

Query to delete duplicate users from DB end

m_praki — Thu, 21 Jul 2011 05:46:36 GMT

Hi Can anyone please tell the query to delete the duplicate users from the database side. I cannot do it from the application. I have already tried the script (remove-duplicate-users.sh) but that got failed. So we planned to delete the dup users from back end.FYI : We are using Alfresco 3.2.ThanksPr

Re: Query to delete duplicate users from DB end

romschn — Thu, 21 Jul 2011 09:23:51 GMT

Are you getting any error when you try to remove the user through alfresco web client?

However, below is the way through which you can delete the duplicates that can no longer be removed using the UI.

1. Create a javascript in Data Dictionary -> Javascripts with the following.

people.deletePerson("UserNameToDelete");

2. Browse to Company Home > More Actions > View Details.

3. On the details screen, select Run Action > Execute Script, and then select the script that you have created.

Check if this is working for you.

Hope this helps.

Thanks,

Re: Query to delete duplicate users from DB end

m_praki — Tue, 26 Jul 2011 10:21:14 GMT

Hi Romschn,

We didn't get any errors while deleting the user from UI. But the user cannot be deleted from UI.

And for the said three steps, I am afraid of doing that in production environment. Because we don't want to lose any user.

Sorry, for the late reply.

Thanks,
Prakash

Re: Query to delete duplicate users from DB end

m_praki — Thu, 28 Jul 2011 04:10:49 GMT

Hi,

Waiting for your's reply. Is that possible to do. without loosing any user. Or any someother way to do?. :?:

Thanks,
Prakash.

Re: Query to delete duplicate users from DB end

romschn — Thu, 28 Jul 2011 07:42:33 GMT

As per my understanding, Directly performing the operations into alfresco database must be avoided.

I am not aware of any other ways to delete duplicate users apart from the one I mentioned earlier of using javascript.

I am not sure about how we can achieve this using database query as I am not aware about the database design/dependency of tables part.

However, just for a trial - you may want to run below 2 queries and see if it is returning you the records you expect or not. Queries might not be absolutely correct.

select count(*), node_id from alf_node_properties where (node_id in (select id from alf_node where type_qname_id='5') and qname_id=11) group by node_id having count(*) >1 

select count(*), id from alf_node  where id in (select node_id from alf_node_properties where qname_id=11) group by id having count(*) >1
‍‍‍‍

Re: Query to delete duplicate users from DB end

m_praki — Thu, 28 Jul 2011 10:34:24 GMT

Hi Romschn

The script you have suggested works well in Alfresco 3.4 windows environment but not working in Alfresco 3.2 Linux environment. Please help me on this. The query you have given didn't fetch any record from my DB. But thanks for the query.

Regards
Prakash