ORA-38029 when upgrading database

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.3.X, Master
• Fix Version/s: 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
• Component/s: Database Upgrade Framework
• Labels:
  • ee-ts
  • liferay-fixpack-dxp-18-7210
  • liferay-fixpack-dxp-27-7110
  • liferay-fixpack-dxp-4-7310
  • liferay-update-dxp-1-7413
  • liferay-update-dxp-4-7310
  • liferay-update-dxp-5-7413
  • pt-upgrade-review
• Branch Version/s:
  7.3.x, 7.2.x, 7.1.x
• Backported to Branch:
  Committed
• Fix Priority: 3
• Git Pull Request: https://github.com/liferay-upgrades/liferay-portal/pull/163
• Bug Type: Database Upgrade

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