<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<section id="JCR.RepositoryCheckController">
  <title>Checking and repairing repository integrity and consistency</title>

  <warning>
    <para>It is highly recommended to back up your data before repairing
    inconsistencies (either automatically or maually). It is also recommended
    to store the results of queries that check the data consistency. This may
    be useful for the support team in case of deeper restoration
    process.</para>
  </warning>

  <para>Production and any systems may have faults in some days. They may be
  caused by hardware and/or software problems, human faults during updates and
  in many other circumstances. It is important to check integrity and
  consistency of the system if it is not backed up or stale, or it takes the
  recovery process much time. The eXo JCR implementation offers an innovative
  JMX-based complex checking tool. Running inspection, this tool checks every
  major JCR component, such as persistent data layer and index. The persistent
  layer includes JDBC Data Container and Value Storage if they are configured.
  The database is verified using the set of complex specialized
  domain-specific queries. The Value Storage tool checks the existence and
  access to each file. Index verification contains two-way pass cycle,
  existence of each node in the index checks on persistent layer along with
  opposite direction, when each node from Data Container is validated in the
  index. Access to the checking tool is exposed via the JMX interface
  (<emphasis role="bold">RepositoryCheckController</emphasis> MBean) with the
  following operations available:</para>

  <mediaobject>
    <imageobject>
      <imagedata align="center"
                 fileref="images/repository-check-controller.png" />
    </imageobject>
  </mediaobject>

  <table>
    <title>Check methods</title>

    <tgroup cols="2">
      <thead>
        <row>
          <entry>Operation</entry>

          <entry>Description</entry>
        </row>
      </thead>

      <tbody>
        <row>
          <entry><code>checkAll()</code></entry>

          <entry>Inspect the full repository data (database, value storage and
          search index).</entry>
        </row>

        <row>
          <entry><code>checkDataBase()</code></entry>

          <entry>Inspect only the DB.</entry>
        </row>

        <row>
          <entry><code>checkValueStorage()</code></entry>

          <entry>Inspect only the ValueStorage.</entry>
        </row>

        <row>
          <entry><code>checkIndex()</code></entry>

          <entry>Inspect only the SearchIndex.</entry>
        </row>
      </tbody>
    </tgroup>
  </table>

  <para>Among the list of known inconsistencies described in the next section,
  see below what can be checked and repaired automatically:</para>

  <itemizedlist>
    <listitem>
      <para><abbrev>An item has no parent node:</abbrev> Properties will be
      removed and the root UUID will be assigned in case of nodes.</para>
    </listitem>

    <listitem>
      <para><abbrev>A node has a single valued property with nothing declared
      in the VALUE table:</abbrev> This property will be removed if it is not
      required by primary type of its node.</para>
    </listitem>

    <listitem>
      <para><abbrev>A node has no primary type property:</abbrev> This node
      and the whole subtree will be removed if it is not required by primary
      type of its parent.</para>
    </listitem>

    <listitem>
      <para><abbrev>Value record has no related property record:</abbrev>
      Value record will be removed from database.</para>
    </listitem>

    <listitem>
      <para><abbrev>An item is its own parent:</abbrev> Properties will be
      removed and root UUID will be assigned in case of nodes.</para>
    </listitem>

    <listitem>
      <para><abbrev>Several versions of same item:</abbrev> All earlier
      records with earlier versions will be removed from ITEM table.</para>
    </listitem>

    <listitem>
      <para><abbrev>Reference properties without reference records:</abbrev>
      The property will be removed if it is not required by the primary type
      of its node.</para>
    </listitem>

    <listitem>
      <para><abbrev>A node is marked as locked in the lockmanager's table but
      not in ITEM table or the opposite:</abbrev> All lock inconsistencies
      will be removed from both tables.</para>
    </listitem>
  </itemizedlist>

  <note>
    <para>The only inconsistency that cannot be fixed automatically is
    <emphasis role="bold">Corrupted VALUE records. Both
    <emphasis>STORAGE_DESC</emphasis> and <emphasis>DATA</emphasis> fields
    contain not null value.</emphasis> Since there is no way to determinate
    which value is valid: either on the file system or in the database.</para>
  </note>

  <para>The list of ValueStorage inconsistencies which can be checked and
  repaired automatically:</para>

  <itemizedlist>
    <listitem>
      <para><abbrev>Property's value is stored in the File System but the
      content is missing:</abbrev> A new empty file corresponding to this
      value will be created.</para>
    </listitem>
  </itemizedlist>

  <para>The list of SearchIndex inconsistencies which can be checked. To
  repair them we need to reindex the content completely, what also can be done
  using JMX:</para>

  <itemizedlist>
    <listitem>
      <para><emphasis role="bold">Not indexed document</emphasis></para>
    </listitem>

    <listitem>
      <para><emphasis role="bold">Document indexed more than one
      time</emphasis></para>
    </listitem>

    <listitem>
      <para><emphasis role="bold">Document corresponds to removed
      node</emphasis></para>
    </listitem>
  </itemizedlist>

  <table>
    <title>Repair methods</title>

    <tgroup cols="2">
      <thead>
        <row>
          <entry>Operation</entry>

          <entry>Description</entry>
        </row>
      </thead>

      <tbody>
        <row>
          <entry><code>repairDataBase()</code></entry>

          <entry>Repair DB incosistencies declared above</entry>
        </row>

        <row>
          <entry><code>repairValueStorage()</code></entry>

          <entry>Repair ValueStorage incosistencies declared above</entry>
        </row>
      </tbody>
    </tgroup>
  </table>

  <para>All tool activities are stored in a file, which can be found in app
  directory by the <filename>report-&lt;repository
  name&gt;-dd-MMM-yy-HH-mm.txt</filename> name.</para>

  <section id="JCR.RepositoryCheckController.RecommendationsOnHowToFixCorruptedJCR">
    <title>Recommendations on how to fix corrupted JCR manually</title>

    <para>Here are examples of corrupted JCR and ways to eliminate them:<note>
        <para>It is assumed that queries for single and multi DB
        configurations are different only in the
        <emphasis>JCR_xITEM</emphasis> table name, otherwise queries will be
        explicitly introduced.</para>

        <para>In some examples, you will be asked to replace some
        identificators with the corresponding value. That basically means that
        you need to insert values, from each row of result of query executed
        during the issue detection stage to the corresponding place. The
        explicit explanation of what to do will be introduced in case
        replacing is needed to be fulfilled in other way.</para>
      </note></para>

    <orderedlist>
      <listitem>
        <para><emphasis role="bold">Items have no parent
        nodes.</emphasis></para>

        <itemizedlist>
          <listitem>
            <para>To detect this issue, you need to execute the following
            query:</para>

            <programlisting language="sql">select * from JCR_SITEM I where NOT EXISTS(select * from JCR_SITEM P where P.ID = I.PARENT_ID)</programlisting>
          </listitem>

          <listitem>
            <para>Fix description: Assign root as parent node to be able to
            delete this node later if it is not needed anymore.</para>
          </listitem>

          <listitem>
            <para>To fix this issue, do as follows:</para>

            <itemizedlist>
              <listitem>
                <para>For all query results rows containing items belonging to
                I_CLASS = 1 (nodes).</para>

                <para>Execute the next query. Replace <code>${ID}</code> and
                <code>${CONTAINER_NAME}</code> with the corresponding
                values:</para>

                <itemizedlist>
                  <listitem>
                    <para>Single DB:</para>

                    <programlisting>update JCR_SITEM set PARENT_ID='${CONTAINER_NAME}00exo0jcr0root0uuid0000000000000' where ID = '${ID}'</programlisting>
                  </listitem>

                  <listitem>
                    <para>Multi DB:</para>

                    <programlisting>update JCR_MITEM set PARENT_ID='00exo0jcr0root0uuid0000000000000' where ID = '${ID}'</programlisting>
                  </listitem>
                </itemizedlist>
              </listitem>

              <listitem>
                <para>For all query results rows containing items belonging to
                I_CLASS = 2 (property).</para>

                <programlisting>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}' </programlisting>
              </listitem>
            </itemizedlist>
          </listitem>
        </itemizedlist>
      </listitem>

      <listitem>
        <para><emphasis role="bold">A node has a single valued property with
        no declaration in the VALUE table.</emphasis></para>

        <itemizedlist>
          <listitem>
            <para>To detect this issue, you need to execute the following
            query:</para>

            <programlisting>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)</programlisting>

            <note>
              <para><code>P_MULTIVALUED=0</code> should be replaced by
              <code>P_MULTIVALUED='f'</code> for PostgreSQL.</para>
            </note>
          </listitem>

          <listitem>
            <para>Fix description: Simply remove corrupted properties.</para>
          </listitem>

          <listitem>
            <para>To fix for every row, execute next queries and replace
            <code>${ID}</code> with the corresponding value:</para>

            <programlisting>delete from JCR_SREF where PROPERTY_ID = '${ID}'
