search

amusarra / liferay-portal-database-all-in-one-support

This is the driver implementation to add support for Oracle, SQL Server and DB2 database for Liferay 7, 7.1, 7.2, 7.3 and 7.4 CE
http://bit.ly/2IQ6X4n
GNU Lesser General Public License v2.1
5 stars 7 forks source link

Upgrade process error on create unique index IX_7CDD4FB0 #11

Closed amusarra closed 1 year ago

amusarra commented 2 years ago

At the first start-up of the Liferay 7.4 GA6 portal using the driver version 1.2.1, you get the error reported below. The error is triggered by an Upgrade Process on the RemoteAppEntry table.

The same problem with Liferay 7.4 GA5, GA4

Caused by: com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: Bundle com.liferay.remote.app.service_2.0.15 [1115] has invalid content in indexes.sql:_create unique index IX_7CDD4FB0 on RemoteAppEntry (companyId, iFrameURL[$COLUMN_LENGTH:4000$]);_create index IX_5F8F9C11 on RemoteAppEntry (uuid_[$COLUMN_LENGTH:75$], companyId); [Sanitized]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep._upgrade(InitialUpgradeExtender.java:311) ~[?:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep.lambda$upgrade$0(InitialUpgradeExtender.java:236) ~[?:?]
    at com.liferay.portal.db.partition.DBPartitionUtil.forEachCompanyId(DBPartitionUtil.java:114) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.process(BaseDB.java:308) ~[portal-impl.jar:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep.upgrade(InitialUpgradeExtender.java:225) ~[?:?]
    ... 148 more
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: java.sql.SQLException: ORA-01450: maximum key length (6398) exceeded_ [Sanitized]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1747) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1712) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1699) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:285) ~[ojdbc8.jar:19.3.0.0.0]
    at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:117) ~[hikaricp.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[hikaricp.jar:?]
    at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:349) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:315) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.runSQLTemplateString(BaseDB.java:496) ~[portal-impl.jar:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep._upgrade(InitialUpgradeExtender.java:307) ~[?:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep.lambda$upgrade$0(InitialUpgradeExtender.java:236) ~[?:?]
    at com.liferay.portal.db.partition.DBPartitionUtil.forEachCompanyId(DBPartitionUtil.java:114) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.process(BaseDB.java:308) ~[portal-impl.jar:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep.upgrade(InitialUpgradeExtender.java:225) ~[?:?]
    ... 148 more
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: Error : 1450, Position : 35, Sql = create unique index IX_7CDD4FB0 on RemoteAppEntry (companyId, iFrameURL), OriginalSql = create unique index IX_7CDD4FB0 on RemoteAppEntry (companyId, iFrameURL), Error Msg = ORA-01450: maximum key length (6398) exceeded_ [Sanitized]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1747) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1712) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1699) ~[ojdbc8.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:285) ~[ojdbc8.jar:19.3.0.0.0]
    at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:117) ~[hikaricp.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[hikaricp.jar:?]
    at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:349) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:315) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.runSQLTemplateString(BaseDB.java:496) ~[portal-impl.jar:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep._upgrade(InitialUpgradeExtender.java:307) ~[?:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep.lambda$upgrade$0(InitialUpgradeExtender.java:236) ~[?:?]
    at com.liferay.portal.db.partition.DBPartitionUtil.forEachCompanyId(DBPartitionUtil.java:114) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.process(BaseDB.java:308) ~[portal-impl.jar:?]
    at com.liferay.portal.spring.extender.internal.upgrade.InitialUpgradeExtender$InitialUpgradeStep.upgrade(InitialUpgradeExtender.java:225) ~[?:?]
    ... 148 more

For more info about Oracle error, read this Oracle post How to Fix ORA-01450: Maximum Key Length (6398) Exceeded Errors

The SQL statement that generates the error is the following.

create unique index IX_7CDD4FB0 on RemoteAppEntry (companyId, iFrameURL)

I found the Oracle side workaround, which consists of using the 32k Buffer Cache and created the tablespace using the 32k block. Following are the SQL statements to be executed also via SQL as sysdba.

-- Set 32k db cache size
alter system set db_32k_cache_size=32M scope=both;

-- Create tablespace (32k blocksize) for schema Liferay
CREATE TABLESPACE liferay_data_32k logging DATAFILE
'/u01/app/oracle/oradata/ORCLCDB/liferay_data_32k.dbf' SIZE 64m
autoextend ON NEXT 32m maxsize 4096m blocksize 32k EXTENT management local;

-- Create user for new schema Liferay Dev with 32k tablespace
CREATE USER lportal IDENTIFIED BY lportal DEFAULT TABLESPACE
liferay_data_32k TEMPORARY TABLESPACE liferay_temp PROFILE
DEFAULT account unlock;

Index created successfully after applying workaround on Oracle.

index_on_remoteappentry

I created an issue related to this issue on Liferay as well Upgrade process error on create unique index IX_7CDD4FB0 on Oracle 19c

amusarra commented 1 year ago

Close this issue because the issue Liferay close https://liferay.atlassian.net/browse/LPS-145019