Error while upgrading "unique constraint (PORTAL_MEDIADOR_PRO_BACKUP.IX_103D6207) violated_"

問題

Portal 6.2 から DXP 7.0+ にアップグレードを開始すると、以下のようなエラーが表示されます。：

ERROR - Failed upgrade process for module com.liferay.journal.service
com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.BatchUpdateException: ORA-00001: unique constraint (PORTAL_MEDIADOR_PRO_BACKUP.IX_103D6207) violated_ [Sanitized]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:109)
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:127)
        at com.liferay.portal.upgrade.internal.executor.UpgradeExecutor$UpgradeInfosRunnable.run(UpgradeExecutor.java:200)
        at com.liferay.portal.output.stream.container.internal.OutputStreamContainerFactoryTrackerImpl.runWithSwappedLog(OutputStreamContainerFactoryTrackerImpl.java:134)
        at com.liferay.portal.upgrade.internal.executor.SwappedLogExecutor.execute(SwappedLogExecutor.java:51)
        at com.liferay.portal.upgrade.internal.executor.UpgradeExecutor.executeUpgradeInfos(UpgradeExecutor.java:155)
        at com.liferay.portal.upgrade.internal.executor.UpgradeExecutor.execute(UpgradeExecutor.java:118)
        at com.liferay.portal.upgrade.internal.release.osgi.commands.ReleaseManagerOSGiCommands.execute(ReleaseManagerOSGiCommands.java:81)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.felix.gogo.runtime.Reflective.invoke(Reflective.java:139)
        at org.apache.felix.gogo.runtime.CommandProxy.execute(CommandProxy.java:91)
        at org.apache.felix.gogo.runtime.Closure.executeCmd(Closure.java:599)
        at org.apache.felix.gogo.runtime.Closure.executeStatement(Closure.java:526)
        at org.apache.felix.gogo.runtime.Closure.execute(Closure.java:415)
        at org.apache.felix.gogo.runtime.Pipe.doCall(Pipe.java:416)
        at org.apache.felix.gogo.runtime.Pipe.call(Pipe.java:229)
        at org.apache.felix.gogo.runtime.Pipe.call(Pipe.java:59)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (PORTAL_MEDIADOR_PRO_BACKUP.IX_103D6207) violated_ [Sanitized]
        at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9657)
        at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1405)
        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9442)

Environment

DXP 7.0+

解決策

この種のエラーは、 JournalArticleImage テーブルのデータ破損に関連している可能性があります。
表示されているエラーはOracle DDBBのものですが、同じ制約を持つ異なるDDBBサービスでも同様のエラーが表示されることがあります。
JournalArticleImages のデータ不整合には、このエラーを発生させるいくつかの種類があります。
これらの異なるエラーは、アップグレードを開始する前に、以下の2つのスクリプトを実行することで解決します。
DDBBを変更するためにGroovyスクリプトを起動するのはいつものことですが、事前にDDBBのバックアップを取得することを強くお勧めします。

スクリプトには、 _safeMode という変数があり、変更を加えずにスクリプトを実行することができます。 _safeMode でスクリプトを実行すると、スクリプトが生み出す変化を知り、それを確認するのに便利です。

スクリプト 1：

import com.liferay.portal.kernel.dao.jdbc.DataAccess;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.util.StringUtil;
import com.liferay.portal.kernel.util.Validator;
import com.liferay.portal.kernel.xml.Document;
import com.liferay.portal.kernel.xml.Element;
import com.liferay.portal.kernel.xml.Node;
import com.liferay.portal.kernel.xml.SAXReaderUtil;
import com.liferay.portal.kernel.xml.XPath;
import com.liferay.portlet.journal.service.JournalArticleImageLocalServiceUtil
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class RemoveUnusedJournalArticleImages {
 public void doRemove() throws Exception {
  _log.info("Starting RemoveUnusedJournalArticleImages process");
  if (_safeMode) {
      _log.info("Safe Mode Execution");
  }
  con = DataAccess.getConnection();
  PreparedStatement ps = null;
  ResultSet rs = null;

  try {
      ps = con.prepareStatement("select content, articleId, version, groupId from JournalArticle where content like ?")
      ps.setString(1, "%type=\"image\"%");
      rs = ps.executeQuery();
      while (rs.next()) {
          String content = rs.getString(1);
          String articleId = rs.getString(2);
          Double version = rs.getDouble(3);
          long groupId = rs.getLong(4);
          _log.info("Found journalArticles references in article " + articleId + " version " + version);
          List<Long> journalArticleImageIds = getJournalArticleImageIds(content)
          printImageIds(journalArticleImageIds)
          removeUnusedImages(journalArticleImageIds, groupId, articleId, version)
      }    
  }
  finally {
      con.close();
      if (ps != null) {
        ps.close();
      }
      if (rs != null) {
        rs.close();
      }
  }

  _log.info("Finishing RemoveUnusedJournalArticleImages process");
 }

 private List<Long> getJournalArticleImageIds(String content) throws Exception {
  Document document = SAXReaderUtil.read(content);
  XPath xPath = SAXReaderUtil.createXPath("//dynamic-element[@type='image']");
  List<Node> imageNodes = xPath.selectNodes(document);
  List<Long> journalArticleImageIds = new ArrayList()
  for (Node imageNode : imageNodes) {
      Element imageElement = (Element)imageNode;
      List<Element> dynamicContentElements = imageElement.elements("dynamic-content");
      String id = null;
      for (Element dynamicContentElement : dynamicContentElements) {
         id = dynamicContentElement.attributeValue("id");
         if (Validator.isNotNull(id)) {
            journalArticleImageIds.add(Long.parseLong(id))
         }
      }

  }
  return journalArticleImageIds      
 }

 private void printImageIds(List<Long> journalArticleImageIds) {
  if (journalArticleImageIds.size() == 0) {
      return;
  }
  String message = "The following journalArticleImageId references have been found: "
  for (long journalArticleImageId : journalArticleImageIds) {
      message += journalArticleImageId + ", "
  }
  _log.info(message.substring(0, message.length() - 2))
 }

 private void removeUnusedImages(List<Long> journalArticleImageIds, long groupId, String journalArticleId, Double version)
  throws Exception {
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
      ps = con.prepareStatement(
    "select articleImageId from JournalArticleImage where groupId = ? and articleId = ? and version = ?")
      ps.setLong(1, groupId);
      ps.setString(2, journalArticleId);
      ps.setDouble(3, version);
      rs = ps.executeQuery();
      while (rs.next()) {
         long articleImageId = Long.parseLong(rs.getString(1));
         if (!journalArticleImageIds.contains(articleImageId)) {
             _log.info("The image with id " + articleImageId + " is not referenced in the journal article content");
             if (!_safeMode) {
                JournalArticleImageLocalServiceUtil.deleteArticleImage(articleImageId)
               _log.info("The image with id " + articleImageId + " has been removed");
             }    
         }
      }
  }
  finally {
      if (ps != null) {
        ps.close();
      }
      if (rs != null) {
        rs.close();
      }
  }
}    

