Oracle DB Text Columns Exceed 255 Character Limit on Import

Symptoms

1. When restoring a backup with over 255 characters in columns of the JIRAISSUE table some databases will truncate the data and successfully to restore the data. Oracle, however, will proceed to import the data and throw the below exception.
2. Certain areas of JIRA will validate a 255 character limit on entry of the summary field, however other areas will not, throwing the below exception as the database is providing the validation.
3. It can be possible to bypass the internal JIRA validation using leading and/or trailing white space.

The below error may be present within the JIRA logs:

ERROR n100123 581x142x1 hc92cw 144.136.126.43 /secure/admin/XmlRestore.jspa [jira.action.admin.OfbizImportHandler] Exception importing entity: org.of
biz.core.entity.GenericEntityException: while inserting: [GenericEntity:Issue][summary,Verify that Publisher will be able to insert accessible data tables for data and for layout, and control:
⢠Table properties, (number Rows & columns, Width (%, px), Horizontal alignment, Border colour, size, cell padding, cell spacing, Background col...][id,10344][project,10000][updated,2010-0422 14:04:37.0][created,2010-04-22 13:53:29.0][status,10000][workflowId,10777][votes,0][type,8][key,ABC-123] (SQL Exception while executing the following:INSERT INTO jiraissue (ID, pkey, PR
OJECT, REPORTER, ASSIGNEE, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (ORA-12899: value too large for column "JIRA"."J
IRAISSUE"."SUMMARY" (actual: 256, maximum: 255)))

Cause

In older versions of JIRA (4.2.1), when creating an issue, the summary field was not restricted to 255 characters and would allow users to exceed this limit. Validation on this field has been since corrected in more recent versions.

Original Description

I had a quick look at the JIRA DB schema and found that e.g. the table JIRAISSUE uses VARCHAR2(255) columns to store text attrbutes in an Oracle database.
Considering that you are supporting UTF-8 encoding through the web interface, you would probably want to change this mapping of Strings to use NVARCHAR2(255) instead, as the VARCHAR2 column type length is the number of bytes (not the number of chars - at least not with multibyte char sets like UTF-8). As opposed to the VARCHAR2 type, the NVARCHAR2 column type length is actually the number of chars - it was created for multibyte char sets.
Otherwise it's a bit hard to predict how many chars (when typing UTF-8 chars outside the ascii range) you can actually type in a field without the Oracle DB throwing an SQLException because the column length constraint was violated.