Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-74425

Possible "ORA-01795 maximum number of expressions in a list is 1000" error during UpgradeResourcePermission

    Details

      Description

      Description
      In LPS-73051, we modified the UpgradeResourcePermission logic to add an IN clause to a SQL query which consists of every unique primKey that does not parse to a number. This is very dangerous because it is likely that many production databases will have too many distinct primKeys that are not numbers, since production databases tend to have large numbers of instanceable portlets across many sites and pages, many of which may have their own set of resource permissions. Adding an IN clause that is too large causes errors with several databases.

      Steps to Reproduce
      1. Start up Liferay 6.2 on an Oracle database.
      2. Place over 1000 instances of instanceable portlets (such as the Asset Publisher portlet) on the page (you can spread them out across several pages if it makes the page easier to render).
      3. Assign each of these portlet instances permissions other than the default permissions.
      4. Shut down Liferay and upgrade to DXP.

      Expected Results: The upgrade completes successfully with no errors.
      Actual Results: The following exception is thrown during the upgrade, causing it to fail:

      com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:93)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:175)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:143)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:125)
      	at com.liferay.portal.events.StartupHelper.upgradeProcess(StartupHelper.java:164)
      	at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:81)
      	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:153)
      	at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:99)
      Caused by: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:93)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:117)
      	at com.liferay.portal.upgrade.UpgradeProcess_7_0_0.doUpgrade(UpgradeProcess_7_0_0.java:99)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90)
      	... 7 more
      Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
      	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
      	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
      	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
      	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
      	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
      	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
      	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
      	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
      	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
      	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
      	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
      	at com.liferay.portal.upgrade.v7_0_0.UpgradeResourcePermission._updatePrimKeyIds(UpgradeResourcePermission.java:120)
      	at com.liferay.portal.upgrade.v7_0_0.UpgradeResourcePermission.upgradeResourcePermissions(UpgradeResourcePermission.java:96)
      	at com.liferay.portal.upgrade.v7_0_0.UpgradeResourcePermission.doUpgrade(UpgradeResourcePermission.java:47)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90)
      	... 10 more
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                joyce.wang Joyce Wang
                Reporter:
                michael.bowerman Michael Bowerman (Inactive)
                Participants of an Issue:
                Recent user:
                Csaba Turcsan
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  1 year, 28 weeks, 5 days ago