Skip to main content
Skip table of contents

How to troubleshoot censhare Asset-Deletion 1 via SQL

How To: censhare Asset-Deletion Jobs Troubleshoot (Professional Version)

If none of the top 13 reasons matches, as described here - go in detail with the following guide 

Short description of the mark for deletion process.
This process puts the trash icon next to the asset versions.
In the first step, our mark for deletion process is checking the deletion rules as setup in Asset Version Management.
If these rules find a version to delete, the asset version is set to a temporary deletion state 9.
Then these marked assets will be checked for parent relations to other assets.
If there is no parent relation, the asset will be marked for deletion.
The assets marked for deletion routine runs as default on the server at:
<cron pattern="0 2 * * *"/>

With the following steps, we can check the marking process manually.


1. Is the process running successfully?


This can only be checked in the serverlog by searching for 'completed all'.
grep "asset_deletion.mark-deletion completed all" server-0.*


How it should look:
server-0.3.log:2011.11.21-14:34:12.151 INFO : T008: CommandExecutor: tr.20111121.143412.150[system]: asset_deletion.mark-deletion completed all in 0ms
server-0.65.log:2011.10.27-05:33:55.660 INFO : T034: CommandExecutor: tr.20111027.053354.000[system]: asset_deletion.mark-deletion completed all in 19ms
server-0.66.log:2011.10.26-23:30:56.597 INFO : T037: CommandExecutor: tr.20111026.053526.676[system]: asset_deletion.mark-deletion completed all in 49660ms


The first entry shows a restart of the command during a configuration refresh (with AdminClient).
The second entry shows the start of the command during the server startup.
The third entry is the successful scheduled run. You can identify the scheduled run based on the duration time.


If there is no 'completed all' entry in the logs, you have to look at the other command-entries in the log -> go to 2.1
If there is a 'completed all' entry in the logs, you have to check the command limit -> go to 2.2


2. Check Command in the log
2.1 If there is no 'completed all' in server log. Check if the server is online or another error occurs.
- Check the cron patten of the command and search for the command in the log.
- list all the command entries by searching the command-ID in the log
-> If a server downtime occurs during the command-runtime, check scheduled backup settings with our IT-Service
-> If no downtime is set, an error-message should be found related to the command


2.2 If there is 'Completed all' in the server log. Check if command limit is still valid.
- go to AdminClient/Configurations/Modules/Asset-Deletion/Mark Asset Version for Deletion (automatic) on open command.xml
- here you will find an entry for the limit. Default: limit="4000"
- the command is making 10 loops and therefore the maximum amount of assets that can be marked is 40,000.
First, the Assets will be pre-marked. Many of the pre-marked assets can not be really marked because of existing relations and they
will be skipped in the next command-step.
If 40,000 Asset versions will be pre-marked and 35,000 will be skipped due to a existing parent relation, then only 5,000 will really be marked.
If necessary you will need to increase the limit.



How it looks like in the server log:
First you can find a entry for each loop:
2011.11.23-21:01:30.336 FINE : T038: Deletion.markDeletion: CommandExecutor: tr.20111123.165822.699[system]: -- loop 8: found 4000 assets
Summary: found Assets of the loop



Next are the skipped Asset versions:
2011.11.23-21:01:59.823 INFO : T038: Deletion.markDeletion: CommandExecutor: tr.20111123.165822.699[system]: Skipping 27234 pre-marked assets.
Find it with:
grep Skipping server-0.* | grep pre-marked



Next are the real marked Asset versions:
2011.11.23-21:02:00.779 INFO : T038: Deletion.markDeletion: CommandExecutor: tr.20111123.165822.699[system]: asset_deletion.mark-deletion: Marking 4813 assets for deletion.
Find it with:
grep Marking server-0.*


If the number of assets marked are below the limit than you will have to check the Asset itself. Goto 3.


