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

Oracle 18c migration from 7.4.3-GA3 to 7.4.3.18-GA18 triggers ORA-22858 #12

Closed xistoso closed 2 years ago

xistoso commented 2 years ago

Looks like ORA-22858 is no longer trigering recriation of tables and instead throws exception and stops upgrade.

2022-04-05 18:06:20.387 INFO  [main][UpgradeProcess:114] Upgrading com.liferay.portal.upgrade.v7_4_x.UpgradeLayout
2022-04-05 18:06:20.448 INFO  [main][UpgradeProcess:129] Failed upgrade process com.liferay.portal.upgrade.v7_4_x.UpgradeLayout in 61 ms
2022-04-05 18:06:20.449 INFO  [main][PortalUpgradeProcess:161] Failed upgrade process com.liferay.portal.upgrade.PortalUpgradeProcess in 2048 ms
2022-04-05 18:06:20.470 ERROR [main][DBUpgrader:79] null
com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLException: ORA-22858: invalid alteration of datatype_ [Sanitized]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:155) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:173) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:136) ~[portal-kernel.jar:?]
        at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:154) ~[portal-impl.jar:?]
        at com.liferay.portal.tools.DBUpgrader._upgradePortal(DBUpgrader.java:386) ~[portal-impl.jar:?]
        at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:162) ~[portal-impl.jar:?]
        at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:154) ~[portal-impl.jar:?]
        at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:130) [portal-impl.jar:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_312]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_312]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_312]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_312]
        at com.liferay.portal.tools.db.upgrade.client.DBUpgraderLauncher.main(DBUpgraderLauncher.java:54) [com.liferay.portal.tools.db.upgrade.client.jar:?]
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLException: ORA-22858: invalid alteration of datatype_ [Sanitized]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:123) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:145) ~[portal-kernel.jar:?]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:176) ~[portal-impl.jar:?]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:150) ~[portal-impl.jar:?]
        ... 12 more
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: java.sql.SQLException: ORA-22858: invalid alteration of datatype_ [Sanitized]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:540) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:256) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:887) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1136) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1678) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1643) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1630) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:282) ~[ojdbc8.jar:18.15.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:481) ~[portal-impl.jar:?]
        at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:447) ~[portal-impl.jar:?]
        at com.liferay.portal.dao.db.BaseDB.alterColumnType(BaseDB.java:213) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.dao.db.BaseDBProcess.alterColumnType(BaseDBProcess.java:203) ~[portal-kernel.jar:?]
        at com.liferay.portal.upgrade.v7_4_x.UpgradeLayout.doUpgrade(UpgradeLayout.java:29) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.lambda$upgrade$0(UpgradeProcess.java:117) ~[portal-kernel.jar:?]
        at com.liferay.portal.db.partition.DBPartitionUtil.forEachCompanyId(DBPartitionUtil.java:118) ~[portal-impl.jar:?]
        at com.liferay.portal.dao.db.BaseDB.process(BaseDB.java:424) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.dao.db.BaseDBProcess.process(BaseDBProcess.java:307) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:104) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:145) ~[portal-kernel.jar:?]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:176) ~[portal-impl.jar:?]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:150) ~[portal-impl.jar:?]
        ... 12 more
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: Error : 22858, Position : 26, Sql = alter table Layout modify title clob, OriginalSql = alter table Layout modify title clob, Error Msg = ORA-22858: invalid alteration of datatype_ [Sanitized]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:540) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:256) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:887) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1136) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1678) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1643) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1630) ~[ojdbc8.jar:18.15.0.0.0]
        at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:282) ~[ojdbc8.jar:18.15.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:481) ~[portal-impl.jar:?]
        at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:447) ~[portal-impl.jar:?]
        at com.liferay.portal.dao.db.BaseDB.alterColumnType(BaseDB.java:213) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.dao.db.BaseDBProcess.alterColumnType(BaseDBProcess.java:203) ~[portal-kernel.jar:?]
        at com.liferay.portal.upgrade.v7_4_x.UpgradeLayout.doUpgrade(UpgradeLayout.java:29) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.lambda$upgrade$0(UpgradeProcess.java:117) ~[portal-kernel.jar:?]
        at com.liferay.portal.db.partition.DBPartitionUtil.forEachCompanyId(DBPartitionUtil.java:118) ~[portal-impl.jar:?]
        at com.liferay.portal.dao.db.BaseDB.process(BaseDB.java:424) ~[portal-impl.jar:?]
        at com.liferay.portal.kernel.dao.db.BaseDBProcess.process(BaseDBProcess.java:307) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:104) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:145) ~[portal-kernel.jar:?]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:176) ~[portal-impl.jar:?]
        at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:150) ~[portal-impl.jar:?]
        ... 12 more
amusarra commented 2 years ago

I don't see any Oracle driver classes I developed on the stacktrace. From the error I see that it is a SQL problem due to the upgrade process, I guess it is invalid SQL.

Have you tried looking for this error on Liferay issues?

As soon as possible I will try to do some tests.

xistoso commented 2 years ago

Ty for your time and for the help. I'm also checking one version at a time. With the setting

database.string.index.max.length[oracle]=1000

Currently from GA3 to GA6 with no problem. Will report first version that breaks. Or if i messed something up.

amusarra commented 2 years ago

Hi @xistoso The upgrade process that migrates the title to TEXT (clob) was introduced in version 7.4 GA4. So if the upgrade went well from GA3 to GA6, it is strange that Alter's SQL error went from GA3 to GA18.

On the GA18, Liferay changed only the NewAPI for altering table https://github.com/liferay/liferay-portal/blob/604ff3c0ca5654302248ef321f4385d66e3f1646/portal-impl/src/com/liferay/portal/upgrade/v7_4_x/UpgradeLayout.java#L28

For more info you can read this issue https://issues.liferay.com/browse/LPS-135254

Let me know

xistoso commented 2 years ago

Hi @amusarra ,

Today i have confirmed that if i migrate 7.4 GA3 -> GA6 -> GA9 -> GA12 -> GA15 -> GA16 -> GA18 It goes well. I'm trying to figure out why a GA3 -> GA18 or 7.3.7 GA8 -> GA16 is not working. Fortunatly i have a DB backup that i can recreate with docker in 10min to test things out with a 6GB DB with live data. Will try more combination tomorow to understand where from 7.4 it starts to break. And i guess it is not related with this connector. If i find some correlation i will open an LPS.

Thank you for your time. And thank you for your work building this connector.

xistoso commented 2 years ago

Continuing with the findings That is now a GA9 -> GA18

2022-04-07 10:31:42.732 INFO  [main][UpgradeProcess:114] Upgrading com.liferay.dynamic.data.mapping.internal.upgrade.v5_1_3.FieldNameUpgradeProcess
2022-04-07 10:31:42.774 INFO  [main][UpgradeProcess:129] Failed upgrade process com.liferay.dynamic.data.mapping.internal.upgrade.v5_1_3.FieldNameUpgradeProcess in 42 ms
2022-04-07 10:31:42.802 ERROR [main][UpgradeStepRegistratorTracker:221] Failed upgrade process for module com.liferay.dynamic.data.mapping.service
com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLException: ORA-22858: invalid alteration of datatype_ [Sanitized]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:123) ~[portal-kernel.jar:?]
        at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:141) ~[portal-kernel.jar:?]

I can confirm some where between GA10 and GA18 Liferay lost the ability to recreate the tables on ORA-22858 exceptions. This is what it should have done:

2022-04-06 17:07:30.303 INFO  [main][UpgradeProcess:119] Upgrading com.liferay.portal.upgrade.v7_4_x.UpgradeAssetEntryTitle
2022-04-06 17:07:30.313 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.kernel.upgrade.UpgradeProcess#alter
2022-04-06 17:07:32.945 WARN  [main][UpgradeProcess:392] Attempting to upgrade table AssetEntry by recreating the table due to: ORA-22858: invalid alteration of datatype_ [Sanitized]
2022-04-06 17:07:32.945 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.kernel.upgrade.UpgradeProcess#upgradeTable#AssetEntry
2022-04-06 17:07:32.948 INFO  [main][Table:164] Starting backup of AssetEntry to /tmp/temp-db-AssetEntry-8879080395973985526.tmp
2022-04-06 17:07:57.646 INFO  [main][Table:198] Finished backup of AssetEntry to /tmp/temp-db-AssetEntry-8879080395973985526.tmp in 24697 ms
xistoso commented 2 years ago

i think i found the commit: LR Github Commit

amusarra commented 2 years ago

You could try to open the issue to Liferay.

amusarra commented 2 years ago

Hi @xistoso Let me know if I can close this issue.

Thanks.

xistoso commented 2 years ago

Hi @amusarra Sure it can be closed, it is a Liferay problem and i can confirm Oracle 10g and 18c is working with 7.4.3.18-GA18. Just make sure you upgrade first to GA13 ;)

Also index IX_7CDD4FB0 issue #11 is working well with our environment where we use a 2byte charset only.

Thank you for this connector.