From do-not-reply at jboss.org Thu Mar 1 08:26:52 2012 Content-Type: multipart/mixed; boundary="===============7733172493917354075==" MIME-Version: 1.0 From: do-not-reply at jboss.org To: exo-jcr-commits at lists.jboss.org Subject: [exo-jcr-commits] exo-jcr SVN: r5720 - jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US/modules/jcr. Date: Thu, 01 Mar 2012 08:26:52 -0500 Message-ID: <201203011326.q21DQqCZ005957@svn01.web.mwc.hst.phx2.redhat.com> --===============7733172493917354075== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Author: dkuleshov Date: 2012-03-01 08:26:52 -0500 (Thu, 01 Mar 2012) New Revision: 5720 Modified: jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US/= modules/jcr/repository-check-controller.xml Log: EXOJCR-1761: added chapter with recommendations on ways to fix corrupted JCR Modified: jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook= /en-US/modules/jcr/repository-check-controller.xml =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --- jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US= /modules/jcr/repository-check-controller.xml 2012-03-01 11:32:22 UTC (rev 5= 719) +++ jcr/trunk/exo.jcr.docs/exo.jcr.docs.developer/en/src/main/docbook/en-US= /modules/jcr/repository-check-controller.xml 2012-03-01 13:26:52 UTC (rev 5= 720) @@ -68,4 +68,486 @@ manually. Warnings found by tool can be a normal situation in some cas= es and usually production system will still remain fully functional. + +
+ 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 req= uire + 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 que= ries + will be explicitly introduced. + + In some examples you'll be asked to replace some identificat= or + with corresponding value. That basically means that you need to in= sert + values, from each row result of query executed during issue detect= ion + 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 whe= re NOT EXISTS(select * from JCR_SITEM P where P.ID =3D 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 belongin= g to + I_CLASS =3D 1 (nodes) + + execute next query replacing ${ID} and ${CONTAINER_N= AME} + by corresponding values: + + + + Single DB + + update JCR_SITEM set PARENT_ID=3D'${CO= NTAINER_NAME}00exo0jcr0root0uuid0000000000000' where ID =3D '${ID}' + + + + Multi DB + + update JCR_MITEM set PARENT_ID=3D'00ex= o0jcr0root0uuid0000000000000' where ID =3D '${ID}' + + + + + + For all query results rows containing items belongin= g to + I_CLASS =3D 2 (property) + + delete from JCR_SREF where PROPERTY_ID =3D= '${ID}' +delete from JCR_SVALUE where PROPERTY_ID =3D '${ID}' +delete from JCR_SITEM where PARENT_ID =3D '${ID}' or ID=3D'${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=3D2 = and P.P_MULTIVALUED=3D0 and NOT EXISTS (select * from JCR_SVALUE V where V.= PROPERTY_ID=3DP.ID) + + + P_MULTIVALUED=3D0 should be replacted by P_MULTIVALUED= =3D'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 =3D '${= ID}' +delete from JCR_SITEM where ID =3D '${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=3D1 = and NOT EXISTS (select * from JCR_SITEM P where P.I_CLASS=3D2 and P.PARENT_= ID=3DN.ID and P.NAME=3D'[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=3D= '${ID}' and I_CLASS=3D1 + + 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 t= ree + structure elements in reverse order (from leaves to + head) + + Execute query for tree element's ${ID} + + select * from JCR_SITEM where PARENT_ID=3D= '${ID}' + + Execute queries for each ${ID} received during menti= oned + above query execution + + delete from JCR_SREF where PROPERTY_ID =3D= '${ID}' +delete from JCR_SVALUE where PROPERTY_ID =3D '${ID}' +delete from JCR_SITEM where PARENT_ID =3D '${ID}' or ID=3D'${ID}' + + + + + + + + All value records have no related pr= operty + record + + + + To detect an issue you need to execute the following + query: + + select * from JCR_SVALUE V where NOT EXISTS(se= lect * from JCR_SITEM P where V.PROPERTY_ID =3D P.ID and P.I_CLASS=3D2) + + + + 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 =3D '${ID}' + + + + + + Corrupted VALUE records. Both STORAG= E_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 =3D null = where ID =3D '${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 =3D I.P= ARENT_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 belong= ing + to I_CLASS =3D 1 (nodes) + + execute next query replacing ${ID} and + ${CONTAINER_NAME} with corresponding values: + + + + Single DB + + update JCR_SITEM set PARENT_ID=3D'${= CONTAINER_NAME}00exo0jcr0root0uuid0000000000000' where ID =3D '${ID}' + + + + Multi DB + + update JCR_MITEM set PARENT_ID=3D'00= exo0jcr0root0uuid0000000000000' where ID =3D '${ID}' + + + + + + For all query results rows containing items belong= ing + to I_CLASS =3D 2 (property) + + delete from JCR_SREF where PROPERTY_ID = =3D '${ID}' +delete from JCR_SVALUE where PROPERTY_ID =3D '${ID}' +delete from JCR_SITEM where PARENT_ID =3D '${ID}' or ID=3D'${ID}' + + + + + + + + Several versions of same + item + + To detect an issue you need to execute the following + query: + + select * from JCR_SITEM I where EXISTS (sele= ct * from JCR_SITEM J WHERE I.CONTAINER_NAME =3D J.CONTAINER_NAME and I.PAR= ENT_ID =3D J.PARENT_ID AND I.NAME =3D J.NAME and I.I_INDEX =3D J.I_INDEX an= d I.I_CLASS =3D J.I_CLASS and I.VERSION !=3D J.VERSION) + + + + Fix description: + + Keep the newest version and remove the others + + + + To fix do the following: + + + + Grouping + + select max(VERSION) as MAX_VERSION, PARE= NT_ID, NAME, CONTAINER_NAME, I_CLASS, I_INDEX from JCR_SITEM WHERE I_CLASS= =3D2 GROUP BY PARENT_ID, CONTAINER_NAME, NAME, I_CLASS, I_INDEX HAVING coun= t(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 DBselect * from JCR_SIT= EM where I.CONTAINER_NAME=3D'${CONTAINER_NAME}' and PARENT_ID=3D'${PARENT_I= D}' and NAME=3D'${NAME}' and I_CLASS=3D'${I_CLASS}' and I_INDEX=3D'${I_INDE= X}' and VERSION < ${MAX_VERSION} + + + + Multi DBselect * from JCR_SITE= M where PARENT_ID=3D'${PARENT_ID}' and NAME=3D'${NAME}' and I_CLASS=3D'${I_= CLASS}' and I_INDEX=3D'${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 = =3D '${ID}' +delete from JCR_SVALUE where PROPERTY_ID =3D '${ID}' +delete from JCR_SITEM where ID=3D'${ID}' + + + + + + + + Reference properties without referen= ce + records + + To detect an issue you need to execute the following + query: + + select * from JCR_SITEM P, JCR_SVALUE V wher= e P.ID =3D V.PROPERTY_ID and P.P_TYPE=3D9 and NOT EXISTS (select * from JCR= _SREF R where P.ID=3DR.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 =3D= '${ID}' +delete from JCR_SITEM where ID =3D '${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, mentione= d in + JCR_xITEM table, by executing a query:select= distinct PARENT_ID from JCR_SITEM where I_CLASS=3D2 and (NAME=3D'[http://w= ww.jcp.org/jcr/1.0]lockOwner' or NAME=3D'[http://www.jcp.org/jcr/1.0]lockIs= Deep') + + Then compare it to nodes IDs from LockManager's + table + + + + JBC + During comparing results be aware that for sin= gle + 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 t= he + whole repository, it is possible to configure separa= te + db lock tables for each workspace, in this case to + obtain information over repository you need to execu= te + queries for each table. + + + + + Non shareable + + select fqn from ${LOCK_TABLE} where = parent=3D'/$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 IDdelete from JCR_SVALUE = where PROPERTY_ID in (select ID from JCR_SITEM where PARENT_ID=3D'${ID}' an= d (NAME =3D '[http://www.jcp.org/jcr/1.0]lockIsDeep' or NAME =3D '[http://w= ww.jcp.org/jcr/1.0]lockOwner'))Than + remove property items themselves, replace ${ID} with + corresponding node ID + + delete from JCR_SITEM where PARENT_ID=3D'${I= D}' and (NAME =3D '[http://www.jcp.org/jcr/1.0]lockIsDeep' or NAME =3D '[ht= tp://www.jcp.org/jcr/1.0]lockOwner') + + Replace ${ID} and ${FQN} with corresponding node ID and + FQN + + + + JBC + + delete from ${LOCK_TABLE} where fqn =3D = '${FQN}' + + + + ISPN + + Execute the following query for each workspace + + delete from ${LOCK_TABLE} where id =3D '= ${ID}' + + + + + + + + A property's value is stored in the = file + system, but its content is missing + + This cannot be checked via simple SQL queries + + +
--===============7733172493917354075==--