private static Connection con = null;
private static final boolean _safeMode = true;
private static Log _log = LogFactoryUtil.getLog(RemoveUnusedJournalArticleImages.class);
}

(new RemoveUnusedJournalArticleImages()).doRemove();

スクリプト 2：

import com.liferay.portal.kernel.dao.orm.DynamicQuery;
import com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portlet.journal.service.JournalArticleImageLocalServiceUtil;
import com.liferay.portlet.journal.model.JournalArticleImage;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.liferay.portal.util.PortalUtil;
import com.liferay.portal.kernel.dao.jdbc.DataAccess;

/////////////////////////////////////
boolean _safeMode = true; // change to false to make actual changes
/////////////////////////////////////

Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;

List<Long> articleImageIds = new ArrayList<Long>();

try{
    con = DataAccess.getConnection();
    sql = PortalUtil.transformSQL("select articleImageId from  JournalArticleImage where articleId in (select distinct articleId from JournalArticleImage where (elinstanceid is null or elinstanceid = ''))");
    ps = con.prepareStatement(sql);

    rs = ps.executeQuery();
    while (rs.next()) {

        long articleImageId = rs.getLong(1);
        articleImageIds.add(articleImageId);
    }
}catch(Exception e) {
    e.printStackTrace(out);
}
finally {
    DataAccess.cleanUp(con, ps, rs);
}

long totalToRemove = articleImageIds.size();
long totalRemoved = 0;

println "Value of _safeMode: " + _safeMode;

for (long articleImageId : articleImageIds) {

    try {
        // check that each article image id has duplicate entries
        JournalArticleImage jai = JournalArticleImageLocalServiceUtil.fetchJournalArticleImage(articleImageId);

        if (jai == null) {
            println "There's no Journal Article Image with articleImageId = " + articleImageId + ". Skipping.";
            continue;
        }

        DynamicQuery dq = JournalArticleImageLocalServiceUtil.dynamicQuery();
        dq.add(RestrictionsFactoryUtil.eq("groupId", jai.getGroupId()));
        dq.add(RestrictionsFactoryUtil.eq("articleId", jai.getArticleId()));
        dq.add(RestrictionsFactoryUtil.eq("version", jai.getVersion()));
        dq.add(RestrictionsFactoryUtil.eq("elName", jai.getElName()));
        dq.add(RestrictionsFactoryUtil.eq("languageId", jai.getLanguageId()));
        long totalJAIs = JournalArticleImageLocalServiceUtil.dynamicQueryCount(dq);

        if (totalJAIs == 1) {
            continue;
        }

        println "Journal Article Image to remove (articleImageId = " + articleImageId + ")";

        if (!_safeMode) {
            JournalArticleImageLocalServiceUtil.deleteArticleImage(articleImageId);
            totalRemoved++;
            println "Deleted Journal Article Image (articleImageId = " + articleImageId + ")";
        }

    } catch (SystemException e) {
        println "Problem deleting Journal Article Image (articleImageId = " + articleImageId + ")";
        e.printStackTrace();
    }
}

println "Summary ::: Journal Article Images removed: " + totalRemoved + "/" + totalToRemove;

アップグレード中のエラー "一意制約（PORTAL_MEDIADOR_PRO_BACKUP.IX_103D6207）に違反しました_"

問題

Environment

解決策