-
Bug
-
Resolution: Unresolved
-
Medium
-
9.1.0, 9.2.0, 9.2.1, 9.3.1
-
14
-
Severity 3 - Minor
-
53
-
Issue Summary
Confluence upgrade fails on Oracle db that using VARCHAR2 when executing label upgrade task with error message:
character set mismatch`
or below error if the type is of different length compared to 255
value too large for column "OWNER_NOT_NULL" (actual: 1020, maximum: 510)
Steps to Reproduce
- Create a confluence instance with version (< 9.1) and Oracle db
- Set `Label` table `OWNER` column and all its foreign key columns with type `
VARCHAR2(255)` - Upgrade confluence to version (>= 9.1) which means to trigger the
LabelUniqueIndexUpgradeTask
Expected Results
Upgrade should success without any issue
Actual Results
The below exception is thrown in the xxxxxxx.log file:
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@2d609342 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [alter table LABEL add (OWNER_NOT_NULL NVARCHAR2(255) GENERATED ALWAYS AS (COALESCE(OWNER, CAST('' AS NVARCHAR2(255)))) VIRTUAL)]; SQL state [72000]; error code [12704]; ORA-12704: character set mismatch
at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:302) at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:258) at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runAllUpgradeTasks(AbstractUpgradeManager.java:177) at com.atlassian.confluence.upgrade.AbstractUpgradeManager.upgrade(AbstractUpgradeManager.java:140) at com.atlassian.confluence.plugin.PluginFrameworkContextListener.launchUpgrades(PluginFrameworkContextListener.java:127) at com.atlassian.confluence.plugin.PluginFrameworkContextListener.contextInitialized(PluginFrameworkContextListener.java:83) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4003) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:4446) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:164) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:599) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:571) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:603) at org.apache.catalina.startup.HostConfig.manageApp(HostConfig.java:1751) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:569) at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:264) at java.management/com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:814) at java.management/com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:802) at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:453) at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:407) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:569) at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:264) at java.management/com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:814) at java.management/com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:802) at java.management/com.sun.jmx.remote.security.MBeanServerAccessController.invoke(MBeanServerAccessController.java:472) at java.management.rmi/javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1472) at java.management.rmi/javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1310) at java.base/java.security.AccessController.doPrivileged(AccessController.java:712) at java.management.rmi/javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1412) at java.management.rmi/javax.management.remote.rmi.RMIConnectionImpl.invoke(RMIConnectionImpl.java:829) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:569) at java.rmi/sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:360) at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:200) at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:197) at java.base/java.security.AccessController.doPrivileged(AccessController.java:712) at java.rmi/sun.rmi.transport.Transport.serviceCall(Transport.java:196) at java.rmi/sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:587) at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:828) at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:705) at java.base/java.security.AccessController.doPrivileged(AccessController.java:399) at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:704) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840)Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [alter table LABEL add (OWNER_NOT_NULL NVARCHAR2(255) GENERATED ALWAYS AS (COALESCE(OWNER, CAST('' AS NVARCHAR2(255)))) VIRTUAL)]; SQL state [72000]; error code [12704]; ORA-12704: character set mismatch
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1549) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:444) at com.atlassian.confluence.upgrade.ddl.HibernateDdlExecutor.executeDdlStatements(HibernateDdlExecutor.java:92) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:569) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
Workaround
As described in CONFSERVER-54048, confluence is using NVARCHAR2 instead of VARCHAR2 as default char set since 5.9. So one possible way is to update all columns that still using `VARCHAR2` to `NVARCHAR2`
It is recommended to contact support to evaluate this approach before doing any column type changes. So you do backup and also understand the effort it will cost based on your db size as it may take very long time to execute.
Versions Tested
Please confirm all versions that have been tested for this issue, and indicate whether the tested version is affected or not affected, below:
| Testing Requirements | Version | Affected Version |
|---|---|---|
| Customers Reported Version | 9.2.0 | Yes |
| Most Recent Bug-Fix Release | 9.1.0 | Yes |
| Previous Major Release | ||
| Most Recent LTS | 9.2.0 | Yes |
| Previous Supported LTS | 8.5 | No |
| Other Versions.. | ||
| (Add rows as needed) |
- is related to
-
-
- Gathering Impact
-
- relates to
-
-
- Closed
-
-
CONFSERVER-51935 Implement Oracle database schema to accept VARCHAR2 datatype for Confluence database column.
- Gathering Interest
- was cloned as
-
-
- Closed
-
(3 mentioned in)
We see also this Problem.