3. Check why an Asset is not marked for deletion
3.1:
Check whether the asset versions of an asset can be temporarily marked (pre-marked) with 'deletion = 9' based on the rules for the Asset Version Management in AdminClient. This does not check any relations.
SELECT t0.id, t0.version, t0.deletion FROM asset t0 WHERE t0.id= XXX AND ((t0.snapshot_name IS NULL AND t0.state = 0 AND t0.deletion <> 1 AND t0.deletion <> 3 AND t0.deletion <> 4 AND t0.deletion <> 9)
AND EXISTS (SELECT t1.* FROM asset_del_policy t1 WHERE ((t0.domain LIKE t1.domain||'%' AND t0.domain2 LIKE t1.domain2||'%') AND t1.asset_type = t0.type)) AND NOT EXISTS
(SELECT t2.* FROM asset_del_policy t2 WHERE ((t0.domain LIKE t2.domain||'%' AND t0.domain2 LIKE t2.domain2||'%' AND NOT ((t2.del_mode = 0 AND t0.currversion <> 0 AND
t2.keep_first_versions < t0.version AND t2.keep_last_versions < (SELECT MAX(version) - t0.version FROM asset WHERE asset.id = t0.id) AND t0.modified_date + t2.keep_last_days < SYSDATE) OR
(t2.del_mode = 2 AND t0.modified_date + t2.keep_last_days < SYSDATE))) AND t2.asset_type = t0.type))) ORDER BY t0.ROWID;


If no result is returned, the problem can be found in the options of this statement.
Then we have to check the options of the statements separately -> Goto Step 3.2


If a result is returned, skip steps 3.2 and 3.3 and goto Step 3.4


3.2:
Check if snapshot-flag or an existing deletion-state is preventing the marking:
SELECT t0.id, t0.version, t0.deletion, snapshot_name, state FROM asset t0 WHERE id= XXX;


If no snapshot_name or deletion-state is set, we check if there is a fitting deletion rule for the asset.


3.3:
Is there a matching deletion rule for the asset:
This is the statement:
SELECT t1.* FROM asset_del_policy t1 WHERE ((t0.domain LIKE t1.domain||'%' AND t0.domain2 LIKE t1.domain2||'%') AND t1.asset_type = t0.type);


You have to replace domain1, domain2 and asset_type with the values of the affected asset:
SELECT t1.* FROM asset_del_policy t1 WHERE (('root.print.kdz.' LIKE t1.domain||'%' AND 'root.k.66.' LIKE t1.domain2||'%') AND t1.asset_type = 'layout.1.');


If no result is returned, that's the problem. No fitting rule is found. To mark an asset, there must be a rule for the asset.


3.4:
Update the Assets to the temporary marking ()pre-marked) state (deletion = 9).



UPDATE asset SET deletion=9 WHERE
(id, version) IN (
SELECT t0.id, t0.version FROM asset t0 WHERE t0.id=XXX AND ((t0.snapshot_name IS NULL AND t0.state = 0 AND t0.deletion <> 1 AND t0.deletion <> 3 AND t0.deletion <> 4 AND t0.deletion <> 9)
AND EXISTS (SELECT t1.* FROM asset_del_policy t1 WHERE ((t0.domain LIKE t1.domain||'%' AND t0.domain2 LIKE t1.domain2||'%') AND t1.asset_type = t0.type)) AND NOT EXISTS
(SELECT t2.* FROM asset_del_policy t2 WHERE ((t0.domain LIKE t2.domain||'%' AND t0.domain2 LIKE t2.domain2||'%' AND NOT ((t2.del_mode = 0 AND t0.currversion <> 0 AND
t2.keep_first_versions < t0.version AND t2.keep_last_versions < (SELECT MAX(version) - t0.version FROM asset WHERE asset.id = t0.id) AND t0.modified_date + t2.keep_last_days < SYSDATE) OR
(t2.del_mode = 2 AND t0.modified_date + t2.keep_last_days < SYSDATE))) AND t2.asset_type = t0.type))));


!!! This is a temporary marking. Do not commit the statement!


3.5:
Check the parent relations to other assets, based on asset version and content version.
These Statements are out of our Scriptlet Deletion.java (Z. 231 ff).


Only censhare version 4:
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1)
OR
EXISTS (SELECT r.* FROM asset_rel r, asset a
WHERE a.id = r.parent_asset AND a.version = r.parent_version AND a.deletion != 9 AND a.deletion != 4
AND r.key = 'actual.' AND r.child_asset = asset.id AND r.child_version = asset.version)
OR
EXISTS (SELECT r.* FROM asset_rel r
WHERE asset.id = r.parent_asset AND asset.version = r.parent_content_version)
);


If a result is returned, the asset version cannot be marked, because it has a relation to one or more parent assets.
To find out relation goto Step 3.6
If no result is returned, the asset should be marked for deletion and there is an unknown problem.


