Data truncation due to data too long for column
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
Symptoms
When you're upgrading a JIRA application, a data truncation error may be encountered.
After configuring a User Directory, synchronization fails with truncation errors.
When adding a group to a Notification, this error will occur.
The following appears in the atlassian-jira.log:
1
2
3
...
Caused by: com.atlassian.crowd.embedded.ofbiz.db.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:DirectoryAttribute][directoryId,10100][name,ldap.user.filter][value,(&(objectclass=unicornRider)(!(ou:dn:=Guardians of The Globe))(|(memberof=cn=Lightning Strike Thunderdome,ou=Groups,ou=The Avengers)(memberof=cn=jiradmins,ou=Groups,ou=The Avengers)(memberof=cn=strikeforceone,ou=Groups,ou=The Avengers)(memberof=cn=UAT,ou=Groups,ou=The Avengers)(memberof=cn=myspace,ou=Groups,ou=The Avengers))] (SQL Exception while executing the following:INSERT INTO cwd_directory_attribute (directory_id, attribute_name, attribute_value) VALUES (?, ?, ?) (Data truncation: Data too long for column 'attribute_value' at row 1))
...Cause
Most description columns in JIRA applications database have a maximum column width of 255 characters. For example, cwd_group.description or cwd_directory_attribute.attribute_value. Attempting to add a value greater than that column width will result in truncation errors. There are several bugs that have been raised for this behaviour within Jira applications:
JRASERVER-36979 - User/group Directory filter throws a 'value too long' error when exceeding 255 characters (fixed in 8.0 and later)
And an Improvement request has been raised to add this restriction to the GUI: JRASERVER-28805 - Prevent data from exceeding the database limitations when configuring User Directories
Workaround
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.
The columns within the database could be altered with the following SQL. This may require changes depending on your DBMS.
1
2
3
alter table cwd_group modify description varchar (400);
alter table cwd_group modify lower_description varchar (400);
alter table cwd_directory_attribute modify attribute_value varchar (400);If you're using PostgreSQL, it will be:
1
2
3
alter table cwd_group alter column description type varchar (400);
alter table cwd_group alter column lower_description type varchar (400);
alter table cwd_directory_attribute alter column attribute_value type varchar (400);If you're using MS SQL, it will be:
1
2
3
alter table cwd_group alter column description type varchar (400);
alter table cwd_group alter column lower_description type varchar (400);
alter table cwd_directory_attribute alter column attribute_value type varchar (400);It is indeed possible that the number "(400)" may not be large enough for some instances. Adjust the number as necessary if the error occurs again even with the initial increase.
You can quickly check the changes by running the following SQL:
1
select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'myTable'Making changes to the database is not something we recommend and can potentially lead to additional problems. If you ever migrate to another database the data truncation problems will occur again unless addressed in the new database.
Resolution
Use shorter LDAP filters or group names.
ℹ️Please see our Troubleshooting LDAP User Management documentation for further assistance with diagnosing LDAP problems.
Was this helpful?