eXo Platform 3.5

## Chapter 40. Checking repository integrity and consistency

Production and any other system may have faults some day. They may be caused by hardware problems, human faults, software errors during updates and many other circumstances. It is important to check integrity and consistency of the system if there is no backup or it is stale, or recovery process takes much. Exo JCR implementation offers an innovative JMX-based complex checking tool. Running inspection, tool check every major JCR component, such as persistent data layer and index. Persistent layer includes JDBC Data Container and Value-Storages if they are configured. Database verified using the set of complex specialized domain-specific queries. Value Storage tool checks existence and access to each file. Index verification contains two-way pass cycle, existence of each node in index checks on persistent layer along with opposite direction, when each node from Data Container validated in index. Access to check tool is exposed via JMX interface, with the following operation available:

checkAll() | Inspect full repository data (db, value storage and search index)
checkDataBase() | Inspect only DB
checkValueStorage() | Inspect only ValueStorage
checkIndex() | Inspect only SearchIndex

Also tools provides abilities to repair possible inconsistencies:

- Item has no parent node - properties will be removed and root UUID will be assigned in case of nodes

- Node has a single valued property with nothing declared in the VALUE table - property will be removed if it is not required by primary type of its node

- Node has no primary type property - node and whole subtree will be removed if node is not required by primary type of its parent

- Value record has no related property record - value record will be removed from database

- Item is its own parent - properties will be removed and root UUID will be assigned in case of nodes

- Several versions of same item - all earler records with earler versions will be removed from ITEM table

- Reference properties without reference records - property will be removed if it is not required by primary type of its node

- Node is marked as locked in the lockmanager's table but is not in the ITEM table or the opposite - all lock inconsistencies will be removed from both tables

- Property's value is stored in the File System but content is missing - new empty file corresponding to value will be created

repairDataBase() | repair DB
repairValueStorage() | repair ValueStorage

All tool activities are stored in file, which can be found in app directory by name `report-<repository name>-dd-MMM-yy-HH-mm.txt`.

## Recommendations on how to fix corrupted JCR

Please, before applying the next recommendations, don't forget to backup your data first. To get the JCR back to normal mode you may require to remove potentially valuable data. It is also recommended to keep the results of queries that check data consistency. This may be needed for the support team in case of deeper restoration process.

Here are listed the examples of corruptions and ways to eliminate them:

### Note

It is assumed that queries for single and multi db configurations differs only by JCR_xITEM table name, otherwise queries will be explicitly introduced.

### Note

In some examples you'll be asked to replace some identificator with corresponding value. That basically means that you need to insert values, from each row result of query executed during issue detection stage, to corresponding place. Explicit explanation of what to do will be introduced in case replacing is needed to be fulfilled in other way.

1. Item has no parent node

   - To detect an issue you need to execute the following query:

         select * from JCR_SITEM I where NOT EXISTS(select * from JCR_SITEM P where P.ID = I.PARENT_ID)

   - Fix description:

     Assign root as parent node to be able to delete later if node is not needed anymore

   - To fix do the following:

     - For all query results rows containing items belonging to I_CLASS = 1 (nodes)

       execute next query replacing ${ID} and ${CONTAINER_NAME} by corresponding values:

       - Single DB

             update JCR_SITEM set PARENT_ID='${CONTAINER_NAME}00exo0jcr0root0uuid0000000000000' where ID = '${ID}'

       - Multi DB

             update JCR_MITEM set PARENT_ID='00exo0jcr0root0uuid0000000000000' where ID = '${ID}'

     - For all query results rows containing items belonging to I_CLASS = 2 (property)

           delete from JCR_SREF where PROPERTY_ID = '${ID}'
           delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
           delete from JCR_SITEM where PARENT_ID = '${ID}' or ID='${ID}'

2. Node has a single valued properties with no declaration in VALUE table

   - To detect an issue you need to execute the following query:

         select * from JCR_SITEM P where P.I_CLASS=2 and P.P_MULTIVALUED=0 and NOT EXISTS (select * from JCR_SVALUE V where V.PROPERTY_ID=P.ID)

     ### Note

     P_MULTIVALUED=0 should be replacted by P_MULTIVALUED='f' for PostgreSQL

   - Fix description:

     Simply remove corrupted properties

   - To fix for every row execute next queries replacing ${ID} by corresponding value:

         delete from JCR_SREF where PROPERTY_ID = '${ID}'
         delete from JCR_SITEM where ID = '${ID}'

