Details

    Description

      Preconditions

      1. Setup an Oracle database as target database for an upgrade process.
      2. Lock table statistics for the tables in the database schema.

      Steps to reproduce

      1. Launch a database upgrade using Database Upgrade Tool.

      Expected behavior

      • No WARN type messages in the upgrade log as
        ...
        WARN  [main][UpgradeProcess:401] Attempting to upgrade table XYZ by recreating the table due to: ORA-38029: object statistics are locked
        ...
        
      • No UpgradeExceptions derived from that ORA-38029 error.

      Observed behavior

      • Upgrade log shows WARN type messages similar to
        ...
        WARN  [main][UpgradeProcess:401] Attempting to upgrade table XYZ by recreating the table due to: ORA-38029: object statistics are locked
        ...
        
      • UpgradeExceptions can happen derived from that ORA-38029 error. Example:
        WARN  [main][UpgradeProcess:401] Attempting to upgrade table AssetVocabulary by recreating the table due to: ORA-38029: object statistics are locked_ [Sanitized]
        INFO  [main][LoggingTimer:83] Starting com.liferay.portal.kernel.upgrade.UpgradeProcess#upgradeTable#AssetVocabulary
        INFO  [main][Table:160] Starting backup of AssetVocabulary to /tmp/temp-db-AssetVocabulary-7175727090129796799.tmp
        INFO  [main][Table:193] Finished backup of AssetVocabulary to /tmp/temp-db-AssetVocabulary-7175727090129796799.tmp in 204 ms
        INFO  [main][LoggingTimer:44] Completed com.liferay.portal.kernel.upgrade.UpgradeProcess#upgradeTable#AssetVocabulary in 2401 ms
        WARN  [main][UpgradeProcess:420] Successfully recreated and upgraded table AssetVocabulary
        INFO  [main][LoggingTimer:44] Completed com.liferay.portal.kernel.upgrade.UpgradeProcess#alter in 2422 ms
        INFO  [main][UpgradeProcess:115] Failed upgrade process com.liferay.portal.upgrade.v7_3_x.UpgradeAssetVocabulary in 2429 ms
        INFO  [main][UpgradeProcess:115] Failed upgrade process com.liferay.portal.upgrade.PortalUpgradeProcess in 101368 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:109)
        	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:109)
        	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:131)
        	at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:136)
        	at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:104)
        	... 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:550)
        	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
        	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:339)
        	at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:305)
        	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:104)
        	... 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
        

      Root cause
      The method java.sql.DatabaseMeta.data.getIndexInfo(...) uses a boolean parameter named
      approximate with following description:

      approximate - when true, result is allowed to reflect approximate or out of data values; when false, results are requested to be accurate
      

      Oracle implementation of this method, through Oracle JDBC driver, carries out a call to analyze table statistics when
      approximate boolean parameter value is false

      public synchronized ResultSet getIndexInfo(String string, String string2, String string3, boolean bl, boolean bl2) throws SQLException {
      ...
      ...
      ...
              if (!bl2) {
                  boolean bl3 = false;
                  try {
                      bl3 = this.connection.getTransactionState().contains((Object)OracleConnection.TransactionState.TRANSACTION_STARTED);
                  }
                  catch (SQLException var8_9) {
                      // empty catch block
                  }
                  if (!bl3) {
                      string4 = "analyze table " + (string2 == null ? "" : new StringBuilder().append(string2).append(".").toString()) + string3 + " compute statistics";
                      statement.executeUpdate(string4);
                  }
              }
      

      So, in case of a table has locked statistics the upgrade process will catch an SQLException caused by an ORA-38029 database error when the upgrade process tries to execute an alter table statement.

      Then, the upgrade process will follow another different execution flow trying to recreate the table using the original table structure: the altered attribute (p.e. A new column to be added) is missed.

      This can go unnoticed when there are not posterior dependencies based on that alter table statement, but it will cause future issues.

      But if there is a posterior dependency, the upgrade process will fail (see example log).

      So, the proposed solution is to check if database is Oracle for setting approximate boolean parameter value to true

      Attachments

        Issue Links

          Activity

            People

              summer.zhang Summer Zhang
              sergio.alonso Sergio Alonso
              Kiyoshi Lee Kiyoshi Lee
              Sergio Alonso Sergio Alonso
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                43 weeks, 5 days ago

                Packages

                  Version Package
                  7.1.X
                  7.2.10 DXP FP18
                  7.2.X
                  7.3.X
                  7.4.3.5 CE GA5
                  7.4.13 DXP U1
                  7.4.3.6 CE GA6
                  7.4.13 DXP U2
                  7.4.3.9 CE GA9
                  7.4.3.16 CE GA16
                  7.4.3.17 CE GA17
                  Master