Submit Feedback
Legacy Knowledge Base
Published Jul. 2, 2025

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

Written By

David Tello

How To articles are not official guidelines or officially supporteddocumentation. They are community-contributed content and may not alwaysreflect the latest updates to Liferay DXP. We welcome your feedback toimprove How to articles!

While we make every effort to ensure this Knowledge Base is accurate, itmay not always reflect the most recent updates or official guidelines.We appreciate your understanding and encourage you to reach out with anyfeedback or concerns.

Legacy Article

You are viewing an article from our legacy "FastTrack"publication program, made available for informational purposes. Articlesin this program were published without a requirement for independentediting or verification and are provided "as is" withoutguarantee.

Before using any information from this article, independently verify itssuitability for your situation and project.

NOTE: This article is an INTERNAL article and is not visible to customers, currently. Please only link this article in internal comments, but not public comments.

Issue

    • When upgrading is launched from Portal 6.2 to DXP 7.0+, an error as the following is shown:
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+

Resolution

  • This kind of error could be related to data corruption in the JournalArticleImage table.
    The error shown is from an Oracle DDBB, but similar errors can appear in different DDBB services with the same constraint.
  • There are several kinds of data inconsistencies with JournalArticleImages that produce this error.
  • These different errors can be solved if the following two scripts are executed before launching the upgrade.
  • As always that a Groovy script is launched to modify the DDBB, it is highly recommended to get a DDBB backup before.
  • The scripts have a variable called _safeMode that lets execute the script without making changes. Running the script with _safeMode can be helpful to know the changes that the scripts will produce and review them.
      • Script 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();
      • Script 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;

 

Did this article resolve your issue ?
Legacy Knowledge Base
Did this article resolve your issue ?