Liferay 7.2升级至7.4数据库失败：bit与boolean类型匹配错误

问题场景 #

尝试将Liferay 7.2数据库升级到7.4，最初发现release_表的verified列类型为bit，按提示改为boolean后，升级仍失败，报错如下：

错误日志 #

2024-05-23 10:12:50.316 INFO  [main][PortalUpgradeProcess:335] 升级流程 com.liferay.portal.upgrade.PortalUpgradeProcess 失败，耗时243毫秒
2024-05-23 10:12:50.327 ERROR [main][DBUpgrader:70] null
com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: 错误：不存在操作符：bit = boolean
提示：没有匹配给定名称和参数类型的操作符。您可能需要添加显式类型转换。
位置：143 [已脱敏]
    at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:329) ~[portal-impl.jar:?]
    at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:172) ~[portal-kernel.jar:?]
    at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:148) ~[portal-kernel.jar:?]
    at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:160) ~[portal-impl.jar:?]
    at com.liferay.portal.tools.DBUpgrader.upgradePortal(DBUpgrader.java:296) ~[portal-impl.jar:?]
    at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:166) [portal-impl.jar:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
    at com.liferay.portal.tools.db.upgrade.client.DBUpgraderLauncher.main(DBUpgraderLauncher.java:45) [com.liferay.portal.tools.db.upgrade.client.jar:?]
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: com.liferay.portal.kernel.upgrade.UpgradeException: org.postgresql.util.PSQLException: 错误：不存在操作符：bit = boolean
提示：没有匹配给定名称和参数类型的操作符。您可能需要添加显式类型转换。
位置：143 [已脱敏]
    at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:128) ~[portal-kernel.jar:?]
    at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:146) ~[portal-kernel.jar:?]
    at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:350) ~[portal-impl.jar:?]
    at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:324) ~[portal-impl.jar:?]
    ... 10 more
Caused by: com.liferay.portal.kernel.log.LogSanitizerException: org.postgresql.util.PSQLException: 错误：不存在操作符：bit = boolean
提示：没有匹配给定名称和参数类型的操作符。您可能需要添加显式类型转换。
位置：143 [已脱敏]
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) ~[postgresql.jar:42.4.3]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) ~[postgresql.jar:42.4.3]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) ~[postgresql.jar:42.4.3]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) ~[postgresql.jar:42.4.3]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) ~[postgresql.jar:42.4.3]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) ~[postgresql.jar:42.4.3]
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) ~[postgresql.jar:42.4.3]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[hikaricp.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[hikaricp.jar:?]
    at com.liferay.portal.upgrade.v7_4_x.UpgradeRegion.doUpgrade(UpgradeRegion.java:42) ~[portal-impl.jar:?]
    at com.liferay.portal.kernel.upgrade.UpgradeProcess.lambda$upgrade$0(UpgradeProcess.java:122) ~[portal-kernel.jar:?]
    at com.liferay.portal.db.partition.util.DBPartitionUtil.forEachCompanyId(DBPartitionUtil.java:178) ~[portal-impl.jar:?]
    at com.liferay.portal.dao.db.BaseDB.process(BaseDB.java:465) ~[portal-impl.jar:?]
    at com.liferay.portal.kernel.dao.db.BaseDBProcess.process(BaseDBProcess.java:378) ~[portal-kernel.jar:?]
    at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:107) ~[portal-kernel.jar:?]
    at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:146) ~[portal-kernel.jar:?]
    at com.liferay.portal.upgrade.PortalUpgradeProcess.doUpgrade(PortalUpgradeProcess.java:350) ~[portal-impl.jar:?]
    at com.liferay.portal.upgrade.PortalUpgradeProcess.upgrade(PortalUpgradeProcess.java:324) ~[portal-impl.jar:?]
    ... 10 more
2024-05-23 10:12:50.345 ERROR [main][UpgradeRecorder:194] 无法检查升级结果，原因是null。请手动检查。
2024-05-23 10:12:50.364 INFO  [main][UpgradeRecorder:154] 主升级流程完成，结果为失败

Liferay升级流程失败，耗时35秒
因升级失败或未完成，正在连接Gogo shell
门户升级失败。修复问题后重试。

解决思路 #

从报错来看，问题出在UpgradeRegion升级步骤中，仍有bit类型字段与boolean类型进行比较的情况。除了release_表的verified列，还需要检查以下内容：

• 检查Region_表中是否存在bit类型的布尔字段，将其转换为PostgreSQL的boolean类型
• 若数据库中存在其他自定义bit类型字段，确认是否需要转换，或在升级前通过SQL添加显式类型转换的逻辑
• 验证PostgreSQL驱动版本是否与Liferay 7.4兼容（当前使用的postgresql.jar为42.4.3，建议使用Liferay官方推荐的适配版本）

内容的提问来源于stack exchange，提问作者Akash