topic Node without parents does not have root aspect in Alfresco Archive

Node without parents does not have root aspect

nimbus58 — Tue, 30 Nov 2010 13:46:54 GMT

Hello, I have a 33g system running fine on Ubuntu 10.04 64 and a Mysql on another machine.I have just tried to do a replica of the system on another server as part of a crash recovery test ( DB and data-store)and I end up with a reindexing going nuts.I tried the same with a 34b system and no luck.I

Re: Node without parents does not have root aspect

morfeusx — Thu, 16 Dec 2010 12:01:02 GMT

I don't know for now what's causing it, but these nodes can be removed.

This select will help you find these nodes:

select an.* from alf_node an where an.NODE_DELETED = '0' and an.id NOT IN(SELECT root_node_id from alf_store) and an.id NOT IN(SELECT child_node_id from alf_child_assoc);‍

This is just checking select if these nodes doesn't have any children:

select * from alf_child_assoc asoc where asoc.parent_node_id IN(select an.id from alf_node an where an.NODE_DELETED = '0' and an.id NOT IN(SELECT root_node_id from alf_store) and an.id NOT IN(SELECT child_node_id from alf_child_assoc));‍

If they have some(never happen to me), you should delete this childrens and associations with parents. Delete should be made same way as lower, setting node_deleted param to 1.

And finally remove of these "lost nodes":

update alf_node an set an.NODE_DELETED = '1' where an.ID IN (select an.id from alf_node an where an.NODE_DELETED = '0' and an.id NOT IN(SELECT root_node_id from alf_store) and an.id NOT IN(SELECT child_node_id from alf_child_assoc));‍

If you ever find out, how they are created, let me know.
I would like to answer to my boss, what cause corruption of our production repository. For now I can just answer…it's mystery sir, but don't worry, I have a magic sql update…

Re: Node without parents does not have root aspect

nimbus58 — Tue, 21 Dec 2010 09:36:59 GMT

Thanks morfeusx,

The nodes are gone but the problem is still there in another way.

The indexer is going crazy,

10:30:43,396  INFO  [node.index.FullIndexRecoveryComponent]    7,710 % complete.
10:30:47,369  INFO  [node.index.FullIndexRecoveryComponent]    7,720 % complete.
10:30:49,860  INFO  [node.index.FullIndexRecoveryComponent]    7,730 % complete.
10:30:54,154  INFO  [node.index.FullIndexRecoveryComponent]    7,740 % complete.
10:30:56,602  INFO  [node.index.FullIndexRecoveryComponent]    7,750 % complete.
10:30:59,053  INFO  [node.index.FullIndexRecoveryComponent]    7,760 % complete.
10:31:03,810  INFO  [node.index.FullIndexRecoveryComponent]    7,770 % complete.
10:31:06,167  INFO  [node.index.FullIndexRecoveryComponent]    7,780 % complete.
10:31:09,688  INFO  [node.index.FullIndexRecoveryComponent]    7,790 % complete.
10:31:11,948  INFO  [node.index.FullIndexRecoveryComponent]    7,800 % complete.
10:31:15,694  INFO  [node.index.FullIndexRecoveryComponent]    7,810 % complete.
10:31:18,250  INFO  [node.index.FullIndexRecoveryComponent]    7,820 % complete.
10:31:20,707  INFO  [node.index.FullIndexRecoveryComponent]    7,830 % complete.
10:31:24,631  INFO  [node.index.FullIndexRecoveryComponent]    7,840 % complete.
10:31:28,213  INFO  [node.index.FullIndexRecoveryComponent]    7,850 % complete.
10:31:32,360  INFO  [node.index.FullIndexRecoveryComponent]    7,860 % complete.
10:31:34,847  INFO  [node.index.FullIndexRecoveryComponent]    7,870 % complete.
10:31:38,607  INFO  [node.index.FullIndexRecoveryComponent]    7,880 % complete.
10:31:41,084  INFO  [node.index.FullIndexRecoveryComponent]    7,890 % complete.
10:31:44,054  INFO  [node.index.FullIndexRecoveryComponent]    7,900 % complete.‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The last run gave me over 1.000.000 % complete (after 4 days) then the JVM died.

I am trying again with a cleaned database (bad nodes removed) and a blank index and indexer to AUTO.

I am completely out of idea.

Re: Node without parents does not have root aspect

nimbus58 — Thu, 06 Jan 2011 10:01:53 GMT

SOLVED (Maybe)

I found that I had a bunch of entries in alf_node where the acl_id where NULL.
Via the node browser from our live system, it seemed that all where linked to "doclib" files peppered all over the system. These files had owners but no rights (I assume).
I deleted all the entries with NULL acl_id and restarted the system.
Reindexing went fine and the whole backup system is up and running.

