Jira is unable to startup due to SQLServerException: String or binary data would be truncated
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Jira doesn't start post-migrating to a new infrastructure due to SQL Server Exception.
Environment
JIRA Software 9.4.1
Diagnosis
From the application logs, an error similar to below is observed while starting up Jira
error trace
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2023-02-21 13:06:49,749+0100 main INFO [c.a.jira.startup.DefaultJiraLauncher] Stopping launchers
2023-02-21 13:06:49,757+0100 main ERROR [o.a.c.c.C.[Catalina].[localhost].[/]] Exception sending context destroyed event to listener instance of class [com.atlassian.jira.startup.LauncherContextListener]
java.lang.IllegalStateException: : interface com.atlassian.jira.cluster.ClusterManager
...
Caused by: org.ofbiz.core.entity.GenericEntityException: while updating: [GenericEntity:ClusterNode][nodeState,ACTIVE][ip,xxxxxxxxxxxxxxxx.xxxxxx-xxxxxx-x.x.xxxx-xxxx-xxxx-xxxxx-xxxx-xxxxx.xxxxxxx][cacheListenerPort,40001][nodeVersion,9.4.1][nodeId,xxxxxxxx ][nodeBuildNumber,940001][timestamp,1676981063784] (SQL Exception while executing the following:UPDATE dbo.clusternode SET NODE_STATE=?, TIMESTAMP=?, IP=?, CACHE_LISTENER_PORT=?, NODE_BUILD_NUMBER=?, NODE_VERSION=? WHERE NODE_ID=? (String or binary data would be truncated.))
at org.ofbiz.core.entity.GenericDAO.singleUpdate(GenericDAO.java:364)
at org.ofbiz.core.entity.GenericDAO.customUpdate(GenericDAO.java:286)
at org.ofbiz.core.entity.GenericDAO.update(GenericDAO.java:266)
at org.ofbiz.core.entity.GenericDAO.update(GenericDAO.java:236)
at org.ofbiz.core.entity.GenericHelperDAO.store(GenericHelperDAO.java:257)
at org.ofbiz.core.entity.GenericDelegator.store(GenericDelegator.java:1725)
at org.ofbiz.core.entity.GenericValue.store(GenericValue.java:118)
at com.atlassian.jira.cluster.OfBizClusterNodeStore.storeFieldMap(OfBizClusterNodeStore.java:115)
... 118 more
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:UPDATE dbo.clusternode SET NODE_STATE=?, TIMESTAMP=?, IP=?, CACHE_LISTENER_PORT=?, NODE_BUILD_NUMBER=?, NODE_VERSION=? WHERE NODE_ID=? (String or binary data would be truncated.)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:570)
at org.ofbiz.core.entity.GenericDAO.singleUpdate(GenericDAO.java:358)
... 126 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
Cause
This kind of issue happens when the hostname of the instance is more than 60 characters. The current DDL for clusternode is:
clusternode ddl
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE public.clusternode (
node_id varchar(60) NOT NULL,
node_state varchar(60) NULL,
"timestamp" numeric(18) NULL,
ip varchar(60) NULL,
cache_listener_port numeric(18) NULL,
node_build_number numeric(18) NULL,
node_version varchar(60) NULL,
CONSTRAINT pk_clusternode PRIMARY KEY (node_id)
);
As evident from the length of node_id column, it only accepts 60 characters by default. Anything above that will cause the insertion to fail and won't let the application start.
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
If changing the hostname is not possible, one can update the node_id column to accept more than 60 characters.
1 2ALTER TABLE clusternode ALTER COLUMN node_id VARCHAR(120);Restart Jira.
Was this helpful?