-
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: Core Upgrade
-
Branch Version/s:7.3.x, 7.2.x, 7.1.x
-
Backported to Branch:Committed
-
Fix Priority:3
-
Git Pull Request:
Preconditions
- Setup an Oracle database as target database for an upgrade process.
- Lock table statistics for the tables in the database schema.
Steps to reproduce
- 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
- causes
-
COMMERCE-8229 WARN message during the upgrade from 73 on DB2 and Oracle databases when create index IX_DB83CD12
-
- Closed
-