Don't know what created these "doclib" files but they are everywhere in our live system.

The backup system is now running with 3.4c.

Re: Node without parents does not have root aspect

boutch55555 — Mon, 14 Feb 2011 14:55:38 GMT

The doclib files are the documents thumbnails.

Re: Node without parents does not have root aspect

marutiprasad — Tue, 26 Jul 2011 07:54:59 GMT

Hi nimbus58,

How you deleted those NULL ACL ID nodes, is it through the sql query.could you please post the procedure.

Thanks
Maruti.

Re: Node without parents does not have root aspect

imagem — Fri, 27 Jan 2012 16:48:16 GMT

We have upgraded from version 3.4 to version 4.0b then to 4.0c. We have the "node without parent".

When we execute the following sql statement :

select an.* from alf_node an where an.NODE_DELETED = '0' and an.id NOT IN(SELECT root_node_id from alf_store) and an.id NOT IN(SELECT child_node_id from alf_child_assoc);

we are not getting any result so we can't use it to delete the nodes. Anyone got an idea why ?

Re: Node without parents does not have root aspect

tgmweb — Sun, 29 Jan 2012 16:54:03 GMT

@imagem

Try:

select an.* from alf_node an where an.NODE_DELETED = '' and an.id NOT IN(SELECT root_node_id from alf_store) and an.id NOT IN(SELECT child_node_id from alf_child_assoc);‍

Re: Node without parents does not have root aspect

x1m — Fri, 27 Jul 2012 19:49:32 GMT

I get an error when trying to remove the nodes found.

update alf_node an set an.NODE_DELETED = '1' where an.id IN(select an.id from alf_node an where an.NODE_DELETED = '0' and an.id NOT IN(SELECT root_node_id from alf_store) and an.id NOT IN(SELECT child_node_id from alf_child_assoc));‍

Give me the following error:

ERROR:  column "an" of relation "alf_node" does not exist
LINE 1: update alf_node an set an.NODE_DELETED = '1' where an.ID IN …
                               ^‍‍‍

I am using Alfresco 4.0.c with postgresql. Anyone had the same error and can help?

Re: Node without parents does not have root aspect

xarope — Wed, 21 Nov 2012 06:45:45 GMT

as with the above entries, 4.0 uses boolean datatype for node_deleted, which is true/false, rather than 0/1, so your query should look like this:

update alf_node an 
set an.NODE_DELETED = true 
where an.ID IN 
(select an.id from alf_node an where an.NODE_DELETED = false 
 and an.id NOT IN(SELECT root_node_id from alf_store)
 and an.id NOT IN(SELECT child_node_id from alf_child_assoc));
‍‍‍‍‍‍‍

Re: Node without parents does not have root aspect

fiferyan — Wed, 25 Sep 2013 16:04:23 GMT

Thank you for the SQL! Bumping because this solved my problem. Thank you.

On 3.4.8 and had to modify the queries a little bit because the node_deleted type is a tinyint and not a string.

I updated the queries a little bit and others may find this useful so I'm sharing. I hope it works for you. Please do backup before trying these though!


– Find children of nodes with no parents
SELECT *
  FROM alf_child_assoc
 WHERE parent_node_id IN(
        SELECT id FROM alf_node
        WHERE node_deleted=0
        AND id NOT IN(SELECT root_node_id FROM alf_store)
        AND id NOT IN(SELECT child_node_id FROM alf_child_assoc)
);


– if you find any, update them to 'deleted'
UPDATE alf_node
   SET node_deleted=1
 WHERE id IN (
        SELECT parent_node_id
          FROM alf_child_assoc
         WHERE parent_node_id IN(
                SELECT id
                  FROM alf_node
                 WHERE node_deleted=0
                   AND id NOT IN(SELECT root_node_id FROM alf_store)
                   AND id NOT IN(SELECT child_node_id FROM alf_child_assoc)
         )
);


– find nodes with no parent
SELECT *
  FROM alf_node
 WHERE node_deleted=0
   AND id NOT IN(SELECT root_node_id FROM alf_store)
   AND id NOT IN(SELECT child_node_id FROM alf_child_assoc);


– Update the nodes with no parent to deleted
UPDATE alf_node
  SET NODE_DELETED=1
WHERE NODE_DELETED=0
  AND id NOT IN(SELECT root_node_id FROM alf_store)
  AND id NOT IN(SELECT child_node_id FROM alf_child_assoc);
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I found many nodes with null acl_id columns but I did not touch those and after a re-index everything worked well so I'm ignoring them for now.