Data truncation due to data too long for column
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
症状
- 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.
atlassian-jira.log に次のメッセージが表示される。
...
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))
...原因
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-13329 - Error when adding a group with name longer than 60 characters to a notification scheme: too long for database column
- JRASERVER-19990 - Data truncation error when entered a long URL custom field which breaks the Move Issue process
- JRASERVER-24526 - Upgrading to JIRA 4.3 fails if connecting to Crowd, and a directory description is longer than 256 characters.
- 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
回避策
データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
The columns within the database could be altered with the following SQL. This may require changes depending on your DBMS.
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:
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:
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:
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.
ソリューション
Use shorter LDAP filters or group names.
Please see our Troubleshooting LDAP User Management documentation for further assistance with diagnosing LDAP problems.