JBoss.orgCommunity Documentation
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:
Table 40.1. Check methods
| 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
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.
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:
It is assumed that queries for single and multi db configurations differs only by JCR_xITEM table name, otherwise queries will be explicitly introduced.
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.
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}' 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)
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}'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=1until 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}' 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}'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}'For Sybase DB "DATA is not null" must be replaced by "not DATA like null"
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}' 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}'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}'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
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.
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
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}'A property's value is stored in the file system, but its content is missing
This cannot be checked via simple SQL queries