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

Upgrading permissions for SO 6.2 to 7.0 causes large performance regression to verify process

    Details

      Description

      When attempting to execute the database upgrade for a client sample database going from 6.1 to 7.0, we got "stuck" in the following call:

      16:59:39,118 INFO  [main][LoggingTimer:70] Starting com.liferay.portal.verify.VerifyPermission#deleteConflictingUserDefaultRolePermissions
      

      Execution of the related query has been running for over 48 hours (and potentially 80 more to go), and therefor not reasonable for the upgrade.

      Query currently running:

      SELECT resourcePermission1.resourcePermissionId
      FROM ResourcePermission resourcePermission1
      INNER JOIN ResourcePermission resourcePermission2
      ON resourcePermission1.companyId = resourcePermission2.companyId
      AND resourcePermission1.name     = resourcePermission2.name
      AND resourcePermission1.scope    = resourcePermission2.scope
      AND resourcePermission1.primKey  = resourcePermission2.primKey
      INNER JOIN Layout
      ON resourcePermission1.companyId = Layout.companyId
      AND resourcePermission1.primKey LIKE REPLACE('[$PLID$]_LAYOUT_%', '[$PLID$]', CAST(Layout.plid AS VARCHAR(4000)))
      AND Layout.type_ = 'portlet'
      INNER JOIN Group_
      ON Layout.groupId                   = Group_.groupId
      WHERE resourcePermission1.companyId = 10154
      AND resourcePermission1.roleId      = 10164
      AND resourcePermission2.roleId      = 10165
      AND resourcePermission1.scope       = 4
      AND (Group_.classNameId             = 10005
      OR Group_.classNameId               = 10006)
      

      Attached are screen shots of query + execution plan, and upgrade log file.

        Attachments

        1. screenshot-1.png
          screenshot-1.png
          371 kB
        2. screenshot-2.png
          screenshot-2.png
          299 kB
        3. upgrade.log
          287 kB

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Days since last comment:
                  3 years, 14 weeks ago

                  Packages

                  Version Package
                  6.2.X EE
                  7.0.0 DXP FP2
                  7.0.0 DXP SP1
                  7.0.3 CE GA4
                  Master