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

UpgradeAssetVocabulary fails with "VISIBILITYTYPE: invalid identifier" error if the table has to be dropped and recreated

    Details

      Description

      Description
      During the upgrade process, we have a number of "Alter table" operations, such as operations to add a new column to the table or change the type of an existing column.

      We attempt to do these operations by running the appropriate SQL statement. However, if an error is occurred when running the SQL, we instead perform a "backup plan", which is to drop and recreate the entire table.

      The "drop and recreate" logic retrieves the "createSQL" from the autogenerated *Table.java class, and it runs this SQL when creating the table.

      Unfortunately, the "createSQL" in the AssetVocabularyTable.java class is outdated. It does not include the "visibilityType" column. So, if we run this "drop and recreate" logic when altering the AssetVocabulary table, it will be recreated without the "visibilityType" column. This causes an error later on in the upgrade when we attempt to modify the value of this "visibilityType" column.

      This issue only affects 7.3.x and earlier, because in master we removed the "drop and recreate" logic as part of LPS-134196.

      Steps to Reproduce
      1. Set up an Oracle 19c database that has a "." character in the schema name (this will force the "Alter" statements during the upgrade to fail).
      2. Start up Liferay 6.2 connected to the Oracle 19c database.
      3. Navigate to Admin > Site Administration > Content > Categories > Add Vocabulary.
      4. Give the Vocabulary a name and click Save.
      5. Shut down Liferay.
      6. Upgrade to 7.3, observing the upgrade logs as the upgrade progresses.
      Expected Result: The upgrade would complete successfully.
      Actual Result: The upgrade fails with the following error:

      2022-02-25 01:05:28.293 INFO  [main][UpgradeProcess:116] Failed upgrade process com.liferay.portal.upgrade.v7_3_x.UpgradeAssetVocabulary in 268086 ms
      2022-02-25 01:05:28.293 INFO  [main][UpgradeProcess:116] Failed upgrade process com.liferay.portal.upgrade.PortalUpgradeProcess in 1047315 ms
      com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLSyntaxErrorException: ORA-00904: "VISIBILITYTYPE": invalid identifier
      
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:110)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:172)
      	at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:135)
      	at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:182)
      	at com.liferay.portal.tools.DBUpgrader._upgradePortal(DBUpgrader.java:340)
      	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:157)
      	at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:149)
      	at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:128)
      	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 com.liferay.portal.tools.db.upgrade.client.DBUpgraderLauncher.main(DBUpgraderLauncher.java:54)
      Caused by: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLSyntaxErrorException: ORA-00904: "VISIBILITYTYPE": invalid identifier
      
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:110)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:132)
      	at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:136)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:105)
      	... 12 more
      Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "VISIBILITYTYPE": invalid identifier
      
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
      	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
      	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
      	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
      	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
      	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
      	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
      	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
      	at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1747)
      	at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1712)
      	at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1699)
      	at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:285)
      	at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:117)
      	at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
      	at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:365)
      	at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:331)
      	at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:71)
      	at com.liferay.portal.upgrade.v7_3_x.UpgradeAssetVocabulary.doUpgrade(UpgradeAssetVocabulary.java:32)
      	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:105)
      	... 15 more
      Caused by: Error : 904, Position : 27, Sql = update AssetVocabulary set visibilityType = 0, OriginalSql = update AssetVocabulary set visibilityType = 0, Error Msg = ORA-00904: "VISIBILITYTYPE": invalid identifier
      
      	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
      	... 35 more
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              support-lep@liferay.com SE Support
              Reporter:
              michael.bowerman Michael Bowerman
              Participants of an Issue:
              Recent user:
              Maria Muriel
              Engineering Assignee:
              Michael Bowerman
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since last comment:
                9 weeks, 2 days ago

                  Packages

                  Version Package
                  7.3.X