[LPS-141855] ORA-38029 when upgrading database - Liferay Issues

XML

Word

Printable

Details

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
Labels:

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

Description

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

Attachments

Issue Links

causes

COMMERCE-8229 WARN message during the upgrade from 73 on DB2 and Oracle databases when create index IX_DB83CD12

Closed

Activity

People

Assignee:: Summer Zhang

Reporter:: Sergio Alonso

Participants of an Issue:: Sam Ziemer, Sergio Alonso, Summer Zhang

Recent user:: Rafaela Nascimento

Engineering Assignee:: Sergio Alonso

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 29/Oct/21 5:23 AM

Updated:: 18/May/22 1:56 PM

Resolved:: 27/Dec/21 1:26 PM

Days since last comment:: 17 weeks, 4 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