Some indexes in JIRA have a maximum value bigger than 900 bytes which is not recommend in MS SQL Server.

XMLWordPrintable

    • 1
    • 17

      NOTE: This suggestion is for JIRA Server. Using JIRA Cloud? See the corresponding suggestion.

      Problem Definition

      Customer has received following warnings when creating the tables in a MS SQL DB with the correct collation.

      Warning! The maximum key length is 900 bytes. The index 'idx_group_attr_dir_name_lval' has maximum length of 1029 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'uk_group_attr_name_lval' has maximum length of 1029 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'idx_mem_dir_parent_child' has maximum length of 1149 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'idx_user_attr_dir_name_lval' has maximum length of 1029 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'entpropindexdoc_module' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'licenserolegroup_index' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'managedconfigitem_id_type_idx' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.

      The problem is that the nvarchar type will take two times the actual length of data entered + 2 bytes (see MS KB) which will take 255*2+2=512 bytes for each column and if the index contains more than one nvarchar column then warning message is thrown.

      Suggested Solution

      In reality it's relatively rare to reach the limit in order for the index to fail. However it would be nice if we can limit the column length at first place.

            Assignee:
            Unassigned
            Reporter:
            Chen Mao (Inactive)
            Votes:
            8 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved: