Deleting broken resources via SQL
From OpenCms Wiki
(Difference between revisions)
ZoranWornack (Talk | contribs) |
(→Oracle 9g) |
||
| (One intermediate revision by one user not shown) | |||
| Line 1: | Line 1: | ||
Under some circumstances it may happen that resources are corrupted in the OpenCms VFS. This e.g. happen if the database encounters an error in the middle of an "OpenCms" - atomic operation. Symptoms for this may be: | Under some circumstances it may happen that resources are corrupted in the OpenCms VFS. This e.g. happen if the database encounters an error in the middle of an "OpenCms" - atomic operation. Symptoms for this may be: | ||
* The context menu is not available. | * The context menu is not available. | ||
| − | |||
* A deleted folder may not be published as it contains files. Restoring these files fails e.g. because "they are not locked by you". | * A deleted folder may not be published as it contains files. Restoring these files fails e.g. because "they are not locked by you". | ||
* The file may not be edited (the CMS_CONTENTS entry is missing). | * The file may not be edited (the CMS_CONTENTS entry is missing). | ||
| Line 27: | Line 26: | ||
=OpenCms 7.0.5= | =OpenCms 7.0.5= | ||
| − | == Oracle | + | == Oracle 9i == |
This script tries to delete any trace of a bad resource by it's resource id read from the table CMS_OFFLINE_STRUCTURE. If the bad resource resides in the online project you might tweak the script. | This script tries to delete any trace of a bad resource by it's resource id read from the table CMS_OFFLINE_STRUCTURE. If the bad resource resides in the online project you might tweak the script. | ||
This script will not delete folders with children. You have to enter the path to the resource. Execute it with sqlplus (SQL>@file) or Toad for Oracle. By default it only simulates (rollback). | This script will not delete folders with children. You have to enter the path to the resource. Execute it with sqlplus (SQL>@file) or Toad for Oracle. By default it only simulates (rollback). | ||
Latest revision as of 11:26, 18 November 2010
Under some circumstances it may happen that resources are corrupted in the OpenCms VFS. This e.g. happen if the database encounters an error in the middle of an "OpenCms" - atomic operation. Symptoms for this may be:
- The context menu is not available.
- A deleted folder may not be published as it contains files. Restoring these files fails e.g. because "they are not locked by you".
- The file may not be edited (the CMS_CONTENTS entry is missing).
- The resource entry of the file is missing but the structure entry is there (thus the file is shown in the explorer.
- The file exists Online but cannot be found Offline.
On this page you may find/post SQL scripts to delete such corrupt files.
There is a high risk of destroying your whole OpenCMS VFS (database) when using direct SQL to modify it. This is not recommended. These scripts are distributed in the hope that they will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Never use these scripts untested. Always test them on a non-productive server. If they work as desired you might want to use them on a production system. For this it is recommended to have a rollback plan: * Do it at a time when a downtime of your server does hurt the fewest. * Dump your OpenCms database. * Lock out users.
A safer option to deal with a corrupt VFS is to set up a fresh instance, export all content from the old system via OpenCms Database Management and import this on the new system.
OpenCms 7.0.5
Oracle 9i
This script tries to delete any trace of a bad resource by it's resource id read from the table CMS_OFFLINE_STRUCTURE. If the bad resource resides in the online project you might tweak the script. This script will not delete folders with children. You have to enter the path to the resource. Execute it with sqlplus (SQL>@file) or Toad for Oracle. By default it only simulates (rollback). In case of questions you could try to contact achim<dot>westermann<at>gmx<dot>de
SET serveroutput ON declare -- CUSTOMIZE START path OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_PATH%type := '/sites/default/testfile.html'; simulate VARCHAR(16) := 'true'; -- CUSTOMIZE END rid OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID%type; sid OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID%type; childcount number; siblingcount number; TYPE ProjectList IS VARRAY(2) OF VARCHAR(16); projects ProjectList := ProjectList('ONLINE','OFFLINE'); project VARCHAR(16); begin FOR i IN projects.FIRST .. projects.LAST LOOP project := projects(i); DBMS_OUTPUT.NEW_LINE; dbms_output.put_line('Project: '||project); -- get the resource id of the resource to delete: SELECT RESOURCE_ID INTO rid FROM OPENCMS.CMS_OFFLINE_STRUCTURE WHERE RESOURCE_PATH LIKE path; SELECT STRUCTURE_ID INTO sid FROM OPENCMS.CMS_OFFLINE_STRUCTURE WHERE RESOURCE_PATH LIKE path; dbms_output.put_line('Resource ID to delete: '||rid); dbms_output.put_line('Structure ID to delete: '||sid); -- check if we deal with a folder: SELECT count(*) INTO childcount FROM OPENCMS.CMS_OFFLINE_STRUCTURE WHERE PARENT_ID = sid; IF childcount > 0 then dbms_output.put_line(path||' has '|| childcount || ' direct children. Delete those first.'); else dbms_output.put_line('No children found, ok to continue'); -- check siblings EXECUTE IMMEDIATE 'SELECT count(*) from OPENCMS.CMS_'||project||'_STRUCTURE WHERE RESOURCE_ID ='''||rid||'''' INTO siblingcount; dbms_output.put_line('Detected '||siblingcount||' siblings.'); -- delete contents: special there is no table CMS_ONLINE_CONTENTS IF project = 'OFFLINE' then EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_OFFLINE_CONTENTS WHERE RESOURCE_ID = '''||rid||''''; else EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_CONTENTS WHERE RESOURCE_ID = '''||rid||''''; end IF; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_'||project||'_CONTENTS.'); -- delete access control EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_ACCESSCONTROL WHERE RESOURCE_ID ='''||rid||''''; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_'||project||'_ACCESSCONTROL.'); -- delete individual properties: EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=1 AND PROPERTY_MAPPING_ID = '''||sid||''''; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' individual properties from CMS_'||project||'_PROPERTIES.'); -- delete shared properties (if no siblings left) IF siblingcount = 1 then EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=2 AND PROPERTY_MAPPING_ID = '''||rid||''''; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' shared properties from CMS_'||project||'_PROPERTIES as no siblings are left.'); else dbms_output.put_line('Deleted no shared properties from CMS_'||project||'_PROPERTIES as '||siblingcount||' siblings are left.'); end IF; -- delete resource relations EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_RESOURCE_RELATIONS WHERE RELATION_SOURCE_ID='''||sid||''''; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' relation sources from CMS_'||project||'_RESOURCE_RELATIONS.'); EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_RESOURCE_RELATIONS WHERE RELATION_TARGET_ID='''||sid||''''; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' relation targets from CMS_'||project||'_RESOURCE_RELATIONS.'); -- Finally delete structure entry (the thing that makes it show in the explorer) EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_STRUCTURE WHERE STRUCTURE_ID='''||sid||''''; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' structure entry from CMS_'||project||'_STRUCTURE.'); end IF; END LOOP; DBMS_OUTPUT.NEW_LINE; dbms_output.put_line('Clearing History'); -- Tables that are not offline/online: -- delete history structure DELETE FROM OPENCMS.CMS_HISTORY_STRUCTURE WHERE STRUCTURE_ID=sid; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_HISTORY_STRUCTURE.'); -- delete history resource DELETE FROM OPENCMS.CMS_HISTORY_RESOURCES WHERE RESOURCE_ID=rid; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_HISTORY_RESOURCE.'); -- delete history properties individual DELETE FROM OPENCMS.CMS_HISTORY_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=1 AND PROPERTY_MAPPING_ID = sid; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' individual properties from CMS_HISTORY_PROPERTIES.'); -- delete history properties shared IF siblingcount = 1 then DELETE FROM OPENCMS.CMS_HISTORY_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=2 AND PROPERTY_MAPPING_ID = rid; dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' shared properties from CMS_HISTORY_PROPERTIES as no siblings are left.'); else dbms_output.put_line('Deleted no shared properties from CMS_HISTORY_PROPERTIES as '||siblingcount||' siblings are left.'); end IF; IF simulate='true' then rollback; dbms_output.put_line('Simulation: rollback'); else commit; dbms_output.put_line('No Simulation: commit'); dbms_output.put_line('Clear the CORE CACHE (below Cache Administration) in OPENCMS to see the effect.'); end IF; exception when NO_DATA_FOUND then dbms_output.put_line('Cannot find the resource to delete: '|| path); end;