delete from JCR_SITEM where ID = '${ID}'</programlisting>
          </listitem>
        </itemizedlist>
      </listitem>

      <listitem>
        <para><emphasis role="bold">Node has no primary type
        property.</emphasis></para>

        <itemizedlist>
          <listitem>
            <para>To detect this issue, you need to execute the following
            query:</para>

            <programlisting>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')
</programlisting>
          </listitem>

          <listitem>
            <para>Fix description: Remove node, all its children, properties,
            values and reference records.</para>
          </listitem>

          <listitem>
            <para>To fix this issue, do as follows:</para>

            <procedure>
              <step>
                <para>Recursively traverse to the bottom of the tree until
                query results are in empty value:</para>

                <programlisting>select * from JCR_SITEM where PARENT_ID='${ID}' and I_CLASS=1</programlisting>

                <para>You will receive a tree structure containing a node, its
                children and properties.</para>
              </step>

              <step>
                <para>Execute the following steps with tree structure elements
                in reverse order (from leaves to head).</para>

                <itemizedlist>
                  <listitem>
                    <para>Execute query for tree element's
                    <code>${ID}</code>.</para>

                    <programlisting>select * from JCR_SITEM where PARENT_ID='${ID}'</programlisting>
                  </listitem>

                  <listitem>
                    <para>Execute queries for each <code>${ID}</code> received
                    during the query execution mentioned above.</para>

                    <programlisting>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}' </programlisting>
                  </listitem>
                </itemizedlist>
              </step>
            </procedure>
          </listitem>
        </itemizedlist>
      </listitem>

      <listitem>
        <para><emphasis role="bold">All value records have no related property
        record.</emphasis></para>

        <itemizedlist>
          <listitem>
            <para>To detect this issue, you need to execute the following
            query:</para>

            <programlisting>select * from JCR_SVALUE V where NOT EXISTS(select * from JCR_SITEM P where V.PROPERTY_ID = P.ID and P.I_CLASS=2)</programlisting>
          </listitem>

          <listitem>
            <para>Fix description: Remove these unnecessary records from the
            <emphasis>JCR_SVALUE</emphasis> table.</para>
          </listitem>

          <listitem>
            <para>To fix this issue, execute next queries and replace
            <code>${ID}</code> with the corresponding value for every
            row:</para>

            <programlisting>delete from JCR_SVALUE where ID = '${ID}'</programlisting>
          </listitem>
        </itemizedlist>
      </listitem>

      <listitem>
        <para><emphasis role="bold">Corrupted VALUE records. Both
        <emphasis>STORAGE_DESC</emphasis> and <emphasis>DATA</emphasis> fields
        contain not null value</emphasis>.<itemizedlist>
            <listitem>
              <para>To detect this issue, you need to execute the following
              query:</para>

              <programlisting>select * from JCR_SVALUE where (STORAGE_DESC is not null and DATA is not null)</programlisting>
            </listitem>

            <listitem>
              <para>Fix description: Set null for the
              <emphasis>STORAGE_DESC</emphasis> field by assuming value stored
              in database is valid</para>
            </listitem>

            <listitem>
              <para>To fix this issue, execute next queries replacing
              <code>${ID}</code> with corresponding value for every
              row:</para>

              <programlisting>update JCR_SVALUE set STORAGE_DESC = null where ID = '${ID}'</programlisting>
            </listitem>
          </itemizedlist></para>

        <note>
          <para>For Sybase DB, "DATA is not null" must be replaced by "not
          DATA like null".</para>
        </note>
      </listitem>

      <listitem>
        <para><emphasis role="bold">Item is its own
        parent.</emphasis><itemizedlist>
            <listitem>
              <para>To detect this issue, you need to execute the following
              query:</para>

              <programlisting>select * from JCR_SITEM I where I.ID = I.PARENT_ID and I.NAME &lt;&gt; '__root_parent'</programlisting>
            </listitem>

            <listitem>
              <para>Fix description: Assign root as parent node to delete this
              node later if it is not needed to use anymore.</para>
            </listitem>

            <listitem>
              <para>To fix this issue, do as follows:</para>

              <itemizedlist>
                <listitem>
                  <para>For all query results rows containing items belonging
                  to I_CLASS = 1 (nodes).</para>

                  <para>Execute the next query, replacing <code>${ID}</code>
                  and <code>${CONTAINER_NAME}</code> with corresponding
                  values:</para>

                  <itemizedlist>
                    <listitem>
                      <para>Single DB:</para>

                      <programlisting>update JCR_SITEM set PARENT_ID='${CONTAINER_NAME}00exo0jcr0root0uuid0000000000000' where ID = '${ID}'</programlisting>
                    </listitem>

                    <listitem>
                      <para>Multi DB:</para>

                      <programlisting>update JCR_MITEM set PARENT_ID='00exo0jcr0root0uuid0000000000000' where ID = '${ID}'</programlisting>
                    </listitem>
                  </itemizedlist>
                </listitem>

                <listitem>
                  <para>For all query results rows containing items belonging
                  to I_CLASS = 2 (property).</para>

                  <programlisting>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}' </programlisting>
                </listitem>
              </itemizedlist>
            </listitem>
          </itemizedlist></para>
      </listitem>

      <listitem>
        <para><emphasis role="bold">Several versions of same
        item.</emphasis><itemizedlist>
            <listitem>
              <para>To detect this issue, you need to execute the following
              query:</para>

              <programlisting>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)</programlisting>
            </listitem>

            <listitem>
              <para>Fix description: Keep the newest version and remove the
              others.</para>
            </listitem>

            <listitem>
              <para>To fix this issue, do as follows:</para>

              <itemizedlist>
                <listitem>
                  <para>Grouping:</para>

                  <programlisting>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) &gt; 1</programlisting>
                </listitem>

                <listitem>
                  <para>Execute the following query, replacing
                  <code>${PARENT_ID}</code> and
                  <code>${CONTAINER_NAME}</code>, <code>${NAME}</code>,
                  <code>${I_CLASS}</code>, <code>${I_INDEX}</code>,
                  <code>${MAX_VERSION}</code> with corresponding values
                  contained in results of the query mentioned above:</para>

                  <itemizedlist>
                    <listitem>
                      <para>Single DB:<programlisting>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 &lt; ${MAX_VERSION}</programlisting></para>
                    </listitem>

                    <listitem>
                      <para>Multi DB:<programlisting>select * from JCR_SITEM where PARENT_ID='${PARENT_ID}' and NAME='${NAME}' and I_CLASS='${I_CLASS}' and I_INDEX='${I_INDEX}' and VERSION &lt; ${MAX_VERSION}</programlisting></para>
                    </listitem>
                  </itemizedlist>

                  <para>Execute the following queries and replace
                  <code>${ID}</code> with corresponding values of newly
                  obtained results.</para>

                  <programlisting>delete from JCR_SREF where PROPERTY_ID = '${ID}'
delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
delete from JCR_SITEM where ID='${ID}'</programlisting>
                </listitem>
              </itemizedlist>
            </listitem>
          </itemizedlist></para>
      </listitem>

      <listitem>
        <para><emphasis role="bold">Reference properties without reference
        records.</emphasis><itemizedlist>
            <listitem>
              <para>To detect this issue, you need to execute the following
              query:</para>

              <programlisting>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)</programlisting>
            </listitem>

            <listitem>
              <para>Fix description: Remove broken reference
              properties.</para>
            </listitem>

            <listitem>
              <para>To fix this issue, execute the following query and replace
              <code>${ID}</code> with the corresponding value.</para>

              <programlisting>delete from JCR_SVALUE where PROPERTY_ID = '${ID}'
delete from JCR_SITEM where ID = '${ID}'</programlisting>
            </listitem>
          </itemizedlist></para>
      </listitem>

      <listitem>
        <para><emphasis role="bold">Node considered to be locked in the
        lockmanager data, is not locked according to the JCR data or the
        opposite situation. </emphasis> <itemizedlist>
            <listitem>
              <para>To detect this issue, you need to:</para>

              <para>First, get all locked nodes IDs in repository, mentioned
              in the JCR_xITEM table by executing a query:<programlisting>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')</programlisting></para>

              <para>Then compare it to nodes IDs from the LockManager's
              table.</para>

              <itemizedlist>
                <listitem>
                  <para>JBC:<note>
                      <para>During comparing results, be aware that for single
                      DB configurations you need to cut off ID prefix
                      representing workspace name for results obtained from
                      the JCR_xITEM table.</para>

                      <para>Though a single lock table is usually used for the
                      whole repository, it is possible to configure separate
                      DB lock tables for each workspace. In this case, you
                      need to execute queries for each table to obtain
                      information over repository.</para>
                    </note></para>

                  <itemizedlist>
                    <listitem>
                      <para>Non shareable:</para>

                      <programlisting>Select fqn from ${LOCK_TABLE} where parent='/$LOCKS'</programlisting>
                    </listitem>

                    <listitem>
                      <para>Shareable:</para>

                      <para>Replace <code>${REPOSITORY_NAME}</code> with the
                      corresponding value:</para>

                      <programlisting>select fqn from ${LOCK_TABLE} where parent like '/${REPOSITORY_NAME}%/$LOCKS/' </programlisting>
                    </listitem>
                  </itemizedlist>
                </listitem>

                <listitem>
                  <para>ISPN:</para>

                  <para><note>
                      <para>For ISPN lock tables which are defined for each
                      workspace separately, you must execute queries for all
                      lock tables to obtain information over
                      repository.</para>
                    </note>To get all set of locked node IDs in repository,
                  you must execute the following query for each
                  workspace.</para>

                  <programlisting>select id from ${LOCK_TABLE}</programlisting>
                </listitem>
              </itemizedlist>
            </listitem>

            <listitem>
              <para>Fix description: Remove inconsistent lock entries and
              properties. Remove entries in <emphasis>LOCK_TABLE</emphasis>
              that have no corresponding properties in
              <emphasis>JCR_xITEM</emphasis> table and remove the
              <emphasis>JCR_xITEM</emphasis> properties that have no
              corresponding entries in <emphasis>LOCK_TABLE</emphasis>.</para>
            </listitem>

            <listitem>
              <para>To fix this, do the followings:</para>

              <para>First, remove property values, and replace
              <code>${ID}</code> with corresponding node ID. <programlisting>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'))</programlisting>then
              remove property items themselves, replace <code>${ID}</code>
              with the corresponding node ID:</para>

              <programlisting>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')</programlisting>

              <para>Replace <code>${ID}</code> and <code>${FQN}</code> with
              the corresponding node ID and FQN:</para>

              <itemizedlist>
                <listitem>
                  <para>JBC:</para>

                  <programlisting>delete from ${LOCK_TABLE} where fqn = '${FQN}'</programlisting>
                </listitem>

                <listitem>
                  <para>ISPN:</para>

                  <para>Execute the following query for each workspace:</para>

                  <programlisting>delete from ${LOCK_TABLE} where id = '${ID}'</programlisting>
                </listitem>
              </itemizedlist>
            </listitem>
          </itemizedlist></para>
      </listitem>

      <listitem>
        <para><emphasis role="bold">A property's value is stored in the file
        system, but its content is missing. </emphasis></para>

        <para>This cannot be checked via simple SQL queries.</para>
      </listitem>
    </orderedlist>
  </section>
</section>
