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

alter_column_type command gets sent to DB2, causing numerous upgrade errors

    Details

      Description

      Description
      In our Compatibility Matrix, we claim to support DB2, yet during the upgrade process, we send many commands to DB2 that are invalid syntax for it. In particular, we send a lot of alter_column_type [tableName] [columnName] [newType] commands, which is invalid syntax for DB2. The correct syntax for DB2 would be alter table [tableName] alter column [columnName] set data type [newType].

      Steps to Reproduce

      1. Start up Liferay 6.2 connected to a DB2 database (I used DB2 10.5).
      2. Shut down Liferay.
      3. Use the DXP Upgrade tool to upgrade your DB2 database to DXP.
      4. Observe the logs as the upgrade moves forward.

      Expected Results: The upgrade would complete successfully with no errors.
      Actual Results: The upgrade completes, but many errors of the following nature appear in the logs:

      com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=alter_column_type RepositoryEntry;BEGIN-OF-STATEMENT;<create_variable>, DRIVER=4.19.66
              at com.ibm.db2.jcc.am.kd.a(kd.java:747)
              at com.ibm.db2.jcc.am.kd.a(kd.java:66)
              at com.ibm.db2.jcc.am.kd.a(kd.java:135)
              at com.ibm.db2.jcc.am.fp.c(fp.java:2788)
              at com.ibm.db2.jcc.am.fp.d(fp.java:2776)
              at com.ibm.db2.jcc.am.fp.b(fp.java:2143)
              at com.ibm.db2.jcc.t4.bb.i(bb.java:226)
              at com.ibm.db2.jcc.t4.bb.c(bb.java:48)
              at com.ibm.db2.jcc.t4.p.b(p.java:38)
              at com.ibm.db2.jcc.t4.vb.h(vb.java:124)
              at com.ibm.db2.jcc.am.fp.kb(fp.java:2138)
              at com.ibm.db2.jcc.am.fp.a(fp.java:3325)
              at com.ibm.db2.jcc.am.fp.c(fp.java:765)
              at com.ibm.db2.jcc.am.fp.executeUpdate(fp.java:744)
              at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120)
              at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
              at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:292)
              at com.liferay.portal.dao.db.DB2DB.runSQL(DB2DB.java:81)
              at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:262)
              at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:66)
              at com.liferay.portal.kernel.upgrade.UpgradeProcess.alter(UpgradeProcess.java:406)
              at com.liferay.portal.upgrade.v7_0_0.UpgradeRepositoryEntry.doUpgrade(UpgradeRepositoryEntry.java:27)
              at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90)
              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:98)
              at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90)
              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)
      

      This causes many columns to retain their old type instead of being given the new type that is necessary for DXP.

      Can't test in master because DB2 is not supported in master. However, the necessary logic to fix this issue appears to be missing from the DB2SQLTransformerLogic class in master, so we should commit the fix to master too (c8370cfc99afbd064a3905d0b4b5239b3766eca7).
      Reproduced in ee-7.0.x (f51c2a99cb6c9b5e7fd6dab46b73a322029c8faa)

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                2 years, 15 weeks ago

                Packages

                Version Package
                7.0.0 DXP FP16
                7.0.3 CE GA4
                7.0.0 DXP SP4
                7.0.X EE
                Master