Only censhare version 3:
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1)
OR
EXISTS (SELECT r.* FROM asset_rel r, asset a
WHERE a.id = r.parent_asset AND a.version = r.parent_version AND a.deletion != 9 AND a.deletion != 4
AND r.type = 1 AND r.child_asset = asset.id AND r.child_version = asset.version)
OR
EXISTS (SELECT r.* FROM asset_rel r
WHERE asset.id = r.parent_asset AND asset.version = r.parent_content_version)
);


If a result is returned, the asset version cannot be marked, because it has a relation to one or more parent assets.
To find out the relation goto Step 3.6
If no result is returned, the asset should be marked for deletion and there is an unknown problem


3.6:
Check the three EXISTS conditions to find out which relation type is preventing the marking.


Only censhare version 4:
a. Asset content-version is used in conjunction with a parent asset relation.
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1));


If a result is returned, the asset cannot be marked because of that condition.


b. Assetversion has a relation to a parent
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT r.* FROM asset_rel r, asset a
WHERE a.id = r.parent_asset AND a.version = r.parent_version AND a.deletion != 9 AND a.deletion != 4
AND r.key = 'actual.' AND r.child_asset = asset.id AND r.child_version = asset.version));


If a result is returned, the asset cannot be marked because of that condition.


c. Assetversion has a relation to a parent based on parent_content_version
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT r.* FROM asset_rel r
WHERE asset.id = r.parent_asset AND asset.version = r.parent_content_version));


If a result is returned, the asset cannot be marked because of that condition.


Only censhare version 3:

a. Asset content-version is used in conjunction with a parent asset
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT 1 FROM asset a WHERE a.id = asset.id AND a.content_version = asset.version
AND a.version != asset.version AND a.deletion != 1));


If a result is returned, the asset cannot be marked because of that condition.


b. Asset version has a relation to a parent


SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT r.* FROM asset_rel r, asset a
WHERE a.id = r.parent_asset AND a.version = r.parent_version AND a.deletion != 9 AND a.deletion != 4
AND r.type = 1 AND r.child_asset = asset.id AND r.child_version = asset.version));


If a result is returned, the asset cannot be marked because of that condition.


c. Asset version has a relation to a parent based on parent_content_version
SELECT id, version, deletion FROM asset WHERE id = XXX AND deletion = 9 AND (
EXISTS (SELECT r.* FROM asset_rel r
WHERE asset.id = r.parent_asset AND asset.version = r.parent_content_version));


If a result is returned, the asset cannot be marked because of that condition.


4. Final Step:
Undo all changes:
rollback;


If you were disconnected from oracle because of a timeout:
select count(*) from asset where deletion = 9;
update asset set deletion = 0 where deletion = 9;
commit;

_________________________________________________________________________________________________________________

Short description of the do-deletion process.

This process checks for all asset versions which are marked for deletion (trash icon) if it's possible to delete them.
In the first step, our do-deletion process is checking the deletion rules as setup in ~/css/work/runtime.*/modules/asset_deletion.xml
The do-deletion routine runs by default on the server at:
<cron pattern="30 3 * * *"/>

With the following steps, we can check the do-deletion process manually.

1. Is the process running successfully?

This can only be checked in the serverlog by searching for 'completed all'.
grep "asset_deletion.do-deletion completed all" server-0.*

How it should look:
server-0.3.log:2011.11.21-14:34:12.151 INFO : T008: CommandExecutor: tr.20111121.143412.150[system]: asset_deletion.do-deletion completed all in 0ms
server-0.65.log:2011.10.27-05:33:55.660 INFO : T034: CommandExecutor: tr.20111027.053354.000[system]: asset_deletion.do-deletion completed all in 19ms
server-0.25.log:2011.12.14-03:32:18.642 INFO : T025: CommandExecutor: tr.20111209.015401.130[system]: asset_deletion.do-deletion completed all in 134535ms

The first entry shows a restart of the command during a configuration refresh (with AdminClient).
The second entry shows the start of the command during the server startup.
The third entry is the successful scheduled run. You can identify the scheduled run based on the duration time.

Since Version 4.2.x the log output has changed and the grep must be adapted to the following:
grep "asset_deletion.do-deletion:" server-0.*

How it should look:
server-0.0.log:2011.12.14-08:30:10.872 INFO : T007: Deletion.preparePhysDelete: CommandExecutor: cs-srv12.20111205.180243.888[system]: asset_deletion.do-deletion: Removed 5 assets.

If there is no 'completed all' entry in the logs, you have to look at the other command-entries in the log -> go to 2.1
If there is a 'completed all' entry in the logs, you have to check the command limit -> go to 2.2

2. Check Command in the log
2.1 If there is no 'completed all' in server log. Check if the server is online or another error occurs.
- Check the cron pattern of the command and search for the command in the log.
- list all the command entries by searching the command-ID in the log
-> If a server downtime occurs during the command-runtime, check scheduled backup settings with our IT-Service
-> If no downtime is set, a error-message should be found related to the command

2.2 If there is 'Completed all' in the server log. Check if command limit is still valid.
- go to AdminClient/Configurations/Modules/Asset-Deletion/Delete Assets (automatic) on open command.xml
- here you will find an entry for the limit. Default: limit="1000"
- the command is making 10 loops and therefore the maximum amount of assets that can be deleted is 10,000.
If necessary you will need to increase the limit.

How it looks like in the server log:
2011.12.14-08:30:10.872 INFO : T007: Deletion.preparePhysDelete: CommandExecutor: cs-srv12.20111205.180243.888[system]: asset_deletion.do-deletion: Removed 5 assets.


If the assets are below the limit than you will have to check the Asset itself. Goto 3.

3. Check why an Asset is not deleted
3.1:
- Check if it's marked for deletion
- Check that only assets which are not in (re-)archiving process can be deleted
- Check that only assets which have no asset versions linked where a parent_content_version points to an assets version not for deletion can be deleted
- Check that only assets which have a valid asset_del_policy entry within the Asset Version Management (censhareAdmin) can be deleted
- Check that only assets which are older than keep_marked_sec within the Asset Version Management (censhareAdmin) can be deleted
- Check that only assets which have no asset versions linked where a content_version points to an asset version not for deletion can be deleted
- Check that the limit in do-deletion.xml is set higher than the releases of the asset with the most versions. If an asset has 1000 and more versions, it can not be deleted with a limit of 1000.
To find Assets with more than 1000 Versions, use this SQL:
select id, count(*) from asset group by id having count(*) > 1000 order by count(*);

- Check that the timeout of Deletion.java is not reached.
There is a timeout in Deletion.java for the deletion of one Asset in the database. This timeout is set to 120 seconds in our default.
This timeout can be reached during deletion of a asset with many versions.
In this case, you can find a cancel message after 120 seconds in the server log:
...
2012.06.08-12:45:37.016 FINE : T028: Deletion.preparePhysDelete: Service: censhare.20120608.122537.009[system]: ServiceCall cancel assetmanager.executeCommand
2012.06.08-12:45:37.016 FINE : T028: Deletion.preparePhysDelete: Service: censhare.20120608.122537.009[system]: execution has been canceled: assetmanager.executeCommand
2012.06.08-12:45:37.016 INFO : T028: Deletion.preparePhysDelete: CommandExecutor: censhare.20120608.122537.009[system]: canceled manager call: assetmanager.executeCommand
...

To increase the timeout, go into Deletion.java and find this line:
final static long TIMEOUT = 120 * 1000;

To delete a asset with 1000 Versions will take around one hour::
final static long TIMEOUT = 3600 * 1000;

_________________________________________________________________________________________________________________

Nice to Know

Check Storagedeletion Queue:

CODE
select count(*) from storage_deletion_queue;

  COUNT(*)
----------
         9

All Assets with deletion flag

CODE
Select count(*) from asset where deletion='1';

  COUNT(*)
----------
      4288

All currversions with deletion flag

CODE
Select count(*) from asset where currversion='0';

  COUNT(*)
----------
    736717

How to check with the relpath if the asset is already in the storagedeletion queue

CODE
select count(*) from storage_deletion_queue where relpath = 'file:30/87/308736.jpg';

How can I delete the storage_deletion_queue

CODE
delete from storage_deletion_queue;

Show assets with more than 10000 versions

CODE
SQL> SELECT id, count(*) FROM asset GROUP BY id HAVING count(*) > 10000 ORDER BY count(*);

ID COUNT(*)
---------- ----------
28479640 20986
28418810 21042
24016210 21050


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.