3. Node has no primary type property

   - To detect an issue you need to execute the following query:

         select * from JCR_SITEM N where N.I_CLASS=1 and NOT EXISTS (select * from JCR_SITEM P where P.I_CLASS=2 and P.PARENT_ID=N.ID and P.NAME='[http://www.jcp.org/jcr/1.0]primaryType')

   - Fix description:

     Remove node, all its children, properties, values and reference records

   - To fix do the following:

     - First step is to recursivly travers to the bottom of the tree:

           select * from JCR_SITEM where PARENT_ID='${ID}' and I_CLASS=1

       until query results in empty value. You'll receive a tree structure containing a node, its children and properties.

     - Second step is to execute the following steps with tree structure elements in reverse order (from leaves to head)

       Execute query for tree element's ${ID}

           select * from JCR_SITEM where PARENT_ID='${ID}'

       Execute queries for each ${ID} received during mentioned above query execution

           delete from JCR_SREF where PROPERTY_ID = '${ID}'
           delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
           delete from JCR_SITEM where PARENT_ID = '${ID}' or ID='${ID}'

4. All value records have no related property record

   - To detect an issue you need to execute the following query:

         select * from JCR_SVALUE V where NOT EXISTS(select * from JCR_SITEM P where V.PROPERTY_ID = P.ID and P.I_CLASS=2)

   - Fix description:

     Remove these unnecessary records from JCR_SVALUE table

   - To fix do the following:

     For every row execute next queries replacing ${ID} with corresponding value:

         delete from JCR_SVALUE where ID = '${ID}'

5. Corrupted VALUE records. Both STORAGE_DESC and DATA fields contain not null value

   - To detect an issue you need to execute the following query:

         select * from JCR_SVALUE where (STORAGE_DESC is not null and DATA is not null)

   - Fix description:

     Set null for STORAGE_DESC field

   - To fix do the following:

     For every row execute next queries replacing ${ID} with corresponding value:

         update JCR_SVALUE set STORAGE_DESC = null where ID = '${ID}'

   ### Note

   For Sybase DB "DATA is not null" must be replaced by "not DATA like null"

6. Item is its own parent

   - To detect an issue you need to execute the following query:

         select * from JCR_SITEM I where I.ID = I.PARENT_ID and I.NAME <> '__root_parent'

   - Fix description:

     Assign root as parent node to be able to delete later if node is not needed to use anymore

   - To fix do the following:

     - For all query results rows containing items belonging to I_CLASS = 1 (nodes)

       execute next query replacing ${ID} and ${CONTAINER_NAME} with corresponding values:

       - Single DB

             update JCR_SITEM set PARENT_ID='${CONTAINER_NAME}00exo0jcr0root0uuid0000000000000' where ID = '${ID}'

       - Multi DB

             update JCR_MITEM set PARENT_ID='00exo0jcr0root0uuid0000000000000' where ID = '${ID}'

     - For all query results rows containing items belonging to I_CLASS = 2 (property)

           delete from JCR_SREF where PROPERTY_ID = '${ID}'
           delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
           delete from JCR_SITEM where PARENT_ID = '${ID}' or ID='${ID}'

7. Several versions of same item

   - To detect an issue you need to execute the following query:

         select * from JCR_SITEM I where EXISTS (select * from JCR_SITEM J WHERE I.CONTAINER_NAME = J.CONTAINER_NAME and I.PARENT_ID = J.PARENT_ID AND I.NAME = J.NAME and I.I_INDEX = J.I_INDEX and I.I_CLASS = J.I_CLASS and I.VERSION != J.VERSION)

   - Fix description:

     Keep the newest version and remove the others

   - To fix do the following:

     - Grouping

           select max(VERSION) as MAX_VERSION, PARENT_ID, NAME, CONTAINER_NAME, I_CLASS, I_INDEX  from JCR_SITEM WHERE I_CLASS=2 GROUP BY PARENT_ID, CONTAINER_NAME, NAME, I_CLASS, I_INDEX HAVING count(VERSION) > 1

     - Execute the following query, replacing ${PARENT_ID} and ${CONTAINER_NAME}, ${NAME}, ${I_CLASS}, ${I_INDEX}, ${MAX_VERSION} with corresponding values contained in results of mentioned above query:

       - Single DB

             select * from JCR_SITEM where I.CONTAINER_NAME='${CONTAINER_NAME}' and PARENT_ID='${PARENT_ID}' and NAME='${NAME}' and I_CLASS='${I_CLASS}' and I_INDEX='${I_INDEX}' and VERSION < ${MAX_VERSION}

       - Multi DB

             select * from JCR_SITEM where PARENT_ID='${PARENT_ID}' and NAME='${NAME}' and I_CLASS='${I_CLASS}' and I_INDEX='${I_INDEX}' and VERSION < ${MAX_VERSION}

       Execute the following queries, replacing ${ID} with corresponding values of newly obtained results.

           delete from JCR_SREF where PROPERTY_ID = '${ID}'
           delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
           delete from JCR_SITEM where ID='${ID}'

8. Reference properties without reference records

   - To detect an issue you need to execute the following query:

         select * from JCR_SITEM P, JCR_SVALUE V where P.ID = V.PROPERTY_ID and P.P_TYPE=9 and NOT EXISTS (select * from JCR_SREF R where P.ID=R.PROPERTY_ID)

   - Fix description:

     Remove broken reference properties

   - To fix do the following:

     Execute the query replacing ${ID} with corresponding value

         delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
         delete from JCR_SITEM where ID = '${ID}'

9. Node, considered to be locked in the lockmanager data, is not locked according to the jcr data or the opposite situation

   - To detect an issue you need:

     First get all locked nodes IDs in repository, mentioned in JCR_xITEM table, by executing a query:

         select distinct PARENT_ID from JCR_SITEM where I_CLASS=2 and (NAME='[http://www.jcp.org/jcr/1.0]lockOwner' or NAME='[http://www.jcp.org/jcr/1.0]lockIsDeep')

     Then compare it to nodes IDs from LockManager's table

     - JBC

       ### Note

       During comparing results be aware that for single DB configurations you need to cut off ID prefix representing workspace name for results obtained from JCR_xITEM table.

       ### Note

       Though usually single lock table is used for the whole repository, it is possible to configure separate db lock tables for each workspace, in this case to obtain information over repository you need to execute queries for each table.

       - Non shareable

             select fqn from ${LOCK_TABLE} where parent='/$LOCKS'

       - Shareable

         Replace ${REPOSITORY_NAME} with corresponding value

             select fqn from ${LOCK_TABLE} where parent like '/${REPOSITORY_NAME}%/$LOCKS/'

     - ISPN

       ### Note

       For ISPN lock tables are defined for each workspace separately, so to obtain information over repository you must execute queries for all lock tables.

       To get all set of locked node IDs in repository you must execute the following query for each workspace

           select id from ${LOCK_TABLE}

   - Fix description:

     Remove inconsistent lock entries and properties. Remove entries in LOCK_TABLE that have no corresponding properties in JCR_xITEM table and remove JCR_xITEM properties that have no corresponding entries in LOCK_TABLE.

   - To fix do the following:

     First remove property values, replace ${ID} with corresponding node ID

         delete from JCR_SVALUE where PROPERTY_ID in (select ID from JCR_SITEM where PARENT_ID='${ID}' and (NAME = '[http://www.jcp.org/jcr/1.0]lockIsDeep' or NAME = '[http://www.jcp.org/jcr/1.0]lockOwner'))

     Than remove property items themselves, replace ${ID} with corresponding node ID

         delete from JCR_SITEM where PARENT_ID='${ID}' and (NAME = '[http://www.jcp.org/jcr/1.0]lockIsDeep' or NAME = '[http://www.jcp.org/jcr/1.0]lockOwner')

     Replace ${ID} and ${FQN} with corresponding node ID and FQN

     - JBC

           delete from ${LOCK_TABLE} where fqn = '${FQN}'

     - ISPN

       Execute the following query for each workspace

           delete from ${LOCK_TABLE} where id = '${ID}'

10. A property's value is stored in the file system, but its content is missing

    This cannot be checked via simple SQL queries

Copyright © 2009-2012. All rights reserved. eXo Platform SAS